1

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

Abdulrahman Falyoun
  • 3,676
  • 3
  • 16
  • 43
  • Please provide sample data as formatted text. Or better yet, the table create and insert statements. Also, what is the expected outcome? Is it distance per shipment_id? – Isolated Oct 06 '22 at 15:44
  • Does this answer your question? [Calculating distance between two points (Latitude, Longitude)](https://stackoverflow.com/questions/13026675/calculating-distance-between-two-points-latitude-longitude) – Meyssam Toluie Oct 06 '22 at 16:08
  • No it does not cuz the source in the same table – Abdulrahman Falyoun Oct 06 '22 at 16:20

2 Answers2

0

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

Sergio Lema
  • 1,491
  • 1
  • 14
  • 25
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

Pepe N O
  • 1,678
  • 1
  • 7
  • 11