CODEWITHSUNDEEP
mysqlgeolocation
Genadinik
Genadinik

Reputation: 18649

Strange result from querying within distance from Latitude/Longitude

I got help here with a query to find records withing a certain radius of a point (lat/lng). The query works great for finding records that are not too close to the actual center. So it can find things within 400 miles, but not 20.

I am using MySQL. Here is the query:

select id , lat , lng , ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) *
cos( radians( lng ) - radians(-122.517349) ) + sin( radians(37.780182) ) * sin( radians( lat ) ) ) ) AS distance FROM my_table HAVING distance < 1000 ORDER BY distance LIMIT 0 , 50

And here is a small data set:

    lat           lng
|  0.000000 |    0.000000 |
| 37.223465 | -122.090363 |
| 39.320980 | -111.093735 |
| 38.031715 |  -84.495132 |
| 37.787144 | -122.493263 |
| 52.975361 |   -1.458620 |
| 40.848557 | -111.906883 |
| 40.572498 | -111.859718 |

And my center starting point is San Francisco, which is lat/lng of 37.787144 , -122.493263

And so none of the nearby lat/lng can be found.

Thank you, Alex

Upvotes: 0

Views: 344

Answers (3)

Genadinik
Genadinik

Reputation: 18649

after working with this, I made a tutorial and a tool that converts address to lat/lng. Maybe it will be helpful: http://www.comehike.com/utils/address_to_geolocation.php

Upvotes: 0

Peter Taylor
Peter Taylor

Reputation: 5086

It would probably help to be consistent on whether the latitude is 37 or 37.780182.

What's the theoretical basis behind acos(cos(LAT0) * cos(LAT1) * cos(LNG1 - LNG0) + sin(LAT0) * sin(LAT1)))? If you're modelling the globe as a sphere then you want acos(dot product of the 3D embedding) which is acos(cos(LAT0) * cos(LAT1) + sin(LAT0) * sin(LAT1) * (cos(LNG0) * cos(LNG1) + sin(LNG0) * sin(LNG1))).

Upvotes: 0

Jim Lewis
Jim Lewis

Reputation: 45135

You have some hardcoded values in your select statement (37, -122.517349, 37.780182) that don't correspond to your center point.

Upvotes: 1

Related Questions