pgrouting

pig repo add pgdg -u          # Add PGDG repo and update cache

CREATE EXTENSION pgrouting CASCADE;  -- requires: 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])

-- Maximum flow
pgr_maxFlow(Edges SQL, source, sink)

-- Specific algorithms
pgr_boykovKolmogorov(Edges SQL, source, sink)
pgr_edmondsKarp(Edges SQL, source, sink)
pgr_pushRelabel(Edges SQL, source, sink)

-- Edge-disjoint paths
pgr_edgeDisjointPaths(Edges SQL, source, sink)

-- Maximum cardinality matching
pgr_maxCardinalityMatch(Edges SQL, [directed])

pgr_kruskal(Edges SQL)         -- minimum spanning tree
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)            -- minimum spanning tree
pgr_primBFS(Edges SQL, root_vid, [max_depth])
pgr_primDFS(Edges SQL, root_vid, [max_depth])
pgr_primDD(Edges SQL, root_vid, distance)

-- Connected components (undirected)
pgr_connectedComponents(Edges SQL)

-- Strongly connected components (directed)
pgr_strongComponents(Edges SQL)

-- Biconnected components
pgr_biconnectedComponents(Edges SQL)

-- Articulation points (cut vertices)
pgr_articulationPoints(Edges SQL)

-- Bridges (cut edges)
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])

-- Extract vertices from edge data
pgr_extractVertices(Edges SQL)

-- Find edges near a point
pgr_findCloseEdges(Edges SQL, point, tolerance, [options])

-- Separate crossing geometries
pgr_separateCrossing(Edges SQL)

-- Separate touching geometries
pgr_separateTouching(Edges SQL)

-- Version info
pgr_version()
pgr_full_version()

-- Extract vertices from edge geometries
SELECT * INTO vertices
FROM pgr_extractVertices('SELECT id, geom FROM edges ORDER BY id');

-- Set source vertex
UPDATE edges AS e
SET source = v.id, x1 = x, y1 = y
FROM vertices AS v
WHERE ST_StartPoint(e.geom) = v.geom;

-- Set target vertex
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 (no edge id required)
pgr_floydWarshall(Edges SQL, [directed])

-- Johnson (no edge id required)
pgr_johnson(Edges SQL, [directed])

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