Archive

Posts Tagged ‘geolocation’

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.

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...