Documentation for osm2pgrouting v2.2
Index of this page:
- What's new
- Caution
- Requirements
- Command
- Table structure
- Data used on this documentation
- Contributors
- mapconfig_for_bicycles.xml
- progress bar
- Foreign keys to tables
- No use of pointers
- table
osm_ways_tagsthat had as columnsclass_id, way_idis not included anymore due to the fact that thewaystable has the columnclass_idso the tableosm_ways_tagsis redundant.
- Can not process "large" files, continents, big countries, or very large states.
- Undefined behaviour when: The tables already exist but not all the columns that are needed
- Use
--cleanto drop old tables (only drops old routing tables)
- Use
##Requirements
- Have a Postgis database
- When using schemas, have the schema already created
- Permition for modifying the database
- pgRouting extension can be added in the database, before or after the data import
$ osm2pgrouting --version
This is osm2pgrouting Version 2.2
$ osm2pgrouting --help
Allowed options:
Help:
--help Produce help message for this version.
-v [ --version ] Print version string
General:
-f [ --file ] arg REQUIRED: Name of the osm file.
-c [ --conf ] arg (=/usr/share/osm2pgrouting/mapconfig.xml)
Name of the configuration xml file.
--schema arg Database schema to put tables.
blank: defaults to default schema
dictated by PostgreSQL Search_path.
--prefix arg Prefix added at the beginning of the
table names.
--suffix arg Suffix added at the end of the table names.
--addnodes Import the osm_nodes table.
--clean Drop previously created tables.
Database options:
-d [ --dbname ] arg Name of your database (Required).
-U [ --username ] arg (=postgres) Name of the user, which have write access
to the database.
-h [ --host ] arg (=localhost) Host of your postgresql database.
-p [ --port ] arg (=5432) db_port of your database.
-W [ --password ] arg Password for database access.
- Create the database and add postGIS
createdb workshop
psql -c "CREATE EXTENSION postgis" --dbname workshop
- Process an osm file (NE)
osm2pgrouting \
-f workshopNE.osm \
--dbname workshop \
-U foo -W bar
- Output of the process
Execution starts at: Fri Nov 4 09:20:46 2016
***************************************************
COMMAND LINE CONFIGURATION *
***************************************************
Filename = workshopNE.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = workshop
username = foo
password = bar
schema=
prefix =
suffix =
Don't drop tables
Don't add nodes
***************************************************
Connecting to the database
host=localhost user=foo dbname=workshop port=5432 password=bar
connection success
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
Parsing configuration
Parsing data (progress line per 100000 elements)
[**************************************| ] (76%) Total osm elements parsed: 2476000 Finish Parsing data
Creating tables...
Creating 'ways_vertices_pgr': OK
Adding Geometry: Creating 'ways': OK
Adding Geometry: Creating 'relations_ways': OK
Creating 'osm_nodes': OK
Adding Geometry: Creating 'osm_relations': OK
Creating 'osm_way_types': OK
Creating 'osm_way_classes': OK
Adding auxiliary tables to database...
Export Types ...
Processing 4 way types: Inserted: 4 in osm_way_types
Export Classes ...
Processing way's classes: Inserted: 36 in osm_way_classes
Export Relations ...
Processing 0 relations: Inserted: 0 in osm_relations
Export RelationsWays ...
Processing way's relations: Inserted: 0 in relations_ways
Export Ways ...
Processing 110706 ways:
[*************************************************| ] (99%) Ways Processed: 110706 Split Ways generated: 26435 Vertices inserted 18885 Inserted 26435 split ways
Creating Foreign Keys ...
Foreign keys for osm_way_classes table created
Foreign keys for relations_ways table created
Foreign keys for Ways table created
#########################
size of streets: 110706
Execution started at: Fri Nov 4 09:20:46 2016
Execution ended at: Fri Nov 4 09:21:03 2016
Elapsed time: 17.002 Seconds.
User CPU time: -> 12.2657 seconds
#########################```
### To a database that already contains data
1. Process an osm file (SE)
osm2pgrouting
-f workshopSE.osm
--dbname workshop
-U foo -W bar
2. Output of the process
Execution starts at: Fri Nov 4 09:24:07 2016
COMMAND LINE CONFIGURATION *
Filename = workshopSE.osm Configuration file = /usr/share/osm2pgrouting/mapconfig.xml host = localhost port = 5432 dbname = workshop username = foo password = bar schema= prefix = suffix = Don't drop tables Don't add nodes
Connecting to the database host=localhost user=foo dbname=workshop port=5432 password=bar connection success Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml Parsing configuration
Parsing data (progress line per 100000 elements)
[**************| ] (28%) Total osm elements parsed: 2029000 Finish Parsing data
Creating tables... Exists: 'ways_vertices_pgr': OK Exists: 'ways': OK Exists: 'relations_ways': OK Exists: 'osm_nodes': OK Exists: 'osm_relations': OK Exists: 'osm_way_types': OK Exists: 'osm_way_classes': OK Adding auxiliary tables to database...
Export Types ... Processing 4 way types: Inserted: 0 in osm_way_types
Export Classes ... Processing way's classes: Inserted: 0 in osm_way_classes
Export Relations ... Processing 1 relations: Inserted: 1 in osm_relations
Export RelationsWays ... Processing way's relations: Inserted: 2 in relations_ways
Export Ways ... Processing 96407 ways: [*************************************************| ] (99%) Ways Processed: 96407 Split Ways generated: 27603 Vertices inserted 20735 Inserted 27564 split ways Creating Foreign Keys ... Foreign keys for osm_way_classes table created Foreign keys for relations_ways table created Foreign keys for Ways table created ######################### size of streets: 96407 Execution started at: Fri Nov 4 09:24:07 2016 Execution ended at: Fri Nov 4 09:24:24 2016 Elapsed time: 16.823 Seconds. User CPU time: -> 10.5931 seconds #########################
### Using the clean flag
1. Process an osm file (workshopSmall)
osm2pgrouting
-f workshopSmall.osm
--dbname workshop
-U foo -W bar --clean
2. Output of the process
Execution starts at: Fri Nov 4 09:26:21 2016
COMMAND LINE CONFIGURATION *
Filename = workshopSmall.osm Configuration file = /usr/share/osm2pgrouting/mapconfig.xml host = localhost port = 5432 dbname = workshop username = foo password = bar schema= prefix = suffix = Drop tables Don't add nodes
Connecting to the database host=localhost user=foo dbname=workshop port=5432 password=bar connection success Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml Parsing configuration
Parsing data (progress line per 100000 elements)
[**| ] (4%) Total osm elements parsed: 304000 Finish Parsing data
Dropping tables...
Creating tables... Creating 'ways_vertices_pgr': OK Adding Geometry: Creating 'ways': OK Adding Geometry: Creating 'relations_ways': OK Exists: 'osm_nodes': OK Exists: 'osm_relations': OK Exists: 'osm_way_types': OK Exists: 'osm_way_classes': OK Adding auxiliary tables to database...
Export Types ... Processing 4 way types: Inserted: 0 in osm_way_types
Export Classes ... Processing way's classes: Inserted: 0 in osm_way_classes
Export Relations ... Processing 0 relations: Inserted: 0 in osm_relations
Export RelationsWays ... Processing way's relations: Inserted: 0 in relations_ways
Export Ways ... Processing 14354 ways: [*************************************************| ] (98%) Ways Processed: 14354 Split Ways generated: 3810 Vertices inserted 3050 Inserted 3810 split ways Creating Foreign Keys ... Foreign keys for osm_way_classes table created Foreign keys for relations_ways table created Foreign keys for Ways table created ######################### size of streets: 14354 Execution started at: Fri Nov 4 09:26:21 2016 Execution ended at: Fri Nov 4 09:26:24 2016 Elapsed time: 2.897 Seconds. User CPU time: -> 1.54912 seconds #########################
## Table structure
createdb tablestructure psql -c "CREATE EXTENSION postgis" --dbname tablestructure psql -c "CREATE SCHEMA the_schema" --dbname tablestructure osm2pgrouting -f workshopSmall.osm --prefix pre_ --suffix _suf --schema the_schema --addnodes -U foo -W bar --dbname tablestructure
In the example:
- the prefix has an underscore at the end. "pre_"
- the suffix has an underscore at the beginning. "_suf"
psql tablestructure \dt+ the_schema.* List of relations Schema | Name | Type | Owner | Size | Description ------------+---------------------------+-------+-------+------------+------------- the_schema | osm_nodes | table | foo | 9040 kB | the_schema | osm_relations | table | foo | 8192 bytes | the_schema | osm_way_classes | table | foo | 16 kB | the_schema | osm_way_types | table | foo | 16 kB | the_schema | pre_relations_ways_suf | table | foo | 0 bytes | the_schema | pre_ways_suf | table | foo | 1192 kB | the_schema | pre_ways_suf_vertices_pgr | table | foo | 336 kB | (7 rows)
- all tables are created on the same schema
- 7 tables are created and the prefix and suffix is used in the routing tables
In the following outputs, the "| Storage | Stats target | Description" are eliminated manually for readability.
the ways table
--------------
\dS+ the_schema.pre_ways_suf
Table "the_schema.pre_ways_suf"
Column | Type | Modifiers
-------------------+---------------------------+-----------------------------------------------------------------------
gid | bigint | not null default nextval('the_schema.pre_ways_suf_gid_seq'::regclass)
class_id | integer | not null
length | double precision |
length_m | double precision |
name | text |
source | bigint |
target | bigint |
x1 | double precision |
y1 | double precision |
x2 | double precision |
y2 | double precision |
cost | double precision |
reverse_cost | double precision |
cost_s | double precision |
reverse_cost_s | double precision |
rule | text |
one_way | integer |
maxspeed_forward | integer |
maxspeed_backward | integer |
osm_id | bigint |
source_osm | bigint |
target_osm | bigint |
priority | double precision | default 1
the_geom | geometry(LineString,4326) |
Indexes:
"pre_ways_suf_pkey" PRIMARY KEY, btree (gid)
"pre_ways_suf_gdx" gist (the_geom)
"pre_ways_suf_source_idx" btree (source)
"pre_ways_suf_source_osm_idx" btree (source_osm)
"pre_ways_suf_target_idx" btree (target)
"pre_ways_suf_target_osm_idx" btree (target_osm)
Foreign-key constraints:
"pre_ways_suf_class_id_fkey" FOREIGN KEY (class_id) REFERENCES the_schema.osm_way_classes(class_id)
"pre_ways_suf_source_fkey" FOREIGN KEY (source) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id)
"pre_ways_suf_target_fkey" FOREIGN KEY (target) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id)
the vertices table
--------------
\dS+ the_schema.pre_ways_suf_vertices_pgr Table "the_schema.pre_ways_suf_vertices_pgr" Column | Type | Modifiers ----------+----------------------+-----------------------------------------------------------------------------------+ id | bigint | not null default nextval('the_schema.pre_ways_suf_vertices_pgr_id_seq'::regclass) osm_id | bigint | cnt | integer | chk | integer | ein | integer | eout | integer | lon | numeric(11,8) | lat | numeric(11,8) | the_geom | geometry(Point,4326) | Indexes: "pre_ways_suf_vertices_pgr_pkey" PRIMARY KEY, btree (id) "vertex_id" UNIQUE CONSTRAINT, btree (osm_id) "pre_ways_suf_vertices_pgr_gdx" gist (the_geom) "pre_ways_suf_vertices_pgr_osm_id_idx" btree (osm_id) Referenced by: TABLE "the_schema.pre_ways_suf" CONSTRAINT "pre_ways_suf_source_fkey" FOREIGN KEY (source) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id) TABLE "the_schema.pre_ways_suf" CONSTRAINT "pre_ways_suf_target_fkey" FOREIGN KEY (target) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id)
## Auxiliary tables
They are mainly related to the configuration
the_schema.osm_way_types
-------------------------
```sql
SELECT * FROM the_schema.osm_way_types ;
type_id | name
---------+-----------
2 | cycleway
1 | highway
4 | junction
3 | tracktype
(4 rows)
SELECT * FROM the_schema.osm_way_classes;
class_id | type_id | name | priority | default_maxspeed
----------+---------+-------------------+----------+------------------
201 | 2 | lane | 0 | 50
204 | 2 | opposite | 0 | 50
203 | 2 | opposite_lane | 0 | 50
202 | 2 | track | 0 | 50
120 | 1 | bridleway | 0 | 50
...SELECT * FROM the_schema.osm_relations;
relation_id | type_id | class_id | name
-------------+---------+----------+------
(0 rows)SELECT node_id, osm_id, lon, lat, numofuse, ST_AsText(the_geom) FROM the_schema.osm_nodes LIMIT 5;
node_id | osm_id | lon | lat | numofuse | st_astext
---------+----------+---------------+-------------+----------+--------------------------------
1 | 36757745 | -122.62153000 | 45.41327120 | 2 | POINT(-122.62153 45.4132712)
2 | 36757748 | -122.62093520 | 45.41285660 | 1 | POINT(-122.6209352 45.4128566)
3 | 36757755 | -122.62127960 | 45.41257250 | 1 | POINT(-122.6212796 45.4125725)
4 | 36758157 | -122.60811920 | 45.40168840 | 1 | POINT(-122.6081192 45.4016884)
5 | 37011329 | -122.61641730 | 45.40751060 | 3 | POINT(-122.6164173 45.4075106)
(5 rows)
The following sample data used in this documentation:
BBOX="-122.8,45.4,-122.5,45.6"
wget --progress=dot:mega -O "workshop.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.65,45.5, -122.5,45.6"
wget --progress=dot:mega -O "workshopNE.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.65,45.4, -122.5,45.5"
wget --progress=dot:mega -O "workshopSE.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.8,45.5, -122.65,45.6"
wget --progress=dot:mega -O "workshopNW.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.8,45.4, -122.65,45.5"
wget --progress=dot:mega -O "workshopSW.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.65,45.4, -122.6,45.45"
wget --progress=dot:mega -O "workshopSmall.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
Version 2.2 contributors in alphabetical order:
- Adrien Pavie - added mapconfig_for_bycicles.xml
- Luís de Sousa - added foreign keys
- Mark Dimond, - testing
- Virginia Vergara - eliminate the use of pointers, progress bar, bugs fixing
Adrien Pavie, Anton Patrushev, Daniel Kastl, Daniel Wendt, Jordan Anderson, J Kishore Kumar, Ko Nagase, Luís de Sousa, Mark Dimond, Regina Obe, Sarthak Agarwal, Virginia Vergara
