-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pgrouting;

-- 验证
SELECT pgr_version();

# Ubuntu 安装
sudo apt install postgresql-16-pgrouting

# Docker 中使用含 pgRouting 的镜像
docker run -d --name postgis-routing \
  -e POSTGRES_PASSWORD=routing123 \
  -p 5432:5432 \
  camptocamp/pgrouting:16-3.4

-- 创建路网表
CREATE TABLE ways (
    gid SERIAL PRIMARY KEY,
    name TEXT,
    road_class TEXT,
    maxspeed INTEGER,
    oneway INTEGER DEFAULT 0,  -- 0=双向, 1=正向, -1=反向
    source INTEGER,
    target INTEGER,
    cost DOUBLE PRECISION,
    reverse_cost DOUBLE PRECISION,
    geom GEOMETRY(LineString, 4326)
);

-- 插入道路数据
INSERT INTO ways (name, road_class, maxspeed, oneway, geom) VALUES
('长安街', '主干道', 60, 0,
 ST_GeomFromText('LINESTRING(116.3470 39.9042, 116.3745 39.9042, 116.4074 39.9042, 116.4612 39.9042)', 4326)),
('建国路', '主干道', 60, 0,
 ST_GeomFromText('LINESTRING(116.4612 39.9042, 116.4612 39.9200, 116.4800 39.9400)', 4326)),
('二环路', '快速路', 80, 0,
 ST_GeomFromText('LINESTRING(116.3576 39.9340, 116.3745 39.9530, 116.4200 39.9530, 116.4450 39.9340)', 4326));

-- 创建拓扑（自动填充 source、target、cost 字段）
-- 步骤 1: 添加拓扑字段
ALTER TABLE ways ADD COLUMN source INTEGER;
ALTER TABLE ways ADD COLUMN target INTEGER;

-- 步骤 2: 创建拓扑
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'gid');

-- 步骤 3: 计算通行成本（使用地理距离，单位米）
UPDATE ways SET
    cost = ST_Length(geom::geography),
    reverse_cost = ST_Length(geom::geography);

-- 单行道处理
UPDATE ways SET
    reverse_cost = -1  -- -1 表示不可通行
WHERE oneway = 1;

UPDATE ways SET
    cost = -1
WHERE oneway = -1;

-- 步骤 4: 创建索引
CREATE INDEX idx_ways_source ON ways(source);
CREATE INDEX idx_ways_target ON ways(target);
CREATE INDEX idx_ways_geom ON ways USING GIST(geom);

-- 查询从节点 1 到节点 10 的最短路径
SELECT * FROM pgr_dijkstra(
    'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
    1,    -- 起始节点
    10,   -- 终止节点
    directed := true
);

-- 获取带几何的路径
SELECT
    d.seq,
    d.node,
    d.edge,
    d.cost,
    d.agg_cost,
    w.name,
    w.geom
FROM pgr_dijkstra(
    'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
    1, 10, directed := true
) d
JOIN ways w ON d.edge = w.gid
ORDER BY d.seq;

-- 封装为函数
CREATE OR REPLACE FUNCTION find_route(
    start_lng DOUBLE PRECISION,
    start_lat DOUBLE PRECISION,
    end_lng DOUBLE PRECISION,
    end_lat DOUBLE PRECISION
) RETURNS TABLE(
    seq INTEGER,
    road_name TEXT,
    segment_cost DOUBLE PRECISION,
    total_cost DOUBLE PRECISION,
    geom GEOMETRY
) AS $$
DECLARE
    start_node INTEGER;
    end_node INTEGER;
BEGIN
    -- 找到最近的路网节点
    SELECT source INTO start_node
    FROM ways
    ORDER BY geom <-> ST_SetSRID(ST_MakePoint(start_lng, start_lat), 4326)
    LIMIT 1;

    SELECT source INTO end_node
    FROM ways
    ORDER BY geom <-> ST_SetSRID(ST_MakePoint(end_lng, end_lat), 4326)
    LIMIT 1;

    -- 查询最短路径
    RETURN QUERY
    SELECT
        d.seq::INTEGER,
        COALESCE(w.name, '未命名道路')::TEXT,
        d.cost,
        d.agg_cost,
        w.geom
    FROM pgr_dijkstra(
        'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
        start_node, end_node, directed := true
    ) d
    JOIN ways w ON d.edge = w.gid
    ORDER BY d.seq;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM find_route(116.3470, 39.9042, 116.4612, 39.9042);

