How to sort locations based on distance using google maps

Question

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

through googleapis it is driving mode but through Haversine formula it is shortest route which does not follow driving mode — Deep 3015

ochhii ochhii · Accepted Answer · 2017-05-29T14:03:49

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

How to sort locations based on distance using google maps

1 Answers