CODEWITHSUNDEEP
phpmysqlgoogle-mapsgoogle-maps-api-3
ashutosh
ashutosh

Reputation: 1232

How to sort locations based on distance using google maps

I have a database in following schema:

- id INT (PRIMARY)
- address (TEXT)
- zipcode (VARCHAR)
- latitude
- longitude

What I want: is to have a function in PHP which, on passing a zipcode as a parameter, can sort out all the entries in above table based on nearest distance.

What I've tried: I know about Haversine formula which can be used in following way to fetch from SQL (as also described in this google documentation ):

SELECT *, 
( 3959 * acos( cos( radians('$lat') ) * 
cos( radians( latitude ) ) * 
cos( radians( longitude ) - 
radians('$lon') ) + 
sin( radians('$lat') ) * 
sin( radians( latitude ) ) ) ) 
AS distance FROM my_table HAVING distance < '$miles' ORDER BY distance ASC

$lat, $lon, $miles can be replaced with actual latitude, longitude and miles to search within, respectively

Results: Using above formula output me result which seemed bit off from what I got from google maps. For ex- for zipcode 00601 and 00631, The distance this query provided was 5.432822479090713 miles, while using google map api, the distance for same was 10.00408 miles.

http://maps.googleapis.com/maps/api/distancematrix/json?origins=00601&destinations=00631&mode=DRIVING&sensor=false

While I can understand that earth being not a perfect sphere as well as the distance I am getting is only true in geometry sense, while google accounts for terrains, routes etc., this will not fit my use-case where many locations are nearby (within 2 mile radius). I know a similar site which implements this is better way, I just wanted to know what possible solution they might've used?

EDIT- The reason I have opted for google maps to sort the location is basically due to its precise distance calculations between location for small distances

Upvotes: 3

Views: 3503

Answers (1)

ADoorMarkedPirate
ADoorMarkedPirate

Reputation: 400

I have a solution calculating distance in KM using the Haversine Formula, which I convert to miles by multiplying 0.621.

SELECT *, ( 6371 * 2 * atan2(SQRT(POW(sin((radians(geo_lat1)-radians(geo_lat2))/2), 2) + cos(radians(geo_lat2)) * cos(radians(geo_lat1)) * POW(sin((radians(geo_lng1)-radians(geo_lng2))/2),2))
                        ,SQRT(1 - POW(sin((radians(geo_lat1)-radians(geo_lat2))/2), 2) + cos(radians(geo_lat2)) * cos(radians(geo_lat1)) * POW(sin((radians(geo_lng1)-radians(geo_lng1))/2),2)))*0.621)
                        as distance FROM my_table HAVING distance < '$miles' ORDER BY distance ASC

Upvotes: 0

Related Questions