Create parking_asset with parking_id text as primary key and geom geometry(Point,4326) NOT NULL with a GiST spatial index. Create capacity_observation with parking_id text NOT NULL referencing parking_asset, observed_at timestamptz NOT NULL, nullable capacity integer, and capacity_state text NOT NULL (known or unreported). Use (parking_id, observed_at) as the observation primary key and an index for chronological history. Enforce nonnegative known capacity while allowing NULL for unreported. The schema should retain multiple readings for one asset.
A Berlin mobility data team has a small OSM-derived cycle-parking extract with repeated IDs and an explicit unknown-capacity sentinel. It needs a database shape that can retain later observations without overwriting history, an exception queue for this incoming batch, and a clean point layer for a map. The source is a teaching extract, not Berlin’s official parking inventory.
Work through three handoffs on one service: design a durable spatial schema, query intake exceptions, then deliver a deduplicated current-view point layer. Unknown capacity remains a place on the map with SQL NULL capacity; no count may treat it as negative. The snapshot has planted training faults. A live service would also need a source timestamp, refresh monitoring and reconciliation with official data.
These are where the teaching is. Read them twice.
Berlin’s mobility administration describes bicycle parking as part of its cycling infrastructure programme (https://www.berlin.de/sen/uvk/mobilitaet-und-verkehr/verkehrsplanung/radverkehr/fahrrad-parken/). Berlin Open Data also lists an official parking-facilities WFS maintained by GB infraVelo (https://daten.berlin.de/datensaetze/fahrradabstellanlagen-seit-2021-wfs-4f226c23). The exercise instead uses a small OSM-derived teaching extract with faults planted for training; it is not the official WFS or a complete Berlin inventory.
8 scored, 0 informational
Stable asset register
8%Dated observation history
8%Typed asset location
17%Indexed spatial asset reads
17%Observation references an asset
17%One observation per asset and time
17%Chronological lookup
8%Declared spatial reference
8%
