Gather amenities sorted by county borders

Question

Hello everyone,

my goal is to gather the number of certain amenitys (for example: amenity=bar) in Germany, but sorted in a csv by the county border (admin_level=6) they belong to.

This works fine for one county border in Overpass Turbo:

[out:csv(::type,::id, name,"addr:postcode", "addr:city")];
area[admin_level=6]["name"="Bautzen"];  
( 
   node["amenity"="bar"](area);
   way["amenity"="bar"](area);
   rel["amenity"="bar"](area);
);
out body;
>;
out skel qt;

But Germany has 403 of them, so inserting each of them in a new query would be way to much effort. On the other hand I don't now how to match the amenitys, when I do a seach for the whole of Germany. For example, this isn't possible via the postcode, because not every amenity have one assigned.

Is there a good and faster way to gather these informations with the right assignment ?

Accepted Answer

3

Since it looks like you want to make many of these queries, don't hammer someone else's server with it - do it yourself. Load the OSM data for Germany into a PostGIS database with osm2pgsql, then run this query:

SELECT 
  county.name, count(*)
FROM 
  planet_osm_point poi, 
  planet_osm_polygon county
WHERE 
  poi.amenity='bar'
  AND county.boundary='administrative'
  AND county.admin_level='6'
  AND st_contains(county.way, poi.way)
GROUP BY county.name;

This will only give you the point-shaped bars, and you have to repeat this with "planet_osm_polygon poi" instead of "planet_osm_point poi" to count the polygon-shaped ones. (You can also count both at once but that makes the query harder to understand.) You can even count a large number of different amenities in one go:

SELECT 
  poi.amenity, county.name, count(*)
FROM 
  planet_osm_point poi, 
  planet_osm_polygon county
WHERE 
  poi.amenity in ('bar', 'restaurant', 'pub')
  AND county.boundary='administrative'
  AND county.admin_level='6'
  AND st_contains(county.way, poi.way)
GROUP BY poi.amenity, county.name;

(BTW, note that these queries will omit Hamburg and Berlin due to lack of an adminlevel 6 boundary.)

If you are interested in OSM statistics then using your own PostGIS and learning a little SQL is really useful.

permanent link

answered 26 Nov '19, 17:38

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

Thanks for your detailed answer. I hoped that there was a more simpler way, but I will try to fiddle around with PostGis and see how far I get.

(28 Nov '19, 18:04) Detektiv Mit...

Hey I have some further questions, if you don't mind and if it is still fitting for this thread. I successfully build the database and run different queries, like you showed me.

I am wondering now about the accuracies of the data. When I am for example run a query for ‘amenity=prison’ I got as a result for planet_osm_point poi: 34 and for planet_osm_polygon county: 285 in total. The official statistic shows the number of 179 prisons in whole Germany.

I am wondering why the difference is so large? I am aware that OSM-Data isn’t often complete, but then I would have expected the number to be smaller than the official one. Do you always have to expect some points to be counted twice? And is normally legit to combine ‘point’ and ‘polygon’ or does it become more inaccurate then? Thanks for your help!

(13 Dec '19, 16:43) Detektiv Mit...

1

Most likely you're hitting issues where there's a prison complex consisting of 10 buildings and each is tagged, individually, as amenity=prison. There are various ways to address this problem. One is to use a suitable "clustering" function in PostGIS, and tell it to combine into one all "prisons" that are less than, say, 500m of each other.

For example, there are four prisons in OSM here: https://www.openstreetmap.org/#map=17/52.54077/13.32037 when in reality it's either one or two depending on what you count.

(13 Dec '19, 18:43) Frederik Ramm ♦

Gather amenities sorted by county borders

Follow this question

Related questions