pgRouting

-- Create a table for our road network
CREATE TABLE roads (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    source BIGINT,  -- Pre-assigned source node ID
    target BIGINT,  -- Pre-assigned target node ID
    cost FLOAT,
    reverse_cost FLOAT,
    length_m FLOAT,
    the_geom geometry
);

-- Create indices for better performance
CREATE INDEX roads_source_idx ON roads(source);
CREATE INDEX roads_target_idx ON roads(target);
CREATE INDEX roads_geom_idx ON roads USING GIST(the_geom);

-- Insert sample road segments with pre-defined source/target nodes
INSERT INTO roads (name, the_geom, length_m, cost, reverse_cost, source, target) VALUES
    ('Main St',
     ST_GeomFromText('LINESTRING(-122.678 45.526, -122.675 45.526)', 4326),
     300, 300, 300, 1, 2),
    ('Oak Ave',
     ST_GeomFromText('LINESTRING(-122.675 45.526, -122.675 45.524)', 4326),
     200, 200, 200, 2, 3),
    ('Pine St',
     ST_GeomFromText('LINESTRING(-122.675 45.524, -122.678 45.524)', 4326),
     300, 300, 300, 3, 4);

-- Find shortest path between two points
SELECT seq, node, edge, cost
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM roads',
    1,  -- starting vertex
    3,  -- ending vertex
    directed := false
);

SELECT seq, node, edge, cost
FROM pgr_astar(
    'SELECT id, source, target, cost,
            ST_X(ST_StartPoint(the_geom)) AS x1,
            ST_Y(ST_StartPoint(the_geom)) AS y1,
            ST_X(ST_EndPoint(the_geom)) AS x2,
            ST_Y(ST_EndPoint(the_geom)) AS y2
     FROM roads',
    1,  -- starting vertex
    3,  -- ending vertex
    directed := false
);

SELECT node, edge, cost
FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM roads',
    1,    -- starting vertex
    1000, -- maximum cost
    directed := false
);