1

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?

6
  • What makes you believe that nodes should have tags? From wiki.openstreetmap.org/wiki/Osm2pgsql#Import_style Tag: the tag to match on. If the fourth column is linear or polygon, a column for this tag will be created in each of the point, line, polygon, and road tables.
    – user30184
    Aug 15 '18 at 14:42
  • 1
    Like I said, I am calling osm2pgsql from within a tool called GridKit which is supposed to compute a power grid structure from the OSM data. What makes me believe that there should be a column "tags" inside the nodes table is the fact, that some of the SQL queries executed by GridKit require that column. See github.com/bdw/GridKit/blob/master/src/prepare-tables.sql, line 150. This query refers to ""n.tags", where n = planet_osm_nodes. Aug 16 '18 at 6:58
  • If I read it right n.tags appear always as a part of hstore(n.tags).
    – user30184
    Aug 16 '18 at 7:12
  • It is hard to find information about the database schema but obviously nodes.tags has been there gis.stackexchange.com/questions/133149/…. Perhaps it is dropped for some reason in later osm2pgsql versions. Do you have the hstore field "tags" in other tables?
    – user30184
    Aug 16 '18 at 7:38
  • Could you add the structure of the planet_osm_nodes table that you have now, including column names and their datatypes?
    – user30184
    Aug 16 '18 at 8:11
3

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.

1

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«

1

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

0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.