I needed to calculate the distance between to gps coordinates. I have found couple of methods.
1. Postgres earthdistance
you need to install the contrib package for postgres. Import the cube.sql
and the earthdistance.sql
. More about this at postgresql docs
psql -d yourdatabase -f cube.sql psql -d yourdatabase -f earthdistance.sql |
To test:
SELECT (point (47.5047554664,19.0479136054) <@> point (47.5049898995,19.0469509363)) AS distance; distance -------------------- 0.0682533383274021 |
This is in statute miles. Simply multiple by 1.609344 to have it in km.
2. postgis 2.0
After installing and importing the required postgis.sql
and spatial_ref_sys.sql
you will have a lot of nice support for geographic object in your database. read more on the PostGIS site. ST_distance_sphere
is considered the most accurate function to calculate the distance between 2 gps points result is in meters.
Here is the verdict:
SELECT round( CAST( ST_Distance_Spheroid( ST_GeomFromText('POINT(47.5047554664 19.0479136054)',4326), ST_GeomFromText('POINT(47.5049898995 19.0469509363)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]') AS NUMERIC),4) AS spheroid, round( CAST( ST_distance_sphere( ST_GeomFromText('POINT(47.5047554664 19.0479136054)',4326), ST_GeomFromText('POINT(47.5049898995 19.0469509363)',4326) ) AS NUMERIC),4) AS sphere, round( CAST( ((point (47.5047554664,19.0479136054) <@> point (47.5049898995,19.0469509363)) * 1.609344) AS NUMERIC) * 1000,4) AS earthdistance; spheroid | sphere | earthdistance ----------+----------+--------------- 109.3804 | 109.8435 | 109.8431 |
there is not much difference, now it will be question of performance.
Thanks, this is a very helpful example of the difference in precision. 109.8435 vs. 109.8431 miles looks to be a difference of a couple feet, and that level of precision is rarely necessary.