pgrouting

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION pgrouting CASCADE;  -- 依赖: plpgsql, postgis

CREATE EXTENSION pgrouting CASCADE;

SELECT id, source, target, cost, reverse_cost FROM edges;

CREATE TABLE wiki (
  id SERIAL,
  source INTEGER,
  target INTEGER,
  cost INTEGER
);

INSERT INTO wiki (source, target, cost) VALUES
  (1, 2, 7),  (1, 3, 9), (1, 6, 14),
  (2, 3, 10), (2, 4, 15),
  (3, 6, 2),  (3, 4, 11),
  (4, 5, 6),
  (5, 6, 9);

SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost FROM wiki',
  1, 5, false);

pgr_dijkstra(Edges SQL, start_vid,  end_vid,  [directed])
pgr_dijkstra(Edges SQL, start_vid,  end_vids, [directed])
pgr_dijkstra(Edges SQL, start_vids, end_vid,  [directed])
pgr_dijkstra(Edges SQL, start_vids, end_vids, [directed])
pgr_dijkstra(Edges SQL, Combinations SQL,     [directed])

SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  6, 10, true);

SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  6, ARRAY[10, 17]);

SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  ARRAY[6, 1], ARRAY[10, 17],
  directed => false);

SELECT * FROM pgr_dijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  'SELECT source, target FROM combinations',
  false);

pgr_dijkstraCost(Edges SQL, start_vid, end_vid, [directed])

pgr_dijkstraCostMatrix(Edges SQL, vids, [directed])

pgr_dijkstraVia(Edges SQL, via_vertices, [directed, strict, U_turn_on_edge])

pgr_dijkstraNear(Edges SQL, start_vid, end_vids, [directed])

pgr_aStar(Edges SQL, start_vid, end_vid, [directed, heuristic, factor, epsilon])

SELECT * FROM pgr_aStar(
  'SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edges',
  6, 12,
  directed => true, heuristic => 2);

SELECT * FROM pgr_bdDijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  6, 10);

pgr_KSP(Edges SQL, start_vid, end_vid, K, [directed, heap_paths])

SELECT * FROM pgr_KSP(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  6, 17, 2);

pgr_drivingDistance(Edges SQL, start_vid, distance, [directed])
pgr_drivingDistance(Edges SQL, start_vids, distance, [directed, equicost])

SELECT * FROM pgr_drivingDistance(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  11, 3.0);

pgr_TSP(Matrix SQL, [start_id, end_id])

SELECT * FROM pgr_TSP(
  $$SELECT * FROM pgr_dijkstraCostMatrix(
    'SELECT id, source, target, cost, reverse_cost FROM edges',
    ARRAY[1, 3, 5, 6, 7, 8, 9, 10, 11, 15, 16, 17],
    directed => false)$$,
  start_id => 1);

pgr_TSPeuclidean(Coordinates SQL, [start_id, end_id])

-- 最大流
pgr_maxFlow(Edges SQL, source, sink)

-- 特定算法
pgr_boykovKolmogorov(Edges SQL, source, sink)
pgr_edmondsKarp(Edges SQL, source, sink)
pgr_pushRelabel(Edges SQL, source, sink)

-- 边不相交路径
pgr_edgeDisjointPaths(Edges SQL, source, sink)

-- 最大基数匹配
pgr_maxCardinalityMatch(Edges SQL, [directed])

pgr_kruskal(Edges SQL)         -- 最小生成树
pgr_kruskalBFS(Edges SQL, root_vid, [max_depth])
pgr_kruskalDFS(Edges SQL, root_vid, [max_depth])
pgr_kruskalDD(Edges SQL, root_vid, distance)

pgr_prim(Edges SQL)            -- 最小生成树
pgr_primBFS(Edges SQL, root_vid, [max_depth])
pgr_primDFS(Edges SQL, root_vid, [max_depth])
pgr_primDD(Edges SQL, root_vid, distance)

-- 连通分量（无向）
pgr_connectedComponents(Edges SQL)

-- 强连通分量（有向）
pgr_strongComponents(Edges SQL)

-- 双连通分量
pgr_biconnectedComponents(Edges SQL)

-- 关节点（割点）
pgr_articulationPoints(Edges SQL)

-- 桥（割边）
pgr_bridges(Edges SQL)

pgr_trsp(Edges SQL, Restrictions SQL, start_vid, end_vid, [directed])
pgr_trspVia(Edges SQL, Restrictions SQL, via_vertices, [directed, strict, U_turn_on_edge])
pgr_trsp_withPoints(Edges SQL, Restrictions SQL, Points SQL, start_pid, end_pid, [options])

pgr_withPoints(Edges SQL, Points SQL, start_pid, end_pid, [options])
pgr_withPointsCost(Edges SQL, Points SQL, start_pid, end_pid, [options])
pgr_withPointsCostMatrix(Edges SQL, Points SQL, pids, [options])
pgr_withPointsKSP(Edges SQL, Points SQL, start_pid, end_pid, K, [options])
pgr_withPointsDD(Edges SQL, Points SQL, start_pid, distance, [options])

pgr_contraction(Edges SQL, contraction_order, [options])

-- 从边数据中提取顶点
pgr_extractVertices(Edges SQL)

-- 查找点附近的边
pgr_findCloseEdges(Edges SQL, point, tolerance, [options])

-- 分离交叉几何体
pgr_separateCrossing(Edges SQL)

-- 分离相切几何体
pgr_separateTouching(Edges SQL)

-- 版本信息
pgr_version()
pgr_full_version()

-- 从边几何体中提取顶点
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');

-- 设置起始顶点
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE ST_StartPoint(e.geom) = v.geom;

-- 设置终止顶点
UPDATE edges AS e
SET target = v.id, x2 = x, y2 = y
FROM vertices AS v
WHERE ST_EndPoint(e.geom) = v.geom;

UPDATE edges SET
  cost = sign(cost) * ST_Length(geom),
  reverse_cost = sign(reverse_cost) * ST_Length(geom);

SELECT seq, node, edge, cost, agg_cost, geom
FROM pgr_dijkstra(
  'SELECT id, source, target, cost, reverse_cost FROM edges',
  6, 10
) AS r
LEFT JOIN edges AS e ON r.edge = e.id;

SELECT * FROM pgr_dijkstra($$
  SELECT id, source, target, cost, reverse_cost
  FROM edges
  WHERE geom && (
    SELECT ST_Buffer(ST_Union(geom), 1)
    FROM edges WHERE source IN (7) OR target IN (8))$$,
  7, 8);

-- Floyd-Warshall（不需要边 ID）
pgr_floydWarshall(Edges SQL, [directed])

-- Johnson（不需要边 ID）
pgr_johnson(Edges SQL, [directed])

SELECT * FROM pgr_floydWarshall(
  'SELECT id, source, target, cost, reverse_cost FROM edges');