Column "tags" is missing in PostgreSQL table created with osm2pgsql

Question

I downloaded osm2pgsql for Windows from AppVeyor (https://ci.appveyor.com/api/projects/openstreetmap/osm2pgsql/artifacts/osm2pgsql_Release_x64.zip?tag=0.96.0&job=Environment%3A%20arch%3Dx64) and osm-data from Geofabrik (http://download.geofabrik.de/europe/germany/bayern-latest.osm.pbf).

I tried to use osm2pgsql to transfer that data to a local PostgreSQL database by running

osm2pgsql.exe -d <my_database> -c -k -s -S <my_stylefile> -P <my_port> -U <my_username> -H <my_hostname> bayern-latest.osm.pbf

Prior to running this command I made sure to store my password in the PGPASS environment variable.

My PostgreSQL database has the following extensions: hstore, plpgsql, postgis. I used the slim-option (-s) when calling osm2pgsql because I need access to the temporary data stored in the planet_osm_nodes table.

The tags specified in the style file are:

# OsmType  Tag          DataType     Flags
node,way   power        text         polygon
node,way   cables       text         linear
node,way   voltage      text         linear
node,way   wires        text         linear 

After the successful execution of osm2pgsql, each of the tables "planet_osm_line", "planet_osm_point" and "planet_osm_polygon" inside my PostgreSQL database contain the columns "osm_id", "power", "cables", "voltage", "wires", "tags" and "way". The table "planet_osm_ways" contains the columns "id", "nodes", "tags".

My problem is that the table "planet_osm_nodes" only has the three columns "id", "lat", "lon" but no column "tags".

I am calling osm2pgsql from within a tool called GridKit (https://github.com/bdw/GridKit) and that tool absolutely needs the "tags" column to further process the data.

Do you know why this column is missing in the table "planet_osm_nodes" and what I can do about it?

Answer 1

Tables planet_osm_nodes, planet_osm_ways, and planet_osm_rels are tracking tables which are made for the system, not for the end user. Those tables are not supposed to contain tags and they would be useless for the end user.

For the needs of end users the OSM data are converted into tables planet_osm_point, planet_osm_line, planet_osm_roads, and planet_osm_polygon. Those tables contain also the attribute data from the tags.

Read the documentation from https://github.com/openstreetmap/osm2pgsql and https://wiki.openstreetmap.org/wiki/Osm2pgsql#Import_style.

Answer 2

the complete data of nodes including tags can be found in planet_osm_point:

\d planet_osm_point Tabelle »public.planet_osm_point« Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert --------------------+----------------------+--------------+---------------+------------- osm_id | bigint | | | access | text | | | addr:housename | text | | | addr:housenumber | text | | | addr:interpolation | text | | | admin_level | text | | | aerialway | text | | | aeroway | text | | | amenity | text | | | area | text | | | barrier | text | | | bicycle | text | | | brand | text | | | bridge | text | | | boundary | text | | | building | text | | | capital | text | | | construction | text | | | covered | text | | | culvert | text | | | cutting | text | | | denomination | text | | | disused | text | | | ele | text | | | embankment | text | | | foot | text | | | generator:source | text | | | harbour | text | | | highway | text | | | historic | text | | | horse | text | | | intermittent | text | | | junction | text | | | landuse | text | | | layer | text | | | leisure | text | | | lock | text | | | man_made | text | | | military | text | | | motorcar | text | | | name | text | | | natural | text | | | office | text | | | oneway | text | | | operator | text | | | place | text | | | population | text | | | power | text | | | power_source | text | | | public_transport | text | | | railway | text | | | ref | text | | | religion | text | | | route | text | | | service | text | | | shop | text | | | sport | text | | | surface | text | | | toll | text | | | tourism | text | | | tower:type | text | | | tunnel | text | | | water | text | | | waterway | text | | | wetland | text | | | width | text | | | wood | text | | | z_order | integer | | | addr:postcode | text | | | maxspeed | text | | | maxspeed:forward | text | | | maxspeed:backward | text | | | name:de | text | | | note | text | | | traffic_sign | text | | | vending_machine | text | | | tags | hstore | | | way | geometry(Point,4326) | | | Indexe: "planet_osm_point_index" gist (way), Tablespace »planet3_is1« "planet_osm_point_pkey" btree (osm_id), Tablespace »planet3_is1« "planet_osm_point_tags_index" gin (tags), Tablespace »planet3_is1« Tablespace: »planet3_ts1«

Answer 3

I had the problem. In past other people of my company used this (bad :() SQL:

select pn.id, pn.tags, pn.lat, pn.lon 
from planet_nodes pn 
where pn.tags is not null and array_to_string(pn.tags,',') like '%LocationCode%'

After my last import in table planet_nodes column tags is missing and the SQL don't working.

The solution is:

Don't use planet_node. This table is not designed for public use. The correct table is planet_point. It's possible to convert the select:

select osm_id, hstore_to_array(tags) tags, 
cast(ROUND(ROUND(CAST(ST_X(way) AS numeric),2) * 100) as int4) lon, 
cast(ROUND(ROUND(CAST(ST_Y(way) AS numeric),2) * 100) as int4) lat
from planet_point 
where array_to_string(akeys(tags),',') like '%LocationCode%'

It is fully compatible and works very well.

For detailed information about the datamodel generated by osm2pgsql see: https://osm2pgsql.org/doc/manual.html#database-structure

Answer 4

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)

As someone stated before, one is probably rather supposed to use the tables created without the --slim option (..._point , ..._line , ..._roads , ..._polygons).