Why not ask your question on the new OpenStreetMap Community Forum?


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 oviously not all of them. I'd like to filter only the supermarkets for example and transfer them directly into a postgres database.

So far I understood that I can archieve 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 I the POIs (shop=supermarket) and set a bounding box fitting germany inside. It worked only once though (when i chosed only nodes and due to access limitations...), but I got also POIS around Germany (as the bbox doesn't match with the country border). Therfore 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 chosing nodes, relations and ways, the query collapses after timeout.

The 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 I could 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 convinient way.

I downloaded the germany osm.pbf file from Geofrabrik and transfered it with osm2pgsql into my postgres database. Its 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 dont need. Alltogether not the most satifiying solution as well.

I heard that I could prefilter the .osm data with osmosis before transfering it with osm2pgsql right?

But my question is:

Do I really have to download the whole dataset .osm.pbf, filter it with osmosis and transfer it with osm2pgsql?

Or is there any other solution regarding overpass that I dont know about?

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

Thanks in advance for any suggestions!

asked 08 Nov '19, 15:11

Pippo87's gravatar image

accept rate: 0%

edited 08 Nov '19, 15:48

andy%20mackey's gravatar image

andy mackey

Do I really have to download the whole dataset .osm.pbf, filter it with osmosis and transfer it with osm2pgsql?

No. For example you could pay somebody or a company to extract the data that you need for you (given that we are only talking about Germany I don't quite see where the pain is supposed to be, but OK). If it makes sense to import the data with osm2pgsql depends on what you actually want to do with the resulting data, which is what you don't mention. In any case you should think about if you actually need the original data, which will include polygons for those POIs that are mapped as areas, or if gettign everything as points would be better.

Or is there any other solution regarding overpass that I dont know about?

Probably not, the overpass servers are a shared, volunteer run resource that can't simply cater for everything people would like to do.

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


permanent link

answered 13 Nov '19, 22:42

SimonPoole's gravatar image

SimonPoole ♦
accept rate: 18%

edited 13 Nov '19, 23:14

Points would be better. Polygons are not needed, as I would use the locations in the database for further map productions later on.

(14 Nov '19, 13:56) Pippo87

Please set a manual timeout. An example:

out center;

Please do not download through Overpass Turbo, because displaying in the browser is not designed for large queries. Use wget or curl instead.

permanent link

answered 14 Nov '19, 04:24

Roland%20Olbricht's gravatar image

Roland Olbricht
accept rate: 35%

Thx! I will try that. But as a newbie in that field, as far as I understood, to use overpass-api I'd have to download a clone of the overpass database (the whole planet-file?) and start queries from there? (As it is described here: https://dev.overpass-api.de/no_frills.html#startup) Then again, I'd download mostly unneccessary data then...

(14 Nov '19, 14:04) Pippo87

You can just use the public instance. It is well within reasonable limits.

(14 Nov '19, 19:01) Roland Olbricht

With the Germany pbf file you can quickly filter out supermarkets using osmfilter. Furthermore you can convert areas to centroids using osmconvert if it suits.

As Roland says do not use Overpass-turbo for large queries. You can export your query in a form suitable for use directly with the overpass-api through the command line.

In both cases you probably need to import data to PostGiS using osm2pgsql because areas are not returned from the overpass-api.

permanent link

answered 14 Nov '19, 11:33

SK53's gravatar image

SK53 ♦
accept rate: 22%

Whats the difference betwenn osmfilter and osmosis?

(14 Nov '19, 14:07) Pippo87

osmconvert is closer to osmosis, osmfilter is for filtering (a bit like tag-filter). They are simpler to run (single executables, no dependencies), they use a proprietary format (o5m) for some actions, and are not as flexible as osmosis. However, their greater simplicity makes them highly suitable for the type of task you describe.

(14 Nov '19, 14:26) SK53 ♦
Your answer
toggle preview

Follow this question

By Email:

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



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:


question asked: 08 Nov '19, 15:11

question was seen: 2,880 times

last updated: 14 Nov '19, 19:01

powered by OSQA