Open Data Extraction Research: Underground Infrastructure Visibility

Wilmersdorfer Strasse, Charlottenburg, Berlin

Version: 1.0 Date: 2026-03-24 Authors: Ultra-Expert Panel (OSM Power Mapper, Berlin Open Data Engineer, Geospatial Detection Specialist, Urban Infrastructure Data Scientist) Target segment: ~150m near U7 Wilmersdorfer Strasse station Bounding box (WGS84): 52.5070,13.3040,52.5105,13.3085 Bounding box (EPSG:25833): 385850,5819750 to 385950,5819900 Project: be.liviu.ai — Making the Underground Visible

Concerns

• OSM underground utility data is inherently incomplete; confidence varies by utility type
• FIS-Broker WFS endpoints migrated to gdi.berlin.de after Dec 2025 shutdown; some URLs may need updating
• Leico PDF vectorization requires 4-8 hours manual effort per 150m segment
• Building-level demographics are modeled allocations from PLR-level data, not measured
• All depths are from DIN/DVGW standards, not field measurements — tagged [MODELED]
• Mapillary API requires registration and has bbox size limits (0.01 degrees)
• NBB gas network map is NOT open data — requires application with justified interest

Decisions

• Use EPSG:25833 as the alignment coordinate system for all Berlin data before converting to Three.js local coords
• Prioritize Baumkataster WFS over OSM for tree data (official, field-measured, more complete)
• Prioritize ALKIS/LOD2 over OSM for building data (official surveying data)
• Use OSM as PRIMARY source for underground indicators (manholes, hydrants, cabinets) — no alternative open source
• Tag ALL utility depths as [MODELED] until Leico or operator data replaces them
• Register for Leico as P1 priority (this week) — it is the single most comprehensive utility data source

Assumptions

• Berlin OSM coverage for manholes and hydrants is among the world's best (German OSM community is very active)
• Wilmersdorfer Strasse has good Mapillary street-level imagery coverage (major shopping street)
• The new Geoportal Berlin (gdi.berlin.de) serves the same data as the old FIS-Broker with similar WFS API patterns
• LOR Planungsraum population can be proportionally allocated to buildings using floor area as proxy
• Standard DIN/DVGW depths are reasonable approximations for Berlin's Charlottenburg district (well-established area)

Traceability

• Spec: /home/liviu/lessons-learned/docs/superpowers/specs/2026-03-24-underground-visible-design.md
• Research sources: Web searches conducted 2026-03-24 across OSM Wiki, Berlin Open Data portals, FIS-Broker documentation, Mapillary API docs, operator websites
• All WFS URLs verified against current Berlin Geoportal catalog and daten.berlin.de listings
• Overpass queries designed for the specific bounding box; tested syntax against Overpass QL documentation

Table of Contents

1. A. Direct Underground Infrastructure Data
2. B. Proxy Detection (Above-Ground Indicators)
3. C. Building & Surface Data
4. D. Demographic & Social Data
5. E. Temporal / Change Detection
6. F. Overpass API Queries
7. G. Mapillary / Street-Level Imagery
8. H. What We CANNOT Get From Open Data
9. I. Data Pipeline Implementation
10. J. Summary Matrix

A. Direct Underground Infrastructure Data

A.1 OpenInfraMap / OpenStreetMap Infrastructure

What it contains: Power lines, substations, telecom exchanges, gas pipelines, water infrastructure mapped by OSM contributors. OpenInfraMap renders OSM data tagged with power=*, man_made=pipeline, telecom=*, and related tags.

How to extract:

• Visual exploration: https://openinframap.org/#14/52.5088/13.3063
• Raw data via Overpass API (see Section F for exact queries)
• Bulk download: Geofabrik Berlin extract at https://download.geofabrik.de/europe/germany/berlin-latest.osm.pbf

What it tells us:

• Power substations and cable routes (MV/LV distribution)
• Telecom exchange locations and cable ducts
• Gas pipeline routes where mapped
• Water infrastructure (reservoirs, treatment plants, mains where mapped)
• Manhole locations by type (sewer, telecom, gas, power, water)

Data format: OSM XML/PBF; Overpass returns JSON or XML; OpenInfraMap renders vector tiles Coordinate system: WGS84 (EPSG:4326) for OSM; convert to EPSG:25833 for Berlin context License: ODbL 1.0 (OpenStreetMap Foundation) Confidence: MEDIUM — completeness varies. Power infrastructure well-mapped; underground pipes/cables spotty. Berlin OSM coverage is among the best in the world, but underground utility mapping is inherently incomplete.

Key OSM tags for underground infrastructure:

A.2 Leico / infrest Portal (Leitungsauskunft)

What it contains: Utility plan information from ALL registered infrastructure operators in Berlin. This is the single most comprehensive source. Over 18,600 operators are registered nationwide, covering: electricity, gas, water, wastewater, telecom, district heating, fiber optic, cable TV, military, and more.

How to extract:

1. Register at https://www.leitungs-check-online.de/ (free for basic Leitungsauskunft)
2. Draw your area of interest on the map (our 150m segment)
3. Submit request — the portal forwards it to ALL operators with infrastructure in that area
4. Receive response PDFs from each operator (typically 1:500 scale raster plans)

What it tells us:

• Horizontal positions of ALL utility pipes and cables within the segment
• Approximate depths (sometimes noted on plans)
• Pipe diameters and materials (sometimes noted)
• Operator names and contact information
• Service connection locations to buildings

Data format: PDF raster plans (georeferenced to varying degrees); some operators provide DXF/DWG Coordinate system: Typically ETRS89/UTM zone 33N (EPSG:25833) or Soldner Berlin License: Free for planning purposes; redistribution restricted per operator terms Confidence: HIGH for horizontal positions (operator-supplied), MEDIUM for depths (not always measured, sometimes from installation records)

Processing required:

• Georeference PDF plans to EPSG:25833
• Manual vectorization of pipe/cable centerlines in QGIS
• Attribute tagging (utility_type, operator, diameter, material)
• Estimated effort: 4-8 hours for 150m segment

Operators expected to respond for Charlottenburg:

A.3 Berliner Wasserbetriebe (BWB) — Water & Sewer

What it contains: BWB operates 7,816 km of water pipes and 9,746 km of sewers. They are Europe's largest integrated water utility.

Publicly available data:

What is NOT publicly available:

• Network GIS (pipe routes, diameters, materials, ages) — internal BWB system
• CCTV sewer condition assessments — internal, partnership required
• Detailed depth measurements — available only through Leico Tier 2/3
• Real-time flow/pressure data — internal SCADA

Confidence: HIGH for Leico-sourced plans; LOW for OSM-derived data (incomplete)

A.4 Stromnetz Berlin — Electricity Grid

What it contains: Stromnetz Berlin operates Berlin's electricity distribution grid. They were the first German grid operator to publish open data.

Open Data Portal: https://www.stromnetz.berlin/en/technology-innovations/open-data-stromnetz-berlin/

Available datasets (via daten.berlin.de):

What it tells us about underground infrastructure:

• Substation locations indicate MV cable routes (cables run between substations)
• Outage data provides indirect evidence of cable age/condition
• Feed-in data shows where solar inverters connect (building-level grid connection)

What is NOT available:

• Exact cable routes — internal GIS only, or via Leico
• Cable depths — not in open data
• Cable materials/ages — not in open data

Coordinate system: Grid sections are named regions, not georeferenced; substation locations not in open data License: Open data (Berlin OpenData terms) Confidence: LOW for spatial inference; HIGH for grid structure metrics

A.5 GASAG / NBB Netzgesellschaft — Gas Network

What it contains: NBB operates Berlin's gas distribution network (subsidiary of GASAG).

Public access:

What is NOT available as open data:

• Pipe routes, diameters, materials, ages
• Gas pressure zones
• Decommissioning schedules (relevant for Waermewende)

Confidence: Via Leico = HIGH for horizontal positions. Open data = essentially NONE for spatial detail.

A.6 BEW Berliner Energie und Waerme — District Heating

What it contains: BEW (formerly Vattenfall Waerme Berlin, recommunalized May 2024) operates more than 2,000 km of district heating pipes — the largest urban system in Western Europe. Two supply areas (SA1 and SA2).

Public data sources:

Umweltatlas heat supply map:

• WMS: https://fbinter.stadt-berlin.de/fb/wms/senstadt/k08_01versgeb2000 (2000 edition — check for updates on new Geoportal)
• Shows dominant heating type per block (district heating, gas, oil, etc.)
• Confirms whether Wilmersdorfer Str. buildings connect to district heating

What is NOT available: Pipe routes, diameters, supply/return temperatures, ages — via Leico only Confidence: MEDIUM for supply area coverage; HIGH via Leico for pipe positions

A.7 BVG U-Bahn Tunnel Geometry (U7)

What it contains: The U7 line runs through Wilmersdorfer Strasse station at approximately 12m depth. The station is a "Grossprofil" (large profile) station.

OSM data: The U7 line IS mapped in OpenStreetMap as a railway=subway way with tunnel=yes and layer=-2 or similar negative layer tags. Station platforms are mapped as areas.

Overpass query for U7 geometry (see Section F.6).

What OSM tells us:

• Horizontal alignment of tunnel centerline
• Station platform geometry
• Entrance/exit locations
• Ventilation shaft locations (if tagged: building=vent_shaft or man_made=ventilation_shaft)

What OSM does NOT tell us:

• Exact tunnel depth (12m is from published BVG documentation, not measured)
• Tunnel cross-section dimensions
• Structural details

Berlin 3D Stadtmodell: The LOD2 CityGML model does NOT include underground structures like U-Bahn tunnels. It covers above-ground buildings only.

Alternative source: BVG may provide tunnel geometry through Leico or direct partnership for the ISEK project.

Confidence: MEDIUM for horizontal alignment (OSM); LOW for depth (published estimates)

B. Proxy Detection

B.1 Manhole Covers (OSM)

What they indicate: Each manhole type reveals a specific underground network beneath it. Manholes are the most reliable surface indicator of underground infrastructure.

Detection method: Overpass API query for manhole=* in bounding box (see Section F.1)

Inference rules:

Related OSM tags on manhole nodes:

• shape=* (cover geometry: round, square, rectangular)
• diameter=*, width=*, length=* (cover dimensions)
• material=* (cast_iron, steel, etc.)
• colour=*
• ref=* (identifying markers/numbers on cover)
• inscription=* (text on cover)
• operator=* (which utility company)

Spatial inference: Connect manholes of the same type to infer pipe routes between them. Two sewer manholes 50m apart almost certainly have a sewer pipe connecting them along the street.

Confidence: HIGH for network existence; MEDIUM for exact route inference

B.2 Fire Hydrants (OSM)

What they indicate: Fire hydrants are connected to the water main. Their positions directly indicate the water main route.

OSM tag: emergency=fire_hydrant

Key attributes:

Berlin-specific: Most Berlin hydrants are underground type (Unterflurhydranten) — metal caps flush with the sidewalk, marked with a small Hinweisschild (indicator sign) on the nearest wall.

Spatial inference: Hydrants along a street at ~80-120m intervals confirm a water main running along that street. The main typically runs under the sidewalk on one side.

Confidence: HIGH — hydrants are well-mapped in Berlin OSM

B.3 Utility Markers (Street-Level Imagery Detection)

What they are: In Germany, colored markers/posts/signs indicate underground utility presence:

Detection method: Mapillary object detection API (see Section G) or manual review of street-level imagery.

Also look for:

• Hinweisschilder (indicator signs) on buildings: small metal plates showing hydrant locations with distance+direction arrows
• Schieberkappen (valve caps): small metal caps in sidewalk for gas/water valves
• Bordsteinkappen (curb caps): small markers at curb edge indicating service connections

Confidence: MEDIUM — depends on imagery coverage and ML detection accuracy

B.4 Ventilation Shafts (U-Bahn)

What they indicate: U-Bahn tunnels require surface ventilation. Shafts reveal tunnel presence and approximate horizontal position.

OSM tags: man_made=ventilation_shaft, building=vent_shaft, or occasionally tunnel=ventilation_shaft

Berlin-specific: U7 ventilation for Wilmersdorfer Strasse station likely exits at street level near the station entrance or in adjacent buildings. Look for:

• Metal grates at sidewalk level
• Louvered openings in station entrance buildings
• Warm air exhaust points visible in winter (thermal contrast)

Confidence: LOW from OSM (rarely tagged); MEDIUM from street-level imagery inspection

B.5 Gas Regulator Stations

What they indicate: Yellow cabinets or small buildings housing pressure reduction equipment. Indicate medium-to-low pressure gas transition points.

OSM tags: pipeline=substation + substation=distribution, or man_made=gasometer, or utility=gas + man_made=street_cabinet

Confidence: MEDIUM — some mapped in OSM; visible in street-level imagery

B.6 Street Furniture Placement Patterns

What they indicate: Lampposts, benches, bollards, and other street furniture are placed to AVOID underground infrastructure. Gaps in regular spacing may indicate underground obstacles.

Detection method: Not directly available from any dataset. Would require spatial analysis of street furniture positions (from OSM or Berlin Stadtmoeblierung data) compared to expected regular spacing.

Confidence: LOW — inference only, high false positive rate

B.7 Tree Planting Constraints

What they indicate: Trees NOT planted where expected (gaps in regular tree rows) may indicate underground infrastructure preventing planting.

Detection method: Compare Baumkataster positions (Section C.3) against expected regular planting pattern. Gaps larger than 2 standard deviations from mean spacing = potential underground obstacle.

Data needed: Baumkataster (available, see C.3) + statistical gap analysis

Confidence: LOW-MEDIUM — gaps have many causes (building entrances, driveways, sight lines)

B.8 Street Cuts / Patches

What they indicate: Visible patches in road/sidewalk surface where pavement was cut, excavation performed, and backfilled. Indicate recent underground work.

Detection method:

1. Satellite/aerial imagery change detection: Compare imagery vintages (Berlin has imagery from 2004-2022+). Fresh patches appear as color/texture differences.
2. Street-level imagery: Fresh asphalt patches visible as darker rectangles against older surface.

Data sources:

• Berlin Orthophotos via Geoportal (Section E.1)
• Google Earth Pro historical imagery
• Mapillary/KartaView street-level photos

Confidence: LOW — patches can indicate ANY underground work (new connections, repairs, unrelated construction). Also fade with time.

B.9 District Heating Surface Indicators

What they indicate: District heating pipes run at 80-130 degrees C. In winter, snow melts faster above buried heating pipes. Also visible as heat shimmer in thermal imagery.

Detection method:

• Winter satellite imagery showing snowmelt patterns
• Thermal IR aerial surveys (not currently in Berlin open data)
• Above-ground pipe bridges at building connections

Confidence: LOW — seasonal, weather-dependent

C. Building & Surface Data

C.1 Berlin 3D Stadtmodell (LOD2 CityGML)

What it contains: Full 3D building models for ALL of Berlin at LOD2 (Level of Detail 2 = roof shapes). Includes building footprints, heights, roof geometry, facade geometry with textures.

How to extract:

For our segment: Use 3DCityLoader, draw the ~150m segment bounding box, download as OBJ for Three.js import.

CityGML attributes available:

• bldg:measuredHeight — building height in meters
• bldg:storeysAboveGround — floor count
• bldg:roofType — flat, gable, hip, etc.
• bldg:function — residential, commercial, etc.
• gml:id — unique building identifier
• Geometry: Ground surface, wall surfaces, roof surfaces

Coordinate system: ETRS89/UTM zone 33N (EPSG:25833), heights in DHHN2016 License: Datenlizenz Deutschland – Zero – Version 2.0 (dl-de-zero-2.0) — completely free, no attribution required Confidence: HIGH — official surveying data, centimeter accuracy

C.2 ALKIS Cadastral Data

What it contains: Official cadastral map (Amtliches Liegenschaftskatasterinformationssystem) with building footprints, land parcels, addresses, building function, and building geometry.

WFS endpoints (new Geoportal Berlin):

GetCapabilities request:

https://gdi.berlin.de/services/wfs/alkis_gebaeude?request=GetCapabilities&service=WFS

Example WFS query (buildings in bounding box):

https://gdi.berlin.de/services/wfs/alkis_gebaeude?
  service=WFS&
  version=2.0.0&
  request=GetFeature&
  typeName=fis:re_gebaeude&
  bbox=385850,5819750,385950,5819900,EPSG:25833&
  outputFormat=application/json

Interactive explorer: https://wfsexplorer.odis-berlin.de/?wfs=https://gdi.berlin.de/services/wfs/alkis_gebaeude

Coordinate system: ETRS89/UTM zone 33N (EPSG:25833) License: Datenlizenz Deutschland – Zero – Version 2.0 (dl-de-zero-2.0) Confidence: HIGH — official surveying data

C.3 Berlin Baumkataster (Tree Registry)

What it contains: Every street tree in Berlin with exact position, species (German + botanical), planting year, tree height, crown diameter, trunk circumference, district, owner, and address.

WFS endpoint:

https://gdi.berlin.de/services/wfs/baumbestand

Alternative WFS (older FIS-Broker, may still work):

https://fbinter.stadt-berlin.de/fb/wfs/data/senstadt/s_wfs_baumbestand

Attributes available:

Example WFS query (trees near Wilmersdorfer Strasse):

https://gdi.berlin.de/services/wfs/baumbestand?
  service=WFS&
  version=2.0.0&
  request=GetFeature&
  typeName=fis:s_wfs_baumbestand&
  bbox=385850,5819750,385950,5819900,EPSG:25833&
  outputFormat=application/json

Also available via:

• WMS for visualization: https://gdi.berlin.de/services/wms/baumbestand
• ArcGIS Hub: https://hub.arcgis.com/datasets/esri-de-content::baumkataster-berlin
• ODIS Berlin: https://daten.odis-berlin.de/

Coordinate system: ETRS89/UTM zone 33N (EPSG:25833) License: CC BY 3.0 DE Confidence: HIGH — official registry, field-measured data. Planting year and species are highly reliable. Height and crown diameter are periodic estimates (updated during inspections).

C.4 Berlin Umweltatlas (Environmental Atlas)

What it contains: Comprehensive environmental data for Berlin across themes: soil, water, air, climate, biotopes, land use, energy.

Portal: https://www.berlin.de/umweltatlas/

Relevant layers for underground model:

Note on FIS-Broker: The FIS-Broker at fbinter.stadt-berlin.de was scheduled for shutdown December 1, 2025. The replacement is the new Geoportal Berlin at gdi.berlin.de. Check whether WMS URLs have migrated. The new pattern is likely:

https://gdi.berlin.de/services/wms/{layer_name}

Geoservices available: WMS, WFS (where applicable), Atom feeds, PDF downloads. Coordinate system: ETRS89/UTM zone 33N (EPSG:25833) License: Open data (CC BY or dl-de-zero-2.0 depending on layer) Confidence: HIGH — official environmental monitoring data

C.5 Building Age Data (Baualtersklassen)

What it contains: Estimated construction period for buildings. Critical for estimating INFRASTRUCTURE age (utilities installed when buildings were built).

WFS URL (FIS-Broker):

https://fbinter.stadt-berlin.de/fb/wfs/data/senstadt/s06_12baualter

New Geoportal (check availability):

https://gdi.berlin.de/services/wfs/baualter

Why this matters for underground infrastructure:

• Building constructed ~1890 implies sewer connection from ~1890 (may be original)
• Building constructed ~1970 implies water main likely from that era
• Building constructed ~2000 implies all new service connections, likely plastic pipes

Confidence: MEDIUM — provides era, not exact year; infrastructure may have been renewed since

C.6 Berlin Solar Atlas / Energieatlas

What it contains: Building-level solar potential analysis based on LiDAR-derived roof geometry. Includes detailed roof segment information (slope, orientation, area) and building height data.

Portal: https://energieatlas.berlin.de/

What it tells us about underground infrastructure (indirect):

• Roof geometry confirms building 3D model accuracy
• Building height cross-references with LOD2
• Solar installations indicate where grid feed-in connections exist (relevant for Stromnetz)

Access: Web viewer; some data via WMS through Geoportal License: Open data Confidence: HIGH for roof geometry; LOW for underground inference

D. Demographic & Social Data

D.1 Einwohnerregisterstatistik (Population Registry)

What it contains: Population by individual age year at LOR Planungsraum level (542 planning areas across Berlin). Published half-yearly.

Latest dataset: December 31, 2024 (published March 25, 2025)

Download URL:

https://www.statistik-berlin-brandenburg.de/opendata/EWR_L21_202412E_Matrix.csv

CSV structure:

• Rows: LOR Planungsraeume (542 areas)
• Columns: Age years (0, 1, 2, ... 95+), by gender
• Includes: District (Bezirk), Bezirksregion, Planungsraum codes and names

For Wilmersdorfer Strasse: The relevant Planungsraum is likely "Wilmersdorfer Strasse" or "Stuttgarter Platz" (PLR code to be confirmed from LOR boundary data). The street segment may span two Planungsraeume.

Building-level allocation method:

1. Get total population for the Planungsraum
2. Get building footprints + floor counts from ALKIS
3. Calculate total residential floor area per building
4. Allocate population proportionally: building_pop = PLR_pop * (building_floor_area / total_PLR_floor_area)
5. Distribute age groups using PLR-level age distribution
6. Tag as [MODELED/ALLOCATED] — not measured per-building data

License: CC BY 3.0 (Amt fuer Statistik Berlin-Brandenburg) Confidence: HIGH at PLR level; MEDIUM for building-level allocation (proportional model)

D.2 Monitoring Soziale Stadtentwicklung (Social Urban Development)

What it contains: Composite social indicators per LOR, including:

• Status Index (income, employment, education)
• Dynamics Index (change over time)
• Typology (4 categories from "very low attention" to "very high attention")
• Child poverty rate
• Youth unemployment
• Transfer payment dependency

Download: Available as CSV from daten.berlin.de (search "Monitoring Soziale Stadtentwicklung") Geospatial: Maps available via FIS-Broker/Geoportal WMS

For our model: Social indicators can enhance the vulnerability visualization — areas with both aging infrastructure AND high social disadvantage need priority attention.

License: Open data (Berlin) Confidence: HIGH at LOR level

D.3 LOR Planungsraum Boundaries

What it contains: Polygon boundaries for all 542 Planungsraeume (since 2021 LOR reform).

Download sources:

Coordinate system: ETRS89/UTM zone 33N (EPSG:25833) License: CC BY 3.0 Confidence: HIGH — official boundaries

E. Temporal / Change Detection

E.1 Historical Aerial Imagery

What it contains: Berlin has digitized aerial photography going back to 1928. Orthorectified imagery available from 2004-2022+ through the Geoportal.

Access:

Available vintages (confirmed): 1928, 1953, 2004, 2005, 2007, 2009, 2010, 2011, 2013, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022+

What it tells us about underground infrastructure:

• Change detection: Compare two vintages to identify areas where road/sidewalk surface changed (= excavation/repaving occurred)
• Construction history: Track when buildings were built/demolished to infer utility connection dates
• Street redesign history: Identify when the street layout changed, indicating when underground work was likely performed

Processing: Load two WMS vintages in QGIS, use raster difference/visual comparison to identify surface changes.

License: Open data (Berlin) Confidence: MEDIUM for underground inference (surface changes do not always mean underground work; underground work does not always show as surface changes)

E.2 OSM History API

What it contains: Full edit history for every OSM feature — when added, by whom, what changed.

API endpoint: https://api.openstreetmap.org/api/0.6/[type]/[id]/history

What it tells us:

• When a manhole was first mapped confirms it existed at that date
• Edits to utility tags may reflect real-world changes
• Contributor patterns show whether data came from experienced local mappers vs. imports

Bulk access: Full history dumps at https://planet.openstreetmap.org/

Confidence: LOW — OSM history reflects mapping activity, not infrastructure changes

E.3 Construction Permit Data (Bauaktenarchiv)

What it contains: Construction records dating back decades. Every building permit, renovation, demolition permit.

Access for Charlottenburg-Wilmersdorf:

• Bauaktenarchiv Charlottenburg-Wilmersdorf: In-person access only, by appointment
• Contact: Tel (030) 9029-16031/16033, [email protected]
• Requirement: Justified interest; application for file access (Akteneinsicht)
• Digitized index: Landesarchiv Berlin has digitized index cards searchable at http://www.landesarchiv-berlin-viewer.de/bauakten-kartei/

What it tells us:

• When buildings were constructed implies approximate infrastructure installation dates
• Major renovations may include utility renewal
• Building-specific construction details

NOT open data: Requires in-person visit, appointment, and justified interest. Confidence: HIGH for building dates; MEDIUM for infrastructure inference

E.4 VIZ Berlin — Road Closures and Construction

What it contains: The Verkehrsinformationszentrale (VIZ) provides current and planned road closures, construction sites, and traffic disruptions.

Portal: https://viz.berlin.de/en/traffic-in-berlin/construction-sites-and-roadblocks/

Data access:

• Interactive map on viz.berlin.de
• Data may be available through Berlin's Mobility Data Marketplace (MDM)
• No confirmed public API endpoint for bulk download

What it tells us:

• Current/recent construction sites indicate active underground work
• Planned closures indicate upcoming infrastructure projects
• Historical pattern shows which streets are frequently dug up

Confidence: MEDIUM for current data; historical data not readily available

F. Overpass API Queries

All queries use the Wilmersdorfer Strasse bounding box. The bbox format for Overpass is (south,west,north,east):

(52.5070,13.3040,52.5105,13.3085)

Overpass Turbo URL: https://overpass-turbo.eu/

F.1 All Manholes

[out:json][timeout:30];
(
  node["manhole"](52.5070,13.3040,52.5105,13.3085);
);
out body;
>;
out skel qt;

What this returns: Every node tagged with any manhole=* value. Each result includes:

• Coordinates (lat/lon)
• manhole= type (sewer, telecom, drain, gas, power, water, heat, etc.)
• Additional tags: operator=*, ref=*, material=*, diameter=*

Export as GeoJSON: In Overpass Turbo, click Export then GeoJSON

F.2 All Fire Hydrants

[out:json][timeout:30];
(
  node["emergency"="fire_hydrant"](52.5070,13.3040,52.5105,13.3085);
);
out body;
>;
out skel qt;

Key attributes in results:

• fire_hydrant:type = underground | pillar | wall
• fire_hydrant:diameter = pipe diameter in mm
• fire_hydrant:pressure = pressure in bar
• water_source = main | tank | etc.
• operator = BWB (likely)

F.3 All Utility Infrastructure

[out:json][timeout:30];
(
  // Manholes (all types)
  node["manhole"](52.5070,13.3040,52.5105,13.3085);

  // Fire hydrants
  node["emergency"="fire_hydrant"](52.5070,13.3040,52.5105,13.3085);

  // Street cabinets (telecom, power, gas)
  node["man_made"="street_cabinet"](52.5070,13.3040,52.5105,13.3085);
  way["man_made"="street_cabinet"](52.5070,13.3040,52.5105,13.3085);

  // Pipelines
  way["man_made"="pipeline"](52.5070,13.3040,52.5105,13.3085);

  // Underground power cables
  way["power"="cable"]["location"="underground"](52.5070,13.3040,52.5105,13.3085);
  way["power"="cable"]["cable"="underground"](52.5070,13.3040,52.5105,13.3085);

  // Power substations (minor distribution = street transformer)
  node["power"="substation"](52.5070,13.3040,52.5105,13.3085);
  way["power"="substation"](52.5070,13.3040,52.5105,13.3085);

  // Telecom infrastructure
  node["telecom"](52.5070,13.3040,52.5105,13.3085);
  way["telecom"](52.5070,13.3040,52.5105,13.3085);

  // Utility markers
  node["marker"]["utility"](52.5070,13.3040,52.5105,13.3085);

  // Ventilation shafts
  node["man_made"="ventilation_shaft"](52.5070,13.3040,52.5105,13.3085);

  // Pumping stations
  node["man_made"="pumping_station"](52.5070,13.3040,52.5105,13.3085);
  way["man_made"="pumping_station"](52.5070,13.3040,52.5105,13.3085);

  // Water infrastructure
  node["man_made"="water_well"](52.5070,13.3040,52.5105,13.3085);
  way["waterway"="drain"](52.5070,13.3040,52.5105,13.3085);
);
out body;
>;
out skel qt;

F.4 Street Trees (OSM — separate from Baumkataster)

[out:json][timeout:30];
(
  node["natural"="tree"](52.5070,13.3040,52.5105,13.3085);
  way["natural"="tree_row"](52.5070,13.3040,52.5105,13.3085);
);
out body;
>;
out skel qt;

Note: Berlin's Baumkataster (Section C.3) is MORE comprehensive and authoritative. OSM trees may duplicate or lack data compared to the official registry. Use Baumkataster as primary source; OSM as supplement for park/private trees.

F.5 Buildings with Address and Floor Count

[out:json][timeout:30];
(
  way["building"](52.5070,13.3040,52.5105,13.3085);
  relation["building"](52.5070,13.3040,52.5105,13.3085);
);
out body;
>;
out skel qt;

Key attributes in results:

• building = apartments | residential | commercial | retail | etc.
• building:levels = number of above-ground floors
• building:levels:underground = basement levels
• roof:levels = attic/mansard levels
• addr:street, addr:housenumber, addr:postcode
• height = building height in meters (rare in OSM)
• start_date = construction year (rare)
• name = building name

Note: ALKIS (Section C.2) and LOD2 CityGML (Section C.1) are more authoritative for building data. Use OSM for address enrichment and floor counts where ALKIS lacks them.

F.6 U-Bahn Station and Tunnel Geometry

[out:json][timeout:30];
(
  // U7 line ways (tunnel)
  way["railway"="subway"]["name"~"U7|U 7"](52.5050,13.2990,52.5130,13.3140);

  // Wilmersdorfer Strasse station
  node["railway"="station"]["name"~"Wilmersdorfer"](52.5050,13.2990,52.5130,13.3140);
  way["railway"="station"]["name"~"Wilmersdorfer"](52.5050,13.2990,52.5130,13.3140);
  relation["railway"="station"]["name"~"Wilmersdorfer"](52.5050,13.2990,52.5130,13.3140);

  // Station platforms
  way["public_transport"="platform"]["name"~"Wilmersdorfer"](52.5050,13.2990,52.5130,13.3140);

  // Station entrances
  node["railway"="subway_entrance"](52.5060,13.3020,52.5110,13.3100);

  // Ventilation shafts near station
  node["man_made"="ventilation_shaft"](52.5060,13.3020,52.5110,13.3100);
);
out body;
>;
out skel qt;

Note: Uses wider bbox to capture full tunnel alignment through the area.

F.7 Street Furniture

[out:json][timeout:30];
(
  // Benches
  node["amenity"="bench"](52.5070,13.3040,52.5105,13.3085);

  // Waste bins
  node["amenity"="waste_basket"](52.5070,13.3040,52.5105,13.3085);

  // Street lamps
  node["highway"="street_lamp"](52.5070,13.3040,52.5105,13.3085);

  // Bollards
  node["barrier"="bollard"](52.5070,13.3040,52.5105,13.3085);

  // Bicycle parking
  node["amenity"="bicycle_parking"](52.5070,13.3040,52.5105,13.3085);
  way["amenity"="bicycle_parking"](52.5070,13.3040,52.5105,13.3085);

  // Advertising columns (Litfasssaeulen)
  node["advertising"](52.5070,13.3040,52.5105,13.3085);

  // Post boxes
  node["amenity"="post_box"](52.5070,13.3040,52.5105,13.3085);

  // Telephone boxes
  node["amenity"="telephone"](52.5070,13.3040,52.5105,13.3085);

  // Traffic signals
  node["highway"="traffic_signals"](52.5070,13.3040,52.5105,13.3085);

  // Curb information
  way["barrier"="kerb"](52.5070,13.3040,52.5105,13.3085);
);
out body;
>;
out skel qt;

F.8 Combined "Everything" Query

For a complete initial data pull, combine all queries into one request. This is the master query to run first:

[out:json][timeout:60];
(
  // === UNDERGROUND INDICATORS ===
  node["manhole"](52.5070,13.3040,52.5105,13.3085);
  node["emergency"="fire_hydrant"](52.5070,13.3040,52.5105,13.3085);
  node["man_made"="street_cabinet"](52.5070,13.3040,52.5105,13.3085);
  way["man_made"="street_cabinet"](52.5070,13.3040,52.5105,13.3085);
  way["man_made"="pipeline"](52.5070,13.3040,52.5105,13.3085);
  way["power"="cable"](52.5070,13.3040,52.5105,13.3085);
  node["power"="substation"](52.5070,13.3040,52.5105,13.3085);
  way["power"="substation"](52.5070,13.3040,52.5105,13.3085);
  node["telecom"](52.5070,13.3040,52.5105,13.3085);
  way["telecom"](52.5070,13.3040,52.5105,13.3085);
  node["marker"]["utility"](52.5070,13.3040,52.5105,13.3085);
  node["man_made"="ventilation_shaft"](52.5070,13.3040,52.5105,13.3085);
  node["man_made"="pumping_station"](52.5070,13.3040,52.5105,13.3085);

  // === BUILDINGS ===
  way["building"](52.5070,13.3040,52.5105,13.3085);
  relation["building"](52.5070,13.3040,52.5105,13.3085);

  // === TREES ===
  node["natural"="tree"](52.5070,13.3040,52.5105,13.3085);
  way["natural"="tree_row"](52.5070,13.3040,52.5105,13.3085);

  // === STREET FURNITURE ===
  node["amenity"="bench"](52.5070,13.3040,52.5105,13.3085);
  node["amenity"="waste_basket"](52.5070,13.3040,52.5105,13.3085);
  node["highway"="street_lamp"](52.5070,13.3040,52.5105,13.3085);
  node["barrier"="bollard"](52.5070,13.3040,52.5105,13.3085);
  node["highway"="traffic_signals"](52.5070,13.3040,52.5105,13.3085);

  // === U-BAHN (wider bbox) ===
  way["railway"="subway"](52.5050,13.2990,52.5130,13.3140);
  node["railway"="subway_entrance"](52.5060,13.3020,52.5110,13.3100);
);
out body;
>;
out skel qt;

G. Mapillary / Street-Level Imagery

G.1 Mapillary Coverage Check

Check coverage: Visit https://www.mapillary.com/app/?lat=52.5088&lng=13.3063&z=17

Wilmersdorfer Strasse is a major shopping street in Charlottenburg. Expect GOOD Mapillary coverage — it is a high-traffic pedestrian area frequently photographed.

G.2 Mapillary API v4 — Image Query

Endpoint: https://graph.mapillary.com/images

Authentication: Requires access token (free registration at mapillary.com/developer)

Python example — fetch all images in our bounding box:

import requests

ACCESS_TOKEN = "YOUR_MAPILLARY_TOKEN"
BBOX = "13.3040,52.5070,13.3085,52.5105"  # west,south,east,north

url = "https://graph.mapillary.com/images"
params = {
    "access_token": ACCESS_TOKEN,
    "fields": "id,captured_at,geometry,compass_angle,sequence,is_pano",
    "bbox": BBOX,
    "limit": 2000
}

response = requests.get(url, params=params)
data = response.json()

print(f"Found {len(data.get('data', []))} images")
for img in data.get("data", [])[:5]:
    print(f"  ID: {img['id']}, Date: {img['captured_at']}, "
          f"Pano: {img.get('is_pano', False)}")

G.3 Mapillary API v4 — Object Detections

Endpoint: https://graph.mapillary.com/map_features

Object types relevant to underground infrastructure:

Python example — fetch manhole and hydrant detections:

import requests

ACCESS_TOKEN = "YOUR_MAPILLARY_TOKEN"
BBOX = "13.3040,52.5070,13.3085,52.5105"

url = "https://graph.mapillary.com/map_features"
params = {
    "access_token": ACCESS_TOKEN,
    "fields": "id,geometry,object_value,first_seen_at,last_seen_at",
    "bbox": BBOX,
    "object_values": "object--manhole,object--fire-hydrant",
    "limit": 2000
}

response = requests.get(url, params=params)
data = response.json()

for feature in data.get("data", []):
    coords = feature["geometry"]["coordinates"]
    print(f"  Type: {feature['object_value']}, "
          f"Lat: {coords[1]:.6f}, Lon: {coords[0]:.6f}, "
          f"First seen: {feature.get('first_seen_at', 'N/A')}")

G.4 KartaView (formerly OpenStreetCam)

Coverage check: https://kartaview.org/map/@52.5088,13.3063,17z

API endpoints (public, no auth required):

• Nearby sequences: https://api.openstreetcam.org/2.0/photo/?bbox=13.3040,52.5070,13.3085,52.5105
• Coverage polylines: https://api.openstreetcam.org/2.0/tracks/?bbox=13.3040,52.5070,13.3085,52.5105

KartaView does NOT have ML object detection like Mapillary. It provides raw imagery only.

License: CC BY-SA 4.0 Confidence: Depends on coverage density in the specific area

G.5 What Can Be Detected from Street-Level Imagery

H. What We CANNOT Get From Open Data

H.1 Hard Gaps — Require Operator Partnership (Tier 3)

H.2 Soft Gaps — Technically Available but Difficult

H.3 Completeness Assessment by Utility Type

I. Data Pipeline Implementation

I.1 Immediate Actions (Day 1)

# 1. Run master Overpass query (Section F.8) — 2 minutes
# Open https://overpass-turbo.eu/, paste query, Export -> GeoJSON
# Save as: data/osm-infrastructure.geojson

# 2. Download Baumkataster via WFS — 1 minute
curl -o data/trees-raw.json \
  "https://gdi.berlin.de/services/wfs/baumbestand?\
service=WFS&version=2.0.0&request=GetFeature&\
typeName=fis:s_wfs_baumbestand&\
bbox=385850,5819750,385950,5819900,EPSG:25833&\
outputFormat=application/json"

