A Berlin mobility team receives a bicycle parking extract for a map and capacity summary. Duplicate source rows and text-coded missing values would inflate supply or turn unknown capacity into a negative number.
Prepare the staging table berlin_bike_parking_capacity_raw as one row per parking_id.
| Incoming issue | Required result |
|---|---|
Repeated parking_id | Keep the lowest source_order. |
| Padded capacity text | Trim and cast to integer. |
-9999 means unreported | Return SQL NULL in integer capacity and capacity_state='unreported'; otherwise capacity_state='known'. |
Return exactly parking_id (text), capacity (integer or null), capacity_state (text), and point geom in EPSG:4326. Do not discard a parking site merely because its capacity is unknown.
berlin_bike_parking_capacity_raw — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
parking_id | text | OSM node reference |
capacity_raw | text | Incoming capacity as text; -9999 means unreported |
source_order | integer | Priority order in incoming file |
geom | geometry(Geometry, 4326) | Point |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
berlin_bike_parking_capacity_rawCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| parking_id | text |
| capacity_raw | text |
| source_order | integer |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
Modelled on a Berlin GIS data handover using © OpenStreetMap contributors (ODbL 1.0). ISO 19157-1:2023 frames quality as fitness against the stated product specification.
7 scored, 0 informational
Cleaned parking records match the source rules
45%One result per parking reference
10%Capacity is an integer field
10%Known capacity is not negative
10%Capacity state uses the two documented values
10%The release schema has only its handover columns
10%Query returns the map-ready columns
5%