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.
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)' );
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:
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.
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.
I wanted to see if these results lined up with others on the internet, which fortunately they do.
I am surprised the results are so different and awaiting clarifcation.
- Stack Overflow - Convert calculated latitude and longitude distance to metres/km
- Stack Overflow - Insert POINT into postgres database
First published: 16th August 2018