Fetching data from mysql based on latitude and longitude

Question

I want to extract data based on user selected latitude and longitude from MySQL. For example i want to extract some listings in California if user requested for listings in California or else if user requested for listings in AlisoViejo i need to fetch listings in AlisoViejo. For this i am using latitude and longitude, could anyone please let me know how to achieve this

We don't anything about your database, your tables, your application, your desired end result. — Antony D'Andrea, May 16 '16 at 07:48
Please describe step-by-step how exactly you want the user to select a location and what result you expect to be displayed. — Zero, May 16 '16 at 08:13
I have some thousands of listings stored in my database. When user selects a state i need to bring all the listings of that state or else if user selects city i need to bring all the listings of that city or else if user selects country i need to bring listings of that country. The only way to interact is latitude / longitude — SuryaTeja Mahidhara, May 16 '16 at 10:28

score 0 · Answer 1 · edited May 23 '17 at 11:44

Keep the data of the latitude and longitude for every record in the listing, either directly or indirectly (using a relation table). Then use something like this (taken from MYSQL Geo Search having distance performance) :

SELECT a .* , 
    round( sqrt( 
        (POW(a.latitude -'53.410778', 2)* 68.1 * 68.1) + 
        (POW(a.latitude -'-2.97784', 2) * 53.1 * 53.1) 
     )) AS distance
FROM adverts a
     WHERE a.type_id = 3
     HAVING distance < 25
     LIMIT 0 , 30

or use MYSQL's geospatial features:

https://dev.mysql.com/doc/refman/5.7/en/spatial-analysis-functions.html

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

Fetching data from mysql based on latitude and longitude

1 Answers1