2

I'd like to extract certain data from OSM for a whole country (Germany). The data I'd like to obtain are POI's, but obviously not all of them. I'd like to filter only the supermarkets for example and transfer the directly into a PostgreSQL database.

So far I understood that I can archive that for example with the Overpass API (via Overpass Turbo). I already tried two options:

Overpass-Turbo (browser-based)

QuickOSM-Plugin for QGIS

In Overpass Turbo I filtered through the query-wizard the POIs (shop=supermarket) and set a bounding box fitting Germany inside. It worked only once though (when I chose only nodes and due to access limitations...), but I got also POIS around Germany (as the BBOX doesn't match with the country border). Therefore I tried to use the area-tags (addr:country=DE) to filter only supermarkets in Germany, but then some Points where left out because missing country-tags... anyways when choosing nodes, relations and ways, the query collapses after timeout.

Then I tried the QuickOSM-Plugin for QGIS using the area-code (3600051477) to filter only the supermarkets inside Germany. Same thing – the area seems too big to rum the query successfully. After 180 seconds (max. timeout I guess…) the query aborts. Therefore I tried the same query with sub-regions (like German provinces, for e.g. Berlin, Bayern, etc.) and it worked.

I mean do several queries for each province in a row to fetch the data and merge it after all together, but there should be a more convenient way.

I downloaded the germany osm.pbf file from Geofrabrik and transferred it with osm2pgsql into my PostgreSQL database. It's a huge file (3 GB- takes a lot of disk-space!) and needed several hours to transfer (even with cached 16 GB ram…) and I have now loads of data in my database, which I actually don't need. Altogether not the most satisfying solution as well.

I heard that I could prefilter the .osm data with osmosis before transferring it with osm2pgsql.

Do I have to download the whole dataset .osm.pbf, filter it with osmosis and transfer it with osm2pgsql or is there any other solution, maybe even regarding overpass that I do not know about?

It would be so much more efficient to download only the data I actually need!

  • Download the zipped shapefiles from download.geofabrik.de/europe/germany.html and use the poi files and filter by type. PGadmin has a shapefile loader for postgres/postgis. – Mapperz Nov 8 '19 at 15:27
  • But then I would have to download 16 zipped shapefiles (for each province) and load them seperately into pgAdmin... I thought there would be a more automatised solution for the whole country... – Philippo Storino Nov 8 '19 at 16:02

Your Answer

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

Browse other questions tagged or ask your own question.