We do routing (routing) on ​​OpenStreetMap. Introduction

I would like to share the experience of creating PostgreSQL / PgRouting routing systems on the OpenStreetMap. It will be about developing [commercial] solutions with complex requirements, for simpler projects it is probably enough to consult the documentation. As far as I know, such things as full support for one-way roads and driving directions, fast routing to thousands of addresses (on the order of seconds on a regular laptop, for example, a Macbook Pro 13 "2013), creating a road graph with specified properties, meta-optimization of routes not covered at all, and as usual, all data and results are available in my GitHub repository of OSM Routing Tricks , which I will add as I publish.

330 OpenStreetMap ( 5 ). , , 5000 ? , , ( ).

, , , . : , , , , , ( )! PgRouting PostgreSQL OpenStreetMap.

, . , , - . , PgRouting . , , , .

, .

PgRouting 3.0 PostgreSQL 12, PgRouting 2.6 PostgreSQL 9 . , MacOS ( ) Debian/Ubuntu ( ) PgRouting pgr_TSP() , , , , "" MacOS , , , , . MacOS Debian/Ubuntu.

OpenStreetMap PostgreSQL

, GDAL, , . , , OpenStreetMap PostgreSQL SQL , . GDAL: PostgreSQL SQL Dump. , . GDAL. OSM (germany-latest.osm.pbf) PostgreSQL (osmrouting):

ogr2ogr \
    -f PGDUMP \
    /vsistdout/ "germany-latest.osm.pbf" \
    -nln "osm_lines" \
    -nlt LINESTRING \
    -progress \
    --config PG_USE_COPY YES \
    --config GEOMETRY_NAME the_geom \
    --config OSM_COMPRESS_NODES YES \
    --config OSM_CONFIG_FILE "osmconf.ini" \
    -sql "select * from lines where highway is not null" \
    -lco FID=id \
    | psql osmrouting

OpenStreetMap OpenStreetMap Data Extracts.

OpenStreetMap

. (), — . , , (). , ? , — , , , , . , , . PgRouting.

( ) , (, , ) . , . , . , .

. , , . , () , .

( ) , , . , . , 10 100 .

, , «» ? — () , (, , ) . . , 10 100 , , — ! , — . , , () , . .

PostgreSQL

"osmrouting", , PostgreSQL SQL , . load.sh ( PgRouting PostGIS).

, , ( start_id end_id , , , length):

--      (  )
-- id -   
-- the_geom -     
-- oneway -   
-- highway -    (      )
-- pedestrian -    (   )
-- start_id -      
-- end_id -      
-- length -   (        .)
osmrouting=# \d osm_network
                       Table "public.osm_network"
   Column   |           Type            | Collation | Nullable | Default 
------------+---------------------------+-----------+----------+---------
 id         | integer                   |           |          | 
 the_geom   | geometry(LineString,4326) |           |          | 
 type       | text                      |           |          | 
 oneway     | boolean                   |           |          | 
 highway    | boolean                   |           |          | 
 pedestrian | boolean                   |           |          | 
 start_id   | bigint                    |           |          | 
 end_id     | bigint                    |           |          | 
 length     | double precision          |           |          | 

:

--     
-- id -   
-- the_geom -     
osmrouting=# \d osm_nodes
                     Table "public.osm_nodes"
  Column  |         Type         | Collation | Nullable | Default 
----------+----------------------+-----------+----------+---------
 id       | bigint               |           |          | 
 the_geom | geometry(Point,4326) |           |          | 

, :

osmrouting=# \d osm_buildings
                   Table "public.osm_buildings"
  Column  |         Type         | Collation | Nullable | Default 
----------+----------------------+-----------+----------+---------
 id       | character varying    |           |          | 
 the_geom | geometry(Point,4326) |           |          | 
...

.

route.sql 330 PgRouting pgr_TSP(). , , ( , . PgRouting). pgr_dijkstraCostMatrix(). , directed=false, pgr_TSP() (, , ). pgr_dijkstraSymmetrizeCostMatrix() pgr_dijkstraValidateCostMatrix() , . , ( — ) pgr_dijkstraVia() .

randomize=false , randomize=true, . SQL "route" , QGIS. QGIS , . route.qgs .

:

, — , 245,246,247 . «» — , , . ( ), , .

— , PgRouting. , ( , ), , . .