-- A* 需要节点的坐标信息
-- 创建包含节点坐标的辅助视图
CREATE OR REPLACE VIEW ways_vertices AS
SELECT id, ST_X(the_geom) AS x, ST_Y(the_geom) AS y
FROM ways_vertices_pgr;

-- 使用 A* 算法
SELECT * FROM pgr_astar(
    'SELECT gid AS id, source, target, cost, reverse_cost,
            ST_X(ST_StartPoint(geom)) AS x1, ST_Y(ST_StartPoint(geom)) AS y1,
            ST_X(ST_EndPoint(geom)) AS x2, ST_Y(ST_EndPoint(geom)) AS y2
     FROM ways',
    1, 10,
    directed := true,
    heuristic := 2  -- 2 = 欧几里得距离
);

-- 查找 3 条最短路径
SELECT * FROM pgr_ksp(
    'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
    1, 10,
    3,            -- K = 3 条路径
    directed := true
);

-- 为导航提供 3 条备选路线
WITH routes AS (
    SELECT
        path_id,
        seq,
        edge,
        cost,
        agg_cost
    FROM pgr_ksp(
        'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
        1, 10, 3, directed := true
    )
)
SELECT
    r.path_id,
    ST_LineMerge(ST_Collect(w.geom)) AS route_geom,
    SUM(r.cost) AS total_distance,
    MAX(r.agg_cost) AS cumulative_distance
FROM routes r
JOIN ways w ON r.edge = w.gid
GROUP BY r.path_id
ORDER BY total_distance;

-- 准备距离矩阵
-- 假设有 5 个配送点
WITH delivery_points AS (
    SELECT id, name, geom FROM stores WHERE id IN (1, 2, 3, 4, 5)
),
distance_matrix AS (
    SELECT
        a.id AS start_id,
        b.id AS end_id,
        ST_Distance(a.geom::geography, b.geom::geography) AS distance
    FROM delivery_points a, delivery_points b
    WHERE a.id != b.id
)
SELECT * FROM pgr_tsp(
    $$
    WITH dm AS (
        SELECT
            a.id AS start_id, b.id AS end_id,
            ST_Distance(a.geom::geography, b.geom::geography) AS dist
        FROM (SELECT id, geom FROM stores WHERE id IN (1,2,3,4,5)) a,
             (SELECT id, geom FROM stores WHERE id IN (1,2,3,4,5)) b
        WHERE a.id != b.id
    )
    SELECT * FROM dm
    $$,
    1  -- 起始节点
);

-- 获取 TSP 路径的几何
WITH tsp_result AS (
    SELECT * FROM pgr_tsp(
        'SELECT * FROM distance_matrix_table',
        1
    )
)
SELECT
    t.seq,
    t.node,
    s.name,
    s.geom,
    t.cost AS leg_distance,
    t.agg_cost AS cumulative_distance
FROM tsp_result t
JOIN stores s ON t.node = s.id
ORDER BY t.seq;

-- 简化的车辆路径规划示例
-- 使用 pgr_dijkstra 的多起终点版本

-- 为多个客户找到最近的配送中心
WITH depots AS (
    SELECT id, name, geom FROM facilities WHERE type = '配送中心'
),
customers AS (
    SELECT id, name, geom FROM customers WHERE status = 'pending'
),
assignments AS (
    SELECT
        c.id AS customer_id,
        c.name AS customer_name,
        d.id AS depot_id,
        d.name AS depot_name,
        ST_Distance(c.geom::geography, d.geom::geography) AS distance_m,
        ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY ST_Distance(c.geom::geography, d.geom::geography)) AS rn
    FROM customers c
    CROSS JOIN depots d
)
SELECT customer_id, customer_name, depot_id, depot_name, distance_m
FROM assignments
WHERE rn = 1
ORDER BY depot_id, distance_m;

-- 创建转弯限制表
CREATE TABLE restrictions (
    id SERIAL PRIMARY KEY,
    cost DOUBLE PRECISION,
    path INTEGER[]  -- 受限的边序列
);

