Skip to content

Instantly share code, notes, and snippets.

@p3t3r67x0

p3t3r67x0/setup_pgrouting.md

Created August 9, 2024 11:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save p3t3r67x0/b2a58ab4f516c6ff0f243af3a20d45c5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save p3t3r67x0/b2a58ab4f516c6ff0f243af3a20d45c5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. p3t3r67x0 created this gist Aug 9, 2024.
    137 changes: 137 additions & 0 deletions setup_pgrouting.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,137 @@
    ## 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 *);
    ```