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 ?

asked 26 Nov, 17:00

Detektiv%20Mittens's gravatar image

Detektiv Mit...
262
accept rate: 0%

edited 26 Nov, 19:22

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
71.3k846451113


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, 17:38

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
71.3k846451113
accept rate: 24%

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, 18:04) Detektiv Mit...
Your answer
toggle preview

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:

×125
×82
×29
×10

question asked: 26 Nov, 17:00

question was seen: 103 times

last updated: 28 Nov, 18:04

powered by OSQA