Question 114328 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Total distance for points in same table sql

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

Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search