Using PostGIS To Calculate Distance Between Two Points

One of the most common things you will want to do with geospatial data is calculate the distance between two points. Here is how you can do so with PostGIS.

Steps

Create our database.

CREATE DATABASE postgis_test
ENCODING 'UTF8' 
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

Now connect to that database and add the postgis extension.

\connect postgis_test
CREATE EXTENSION postgis;

Create our points table for storing points we are interested in.

CREATE TABLE pointsTable (
    id serial NOT NULL,
    name varchar(255) NOT NULL,
    location Point NOT NULL,
    PRIMARY KEY (id)
);

543797.01645757 Now look up some points. I used Google to find Woking and Edinburgh.

INSERT INTO pointsTable (name, location) VALUES 
( 'Woking', '(-0.56, 51.3168)' ),
( 'Edinburgh', '(-3.1883, 55.9533)' );

Points are in x,y format which means it is longitude, then lattitude rather than the order of latitude, longitude you get when fetching coordinates from Google, or when plugging in points on various sites.

Now if we were to select the data, we would use:

SELECT * FROM pointsTable;

 id |   name    |     location      
----+-----------+-------------------
  1 | Woking    | (-0.56,51.3168)
  2 | Edinburgh | (-3.1883,55.9533)
(2 rows)

Now to select distance, we can either make use of the ST_Distance_Sphere which is always uses the earth as a perfect sphere, which is quite fast, but slightly less accurate than using ST_DistanceSpheroid. I will list both:

ST_Distance_Sphere

SELECT ST_Distance_Sphere(geometry(a.location), geometry(b.location))
FROM pointsTable a, pointsTable b
WHERE a.id=1 AND b.id=2;

Result: 543,797.01645757 meters.

ST_Distance_Spheroid

SELECT ST_Distance_Spheroid(geometry(a.location), geometry(b.location), 'SPHEROID["WGS 84",6378137,298.257223563]')
FROM pointsTable a, pointsTable b
WHERE a.id=1 AND b.id=2;

Result: 544,430.941199621 meters.

Checking

I wanted to see if these results lined up with others on the internet, which fortunately they do.

• source 1 - 338 miles (543.958 km)
• source 2 - 544.410km
• source 3 - 543.8km

I am surprised the results are so different and awaiting clarifcation.

References

• Stack Overflow - Convert calculated latitude and longitude distance to metres/km
• Stack Overflow - Insert POINT into postgres database