-
-
Save carlzulauf/1724506 to your computer and use it in GitHub Desktop.
| -- Haversine Formula based geodistance in miles (constant is diameter of Earth in miles) | |
| -- Based on a similar PostgreSQL function found here: https://gist.github.com/831833 | |
| -- Updated to use distance formulas found here: http://www.codecodex.com/wiki/Calculate_distance_between_two_points_on_a_globe | |
| CREATE OR REPLACE FUNCTION public.geodistance(alat double precision, alng double precision, blat double precision, blng double precision) | |
| RETURNS double precision AS | |
| $BODY$ | |
| SELECT asin( | |
| sqrt( | |
| sin(radians($3-$1)/2)^2 + | |
| sin(radians($4-$2)/2)^2 * | |
| cos(radians($1)) * | |
| cos(radians($3)) | |
| ) | |
| ) * 7926.3352 AS distance; | |
| $BODY$ | |
| LANGUAGE sql IMMUTABLE | |
| COST 100; |
Thank you , that is awesome
Thank you for posting it -- really helps. FYI, https://gist.github.com/831833 link in the description is broken.
Brilliant. Thanks.
Why 7926.3352? How do I get the result in kms?
This, for example, uses 3960 and 6371!?
One question I have a problem, in my application I use this implementation to get a distance between two points:
6371 * acos(cos(radians(-22.9557306)) * cos(radians(addresses.latitude)) * cos(radians(addresses.longitude) - radians(-43.186176)) + sin(radians(-22.9557306)) * sin(radians(addresses.latitude))) as distance
But when two points are equal, this value is returned NaN
With your implementation worked for me, however in case the same points return to number like this 9.05124604553969e-13, you know that happening?
@FruitAndAShape Late to the party but Haversine requires a final multiplication by 2. 7926 is twice the radius of the earth in miles. You could switch that to 2 * 6371 = 12742 for kilometers.
@FruitAndAShape Late to the party but Haversine requires a final multiplication by 2. 7926 is twice the radius of the earth in miles. You could switch that to 2 * 6371 = 12742 for kilometers.
Thanks :)
This is great! Thanks for sharing!