NULL values in length_m after running the application

[DrDanke]

I imported a dataset for whole Germany and prepared the data for routing, when I trapped into some problems due to NULL values in the column length_m. I have been using the standard configuration file. Further, the fields cost_s, reverse_cost_s are also NULL (I think caused by the missing values).

How can this happen for 2 records out of a very big dataset?

Attached you can find an extract of the greater area. After the conversion with osm2pgrouting I found it with the following query: select * from ways where length_m is NULL;

The command for importing it: osm2pgrouting --f nulldata2.osm --conf osm2pgrouting/mapconfig.xml --dbname postgres --username postgres --chunk 10000000 --clean

I'm using osm2pgrouting Version 2.3.6 and psql (9.6.7)

You must remove the extension ".txt" from the file name.

nulldata2.osm.txt

[cayetanobv]

Hi @DrDanke ,

I've reviewed your dataset and your problem is you have maxspeed_backward = 0 and maxspeed_forward = 0. You can not have cost_s and reverse_cost_s (and length_m) with a explicit zero value for maxspeed.

Problematic rows:

----------------
gid               | 737
osm_id            | 40804476
tag_id            | 110
length            | 0.000699683590426257
length_m          | [NULL]
name              | Silcherstraße
source            | 230
target            | 423
source_osm        | 496125020
target_osm        | 496125035
cost              | 0.000699683590426257
reverse_cost      | 0.000699683590426257
cost_s            | [NULL]
reverse_cost_s    | [NULL]
rule              | [NULL]
one_way           | 0
oneway            | UNKNOWN
x1                | 9.4450801
y1                | 48.7455901
x2                | 9.4445783
y2                | 48.746072
maxspeed_forward  | 0
maxspeed_backward | 0
priority          | 0
the_geom          | 0102000020E6100000050000006933F389E1E3224056DC137F6F5F484030FC96EFCFE322409B7C6940735F48409D2E8B89CDE322407DBFE2BA735F484057EA5910CAE322405FA0FF79755F4840C60556C49FE32240
0C3B8C497F5F4840
-[ RECORD 2 ]-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------
gid               | 1122
osm_id            | 40804475
tag_id            | 110
length            | 0.000730832545798908
length_m          | [NULL]
name              | Hauffstraße
source            | 346
target            | 634
source_osm        | 496125015
target_osm        | 491543927
cost              | 0.000730832545798908
reverse_cost      | 0.000730832545798908
cost_s            | [NULL]
reverse_cost_s    | [NULL]
rule              | [NULL]
one_way           | 0
oneway            | UNKNOWN
x1                | 9.4455758
y1                | 48.7458759
x2                | 9.4450008
y2                | 48.746327
maxspeed_forward  | 0
maxspeed_backward | 0
priority          | 0
the_geom          | 0102000020E61000000200000030C7E18222E42240F2AF8ADC785F48403F631525D7E32240978FA4A4875F4840

You can see that if you use --attributes flag with osm2pgrouting.

These ways in original OSM file:

<way id="40804476">
        <nd ref="496125020"/>
        <nd ref="496125029"/>
        <nd ref="496125021"/>
        <nd ref="496125023"/>
        <nd ref="496125035"/>
        <tag k="highway" v="residential"/>
        <tag k="lit" v="yes"/>
        <tag k="maxspeed" v="0"/>
        <tag k="maxspeed:type" v="sign"/>
        <tag k="name" v="Silcherstraße"/>
        <tag k="surface" v="asphalt"/>
    </way>

<way id="40804475">
        <nd ref="496125015"/>
        <nd ref="491543927"/>
        <tag k="highway" v="residential"/>
        <tag k="lit" v="yes"/>
        <tag k="maxspeed" v="0"/>
        <tag k="maxspeed:type" v="sign"/>
        <tag k="name" v="Hauffstraße"/>
        <tag k="surface" v="asphalt"/>
    </way>

[DrDanke]

Thanks, so far it's understandable, but I find it a bit strange that 2 out of xxx million records don't contain these values, since this is from Geofabrik without any modifications from my side.

1) How can this happen? 2) How can I go around it? Either removing or fixing these values?

[cayetanobv]

Hi @DrDanke ,

I answer your questions:

1. How can this happen?

   • It is a OSM error. OSM editors community is big so it is a very normal issue to find some fails (2 ways with this fail in Germany is a low rate ;) ).

2. How can I go around it? Either removing or fixing these values?

   • If you don't want to download dataset for Germany again you can compute cost_s and reverse_cost_s for these ways at DB using SQL. First you need to compute length_m (unit is meters):

     update yourschemaname.ways
     set length_m = ST_length(geography(ST_Transform(the_geom, 4326))) 
     where length_m is null;

   • Compute with another update statement the cost_s and reverse_cost_s (unit is seconds). You need to use a max speed for these ways (you can use 50 km/h if they are urban streets).
   • You should fix this error directly in OSM so next time you download data and run osm2pgrouting you will find the error fixed (Geofabrik updates downloadable files all days).