Total distance for points in same table sql-CodePudding

I do have the following three tables within postgres

ShipmentTrip

id,shipment_id, type,status,lat,long
1, 1, pickup, whatever, 25, 75
2, 1, dropoff, whatever, 27, 76
3, 2, pickup, whatever, 25, 75
4, 2, dropoff, whatever, 27, 76

Shipment
id,...,driver_id

Driver
id

I am trying to calculate the full distance a driver made

I tried different ways but I am not able to solve it

Tried cross join and subqueries and many different approaches but still no result

CodePudding user response：

You have the PostGIS extension for PostgreSQL to calculate distances from latitude and longitude values.

CodePudding user response：

You should use PostGIS functions for that purposes, here an example with your shipment_id 1:

SELECT ST_Distance(
    ST_Transform('SRID=4326;POINT(75 25)'::geometry, 3857),
    ST_Transform('SRID=4326;POINT(76 27)'::geometry, 3857));

Based on your sample, query should look like

select driver_id, shipment_id, points[1] p1, points[2] p2, 
    ST_Distance(
    ST_Transform(('SRID=4326;POINT('||points[1]||')')::geometry, 3857),
    ST_Transform(('SRID=4326;POINT('||points[2]||')')::geometry, 3857)) distance
from (select s.driver_id, st.shipment_id, array_agg(st.long||' '||st.lat::text) points 
    from "ShipmentTrip" st
    join "Shipment" s on st.shipment_id = s.id
    join "Driver" d on d.id = s.driver_id group by st.shipment_id, s.driver_id) trip;

Coordinates in lat long system of reference SRID (units in degrees) are transformed to a metric system (3857) in order to obtain a distance in meters.

If you don't have PostGIS extension installed in your database

create extension postgis;

However it's a straight linear distance between two points, not a distance following roads. Docs for distance https://postgis.net/docs/ST_Distance.html and for points https://postgis.net/docs/ST_MakePoint.html

Fiddle https://dbfiddle.uk/7XFHZWLA