Postgres -- Calculating average distance between two points (long/lat)

by Freedom   Last Updated October 20, 2018 23:06 PM

I am new to Postgres and really SQL in general, so please bear with me. For a project of mine, I'm trying to calculate the average distance between two points, given their longitude and latitude

I have one table, with the schema being a row has a p1.lat and p1.long as well as a p2.long and p2.lat

While searching online, I've encountered possibly using earth_distance to calculate the distance; however, I'm not too sure how to implement that?

My table name is location

Here's a guess (I know it will be wrong!):

WITH points AS (
SELECT 
startlat, startlong::point as p1
, endlat, endlong::point as p2

FROM location
)


SELECT AVG(*) FROM (SELECT ((p1 <@> p2) * 1609.34::double precision) 
AS distance FROM points) AS avg

Any ideas how I can refine this?

Tags : postgresql


Related Questions


Postgres gist slow index f_unaccent

Updated April 09, 2018 20:06 PM