Deliver a field-ready public asset register

A Melbourne visitor-service team is taking toilet and drinking-water locations into an offline field app. Operations needs one stable asset key, the field crew needs to see which access status needs checking, and the importer accepts only a named GeoPackage layer in the WGS 84 with a fixed schema and coordinate precision.

Work through four checked stages to deliver a public-asset register for an offline field app. The Data tab carries the two raw GeoJSON layers. Each stage states its own hand-in contract; the last stage is a single named GeoPackage with stable keys, normalized access, verified coordinates and a fixed schema. The source observations are an OpenStreetMap teaching extract, not the City of Melbourne municipal records.

Build one final GeoPackage from the supplied toilet and drinking-water raw layers. Upload the file as your answer. Use the first source_order for each source_id within its source; omit records without geometry. Map trimmed, lowercased access_raw: yes/public → public, private/customers → restricted, everything else → unknown. Set needs_verification to integer 1 exactly when access is unknown, otherwise 0. Prefix each toilet source reference with T- and each drinking-water reference with W- to make asset_id; keep the original source_id too.

The handover contract is a single GeoPackage layer named public_assets, Point geometry in EPSG:4326 (WGS 84), coordinates rounded to six decimal places in longitude and latitude, and exactly these attributes in this order: asset_id text, source_id text, asset_kind text (toilet or drinking_water), access_class text (public, restricted, unknown), needs_verification integer. Verify that coordinates are WGS 84 longitude and latitude before rounding; do not relabel projected coordinates as degrees. Preserve OpenStreetMap attribution with a redistributed derivative.

The supplied layer

melbourne-toilets-raw — GeoJSON, EPSG:4326, Point geometry. The fields arrive under these names:

Converting this layer to a Shapefile cuts a field name to ten characters — source_order becomes source_ord — so read the names back after any conversion rather than assuming them.

melbourne-drinking-water-raw — GeoJSON, EPSG:4326, Point / geometry. The fields arrive under these names:

Converting this layer to a Shapefile cuts a field name to ten characters — source_order becomes source_ord — so read the names back after any conversion rather than assuming them.