PostgreSQL中计算司机总行驶距离的SQL实现求助

要计算司机的总行驶距离，核心是关联司机、运单及对应的取/送货行程，再计算每个运单的取货到送货点的球面距离，最后按司机汇总。以下提供三种可行方案，适配不同环境：

方案1：使用PostGIS扩展（推荐，精度最高） #

PostGIS是PostgreSQL的空间扩展，提供专业的地理计算函数，适合处理GPS经纬度的距离计算。

首先确保PostGIS已安装启用，然后执行SQL：

-- 配对每个运单的取货、送货坐标
WITH shipment_coords AS (
    SELECT
        s.driver_id,
        ST_MakePoint(st_pickup.long, st_pickup.lat) AS pickup_point,
        ST_MakePoint(st_dropoff.long, st_dropoff.lat) AS dropoff_point
    FROM Shipment s
    JOIN ShipmentTrip st_pickup ON s.id = st_pickup.shipment_id AND st_pickup.type = 'pickup'
    JOIN ShipmentTrip st_dropoff ON s.id = st_dropoff.shipment_id AND st_dropoff.type = 'dropoff'
)
SELECT
    d.id AS driver_id,
    -- 计算总距离（单位：米，转公里可除以1000）
    SUM(ST_DistanceSphere(pickup_point, dropoff_point)) AS total_distance_meters
FROM Driver d
LEFT JOIN shipment_coords sc ON d.id = sc.driver_id
GROUP BY d.id
ORDER BY total_distance_meters DESC;

• 逻辑：先通过CTE关联运单与对应的取/送货行程，生成每个运单的起止坐标点；再用ST_DistanceSphere计算两点间的球面距离（基于WGS84坐标系），最后按司机ID汇总总距离。
• LEFT JOIN保证无运单的司机也会被纳入结果，其总距离显示为NULL，如需转为0可嵌套COALESCE()函数。

方案2：使用PostgreSQL自带earthdistance扩展 #

如果无法使用PostGIS，可启用PostgreSQL原生的earthdistance扩展完成计算：

先启用依赖扩展：

CREATE EXTENSION IF NOT EXISTS earthdistance;
CREATE EXTENSION IF NOT EXISTS cube;

然后执行计算SQL：

WITH shipment_coords AS (
    SELECT
        s.driver_id,
        st_pickup.lat AS pickup_lat,
        st_pickup.long AS pickup_long,
        st_dropoff.lat AS dropoff_lat,
        st_dropoff.long AS dropoff_long
    FROM Shipment s
    JOIN ShipmentTrip st_pickup ON s.id = st_pickup.shipment_id AND st_pickup.type = 'pickup'
    JOIN ShipmentTrip st_dropoff ON s.id = st_dropoff.shipment_id AND st_dropoff.type = 'dropoff'
)
SELECT
    d.id AS driver_id,
    -- earth_distance返回英里，转为米需乘以1609.34
    SUM(earth_distance(ll_to_earth(pickup_lat, pickup_long), ll_to_earth(dropoff_lat, dropoff_long))) * 1609.34 AS total_distance_meters
FROM Driver d
LEFT JOIN shipment_coords sc ON d.id = sc.driver_id
GROUP BY d.id
ORDER BY total_distance_meters DESC;

• 逻辑：ll_to_earth将经纬度转换为地球表面的空间点，earth_distance计算两点间的球面距离（英里单位），最后转换为米并汇总。

方案3：手动实现Haversine公式（无需任何扩展） #

如果无法启用任何扩展，可通过Haversine公式手动计算球面距离：

WITH shipment_coords AS (
    SELECT
        s.driver_id,
        RADIANS(st_pickup.lat) AS pickup_lat_rad,
        RADIANS(st_pickup.long) AS pickup_long_rad,
        RADIANS(st_dropoff.lat) AS dropoff_lat_rad,
        RADIANS(st_dropoff.long) AS dropoff_long_rad
    FROM Shipment s
    JOIN ShipmentTrip st_pickup ON s.id = st_pickup.shipment_id AND st_pickup.type = 'pickup'
    JOIN ShipmentTrip st_dropoff ON s.id = st_dropoff.shipment_id AND st_dropoff.type = 'dropoff'
),
distance_calculations AS (
    SELECT
        driver_id,
        -- Haversine公式计算距离（地球半径取6371000米）
        2 * 6371000 * ASIN(
            SQRT(
                SIN((dropoff_lat_rad - pickup_lat_rad)/2)^2 +
                COS(pickup_lat_rad) * COS(dropoff_lat_rad) *
                SIN((dropoff_long_rad - pickup_long_rad)/2)^2
            )
        ) AS trip_distance
    FROM shipment_coords
)
SELECT
    d.id AS driver_id,
    COALESCE(SUM(trip_distance), 0) AS total_distance_meters
FROM Driver d
LEFT JOIN distance_calculations dc ON d.id = dc.driver_id
GROUP BY d.id
ORDER BY total_distance_meters DESC;

• 逻辑：先将经纬度转换为弧度（三角函数计算要求），再通过Haversine公式计算每个运单的行驶距离，最后汇总司机的总距离。COALESCE()将无运单司机的总距离设为0。

内容的提问来源于stack exchange，提问作者Falyoun