Selecting all coordinates withing a distance
-
This little gem is used to construct the where condition for an SQL query that selects all coordinates within a certain distance from a point (in a PHP app). The coordinates are stored in the db in a single field, where the lat and lon are separated by a comma. The SQL is splitting on that and running a whole bunch of calculations. The table can be millions of entries big. I kid you not. $distance = "ROUND(((ACOS( SIN({$latitude} * PI()/180 ) * SIN(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) + COS({$latitude} * PI()/180 ) * COS(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) * COS(({$longitude} - SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',-1)) * PI()/180))*180/PI())*60*1.1515),6)"; $where = "{$distance} <= " . $dbs->addQuotes( "5" );
-
This little gem is used to construct the where condition for an SQL query that selects all coordinates within a certain distance from a point (in a PHP app). The coordinates are stored in the db in a single field, where the lat and lon are separated by a comma. The SQL is splitting on that and running a whole bunch of calculations. The table can be millions of entries big. I kid you not. $distance = "ROUND(((ACOS( SIN({$latitude} * PI()/180 ) * SIN(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) + COS({$latitude} * PI()/180 ) * COS(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) * COS(({$longitude} - SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',-1)) * PI()/180))*180/PI())*60*1.1515),6)"; $where = "{$distance} <= " . $dbs->addQuotes( "5" );
It's probably immaterial, but what does the constant 1.1515 represent in the equation? :confused: The great thing about generated SQL is that you never have to maintain the SQL. The horror about generated SQL is that you have to maintain the generator. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
It's probably immaterial, but what does the constant 1.1515 represent in the equation? :confused: The great thing about generated SQL is that you never have to maintain the SQL. The horror about generated SQL is that you have to maintain the generator. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
It's probably immaterial, but what does the constant 1.1515 represent in the equation? :confused: The great thing about generated SQL is that you never have to maintain the SQL. The horror about generated SQL is that you have to maintain the generator. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
Chris Meech wrote: It's probably immaterial, but what does the constant 1.1515 represent in the equation? Confused Its the distance of one nautical mile.
-
Chris Meech wrote: It's probably immaterial, but what does the constant 1.1515 represent in the equation? Confused Its the distance of one nautical mile.
Thanks. It was a constant that didn't ring any bells for me. Now that I know it's for nautical miles, it will be a ship's bell that should go off next time. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
Chris Meech wrote: It's probably immaterial, but what does the constant 1.1515 represent in the equation? Confused Its the distance of one nautical mile.
elchupathingy wrote:
Its the distance of one nautical mile
In case anyone is wondering about the relevance of a nautical mile, it's because a nautical mile is defined as one minute of arc around the Earth's circumference. So this calculation is converting minutes of arc into miles. According to a quick search around the interweb, a nautical mile is actually defined as 1.1508 miles, not 1.1515. So for large distances, this calculation is going to be slightly off. If you sail half way round the world, you'll be 8 miles away from where you think you are with this calculation.
-
It's probably immaterial, but what does the constant 1.1515 represent in the equation? :confused: The great thing about generated SQL is that you never have to maintain the SQL. The horror about generated SQL is that you have to maintain the generator. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
It's actually not 1.1515, but 60*1.1515, i.e. 69.09. One degree corresponds to 69.09 miles (non-nautical). I found that out by doing the maths behind that formula. He converts lots of degree-coordinates into radian-coordinates by that *PI/180, then calculates the shortest distance along a great circle in radians (that's the spherical geometry equivalent of the Pythagoras formula for orthogonal triangles), converts that back to degrees along that circle, and then transforms that to miles; for kilometers use 111.1. Nonetheless, I do not understand why he uses 60*1.1515 instead of 69.09.
-
This little gem is used to construct the where condition for an SQL query that selects all coordinates within a certain distance from a point (in a PHP app). The coordinates are stored in the db in a single field, where the lat and lon are separated by a comma. The SQL is splitting on that and running a whole bunch of calculations. The table can be millions of entries big. I kid you not. $distance = "ROUND(((ACOS( SIN({$latitude} * PI()/180 ) * SIN(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) + COS({$latitude} * PI()/180 ) * COS(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) * COS(({$longitude} - SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',-1)) * PI()/180))*180/PI())*60*1.1515),6)"; $where = "{$distance} <= " . $dbs->addQuotes( "5" );
A full circle means 360 degrees or 2*PI radians. Consequently, I would suggest the original programmer replace all
PI()/180by2*PI()/360. That makes things much easier to understand! -
This little gem is used to construct the where condition for an SQL query that selects all coordinates within a certain distance from a point (in a PHP app). The coordinates are stored in the db in a single field, where the lat and lon are separated by a comma. The SQL is splitting on that and running a whole bunch of calculations. The table can be millions of entries big. I kid you not. $distance = "ROUND(((ACOS( SIN({$latitude} * PI()/180 ) * SIN(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) + COS({$latitude} * PI()/180 ) * COS(SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',1) * PI()/180 ) * COS(({$longitude} - SUBSTRING_INDEX({$tablename}.{$fieldname}, ',',-1)) * PI()/180))*180/PI())*60*1.1515),6)"; $where = "{$distance} <= " . $dbs->addQuotes( "5" );
The ugliest part is database structure. The coordinates are stored in the db in a single field, where the lat and lon are separated by a comma I would TALK to the programmer, but I would FIRE the database architect immediately. I did similar thing (if distances you deal with are not that big, you can do it without acos and sin, and even without SQRT in SQL code if you calculate distance*distance before you run the query). Yet it works horribly slow. I made it run very fast by indexing lat and long and finding all locations within the lat,long square, then cutting off everything outside of a circle... Now you see why I suggest to fire the database architect. You cannot use index on lat, long, so any code you write is doomed to work sluggish.
-
elchupathingy wrote:
Its the distance of one nautical mile
In case anyone is wondering about the relevance of a nautical mile, it's because a nautical mile is defined as one minute of arc around the Earth's circumference. So this calculation is converting minutes of arc into miles. According to a quick search around the interweb, a nautical mile is actually defined as 1.1508 miles, not 1.1515. So for large distances, this calculation is going to be slightly off. If you sail half way round the world, you'll be 8 miles away from where you think you are with this calculation.
While we're all dragging out our ocean navigation knowledge...
David Skelly wrote:
a nautical mile is defined as one minute of arc around the Earth's circumference
It's worth highlighting that this works at the equator and along meridians, but is not accurate if you are traveling east/west along say 45 degrees north lattitude. Another reason this calculation probably isn't going to be accurate is that the shortest distance between two points is an arc. The further you get from the equator the more curve there is in that arc. Of course if they are just trying to locate stores within 50 miles of your location, their math is probably good enough...