search

pgRouting / osm2pgrouting

Import tool for OpenStreetMap data to pgRouting database
https://pgrouting.org
GNU General Public License v2.0
289 stars 111 forks source link

Error using OSM tag keys containing "spaces" (not allowed as HSTORE keys) and bad_alloc exception with osm_relations #189

Closed cayetanobv closed 6 years ago

cayetanobv commented 6 years ago

Hi,

If I use the new flags (added in v.2.3) --addnodes and --tags and OSM input file has tag keys containing spaces it raises an error (you can see below the details). I think this is because HSTORE keys can not include spaces in PostgreSQL . Moreover when osm_ways processing has finished it raises a failure allocating memory with osm_relations : std::bad_alloc. The process is stopped and the main table to use with Pgrouting (ways) remains empty.

HSTORE keys error disappears removing tags with spaces from osm input file but bad_alloc exception raises anyway with osm_relations.

OSM file to reproduce this error (downloaded with Overpass API): https://drive.google.com/file/d/0B8PuZrBkyPBuVk9KUE93SVZqWVE/view?usp=sharing

Error details ("sport pitch" is a OSM tag key with spaces):

$ osm2pgrouting --file /tmp/output_data.osm  --clean --dbname routing --user postgres  --password postgres --port 5432 --host localhost --schema bcn_tags_demo2 --addnodes --tags
***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = /tmp/output_data.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = routing
username = postgres
password = postgres
schema= bcn_tags_demo2
prefix = 
suffix = 
Don't Install postgis if not found
Drop tables
Don't create indexes
Add OSM nodes
Don't add OSM ways
Don't add OSM relations
***************************************************
Testing database connection: routing
database connection successfull: routing
Connecting to the database
connection success

Dropping tables...
TABLE: bcn_tags_demo2.ways droped ... OK.
TABLE: bcn_tags_demo2.ways_vertices_pgr droped ... OK.
TABLE: bcn_tags_demo2.pointsofinterest droped ... OK.
TABLE: bcn_tags_demo2.configuration droped ... OK.
TABLE: bcn_tags_demo2.osm_nodes droped ... OK.
TABLE: bcn_tags_demo2.osm_ways droped ... OK.
TABLE: bcn_tags_demo2.osm_relations droped ... OK.

Creating tables...
TABLE: bcn_tags_demo2.ways_vertices_pgr created ... OK.
TABLE: bcn_tags_demo2.ways created ... OK.
TABLE: bcn_tags_demo2.pointsofinterest created ... OK.
TABLE: bcn_tags_demo2.configuration created ... OK.
TABLE: bcn_tags_demo2.osm_nodes created ... OK.
TABLE: bcn_tags_demo2.osm_ways created ... OK.
TABLE: bcn_tags_demo2.osm_relations created ... OK.
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

Exporting configuration ...
  - Done 
Counting lines ...
  - Done 
Opening data file: /tmp/output_data.osm total lines: 5075286
    Parsing data

Current osm_nodes:ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 8827, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 8827, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 3827, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 1327, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 77, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 77, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 77, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 77, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 77, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 38, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 19, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 9, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 4, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 2, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 1, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __osm_nodes2312, line 1, column tags: "sport pitch => "gymnastics" "
Current osm_nodes:  1420000
*****ERROR HERE:
 4551477890 srid=4326; POINT(2.2132757 41.4003267)  \N  \N  \N  sport pitch => "gymnastics" 

ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 647, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 19, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 19, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 19, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 19, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 19, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 19, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 10, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 5, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 3, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 2, column tags: "sport pitch => "gymnastics" "
ERROR:  Syntax error near 'p' at position 6
CONTEXT:  COPY __pointsofinterest2312, line 1, column tags: "sport pitch => "gymnastics" "
******
*****ERROR HERE:
 4551477890 srid=4326; POINT(2.2132757 41.4003267)  \N  \N  \N  sport pitch => "gymnastics" 

Current osm_nodes:  1580000
Final osm_nodes:    1582048
Current osm_ways:   0
Final osm_ways: 173513
Current osm_relations:  0std::bad_alloc

Thanks, Cayetano

cvvergara commented 6 years ago

while I make time to fix it, can you make:

perl -pi -e 's/sport pitch/sport_ptich'/g' /tmp/output_data.osm

I misunderstood and thought osm tags didn't have spaces

cayetanobv commented 6 years ago

Thanks @cvvergara. I think whitespaces in osm tag keys is a very bad practice but you can find it because is legal. You can read here about it: https://taginfo.openstreetmap.org/reports/characters_in_keys#space

