planet_osm_nodes missing "tags" column

Question

I'm trying to recreate a dataset produced by the SciGRID energy mapping project detailed here https://www.power.scigrid.de/. I used osm2pgsql to export German transmission line data to a postgreSQL database and I'm trying to run a Python script written by the SciGRID people that abstracts this data so that it can be turned into a .csv. Osm2pgsql creates a few tables with the prefix "planet_osm_".

The script requires the "planet_osm_nodes" table to have a "tags" column but it does not. I'm told this table used to have such a column but isn't meant for front end use so doesn't have it from osm2pgsql version 0.88 onwards. I cannot use "planet_osm_point" instead because it lacks all the "planet_osm_nodes" columns that I need (ie. "id", "lat", "lon"). Is there a way to bring the "tags" column back (perhaps with the style file or flex output?). Otherwise, are there any pre-built binaries of older versions of osm2pgsql, pre-0.88? I have no experience building from source.

Accepted Answer

Your approach is deeply flawed, not your fault, likely the programmers of whatever software you are using did not know what they were doing. planet_osm_point has osm_id which is what id is in planet_osm_nodes, and lat and lon are contained in the way column. If you have used the -l flag (ell) on import, you can extract IDs and lat and lon like this:

SELECT
   osm_id as id,
   st_x(way) as lon,
   st_y(way) as lat
FROM
   planet_osm_point;

The planet_osm_point table will not contain all nodes, just those selected based on the style file you specify (or the default osm2pgsql.style if you do not specify any).

If you want to export all nodes, then the process involving osm2pgsql is unnecessary; you can use the command-line tool osmium to convert an .osm.pbf file to a text-based representation called the "opl" format, and that can be converted to CSV with a few trivial transformations.

Do not try to revive decades-old osm2pgsql versions, it's not worth the hassle.

planet_osm_nodes missing "tags" column

Follow this question

Related questions