hstore not importing some tags? #2192

StyXman · 2024-06-02T12:11:19Z

StyXman
Jun 2, 2024

I'm not 100% sure this is a bug, so I start this as a discussion first.

I have a osm-carto fork where I specialize the rendering to my personal needs. The latest change I wanted to do was to not render the maritime borders, so I modified the country border query to this:

        (SELECT
            way,
            tags->'maritime' as maritime,
            admin_level
          FROM planet_osm_roads
          WHERE boundary = 'administrative'
            -- was:         IN ('2', '3')
            AND admin_level = '2'
            AND osm_id < 0
          -- ORDER BY admin_level DESC
        ) AS admin_low_zoom

just to find that maritime is not in the tags:

mdione@ioniq:~/src/projects/elevation/data/osm$ osmium show -f debug monaco-latest.osm.pbf | grep --color=no -E '(admin_level|*maritime")' | grep -B 1 maritime
grep: warning: * at start of expression
    "admin_level" = "10"
    "office"     = "maritime"
--
    "admin_level" = "10"
    "museum"          = "maritime"
--
    "admin_level"    = "2"
    "maritime"       = "yes"
--
    "admin_level"  = "2"
    "boundary"     = "maritime"

(granted, one has maritime=yes and the other border=maritime, but bare with me).

I import that .pbf:

osm2pgsql --create --port 5432 --database mc --cache 0 --number-processes 8 --verbose --slim --flat-nodes /home/mdione/src/projects/elevation/data/osm/nodes.cache --hstore --multi-geometry --style /home/mdione/src/projects/elevation/osm-carto/openstreetmap-carto.style --tag-transform-script /home/mdione/src/projects/elevation/osm-carto/openstreetmap-carto.lua monaco-latest.osm.pbf

and check if it imported:

mdione@ioniq:~/src/projects/elevation$ psql -d mc
psql (16.3 (Debian 16.3-1))
Type "help" for help.

mc=# SELECT
            tags->'maritime' as maritime,
            admin_level
          FROM planet_osm_roads
          WHERE boundary = 'administrative'
            -- was:         IN ('2', '3')
            AND admin_level = '2'
            AND osm_id < 0;
 maritime | admin_level 
----------+-------------
          | 2
          | 2
          | 2
          | 2
(4 rows)

Am I doing something wrong or is this a bug?

Hint: I made no changes to openstreetmap-carto.lua, so this is not being stored as a column:

mc=# SELECT
            maritime,                    
            admin_level
          FROM planet_osm_roads
          WHERE boundary = 'administrative'
            -- was:         IN ('2', '3')
            AND admin_level = '2'
            AND osm_id < 0;
ERROR:  column "maritime" does not exist
LINE 2:             maritime,
                    ^

StyXman · 2024-06-02T12:20:12Z

StyXman
Jun 2, 2024
Author

Nor it's stored as boundary:

mc=# SELECT
            tags->boundary as boundary, tags->'maritime' as maritime,
            admin_level
          FROM planet_osm_roads
          WHERE boundary = 'administrative'
            -- was:         IN ('2', '3')
            AND admin_level = '2'
            AND osm_id < 0;
 boundary | maritime | admin_level 
----------+----------+-------------
          |          | 2
          |          | 2
          |          | 2
          |          | 2
(4 rows)

and why do I get 4 rows?

4 replies

StyXman Jun 2, 2024
Author

Answer: every linestring is imported twice! WTF?

