Postgres earthdistance vs PostGIS
April 24th, 2012
1 comment
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.
Categories: IT
earthdistance, geolocation, geometry, GPS, gps coordinates, gps points, postgis, postgresql
earthdistance, geolocation, geometry, GPS, gps coordinates, gps points, postgis, postgresql
(No Ratings Yet)
Loading...
