Geek asks and answers

SQL Select Radius Search Based on Longitude Latitude

I have 2 tables:

Events:
 - id
 - name
 - place

Places:
 - id
 - name
 - lat
 - lng

I would like to get all the events that are in 10KM radius(based on location latand lng) from current latand lng. How can i do this?

Thank!

+5
source share
3 answers

If you want to use the extension, the geospatial extension in MySQL 5.6 and later is intended to solve just such a problem. You will need to create a spatial index in the places table:

ALTER TABLE places ADD SPATIAL INDEX lat, lng

select name from places
order by st_distance(point(@lng, @lat), point(lng, lat))
limit 10

The actual finding of the actual distances is a bit calculation. The following post outlines some of the methods you can try: http://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/

. http://www.percona.com/blog/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

+3

, http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

, lat/lng (lat lng ), , . , , . , , .

+1

,

SELECT 
    *
FROM 
    `locator`
WHERE
    SQRT(POW(X(`center`) - 49.843317 , 2) + POW(Y(`center`) - 24.026642, 2)) * 100 < `radius`

http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql

0

More articles:


All Articles