NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

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.

asked 21 Jul '21, 23:19

kev_7's gravatar image

kev_7
31225
accept rate: 0%


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.

permanent link

answered 22 Jul '21, 01:06

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

edited 22 Jul '21, 08:34

Jochen%20Topf's gravatar image

Jochen Topf
5.2k55074

1

Thank you! And is there a way to reveal the tags column for planet_osm_point? Currently this table only has "osm_id", "power", "cables", "voltage", "wires" and "way".

(22 Jul '21, 18:06) kev_7
1

Yes and no. If you create extension hstore in your postgres database and then use the --hstore flag on osm2pgsql, you will get a tags column. But it will be different from the tags column that used to be in planet_osm_nodes; the latter was a string array with 2 entries for every tag - first the key, then the value, then the next key, etc., whereas the the tags column in planet_osm_point will be "hstore" type column that represents a proper key-value map.

(23 Jul '21, 00:26) Frederik Ramm ♦

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×263
×205
×191
×4
×3

question asked: 21 Jul '21, 23:19

question was seen: 1,766 times

last updated: 23 Jul '21, 00:26

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum