13

Possible Duplicate:
Radius of multiple latitude/longitude points
distance calculations in mysql queries
select within 20 kilometers based on latitude/longitude

I have a MyISAM table, which the data is stored in. The data includes latitude and longitude for Google Maps. What I am trying to achieve is to select all objects within the radius of 40 kilometers of the sample latitude and longitude.

Thanks!

Community
  • 1
  • 1
  • There you go http://stackoverflow.com/questions/27928/how-do-i-calculate-distance-between-two-latitude-longitude-points – Sergey Benner Jan 13 '12 at 12:10
  • possible duplicate of [Radius of multiple latitude/longitude points](http://stackoverflow.com/questions/2716622/radius-of-multiple-latitude-longitude-points) (and about 25 others, see the "Related" sidebar. This one looks like an exact duplicate: http://stackoverflow.com/questions/6919661/select-within-20-kilometers-based-on-latitude-longitude ) – Piskvor left the building Jan 13 '12 at 12:12
  • 1
    try this class for it, it works fine: http://opengeodb.giswiki.org/wiki/GeoClass – Abadon Jan 13 '12 at 12:32

1 Answers1

43

I use this query to get all points within a $radius around $lat/$lng:

SELECT
    *,
    ( 6371 * acos( cos( radians({$lat}) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians({$lng}) ) + sin( radians({$lat}) ) * sin( radians( `lat` ) ) ) ) AS distance
FROM `positions`
HAVING distance <= {$radius}
ORDER BY distance ASC

6371 is the earth radius im km. And I have not invented it: http://code.google.com/intl/en/apis/maps/articles/phpsqlsearch.html https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql (edit: URL changed)

DerVO
  • 3,679
  • 1
  • 23
  • 27
  • if i have to use in miles then will i have to change (6371) ?? – Mustafa bw Jun 23 '16 at 06:24
  • The earth radius in miles is `3959` - just replace the number with that and your results will be in miles instead of km. – DerVO Jun 23 '16 at 07:22
  • @DerVO What is the format of the $lat and $long variables in the query? – Ashif Shereef Jul 13 '16 at 08:53
  • @AshifShereef both from -180 to +180, e.g. `$lat = -74.016617; $lng = 40.747933;` for an arbitrary point in the Hudson River. – DerVO Jul 13 '16 at 09:32
  • @DerVO I asked whether the unit is in degree, radian or in second – Ashif Shereef Jul 13 '16 at 11:20
  • 1
    @AshifShereef And - at least I think - I have answerd you, it is decimal degrees (https://en.wikipedia.org/wiki/Decimal_degrees) – DerVO Jul 13 '16 at 11:28
  • SELECT s.*, (((acos(sin(($latitude*pi()/180)) * sin((s.lat*pi()/180))+cos(($latitude*pi()/180)) * cos((s.lat*pi()/180)) * cos((($longitude - s.lang)*pi()/180))))*180/pi())*60*1.1515*1.609344) AS distance FROM (`tbl_area` as s) HAVING distance <= 40 ORDER BY `s`.id DESC This query I have written to get the list all the area wihin 40 km It works for me.You can change the table name and as per your requirement. – vaibhav kulkarni Nov 08 '16 at 06:40
  • Works good. I just want to add hint. Radius variable should be KM unit, If you get radius from leaflet circle be sure radius / 1000 for meter to km. – Dreamcatcher Jun 07 '22 at 08:39