Table openstreetmap.osm_nodes is missing column 'tags'

[IlkaCu]

I am working on integrating the abstraction of HVMV substations into our workflow. The script taken from the old data processing (https://github.com/openego/data_processing/blob/master/dataprocessing/sql_snippets/ego_dp_substation_hvmv.sql#L102-L109) accesses column 'tags' from table 'openstreetmap.osm_deu_nodes'. In eGon-data the equivalent table 'openstreetmap.osm_nodes' is missing the column 'tags'. @gplssm: Can you help?

[gplssm]

Maybe ;-)

Honestly, I don't have too much a clue about how osm2pgsql imports OSM into the database in detail. I more or less copied the code from openego/dataprocessing...

I think as possible solution is related to the definition of the stylefile https://osm2pgsql.org/doc/manual.html#preparing-the-database in combination with the use of --hstore https://osm2pgsql.org/doc/manual.html#use-of-hstore

Right now, we'using --hstore-all flag for osm2pgsql. According to the documentation this should actually result in all data is being stored in the "tags" column even if a column is already created for this tag.

--hstore-all or -j adds all tags to a hstore column called tags, even if they’re already stored in a conventional column. With the standard stylesheet this would result in tags like highway appearing in conventional column and the hstore column while tags not in the style like name:en or lanes:forward would appear only in the hstore column.

In order to get an idea how to change the style such that the columns "tags" appears for osm_nodes, I would like to know for which data you're actually looking for.

[IlkaCu]

The corresponding table created by our former data processing contains a wide variety of data in its 'tags' column: https://openenergy-platform.org/dataedit/view/openstreetmap/osm_deu_nodes I am mostly interested in power and voltage tags.

[gplssm]

I checked for the difference between what we did in openego and what we do now regarding the import of OSM data. It's the exactly that. The same style file and the same arguments used for `osm2pgsql'.

Interestingly, someone using GridKit was facing the same problem: https://gis.stackexchange.com/questions/292860/column-tags-is-missing-in-postgresql-table-created-with-osm2pgsql There, it was suggested from multiple persons to use _points table instead of _nodes. The first is a subset of the latter

planet_osm_point is a subset of the node table, containing only the points with attributes, but not the vertices. Furthermore, it has a geometry column to use with GIS applications. That's why a postgis connection only shows this table, unless you allow for geometryless tables too. Spatial indices are built on this table, not the node one.

As suggested in https://gis.stackexchange.com/questions/133149/planet-osm-point-versus-planet-osm-nodes-for-querying-places The only downside would be that vertices are not included there. I do not really know what this is. Do you need them?

You might ask why we have been able to use the tags column in openego? The last comment in the first link says that the import behavior was changed in osm2pgsql >0.88.1

From some version of osm2pgsql around 2016 on, it doesn't create a column 'tags' in the 'planet_osm_nodes' table anymore. (osm2pgsql-0.88.1 still does it)

Would it be an option to use the _points table instead of the _nodes table?

[gplssm]

In addition, just because I wasn't sure if I understood osm2pgsql docs and dicussion on StackOverflow correct, I tested with --extra-attributes. Same same, not column tags for osm_nodes.

[IlkaCu]

What a pity! I will check how using _points table instead of _nodes would influence the downstream operations. Thank you for your investigations.

[IlkaCu]

I am trying to adjust the code to make use of the _points table, but I stumbled over the data types of different tags columns in the osm tables. openstreetmap.osm_ways.tags is of type text[] but openstreetmap.osm_point.tags is of type hstore @gplssm: Do you have an idea why this happens?

[gplssm]

As suggested in the last comment of https://gis.stackexchange.com/questions/292860/column-tags-is-missing-in-postgresql-table-created-with-osm2pgsql it is recommended to use

• osm_point
• osm_line
• osm_roads
• osm_polygons

only. All these tables have a "tags" column of type hstore.

The other tables are intermediate table created during importing with osm2pgsql as far as I understood. And thus, I guess, no hstore column is created.

[IlkaCu]

As suggested I retrieved the tags-information from table osm_point.