views:

664

answers:

3

I'm aiming to create a feature in my latest project preferably using PHP. When each user signs up they are going to input their postcode. Then hopefully I will be converting this to lat/long using Open Street Map.

Anyway, I want to be able to find out other users located near the current user. I have seen a lot of people using the Haversine formula, however this would mean that the user queried every other user's details to work out the distance. I could cache this but its soon going to become outdated as new users sign up.

What sort of effect would running the following query have on my system?

sql = "SELECT zipcode, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";

Thats pulled from somone's blog.

I dont have any figures for the signup rate or the amount of users as it's still in development.

I would apriciate any feedback or other methods that I could use to find matching users within a specific radius.

Thanks
Jamie

+3  A: 

There are GIS and Spatial Extensions to mySql in version 4.1, see here. From the description you will find, that it is used for problems like you have here:

A GIS (geographic information system) stores and looks up objects which have one or more spatial attributes, such as size and position, and is used to process such objects. A simple example would be a system that stores addresses in a town using geographic coordinates. If this rather static data was then combined with other information, such as the location of a taxi-cab, then this data could be used to find the closest cab to a certain location.

It adds several things to MySql like:

  • Spacial keys and the POINT type:

    CREATE TABLE address ( address CHAR(80) NOT NULL, address_loc POINT NOT NULL, PRIMARY KEY(address), SPATIAL KEY(address_loc) );

  • Conversion routines

    INSERT INTO address VALUES('Foobar street 12', GeomFromText('POINT(2671 2500)'));

  • GIS calculation functions

    SELECT c.cabdriver, ROUND(GLength(LineStringFromWKB(LineString(AsBinary(c.cab_loc), AsBinary(a.address_loc))))) AS distance FROM cab c, address a ORDER BY distance ASC LIMIT 1;

(Examples taken from link above)

Ralph Rickenbach
Except that coordinate systems work on a 2D basis. I looked up GLength and there's no indication whatsoever that it takes into account spherical geometry or does anything other than a Pythagorean theorem calculation.
Jason S
This looks great! I'm going to have a read over it tonight after work, looks like what I'm looking for!
Jamie
+1  A: 

Admittedly this is Javascript not PHP, but it would be trivial to convert I'd imagine.

It calculates the distance between two points, accounting for the curvature of the Earth. Is used it in a logistics app a while back before replacing it with code that does it using a proper road route.

It might be of use to you....

<script type="text/javascript">
function getDistance(lat1,lng1,lat2,lng2)
 {
  p1 = new VELatLong(lat1,lng1);
  p2 = new VELatLong(lat2,lng2);
  miles = true;
  p1.Latitude= latLonToRadians(p1.Latitude);
  p1.Longitude= latLonToRadians(p1.Longitude);
  p2.Latitude= latLonToRadians(p2.Latitude);
  p2.Longitude= latLonToRadians(p2.Longitude);
  var R = 6371; // earth's mean radius in km
  var dLat  = p2.Latitude- p1.Latitude;
  var dLong = p2.Longitude- p1.Longitude;
  var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
  Math.cos(p1.Latitude) * Math.cos(p2.Latitude) * Math.sin(dLong/2) * 
Math.sin(dLong/2);
  var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
  var disKm = R * c;
  var disMiles = disKm * 0.6214;
  alert (miles ? disMiles : disKm);
 }
 //  convert lat/long in degrees to radians
 function latLonToRadians( point )
 {
  return point * Math.PI / 180;
 }
</script>

Oh, and VELatLong objects come from the Virtual Earth API (http://msdn.microsoft.com/en-us/library/bb412519.aspx), but are basically a glorified struct, so you should be able to find a suitable replacement

Rob Cowell
OP is not asking about PHP (tag should probably be removed) but rather about SQL queries.
Jason S
Actually, he does say that he wants to implement this project in PHP and that he's interested in any methods to match users in a specified radius.
Andre Miller
Thank's for the input. Not exactly what I was looking for, but it does give me a bit of insight of the maths required!
Jamie
+1  A: 

The problem can be greatly simplified if you are willing to loosen the definition of "within a certain radius" to not specifically be a circle. If you simplify to a "square", you can find all location within the "radius" with 2 simple "between" clauses (one for lat one for long). eg:

SELECT * FROM location WHERE
  lat BETWEEN (my_lat - radius) AND (my_lat + radius)
  AND long BETWEEN (my_long - radius) AND (my_long + radius);

Of course, this could be used to select a subset of your locations before using a more accurate method to calculate the actual distance to them.

Brenton Alker
true... although technically this discriminates against people living near the international date line or the north/south poles :-)
Jason S
True, which is why "square" is in quotes :)
Brenton Alker
This is also great! I have never worked with lat-long before. So theoretically, I could perform my original query on a subset of data returned from this. Good thinking!
Jamie