How to select longitude, latitude around specifc location from mysql?

Question

I'm trying to get the longitude and latitude around specific location. Actually I have some records (longitude and latitude) in mysql table. Now i want tofetch those longitude and latitude from table which should have around 500 meter of specific longitude. Please view the code, i hope you will understand my question.

Mysql Location Table

-------------------------------------
id  |     longitude    |    latitude
-------------------------------------
1  |   25.058036      |    55.133467
-------------------------------------
2  |   25.056986      |    55.135755
-------------------------------------
3  |   25.059163       |   55.135616
-------------------------------------
4  |   25.056860       |  55.132515
-------------------------------------
5  |   25.055683       |   55.134792

I want to get those longitude, latitude which distance should have around 500 meters of specific (25.057550, 55.134729).

$user_long= $_REQUEST['user_long']; // 25.057550
$user_lat= $_REQUEST['user_lat']; // 55.134729

Select * from location where .... (fetch record around 500 meters)

I have searched about it and find some well expnained haversine formula answer and Google Map With Php/Mysql but i did not got how can i develop this kind of functionality. I know this question may be stupid for someone because i'm not adding my code, but trust me i don't know how can i do it and from where i should start functionality. I will appreciate if someone guide me regarding this functionality.

Thank You

possible duplicate of [Get results from mysql based on latitude longitude](http://stackoverflow.com/questions/3528754/get-results-from-mysql-based-on-latitude-longitude) — geocodezip, Apr 27 '16 at 13:31
possible duplicate of [distance calculations in mysql queries](http://stackoverflow.com/questions/3986556/distance-calculations-in-mysql-queries) — geocodezip, Apr 27 '16 at 13:32
possible duplicate of [select within 20 kilometers based on latitude/longitude](http://stackoverflow.com/questions/6919661/select-within-20-kilometers-based-on-latitude-longitude) — geocodezip, Apr 27 '16 at 13:33

Web Artisan · Answer 1 · 2016-04-27T07:52:49.373

1

Use this query

$query = "SELECT *,ROUND(1.609344*(((acos(sin((" . $latitude .    "*pi()/180)) * 
        sin((`Latitude`*pi()/180))+cos((" . $latitude . "*pi()/180)) * 
        cos((`Latitude`*pi()/180)) * cos(((" . $longitude . "- `Longitude`)* 
        pi()/180))))*180/pi())*60*1.1515
    ),2) as distance 
    FROM `table`  HAVING distance<=" . MAX_DISTANCE . " ";

where $latitude is user latitude and $longitude is user longitude and MAX_DISTANCE is maximum distance you want to get that result in km.

edited Apr 27 '16 at 07:52

answered Apr 27 '16 at 06:55

Web Artisan

1,870
3
23
33

Is your query relevant to the question content? – Apr 27 '16 at 07:13
@BikashP Thanks for sharing knowledge, i'm not using store_type column in my table. Can you explain what is purpose of this table that i can check your query. Thanks a lot – ayaz khan Apr 27 '16 at 07:44
updated my query. check it.I just removed where clause. – Web Artisan Apr 27 '16 at 07:53

score 1 · Accepted Answer · answered Apr 27 '16 at 06:56

1

SELECT *,3956*2*ASIN(SQRT(POWER(SIN((19.286558 - latitude)*pi()/180/2),2)+COS(19.286558 * pi()/180)
*COS(latitude * pi()/180)*POWER(SIN((-99.612494 -longitude)* pi()/180/2),2)))
as distance FROM table  having distance < 10 ORDER BY distance;

This will give you records within 10 Km range. modify query for 500 meters in having clause.

answered Apr 27 '16 at 06:56

Priyanshu

885
6
12

Thank you so much its working for me... I appreciate you – ayaz khan Apr 27 '16 at 07:49

score 0 · Answer 3 · answered Apr 27 '16 at 06:57

Try this

SELECT *,
(6371*acos(cos(radians(" . $user_lat ."))*cos(radians(`latitude`))*cos(radians(`longitude`)-radians(" . $user_long . "))+sin(radians(" . $user_lat . "))*sin(radians(`latitude`)))) AS distance
FROM `location` WHERE `distance` < '0.5' ORDER BY `distance` ASC

where 0.5 is in km

score 0 · Answer 4 · answered Apr 27 '16 at 11:49

If the performance of a custom implemented haversine formula will be too slow for you, and you don't mind using MySQL 5.7.6+, then you can leverage from build-in ST_Distance_Sphere function, which can be ±20times faster then custom implemented procedures for calculating haversine diestance. You can check the benchmarks and exmaples in this blogpost. Also, if you don't mind storing your locations as Point geometry objects, you can also leverage from spatial indexes. Also you can first pre-filter locations based on bounding box (example in the end of this blogpost) which could also speed up the query.

How to select longitude, latitude around specifc location from mysql?

Mysql Location Table

4 Answers4

Linked