-- 插入限制（例如：禁止从边 1 直接转到边 3）
INSERT INTO restrictions (cost, path) VALUES
(-1, ARRAY[1, 3]),  -- -1 表示禁止
(-1, ARRAY[5, 7]);

-- 使用带转弯限制的路径查询
SELECT * FROM pgr_trsp(
    'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
    'SELECT id, cost, path FROM restrictions',
    1, 10,
    directed := true
);

-- 创建等时圈函数
CREATE OR REPLACE FUNCTION isochrone(
    start_lng DOUBLE PRECISION,
    start_lat DOUBLE PRECISION,
    max_cost DOUBLE PRECISION,  -- 最大通行成本（米）
    step_cost DOUBLE PRECISION DEFAULT 500  -- 步长（米）
) RETURNS TABLE(
    cost_level DOUBLE PRECISION,
    geom GEOMETRY
) AS $$
DECLARE
    start_node INTEGER;
BEGIN
    -- 找到最近节点
    SELECT source INTO start_node
    FROM ways
    ORDER BY geom <-> ST_SetSRID(ST_MakePoint(start_lng, start_lat), 4326)
    LIMIT 1;

    -- 使用 drivingDistance 计算可达范围
    RETURN QUERY
    WITH reachable AS (
        SELECT node, agg_cost
        FROM pgr_drivingDistance(
            'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
            start_node,
            max_cost,
            directed := true
        )
    ),
    grouped AS (
        SELECT
            CEIL(r.agg_cost / step_cost) * step_cost AS cost_level,
            array_agg(r.node) AS nodes
        FROM reachable r
        GROUP BY CEIL(r.agg_cost / step_cost)
    )
    SELECT
        g.cost_level,
        ST_ConvexHull(ST_Collect(v.the_geom)) AS geom
    FROM grouped g
    JOIN ways_vertices_pgr v ON v.id = ANY(g.nodes)
    GROUP BY g.cost_level
    ORDER BY g.cost_level;
END;
$$ LANGUAGE plpgsql;

-- 使用：15 分钟步行等时圈（步行速度 1.2m/s，15分钟 = 1080m）
SELECT cost_level, geom
FROM isochrone(116.4074, 39.9042, 1080, 200);

-- 1. 只加载需要的路网子集
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'gid',
    rows_where := 'road_class IN (''主干道'', ''快速路'', ''高速'')');

-- 2. 添加路网分区
ALTER TABLE ways ADD COLUMN partition_id INTEGER;

-- 3. 使用分区查询加速
SELECT * FROM pgr_dijkstra(
    'SELECT gid AS id, source, target, cost, reverse_cost
     FROM ways WHERE partition_id = ANY(ARRAY[1, 2, 3])',
    1, 10
);

-- 创建路线缓存表
CREATE TABLE route_cache (
    id SERIAL PRIMARY KEY,
    start_node INTEGER,
    end_node INTEGER,
    route_geom GEOMETRY,
    total_cost DOUBLE PRECISION,
    created_at TIMESTAMP DEFAULT now()
);

CREATE INDEX idx_route_cache_nodes ON route_cache(start_node, end_node);

-- 查询前先检查缓存
CREATE OR REPLACE FUNCTION get_cached_route(
    p_start INTEGER, p_end INTEGER
) RETURNS TABLE(route_geom GEOMETRY, total_cost DOUBLE PRECISION) AS $$
BEGIN
    -- 检查缓存
    RETURN QUERY
    SELECT rc.route_geom, rc.total_cost
    FROM route_cache rc
    WHERE rc.start_node = p_start AND rc.end_node = p_end
      AND rc.created_at > now() - INTERVAL '1 day';

    IF NOT FOUND THEN
        -- 缓存未命中，计算新路线并缓存
        INSERT INTO route_cache (start_node, end_node, route_geom, total_cost)
        SELECT p_start, p_end,
               ST_LineMerge(ST_Collect(w.geom)),
               MAX(d.agg_cost)
        FROM pgr_dijkstra(
            'SELECT gid AS id, source, target, cost, reverse_cost FROM ways',
            p_start, p_end
        ) d
        JOIN ways w ON d.edge = w.gid;

        RETURN QUERY
        SELECT rc.route_geom, rc.total_cost
        FROM route_cache rc
        WHERE rc.start_node = p_start AND rc.end_node = p_end;
    END IF;
END;
$$ LANGUAGE plpgsql;