mc=# SELECT ST_AsText(way),
          FROM planet_osm_roads
          WHERE boundary = 'administrative'
            AND admin_level = '2'
            AND osm_id < 0;
 LINESTRING(828124.7276440877 5426682.842113642,828044.0210132627 5426740.121963693,828042.3734847989 5426741.863326018,828041.1823662475 5426743.9437150685,828040.1693588811 5426746.239848712,828039.5237058346 5426747.673006246,828038.5774901628 5426751.756735914,82803>
 LINESTRING(828124.7276440877 5426682.842113642,828044.0210132627 5426740.121963693,828042.3734847989 5426741.863326018,828041.1823662475 5426743.9437150685,828040.1693588811 5426746.239848712,828039.5237058346 5426747.673006246,828038.5774901628 5426751.756735914,82803>
 LINESTRING(825178.0673269426 5424477.060268598,825175.8298051775 5424473.008358785,825176.9207361874 5424455.3679356715,825168.4047951417 5424443.96715614,825143.2131943752 5424425.833783129,825137.5025044975 5424421.319703462,825149.647460943 5424405.589770743,825163.>
 LINESTRING(825178.0673269426 5424477.060268598,825175.8298051775 5424473.008358785,825176.9207361874 5424455.3679356715,825168.4047951417 5424443.96715614,825143.2131943752 5424425.833783129,825137.5025044975 5424421.319703462,825149.647460943 5424405.589770743,825163.>
(4 rows)

StyXman Jun 2, 2024
Author

Ok, here's a better query, just a rewrite of the one above, and I can confirm some data is duplicated:

select osm_id, boundary, admin_level, tags->'maritime' as maritime, ST_AsText(way) from planet_osm_roads where boundary = 'administrative' and admin_level = '2';
  osm_id   |    boundary    | admin_level | maritime |                                                                                                                                                                                                                        >
-----------+----------------+-------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
 176533407 | administrative | 2           |          | LINESTRING(825174.7388741678 5424005.540838931,825175.3845272146 5424001.997524651,825173.4253041764 5424000.811284956,825158.4528326647 5423995.711996581,825150.916503138 5423994.001963949,825142.6454649721 5423996>
  37811853 | administrative | 2           |          | LINESTRING(825178.0673269426 5424477.060268598,825175.8298051775 5424473.008358785,825176.9207361874 5424455.3679356715,825168.4047951417 5424443.96715614,825143.2131943752 5424425.833783129,825137.5025044975 542442>
  -1124039 | administrative | 2           |          | LINESTRING(828124.7276440877 5426682.842113642,828044.0210132627 5426740.121963693,828042.3734847989 5426741.863326018,828041.1823662475 5426743.9437150685,828040.1693588811 5426746.239848712,828039.5237058346 54267>
  -2202162 | administrative | 2           |          | LINESTRING(828124.7276440877 5426682.842113642,828044.0210132627 5426740.121963693,828042.3734847989 5426741.863326018,828041.1823662475 5426743.9437150685,828040.1693588811 5426746.239848712,828039.5237058346 54267>
  37794471 | administrative | 2           |          | LINESTRING(826965.1904321377 5426221.549643595,826960.5706732698 5426207.850680935,826957.8433457454 5426208.405419183,826946.7336605643 5426179.898080082,826946.1993270084 5426161.345242933,826949.4832519869 542615>
 212810311 | administrative | 2           |          | LINESTRING(825104.6966505608 5423460.302399664,825137.4245808539 5423436.656051399,825151.328385254 5423425.749482522,825244.7477019278 5423356.489990072,825304.158914164 5423314.497054519,825502.0515729473 5423178.>
 398372186 | administrative | 2           |          | LINESTRING(827870.2512881343 5427130.73471893,827857.6276578784 5427082.54484401,827921.1020315287 5427033.322698925,827949.2102029539 5427022.381055523,827814.0126813855 5426880.5415459685,827769.3179058322 5426834>
  37794470 | administrative | 2           |          | LINESTRING(828040.1693588811 5426746.239848712,828039.5237058346 5426747.673006246,828038.5774901628 5426751.756735914,828038.1099483015 5426756.426059223,828038.2880594868 5426761.603925461,828039.5459697328 542676>
 770774507 | administrative | 2           |          | LINESTRING(828124.7276440877 5426682.842113642,828044.0210132627 5426740.121963693,828042.3734847989 5426741.863326018,828041.1823662475 5426743.9437150685,828040.1693588811 5426746.239848712)
  -2202162 | administrative | 2           |          | LINESTRING(825178.0673269426 5424477.060268598,825175.8298051775 5424473.008358785,825176.9207361874 5424455.3679356715,825168.4047951417 5424443.96715614,825143.2131943752 5424425.833783129,825137.5025044975 542442>
  -1124039 | administrative | 2           |          | LINESTRING(825178.0673269426 5424477.060268598,825175.8298051775 5424473.008358785,825176.9207361874 5424455.3679356715,825168.4047951417 5424443.96715614,825143.2131943752 5424425.833783129,825137.5025044975 542442>
  30837497 | administrative | 2           | yes      | LINESTRING(825826.2473259845 5422946.473967987,825921.0136084969 5422612.938286177,826015.7687590601 5422279.414659127,826110.5350415724 5421945.872280919,826205.3013240846 5421612.341956996,826300.056474648 5421278>
(12 rows)

As for why my thing is not matching, the original query is filtering out:

non relations (osm_id < 0); and
only boundary = 'administrative'

so in my case, I have:

a non relation with tags->'maritime => 'yes'; and
a relation that instead has boundary = 'maritime'

so that's why I don 't get anything in my query.

ImreSamu Jun 2, 2024

Ok, here's a better query, just a rewrite of the one above, and I can confirm some data is duplicated:

In OSM, the two relations/linestrings are not exactly the same,
although there may be overlaps.
It would be advisable to also output the number of points in the linestring in the select statement. ( ST_NumPoints() )

 osm_id   |    boundary    | admin_level | maritime |                                                                                                                                                                                                                        >
-----------+----------------+-------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------>
  -1124039 | administrative | 2           |          | LINESTRING(828124.7276440877 5426682.842113642,828044.0210132627 5426740.121963693,828042.3734847989 5426741.863326018,828041.1823662475 5426743.9437150685,828040.1693588811 5426746.239848712,828039.5237058346 54267>
  -2202162 | administrative | 2           |          | LINESTRING(828124.7276440877 5426682.842113642,828044.0210132627 5426740.121963693,828042.3734847989 5426741.863326018,828041.1823662475 5426743.9437150685,828040.1693588811 5426746.239848712,828039.5237058346 54267>

osm_id = -1124039 --> https://www.openstreetmap.org/relation/1124039 ( Monaco )
osm_id = -2202162 --> https://www.openstreetmap.org/relation/2202162 ( France )

StyXman Jun 5, 2024
Author

Mystery solved. Thanks.

StyXman · 2024-11-02T11:37:53Z

StyXman
Nov 2, 2024
Author

Ok, I know this was closed, but the only answer I got was why I was getting two borders instead of one.

But the original question remains: why some tags are not imported? I just found the same again today.

While trying to report another instance of this, I got a question about osm_id: are they stable?

Exhibit A: https://www.openstreetmap.org/way/983355274

Exhibit B:

europe=# select tags from planet_osm_roads where osm_id = 983355274;
 tags 
------
(0 rows)

This way is 3yo and I reimported Europe recently, and been updating it daily.

10 replies

StyXman Nov 2, 2024
Author

I have another example:

-- freshly imported ile-de-france-latest.osm.pbf
paris=# SELECT
            COALESCE(tags->'colour', tags->'color', 'red') as color
          FROM planet_osm_line
          WHERE osm_id = -3328716; -- Ligne 11 subway
  color
---------
 #d2d200
 #d2d200
(2 rows)

vs.

-- Europe imported on 2024-09-30T20:21:15Z updated daily
europe=# SELECT
            COALESCE(tags->'colour', tags->'color', 'red') as color
          FROM planet_osm_line
          WHERE osm_id = -3328716;
 color
-------
 red
 red
(2 rows)

I still think something's fishy.

joto Nov 3, 2024
Maintainer

Sorry, you lost me. The issues seem to be jumping around here. If I understand correctly you are saying that some data is not updated correctly. But without a clear repeatable minimal case, nobody is going to be able to track this down. So: We need an input file, a change file, all config files and the command line used that clearly shows the problem.

And another thing: The pgsql output is now deprecated. Even if there is something wrong with it, it is unlikely this will get much attention. Please switch to the flex output.

StyXman Nov 3, 2024
Author

osm-carto still uses the pgsql output, and my style is a fork of that one, so for now I'm forced to keep using it. There are efforts to use flex, but as far as my low attention span can tell, it's not done yet. Here:

gravitystorm/openstreetmap-carto#4112

As for a reproducer, I just wrote a script to count subway routes with a colour tag before and after updating the Paris region. I know for a fact there are 190 routes and all have the tag, and I hope no new lines are in the near future and nobody breaks them :)

Once I catch a difference, I can come up with a reproducer. This might take a while, tho. I'm closing for now.

joto Nov 3, 2024
Maintainer

Carto switched to flex last week, see gravitystorm/openstreetmap-carto#4977.

StyXman Nov 3, 2024
Author

Thanks for the heads up. I'm forking osm-carto, but I haven't been following it much, mostly for lack of time. I can keep the experiment alive while I reimport Europe, if you want.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

hstore not importing some tags? #2192

{{title}}

Replies: 2 comments 14 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

hstore not importing some tags? #2192

StyXman Jun 2, 2024

Replies: 2 comments · 14 replies

StyXman Jun 2, 2024 Author

StyXman Jun 2, 2024 Author

StyXman Jun 2, 2024 Author

ImreSamu Jun 2, 2024

StyXman Jun 5, 2024 Author

StyXman Nov 2, 2024 Author

StyXman Nov 2, 2024 Author

joto Nov 3, 2024 Maintainer

StyXman Nov 3, 2024 Author

joto Nov 3, 2024 Maintainer

StyXman Nov 3, 2024 Author

StyXman
Jun 2, 2024

Replies: 2 comments 14 replies

StyXman
Jun 2, 2024
Author

StyXman Jun 2, 2024
Author

StyXman Jun 2, 2024
Author

StyXman Jun 5, 2024
Author

StyXman
Nov 2, 2024
Author

StyXman Nov 2, 2024
Author

joto Nov 3, 2024
Maintainer

StyXman Nov 3, 2024
Author

joto Nov 3, 2024
Maintainer

StyXman Nov 3, 2024
Author