setup_pgrouting.md

Install pgrouting2 on Ubuntu

sudo apt install osm2pgrouting
sudo apt install cmake g++ libboost-graph-dev
sudo apt install postgresql-16 postgresql-16-postgis postgresql-server-dev-16
sudo apt install postgresql-10-pgtap
sudo apt install sphinx-common
sudo apt install bzip2
wget -O pgrouting-3.0.6.tar.gz https://github.com/pgRouting/pgrouting/archive/v3.0.6.tar.gz
tar -xvf pgrouting-3.0.6.tar.gz
cd pgrouting-3.0.6
mkdir build
cd build
cmake -L ..
make
sudo make install
cd ..
rm -rf build

Create extensions

sudo -i -u oklab
psql
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgrouting;
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS pgtap;
SELECT pgr_version();

Create mapconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- a way is imported if it matches one of the following tag_values -->
<!-- tag_values are processed in the order of their ID -->
<configuration>
  <tag_name name="highway" id="1">
    <tag_value name="road" id="100" />
    <tag_value name="motorway" id="101" />
    <tag_value name="motorway_link" id="102" />
    <tag_value name="motorway_junction" id="103" />
    <tag_value name="trunk" id="104" />
    <tag_value name="trunk_link" id="105" />    
    <tag_value name="primary" id="106" />
    <tag_value name="primary_link" id="107" />    
    <tag_value name="secondary" id="108" />
    <tag_value name="secondary_link" id="124" />
    <tag_value name="tertiary" id="109" />
    <tag_value name="tertiary_link" id="125" />
    <tag_value name="residential" id="110" />
    <tag_value name="living_street" id="111" />
    <tag_value name="service" id="112" />
    <tag_value name="track" id="113" />
    <tag_value name="pedestrian" id="114" />
    <tag_value name="services" id="115" />
    <tag_value name="bus_guideway" id="116" />
    <tag_value name="path" id="117" />
    <tag_value name="cycleway" id="118" />
    <tag_value name="footway" id="119" />
    <tag_value name="bridleway" id="120" />
    <tag_value name="byway" id="121" />
    <tag_value name="steps" id="122" />
    <tag_value name="unclassified" id="123" />
  </tag_name>
  <!-- the following lines are only evaluated if there is no highway-tag at the way -->
  <tag_name name="cycleway" id="2">
    <tag_value name="lane" id="201" />
    <tag_value name="track" id="202" />
    <tag_value name="opposite_lane" id="203" />
    <tag_value name="opposite" id="204" />
  </tag_name>  
  <tag_name name="tracktype" id="3">
    <tag_value name="grade1" id="301" />
    <tag_value name="grade2" id="302" />
    <tag_value name="grade3" id="303" />
    <tag_value name="grade4" id="304" />
    <tag_value name="grade5" id="305" />
  </tag_name>  
  <tag_name name="junction" id="4">
    <tag_value name="roundabout" id="401" />
  </tag_name>  
</configuration>

Import OSM Data Routing

wget https://download.geofabrik.de/europe/germany/hamburg-latest.osm.bz2
osm2pgrouting --f hamburg-latest.osm  --conf mapconfig.xml --dbname oklab --username oklab --clean --addnodes  --attributes --tags

TABLE: ways_vertices_pgr created ... OK.
TABLE: ways created ... OK.
TABLE: pointsofinterest created ... OK.
TABLE: configuration created ... OK.
TABLE: osm_nodes created ... OK.
TABLE: osm_ways created ... OK.
TABLE: osm_relations created ... OK.

PSQL to STDOUT in CSV format

COPY (
    WITH path AS (
        SELECT * FROM pgr_dijkstra(
            'SELECT gid AS id, source, target, cost FROM ways',
            93,  -- Start vertex ID (replace with your actual start vertex ID)
            6,   -- End vertex ID (replace with your actual end vertex ID)
            directed := true
        )
    )
    SELECT 
        path.seq,            -- The sequence of steps in the path
        path.node,           -- The vertex ID at this step
        path.edge,           -- The edge ID (gid) used to reach this vertex
        path.cost,           -- The cost to reach this vertex
        w.name,              -- The name of the road/way (if available)
        w.length,            -- The length of the edge
        w.length_m,          -- The length in meters
        w.oneway,            -- One-way direction information
        w.maxspeed_forward,  -- Maximum speed in forward direction
        w.maxspeed_backward, -- Maximum speed in backward direction
        ST_AsText(w.the_geom) -- The geometry of the way
    FROM path
    JOIN ways w ON path.edge = w.gid
    ORDER BY path.seq
) TO STDOUT WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *);