Koppel waterschappen en CBS gemeentes en provincies aan BAG woonplaatsen #pdok #ogr2ogr #spatialite #gpkg #bash #bag #intersects #csv #xmlstarlet

README.md

Koppel BAG woonplaatsen aan waterschappen en CBS gemeentes en provincies

Draai het script om de waterschappen te koppelen met woonplaatsen, door middel van een Intersects join. Het resultaat van dit script is een data.gpkg bestand met de volgende laag:

• woonplaatsen uit de BAG WFS), gekopppeld met:
  • hwh/eenheden, in attributen waterschappen_{namen,codes} (woonplaats kan in meerdere waterschappen liggen)
  • woonplaatsen CBS in attributen {gemeente,provincie,landsdeel}_{naam,code}

Draai het script met:

wget -q https://gist.githubusercontent.com/arbakker/3940b074567a8afb7eccae2359cedefa/raw/script.sh && chmod +x script.sh
./script.sh

script.sh

#!/usr/bin/env bash
#
# MIT License
#
# Copyright (c) 2022 Anton Bakker
#
# https://choosealicense.com/licenses/mit/
#
# developed with:
# - "GDAL 3.4.1, released 2021/12"
# - "jq-1.6"
# - "sqlite3 3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5dalt1"

set -euo pipefail
output_gpkg="${1:-data.gpkg}"
temp=$(mktemp -u --suffix .gpkg)
rm -f "$output_gpkg"

echo "> downloading woonplaatsen from lv/bag WFS"
ogr2ogr -f GPKG "$temp" "WFS:https://service.pdok.nl/lv/bag/wfs/v2_0?request=getCapabilities&service=WFS" bag:woonplaats -nln woonplaatsen_src -nlt PROMOTE_TO_MULTI
ogr2ogr -update -f GPKG "$temp" "$temp" woonplaatsen_src -nln woonplaatsen -nlt MULTIPOLYGON > /dev/null # zet geometrietype expliciet op MULTIPOLYGON

echo "> downloading waterschappen from hwh/eenheden ATOM"
wget -q https://geodata.nationaalgeoregister.nl/hwh/eenheden/atom/v1_0/downloads/AdministrativeUnits.zip
unzip AdministrativeUnits.zip > /dev/null
ogr2ogr -update -f GPKG "$temp" AdministrativeUnits_ETRS89.gml AdministrativeUnit -t_srs EPSG:28992 -nln waterschappen -lco GEOMETRY_NAME=geom > /dev/null
rm AdministrativeUnits*

echo "> joining intersecting waterschappen to woonplaatsen"
ogr2ogr -overwrite -f GPKG "$temp" "$temp" -sql "SELECT woonplaatsen.identificatie, woonplaatsen.woonplaats, woonplaatsen.status, group_concat(waterschappen.nationalCode,',') as waterschappen_codes, group_concat(waterschappen.text,',') as waterschappen_namen, woonplaatsen.geom FROM woonplaatsen LEFT OUTER JOIN waterschappen ON Intersects(woonplaatsen.geom, waterschappen.geom) GROUP BY woonplaatsen.identificatie, woonplaatsen.woonplaats, woonplaatsen.status" -nln woonplaatsen_ws > /dev/null

echo "> download woonplaatsen CSV from CBS and transform JSON to CSV"
filter=$(mktemp --suffix .jq)
cat > $filter << "EOF"
def tocsv:
    (map(keys)
        |add
        |unique
    ) as $cols
    |map(. as $row
        |$cols
        |map($row[.]|tostring | gsub("\\s+";" ";"g") | gsub("^\\s?(?<a>.*?)\\s?$";"\(.a)";"g")) # gsub to trim whitespace - first normalize spaces, then trim leading and trailing whitespace
    ) as $rows
    |$cols,$rows[]
    | @csv;
tocsv
EOF

attr_map=$(cat <<"EOF"
{
    "Woonplaatsen": "woonplaats",
    "Woonplaatscode_1": "woonplaats_code",
    "Naam_2": "gemeente_naam",
    "Code_3": "gemeente_code",
    "Naam_4": "provincie_naam",
    "Code_5": "provincie_code",
    "Naam_6": "landsdeel_naam",
    "Code_7": "landsdeel_code"
}
EOF
)
curl -s -H "accept: application/json" https://opendata.cbs.nl/ODataFeed/odata/85210NED/UntypedDataSet | \
    jq -r ".value" | \
    jq --argjson _lookup "$attr_map" '.[] |= with_entries(if ($_lookup[.key] != null) then (.key = $_lookup[.key]) else (.) end)' | \
    jq -r -f $filter > woonplaatsen.csv

echo "> joining CBS woonplaatsen CSV to BAG woonplaatsen"
sqlite3 "$temp" -cmd ".mode csv" ".import woonplaatsen.csv woonplaatsen_csv" > /dev/null
rm -f woonplaatsen.csv

sqlite3 "$temp" << EOF
SELECT load_extension('mod_spatialite');
ALTER TABLE woonplaatsen_ws ADD woonplaats_code TEXT;
ALTER TABLE woonplaatsen_ws ADD gemeente_naam TEXT;
ALTER TABLE woonplaatsen_ws ADD gemeente_code TEXT;
ALTER TABLE woonplaatsen_ws ADD provincie_naam TEXT;
ALTER TABLE woonplaatsen_ws ADD provincie_code TEXT;
ALTER TABLE woonplaatsen_ws ADD landsdeel_naam TEXT;
ALTER TABLE woonplaatsen_ws ADD landsdeel_code TEXT;
UPDATE woonplaatsen_ws SET 
woonplaats_code = csv.woonplaats_code, gemeente_naam = csv.gemeente_naam, gemeente_code = csv.gemeente_code, provincie_naam = csv.provincie_naam, provincie_code = csv.provincie_code, landsdeel_naam = csv.landsdeel_naam, landsdeel_code = csv.landsdeel_code
FROM (select * FROM woonplaatsen_csv) AS csv 
WHERE substr(csv.woonplaats_code, 3 ) = woonplaatsen_ws.identificatie;
EOF

ogr2ogr -f GPKG "$output_gpkg" "$temp" woonplaatsen_ws -nln woonplaatsen

echo "> finished script, result saved in $(realpath $output_gpkg) in layer woonplaatsen"