Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table "ShipmentTrip" (
   id int, shipment_id int, type varchar, status varchar, lat float8, long float8);
   
create table "Shipment" (
  id int, driver_id int
  );
  
create table "Driver" (
 id int );

CREATE TABLE

CREATE TABLE

CREATE TABLE

insert into "Driver" values (1);

insert into "Shipment" values (1,1);
insert into "Shipment" values (2,1);

insert into "ShipmentTrip" values (1,1,'pickup', 'whatever', 25, 75); 
insert into "ShipmentTrip" values (2,1,'dropoff', 'whatever', 27, 76);
insert into "ShipmentTrip" values (3,2,'pickup', 'whatever', 25, 75);
insert into "ShipmentTrip" values (4,2,'dropoff', 'whatever', 27, 76);

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

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;

driver_id
shipment_id
p1
p2
distance

1
1
75 25
76 27
271589.3174786751

1
2
75 25
76 27
271589.3174786751

SELECT 2