A Singapore transport data engineer is staging bus stop locations for a timetable join. Spreadsheet handling removed leading zeros from some codes, while a stale repeated source row and a blank code would make the join unreliable.
Prepare singapore_bus_stop_code_raw for the timetable loader.
| Incoming issue | Required result |
|---|---|
Repeated stop_id | Keep the lowest source_order. |
A one- to five-digit stop_code_raw with padding or lost leading zeros | Trim and left-pad to exactly five digits, keeping it as text. |
| Blank or nonnumeric stop code | Omit that source row; never invent a code. |
Padded name_raw | Trim into name. |
Return exactly stop_id, stop_code, name as text and point geom in EPSG:4326. A five-character code is an identifier, not an integer.
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
singapore_bus_stop_code_rawCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| stop_id | text |
| stop_code_raw | text |
| name_raw | text |
| source_order | integer |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
Modelled on a Singapore 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
Stop records agree with the key-cleaning rules
45%Stop codes are five digits
10%Each OSM stop reference appears once
10%Stop code remains text
10%The load has only its required fields
10%All delivered stops have geometry
10%Query returns the map-ready columns
5%