Lost relation when importing from OSM to PostgreSQL with ogr2ogr

Question

I am currently trying to import all administrative boundaries (countries, states, cities, etc) from OSM.

I downloaded a small file from Geofabrik (Corsica), in order to run my tests. I understand that I could run the same operation worldwide using planet-latest.osm.

First, I extracted all boundaries using the following command like

osmosis --read-xml file="corse-latest.osm" --tf accept-ways boundary=administrative --used-node --write-xml out1.osm

Then, I import everything into PostgreSQL using

ogr2ogr -f PostgreSQL PG:"[connection details]" out1.osm -lco COLUMN_TYPES=other_tags=hstore

Which fills up the multipolygon table with the list of cities, and that is exactly what I wanted.

However, if I look for "Ajaccio", there is a lot of useful tags associated with the node 51341115, that do not show up in the multipolygon. Instead, they are associated to a point linked to that node ID.

In the XML file, I have been able to link that point to the relation 73283, inside of which are standing all the ways of the city, and the tags that I actually find in the multipolygon table.

The question is then the following: how can I link the multipolygon to the point in order to extract the tags, using solely the database?

I'm not specifically tied to those tools, I'll use anything that works and that spares me writing code.

Many thanks :)

Answer 1

According to the specification of the GDAL driver http://www.gdal.org/ogr/drv_osm.html, only way elements are regarded as valid members of boundary or multipolygon relations. This makes sense in order to form polygon geometries.

The only way I see to solve your problem is to load the OSM data 1:1 into postgres (that is tables for nodes, ways and relations), then look up the point members in the relation table, add the point tags from the point table to the multipolygon in the polygon table created by the ogr2ogr importer according to the osm_id. But this is not an easy job for postgis newbies.

Apart from that, you first have to go through all boundary relatiosn and see if they form closed rings. In many cases, they will not. There is no integrity test inside the OSM server for that. And border relations that consist of other relations are yet another problem ...