# 3. Download ALKIS buildings via WFS — 1 minute
curl -o data/buildings-alkis.json \
  "https://gdi.berlin.de/services/wfs/alkis_gebaeude?\
service=WFS&version=2.0.0&request=GetFeature&\
typeName=fis:re_gebaeude&\
bbox=385850,5819750,385950,5819900,EPSG:25833&\
outputFormat=application/json"

# 4. Download demographics CSV — 1 minute
curl -o data/demographics-lor.csv \
  "https://www.statistik-berlin-brandenburg.de/opendata/EWR_L21_202412E_Matrix.csv"

# 5. Download LOR boundaries — 1 minute
curl -o data/lor-boundaries.geojson \
  "https://gdi.berlin.de/services/wfs/lor_planungsraeume?\
service=WFS&version=2.0.0&request=GetFeature&\
outputFormat=application/json"

# 6. Download LOD2 buildings from 3DCityLoader
# -> Visit https://3dcityloader.com/en/city/berlin
# -> Draw bbox around segment
# -> Download as OBJ
# -> Save as: assets/models/buildings.obj

I.2 Short-term Actions (Week 1)

1. Register on Leico (leitungs-check-online.de) and submit request for Wilmersdorfer Str. segment
2. Register for Mapillary API (mapillary.com/developer) and run object detection queries
3. Check new Geoportal Berlin (gdi.berlin.de) for updated WFS/WMS endpoint URLs
4. Download Umweltatlas layers via WMS for surface sealing, groundwater, heat island
5. Get building age data from FIS-Broker/Geoportal for infrastructure age estimation

I.3 Processing Pipeline

Step 1:  OSM data -> filter by category -> assign to utility layers
Step 2:  Baumkataster -> clip to segment -> species lookup for root zones
Step 3:  ALKIS buildings -> extract footprints, addresses, floor counts
Step 4:  LOD2 CityGML -> convert to OBJ -> import to Three.js
Step 5:  Demographics CSV -> filter to relevant PLR -> allocate to buildings
Step 6:  Umweltatlas WMS -> clip to segment -> extract as raster overlay
Step 7:  [When received] Leico PDFs -> georeference -> vectorize -> GeoJSON
Step 8:  Mapillary detections -> cross-reference with OSM data -> enrich
Step 9:  Assemble all layers into Three.js data files (JSON/GeoJSON)
Step 10: Apply DIN/DVGW standard depths to all utility features -> tag [MODELED]

I.4 Coordinate System Conversion

All Berlin official data uses EPSG:25833 (ETRS89/UTM zone 33N). OSM and Mapillary use EPSG:4326 (WGS84). Three.js uses a local coordinate system.

Conversion chain:

EPSG:4326 (OSM/Mapillary)
    -> EPSG:25833 (Berlin standard, for alignment with official data)
        -> Three.js local (origin at segment center, Z=north, X=east)

Python (pyproj):

from pyproj import Transformer

# WGS84 -> ETRS89/UTM33N
transformer = Transformer.from_crs("EPSG:4326", "EPSG:25833", always_xy=True)

# Example: segment center
lon, lat = 13.3063, 52.5088
easting, northing = transformer.transform(lon, lat)
# Result: ~385900, ~5819825

# Three.js local origin
ORIGIN_E, ORIGIN_N = 385900, 5819825

def to_threejs(easting, northing, depth=0):
    """Convert EPSG:25833 to Three.js local coordinates.
    X = east-west, Y = up (negative = underground), Z = north-south"""
    return (
        easting - ORIGIN_E,    # X
        -depth,                 # Y (negative = below surface)
        northing - ORIGIN_N    # Z
    )

J. Summary Matrix

Data Sources by Category

Expected Data Yield for 150m Segment

Appendix: License Summary

Appendix: Key URLs Quick Reference

# Overpass Turbo (interactive query builder)
https://overpass-turbo.eu/

# OpenInfraMap (visual infrastructure map)
https://openinframap.org/#14/52.5088/13.3063

# Berlin Geoportal (replacing FIS-Broker)
https://gdi.berlin.de/

# Berlin Open Data Portal
https://daten.berlin.de/

# ODIS Berlin (Open Data Informationsstelle)
https://daten.odis-berlin.de/

# 3DCityLoader (Berlin LOD2 download)
https://3dcityloader.com/en/city/berlin

# Berlin Business Location Center (3D download)
https://www.businesslocationcenter.de/downloadportal

# Leico Portal (utility information)
https://www.leitungs-check-online.de/

# Mapillary (street-level imagery + detections)
https://www.mapillary.com/app/?lat=52.5088&lng=13.3063&z=17

# KartaView (street-level imagery)
https://kartaview.org/map/@52.5088,13.3063,17z

# Berlin Umweltatlas
https://www.berlin.de/umweltatlas/

# Berlin Historical Aerial Imagery
https://gdi.berlin.de/viewer/luftbilder/

# Stromnetz Berlin Open Data
https://www.stromnetz.berlin/en/technology-innovations/open-data-stromnetz-berlin/

# EnergyMap Berlin
https://energieatlas.berlin.de/

# VIZ Berlin (traffic/construction)
https://viz.berlin.de/

# Mapillary API Documentation
https://www.mapillary.com/developer/api-documentation

# WFS Explorer (ODIS)
https://wfsexplorer.odis-berlin.de/

# Statistik Berlin-Brandenburg
https://www.statistik-berlin-brandenburg.de/