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?