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;
Copy to clipboard

Now connect to that database and add the postgis extension.

\connect postgis_test
CREATE EXTENSION postgis;
Copy to clipboard

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)
);
Copy to clipboard

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)' );
Copy to clipboard

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;
Copy to clipboard

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

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;
Copy to clipboard

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;
Copy to clipboard

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