On the other hand what do you think about the bad_alloc error with osm_relations? This error stops the process.

cvvergara commented 6 years ago

I really need time for checking exactly what is causing the error, I removed as much as possible the use of pointers, is obvious that something I missed. I might have some time on November to review this issue. But, as a test without using the --add nodes flag do you have the bad alloc problem?

cvvergara commented 6 years ago

Hello @cayetanobv

Finally weekend arrived and time was found and a PR #190 is there. I will merge tonight and make the new micro release 2.3.1 Feel free to double check this branch: https://github.com/cvvergara/pgrouting/tree/fix/issue984

I tested using your data I am getting this output:

 osm2pgrouting --file output_data.osm  --clean --dbname issue189  --port 5432 --host localhost --schema bcn_tags_demo2 --addnodes --tags
Execution starts at: Sat Oct 14 11:57:09 2017

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = output_data.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = issue189
username = 
password = 
schema= bcn_tags_demo2
prefix = 
suffix = 
Don't Install postgis if not found
Drop tables
Don't create indexes
Add OSM nodes
Don't add OSM ways
Don't add OSM relations
***************************************************
Testing database connection: issue189
database connection successful: issue189
Connecting to the database
connection success

Dropping tables...
TABLE: bcn_tags_demo2.ways dropped ... OK.
TABLE: bcn_tags_demo2.ways_vertices_pgr dropped ... OK.
TABLE: bcn_tags_demo2.pointsofinterest dropped ... OK.
TABLE: bcn_tags_demo2.configuration dropped ... OK.
TABLE: bcn_tags_demo2.osm_nodes dropped ... OK.
TABLE: bcn_tags_demo2.osm_ways dropped ... OK.
TABLE: bcn_tags_demo2.osm_relations dropped ... OK.

Creating tables...
TABLE: bcn_tags_demo2.ways_vertices_pgr created ... OK.
TABLE: bcn_tags_demo2.ways created ... OK.
TABLE: bcn_tags_demo2.pointsofinterest created ... OK.
TABLE: bcn_tags_demo2.configuration created ... OK.
TABLE: bcn_tags_demo2.osm_nodes created ... OK.
TABLE: bcn_tags_demo2.osm_ways created ... OK.
TABLE: bcn_tags_demo2.osm_relations created ... OK.
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

Exporting configuration ...
  - Done 
Counting lines ...
  - Done 
Opening data file: output_data.osm      total lines: 5075288
    Parsing data

Current osm_nodes:      1580000
Final osm_nodes:        1582048
Current osm_ways:       160000
Final osm_ways: 173513
Current osm_relations:  0
Final osm_relations:    94
End Of file

    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 173513 ways:
[*****|                                             ] (11%) Total porcessed: 20000       Vertices inserted: 38005       Split ways inserted 37861
[***********|                                       ] (23%) Total porcessed: 40000       Vertices inserted: 19117       Split ways inserted 23542
[*****************|                                 ] (34%) Total porcessed: 60000       Vertices inserted: 18531       Split ways inserted 23174
[***********************|                           ] (46%) Total porcessed: 80000       Vertices inserted: 11884       Split ways inserted 18771
[****************************|                      ] (57%) Total porcessed: 100000      Vertices inserted: 8028        Split ways inserted 14329
[**********************************|                ] (69%) Total porcessed: 120000      Vertices inserted: 7205        Split ways inserted 12944
[****************************************|          ] (80%) Total porcessed: 140000      Vertices inserted: 6712        Split ways inserted 12292
[**********************************************|    ] (92%) Total porcessed: 160000      Vertices inserted: 4941        Split ways inserted 9303
[**************************************************|] (100%) Total porcessed: 173513     Vertices inserted: 2917        Split ways inserted 6845

Creating indexes ...

Processing Points of Interest ...

Adding functions for processing Points of Interest ...

To process pointsOfInterest table:
osm2pgr_pois_update(radius default 200, within default 50)

  - Using areas of (radius)mts on POIS
  - Using edges that are at least (within) mts of each POI
POIS that do not have a closest edge is considered as too far
#########################
size of streets: 173513
Execution started at: Sat Oct 14 11:57:09 2017
Execution ended at:   Sat Oct 14 12:00:04 2017
Elapsed time: 174.6 Seconds.
User CPU time: -> 56.2753 seconds
#########################
cayetanobv commented 6 years ago

Awesome @cvvergara !!

I've just compiled your branch https://github.com/cvvergara/pgrouting/tree/fix/issue984 and it works well.

Two errors solved with PR #190:

Thanks!!