CODEWITHSUNDEEP
phpmysqlgoogle-maps
ayaz khan
ayaz khan

Reputation: 141

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

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

Upvotes: 0

Views: 749

Answers (4)

Matej P.
Matej P.

Reputation: 5383

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.

Upvotes: 0

Bikash
Bikash

Reputation: 1938

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.

Upvotes: 1

user3785693
user3785693

Reputation:

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

Upvotes: 0

Priyanshu
Priyanshu

Reputation: 881

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.

Upvotes: 1

Related Questions