Hello from Trans Alpine Germania! Alas i only speak Germanic languages (English & German). Please use your machine translation tool of choice. 
Do you know about Postpass? It’s a SQL interface to OSM data (Overpass + PostgreSQL). Someone asked me about queries for this project. While Geofabrk’s TagInfor for Italy can tell you how many hydrants there are, that’s all a little boring. 
It’s easy to count the number of things in an admin area. But some are larger than others, and some have more “base mapping” than others. So, for each admin (admin_level=4) area, we also calculate it’s area. The number of buildings (& their total) area are also calculated, to get a rough number for “base mapping”.
Here’s some maps that you can generate yourself.
Fire hydrants per million buildings.
view on overpass ultra.
AEDs per million buildings:
view map on overpass ultra
Each of the admin areas has:
nameadmin_area_km2: Area of that admin in km²building_count: Number of building’s in this areabuilding_area_km2: total area of all buildings in this area (in km²)aed_count: Number of AEDs in this areahydrant_count: number of fire hydrants in this areahydrant_per_admin_km2: number of hydrants per admin area km².hydrant_per_mbuilding: number of hydrants per million buildings (megabuilding!)hydrant_per_building_km2: Hydrants per building km²aed_per_admin_km2: AEDs per admin km².aed_per_mbuilding: AEDs per million buildings.aed_per_building_km2: AEDs per total building km²building_km2_per_admin_km2: Area of all buildings divided by area of admin
Show me the giant SQL query
Put this into Overpass Turbo, with this line first: {{data:sql,server=https://postpass.geofabrik.de/api/0.2/}}. For Overpass Ultra, put this first:
---
type: postpass
style:
layers:
- type: fill
fill-opacity: 0.5
fill-color:
- "interpolate-lab"
- ["linear"]
- ["get", "hydrant_per_mbuilding"]
- 0.0
- "#a35"
- 5000
- "#9d5"
- type: line
line-color: '#000'
line-width: 2
line-opacity: 0.5
- type: symbol
text-color: black
text-halo-color: "white"
text-halo-width: 2
text-field: ["concat", [ "get", "admin_name" ], "\n", ["round", ["get", "hydrant_per_mbuilding"]]]
---
SQL query:
with
-- Prepare geometry for Italy
italy as materialized (select geom it_geom from postpass_polygon where tags->>'boundary' = 'administrative' and tags->>'ISO3166-1' = 'IT')
-- Get all the admin areas
,admins as materialized (select osm_id admin_osm_id, tags->>'name' admin_name, geom admin_geom, ST_Area(geom::geography)/1000000 admin_area_km2 from postpass_polygon join italy ON (geom && it_geom AND ST_Contains(it_geom, ST_Centroid(geom))) where tags->>'boundary' = 'administrative' AND osm_type = 'R'
-- ↓ The admin level we're getting
and tags->>'admin_level' = '4'
)
-- Buildings
,buildings as materialized (select count(*) building_count, sum(st_Area(b.geom::geography))/1000000 as building_area_km2, admin_osm_id from postpass_pointlinepolygon b join admins ON (b.geom && admin_geom AND ST_Intersects(b.geom, admin_geom)) where tags?'building' group by admin_osm_id )
-- Hydrants
,hydrants as materialized (select count(*) hydrant_count, admin_osm_id from postpass_pointlinepolygon fh join admins ON (fh.geom && admin_geom AND ST_Intersects(fh.geom, admin_geom)) where tags->>'emergency' = 'fire_hydrant' group by admin_osm_id )
-- AEDs
,aeds as materialized (select count(*) aed_count, admin_osm_id from postpass_pointlinepolygon aed join admins ON (aed.geom && admin_geom AND ST_Intersects(aed.geom, admin_geom)) where tags->>'emergency' = 'defibrillator' group by admin_osm_id )
-- assemble results
select
admin_name,
building_count, aed_count, hydrant_count,
hydrant_count/admin_area_km2 hydrant_per_admin_km2,
hydrant_count/(building_count::numeric/1000000.0) hydrant_per_mbuilding,
hydrant_count/building_area_km2 hydrant_per_building_km2,
aed_count/admin_area_km2 aed_per_admin_km2,
aed_count/(building_count::numeric/1000000.0) aed_per_mbuilding,
aed_count/building_area_km2 aed_per_building_km2,
building_area_km2/admin_area_km2 building_km2_per_admin_km2
from admins join buildings USING (admin_osm_id) JOIN hydrants USING (admin_osm_id) JOIN aeds USING (admin_osm_id)
Have fun. Feel free to modify the maplibre
e AlessioMela sul gruppo Telegram di OSM Emilia-Romagna: