GLE-Postgis-BLPS
Aus kvwmap
Version vom 23. Oktober 2023, 10:25 Uhr von Markus Hentschel (Diskussion | Beiträge)
#!/bin/bash
DOCKER='docker exec -i pgsql'
PGHOST='-h pgsql'
PGPORT='-p 5432'
PGDATABASE='-d xxxxxx'
PGUSER='-U xxxxxx'
PGOPTIONS='-v ON_ERROR_STOP=ON'
PSQL="${DOCKER} psql ${PGHOST} ${PGPORT} ${PGDATABASE} ${PGUSER} ${PGOPTIONS}"
pgConnection="PG:host='pgsql' port='5432' user='xxxxxx' dbname='xxxxxx' password='xxxxxx'"
schema="xplan"
# 17000 = User-ID von gisadmin
OGR2OGR="docker exec --user 17000 -i gdal ogr2ogr"
wfsURL="https://bauleitplaene-mv.de/dienste/basic?version=2.0.0&"
getFeature="service=WFS&request=GetFeature&srsname=EPSG:25833&typename="
featureTypes=("B_Plan" "F_Plan")
xplanURL="https://xleitstelle.de/downloads/xplanung/releases/"
xPlanV="${xplanURL}XPlanung Version 5.4/XPlanGML_5_4_Enumerationen_XML-Dictionary.XML"
blpMVURL="https://bauleitplaene-mv.de/codelist/"
################################################################################
############################## Schema-Definition ###############################
################################################################################
${PSQL} <<-SQL
SELECT to_regnamespace('${schema}') IS NULL AS createschema
\gset
\if :createschema
BEGIN;
CREATE SCHEMA "${schema}";
/*
* Data Type xplan:...
*/
CREATE TYPE "${schema}".xp_begruendungabschnitt AS (
schluessel text,
"text" text);
CREATE TYPE "${schema}".xp_gemeinde AS (
ags text,
rs text,
gemeindename text,
ortsteilname text);
CREATE TYPE "${schema}".xp_generattribut AS (
"name" text);
CREATE TYPE "${schema}".xp_plangeber AS (
"name" text,
kennziffer text);
CREATE TYPE "${schema}".xp_spezexternereferenz AS (
georefurl text,
art text,
referenzname text,
referenzurl text,
beschreibung text,
datum date,
typ text);
CREATE TYPE "${schema}".xp_textabschnitt AS (
schluessel text,
gesetzlichegrundlage text,
"text" text);
CREATE TYPE "${schema}".xp_verbundenerplan AS (
planname text,
rechtscharakter text,
nummer text,
verbundenerplan text);
CREATE TYPE "${schema}".xp_verfahrensmerkmal AS (
vermerk text,
datum date,
signatur text,
signiert bool);
/*
* Feature Type xplan:...
*/
CREATE TABLE "${schema}".bp_plan (
gml_id text NOT NULL,
"name" text NULL,
nummer text NULL,
planart text[] NULL,
sonstplanart text NULL,
gemeinde "${schema}".xp_gemeinde[] NULL,
rechtsstand text NULL,
verfahren text NULL,
externereferenz "${schema}".xp_spezexternereferenz[] NULL,
texte "${schema}".xp_textabschnitt[] NULL,
begruendungstexte "${schema}".xp_begruendungabschnitt[] NULL,
beschreibung text NULL,
kommentar text NULL,
plangeber "${schema}".xp_plangeber NULL,
planaufstellendegemeinde "${schema}".xp_gemeinde[] NULL,
internalid text NULL,
status text NULL,
aufstellungsbeschlussdatum date NULL,
technherstelldatum date NULL,
ausfertigungsdatum date NULL,
traegerbeteiligungsstartdatum date[] NULL,
traegerbeteiligungsenddatum date[] NULL,
auslegungsstartdatum date[] NULL,
auslegungsenddatum date[] NULL,
aenderungenbisdatum date NULL,
genehmigungsdatum date NULL,
inkrafttretensdatum date NULL,
rechtsverordnungsdatum date NULL,
satzungsbeschlussdatum date NULL,
veraenderungssperre bool NULL,
veraenderungssperredatum date NULL,
untergangsdatum date NULL,
erschliessungsvertrag bool NULL,
durchfuehrungsvertrag bool NULL,
staedtebaulichervertrag bool NULL,
gruenordnungsplan bool NULL,
erstellungsmassstab int NULL,
bezugshoehe numeric NULL,
hoehenbezug text NULL,
verfahrensmerkmale "${schema}".xp_verfahrensmerkmal[] NULL,
hatgenerattribut "${schema}".xp_generattribut[] NULL,
aendert "${schema}".xp_verbundenerplan[] NULL,
wurdegeaendertvon "${schema}".xp_verbundenerplan[] NULL,
raeumlichergeltungsbereich geometry(multipolygon, 25833) NOT NULL);
CREATE TABLE "${schema}".fp_plan (
gml_id text NOT NULL,
"name" text NULL,
nummer text NULL,
planart text NOT NULL,
sonstplanart text NULL,
gemeinde "${schema}".xp_gemeinde[] NOT NULL,
rechtsstand text NULL,
verfahren text NULL,
externereferenz "${schema}".xp_spezexternereferenz[] NULL,
texte "${schema}".xp_textabschnitt[] NULL,
begruendungstexte "${schema}".xp_begruendungabschnitt[] NULL,
beschreibung text NULL,
kommentar text NULL,
plangeber "${schema}".xp_plangeber NULL,
planaufstellendegemeinde "${schema}".xp_gemeinde[] NULL,
internalid text NULL,
status text NULL,
entwurfsbeschlussdatum date NULL,
aufstellungsbeschlussdatum date NULL,
technherstelldatum date NULL,
traegerbeteiligungsstartdatum date[] NULL,
traegerbeteiligungsenddatum date[] NULL,
auslegungsstartdatum date[] NULL,
auslegungsenddatum date[] NULL,
aenderungenbisdatum date NULL,
planbeschlussdatum date NULL,
genehmigungsdatum date NULL,
wirksamkeitsdatum date NULL,
untergangsdatum date NULL,
erstellungsmassstab int NULL,
bezugshoehe numeric NULL,
verfahrensmerkmale "${schema}".xp_verfahrensmerkmal[] NULL,
sachgebiet text NULL,
hatgenerattribut "${schema}".xp_generattribut[] NULL,
aendert "${schema}".xp_verbundenerplan[] NULL,
wurdegeaendertvon "${schema}".xp_verbundenerplan[] NULL,
raeumlichergeltungsbereich geometry(multipolygon, 25833) NOT NULL);
/*
* Enumerationen xplan:...
*/
SELECT format(
'CREATE TABLE %I.%I (wert text, beschreibung text, dokumentation text);',
'${schema}',
relname
)
FROM (
VALUES ('bp_planart'::text), ('bp_rechtsstand'), ('bp_verfahren'),
('fp_planart'), ('fp_rechtsstand'), ('fp_verfahren'),
('xp_externerefernztyp'), ('xp_rechtscharakterplanaenderung')
) AS v (relname)
\gexec
/*
* Enumerationen externe Codelisten
*/
SELECT format(
'CREATE TABLE %I.%I (wert text, beschreibung text);',
'${schema}',
relname
)
FROM (
VALUES ('bp_sonstplanart'::text),
('bp_status'),
-- ('fp_sonstplanart'),
('fp_status')
) AS v (relname)
\gexec
COMMIT;
\else
\q
\endif
SQL
################################################################################
###################### Download der Pläne und Codelisten #######################
################################################################################
${OGR2OGR} -f PostgreSQL \
"${pgConnection}" \
"WFS:${wfsURL}${getFeature}ms:B_Plan" \
-oo EXPOSE_GML_ID=NO \
-nln "${schema}.bp_plan_tmp" \
-lco UNLOGGED=ON \
-lco OVERWRITE=YES \
-lco GEOMETRY_NAME=wkb_geometry \
-lco SPATIAL_INDEX=GIST \
-forceNullable \
--config PG_USE_COPY YES \
--config OGR_TRUNCATE YES &
${OGR2OGR} -f PostgreSQL \
"${pgConnection}" \
"WFS:${wfsURL}${getFeature}ms:F_Plan" \
-oo EXPOSE_GML_ID=NO \
-nln "${schema}.fp_plan_tmp" \
-lco UNLOGGED=ON \
-lco OVERWRITE=YES \
-lco GEOMETRY_NAME=wkb_geometry \
-lco SPATIAL_INDEX=GIST \
-forceNullable \
--config PG_USE_COPY YES \
--config OGR_TRUNCATE YES &
wait
xplanDict=$(wget -q -O "-" "${xPlanV}" | tail -n +2)
bp_sonstplanartDict=$(wget -q -O "-" "${blpMVURL}BP_SonstPlanArt/BP_SonstPlanArt.xml" | tail -n +2)
bp_statusDict=$(wget -q -O "-" "${blpMVURL}BP_Status/BP_Status.xml" | tail -n +2)
#fp_sonstplanartDict=$(wget -q -O "-" "${blpMVURL}FP_SonstPlanArt/FP_SonstPlanArt.xml" | tail -n +2)
fp_statusDict=$(wget -q -O "-" "${blpMVURL}FP_Status/FP_Status.xml" | tail -n +2)
################################################################################
######################### Daten einlesen und bereinigen ########################
################################################################################
formString1=$( cat <<-SQL
INSERT INTO "${schema}".%I
SELECT split_part(xt.wert, ':', 2) AS wert,
xt.beschreibung,
trim(xt.dokumentation, E'\n') AS dokumentation
FROM xp_dictionary,
xmltable(
xmlnamespaces(
'http://www.opengis.net/gml/3.2' AS xmlns,
'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
'http://www.opengis.net/gml/3.2' AS gml
),
'/xmlns:Dictionary/xmlns:dictionaryEntry/xmlns:DefinitionCollection[@gml:id="%s"]/xmlns:definitionMember'
PASSING d
COLUMNS wert text PATH 'xmlns:Definition/xmlns:identifier',
beschreibung text PATH 'xmlns:Definition/xmlns:name',
dokumentation text PATH 'xmlns:Definition/xmlns:description'
) xt;
SQL
)
formString2=$( cat <<-SQL
INSERT INTO "${schema}".%I
SELECT xt.*
FROM xmltable(
xmlnamespaces(
'http://www.opengis.net/gml/3.1.1' AS xmlns,
'http://www.opengis.net/gml/3.1.1' AS gml
),
'/xmlns:Dictionary/gml:DictionaryEntry'
PASSING xmlparse(document %L)
COLUMNS wert text PATH 'gml:Definition/gml:identifier',
beschreibung text PATH 'gml:Definition/gml:name'
) xt;
SQL
)
${PSQL} <<-SQL
BEGIN;
-- Tabellen leeren (außer die zuvor importierten)
SELECT 'TRUNCATE TABLE '
|| string_agg(format('%I.%I', '${schema}', relname), ', ')
|| ';'
FROM pg_catalog.pg_class
WHERE (relnamespace, relkind, relpersistence) = ('${schema}'::regnamespace, 'r', 'p')
\gexec
-- Indizes löschen (außer die zuvor importierten)
SELECT format('DROP INDEX IF EXISTS %I.%I', relnamespace::regnamespace, relname)
FROM pg_catalog.pg_class
WHERE (relnamespace, relkind, relpersistence) = ('${schema}'::regnamespace, 'i', 'p')
\gexec
-- Enumerations-Tabellen füllen
CREATE TEMP TABLE xp_dictionary AS
SELECT xmlparse(document \$\$${xplanDict}\$\$) AS d\;
SELECT format(\$\$${formString1}\$\$, relname, enumeration)
FROM (
VALUES ('bp_planart'::text, 'BP_PlanArt'::text),
('bp_rechtsstand', 'BP_Rechtsstand'),
('bp_verfahren', 'BP_Verfahren'),
('fp_planart', 'FP_PlanArt'),
('fp_rechtsstand', 'FP_Rechtsstand'),
('fp_verfahren', 'FP_Verfahren'),
('xp_externerefernztyp', 'XP_ExterneReferenzTyp'),
('xp_rechtscharakterplanaenderung', 'XP_RechtscharakterPlanaenderung')
) AS v (relname, enumeration)
\gexec
-- Codelisten-Tabellen füllen
SELECT format(\$\$${formString2}\$\$, relname, enumeration)
FROM (
VALUES ('bp_sonstplanart'::text, \$\$${bp_sonstplanartDict}\$\$::text),
('bp_status', \$\$${bp_statusDict}\$\$),
--('fp_sonstplanart'::text, \$\$${fp_sonstplanartDict}\$\$),
('fp_status', \$\$${fp_statusDict}\$\$)
) AS v (relname, enumeration)
\gexec
/*
* BP_Plan
*/
CREATE TEMP TABLE bp_plan_13073 AS
SELECT DISTINCT ON (a.gml_id)
a.*
FROM "${schema}".bp_plan_tmp a
JOIN alkis_eigen.pp_kreis AS b
ON ST_Intersects(b.wkb_geometry, a.wkb_geometry);
CREATE TEMP TABLE bp_verbundenerplan AS
SELECT DISTINCT ON (gml_id, verbundenerplan)
gml_id,
verbundenerplan,
rechtscharakter
FROM (
SELECT b.gml_id, j.verbundenerplan, j.rechtscharakter
FROM bp_plan_13073 b,
json_populate_recordset(NULL::"${schema}".xp_verbundenerplan, b.aendert::json) j
WHERE j.verbundenerplan IS NOT NULL AND j.rechtscharakter IS NOT NULL
UNION
SELECT j.verbundenerplan, b.gml_id, j.rechtscharakter
FROM bp_plan_13073 b,
json_populate_recordset(NULL::"${schema}".xp_verbundenerplan, b.wurdegeaendertvon::json) j
WHERE j.verbundenerplan IS NOT NULL AND j.rechtscharakter IS NOT NULL
) foo
ORDER BY 1, 2, 3;
-- Lösche Verweise auf nicht existierende Pläne
DELETE FROM bp_verbundenerplan v
WHERE NOT EXISTS (SELECT FROM bp_plan_13073 b
WHERE b.gml_id = v.gml_id);
-- Lösche Verweise auf nicht existierende Pläne
DELETE FROM bp_verbundenerplan v
WHERE NOT EXISTS (SELECT FROM bp_plan_13073 b
WHERE b.gml_id = v.verbundenerplan);
INSERT INTO "${schema}".bp_plan
SELECT gml_id,
name,
nummer,
planart::text[],
sonstplanart,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_gemeinde
FROM json_populate_recordset(NULL::"${schema}".xp_gemeinde, gemeinde::json) j
), '{}') AS gemeinde,
rechtsstand,
verfahren,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_spezexternereferenz
FROM json_populate_recordset(NULL::"${schema}".xp_spezexternereferenz, externereferenz::json) j
), '{}') AS externereferenz,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_textabschnitt
FROM json_populate_recordset(NULL::"${schema}".xp_textabschnitt, texte::json) j
), '{}') AS texte,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_begruendungabschnitt
FROM json_populate_recordset(NULL::"${schema}".xp_begruendungabschnitt, begruendungstexte::json) j
), '{}') AS begruendungstexte,
beschreibung,
kommentar,
json_populate_record(NULL::"${schema}".xp_plangeber, plangeber::json)::"${schema}".xp_plangeber AS plangeber,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_gemeinde
FROM json_populate_recordset(NULL::"${schema}".xp_gemeinde, planaufstellendegemeinde::json) j
), '{}') AS planaufstellendegemeinde,
internalid,
(status::json->>'id') AS status,
aufstellungsbeschlussdatum::date,
technherstelldatum::date,
ausfertigungsdatum::date,
traegerbeteiligungsstartdatum::date[],
traegerbeteiligungsenddatum::date[],
auslegungsstartdatum::date[],
auslegungsenddatum::date[],
aenderungenbisdatum::date,
genehmigungsdatum::date,
inkrafttretensdatum::date,
rechtsverordnungsdatum::date,
satzungsbeschlussdatum::date,
veraenderungssperre::bool,
veraenderungssperredatum::date,
untergangsdatum::date,
erschliessungsvertrag::bool,
durchfuehrungsvertrag::bool,
staedtebaulichervertrag::bool,
gruenordnungsplan::bool,
erstellungsmassstab::int,
bezugshoehe::numeric,
hoehenbezug::text,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_verfahrensmerkmal
FROM json_populate_recordset(NULL::"${schema}".xp_verfahrensmerkmal, verfahrensmerkmale::json) j
), '{}') AS verfahrensmerkmale,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_generattribut
FROM json_populate_recordset(NULL::"${schema}".xp_generattribut, hatgenerattribut::json) j
), '{}') AS hatgenerattribut,
nullif(ARRAY(
SELECT ROW(tp.name, v.rechtscharakter, tp.nummer, tp.gml_id)::"${schema}".xp_verbundenerplan
FROM bp_verbundenerplan v
JOIN bp_plan_13073 tp
ON tp.gml_id = v.verbundenerplan
WHERE v.gml_id = p.gml_id
), '{}') AS aendert,
nullif(ARRAY(
SELECT ROW(tp.name, v.rechtscharakter, tp.nummer, tp.gml_id)::"${schema}".xp_verbundenerplan
FROM bp_verbundenerplan v
JOIN bp_plan_13073 tp
ON tp.gml_id = v.gml_id
WHERE v.verbundenerplan = p.gml_id
), '{}') AS wurdegeaendertvon,
ST_Multi(wkb_geometry)::geometry('MultiPolygon', 25833) AS raeumlichergeltungsbereich
FROM bp_plan_13073 p
ORDER BY gml_id;
DROP TABLE "${schema}".bp_plan_tmp;
CREATE UNIQUE INDEX bp_plan_gml_id
ON "${schema}".bp_plan
USING btree (gml_id);
CREATE INDEX bp_plan_raeumlichergeltungsbereich
ON "${schema}".bp_plan
USING gist (raeumlichergeltungsbereich);
/*
* FP_Plan
*/
CREATE TEMP TABLE fp_plan_13073 AS
SELECT DISTINCT ON (a.gml_id)
a.*
FROM "${schema}".fp_plan_tmp a
JOIN alkis_eigen.pp_kreis AS b
ON ST_Intersects(b.wkb_geometry, a.wkb_geometry);
CREATE TEMP TABLE fp_verbundenerplan AS
SELECT DISTINCT ON (gml_id, verbundenerplan)
gml_id,
verbundenerplan,
rechtscharakter
FROM (
SELECT b.gml_id, j.verbundenerplan, j.rechtscharakter
FROM fp_plan_13073 b,
json_populate_recordset(NULL::"${schema}".xp_verbundenerplan, b.aendert::json) j
WHERE j.verbundenerplan IS NOT NULL AND j.rechtscharakter IS NOT NULL
UNION
SELECT j.verbundenerplan, b.gml_id, j.rechtscharakter
FROM fp_plan_13073 b,
json_populate_recordset(NULL::"${schema}".xp_verbundenerplan, b.wurdegeaendertvon::json) j
WHERE j.verbundenerplan IS NOT NULL AND j.rechtscharakter IS NOT NULL
) foo
ORDER BY 1, 2, 3;
-- Lösche Verweise auf nicht existierende Pläne
DELETE FROM fp_verbundenerplan v
WHERE NOT EXISTS (SELECT FROM fp_plan_13073 b
WHERE b.gml_id = v.gml_id);
-- Lösche Verweise auf nicht existierende Pläne
DELETE FROM fp_verbundenerplan v
WHERE NOT EXISTS (SELECT FROM fp_plan_13073 b
WHERE b.gml_id = v.verbundenerplan);
INSERT INTO "${schema}".fp_plan
SELECT gml_id,
name,
nummer,
planart::text,
sonstplanart,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_gemeinde
FROM json_populate_recordset(NULL::"${schema}".xp_gemeinde, gemeinde::json) j
), '{}') AS gemeinde,
rechtsstand,
verfahren,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_spezexternereferenz
FROM json_populate_recordset(NULL::"${schema}".xp_spezexternereferenz, externereferenz::json) j
), '{}') AS externereferenz,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_textabschnitt
FROM json_populate_recordset(NULL::"${schema}".xp_textabschnitt, texte::json) j
), '{}') AS texte,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_begruendungabschnitt
FROM json_populate_recordset(NULL::"${schema}".xp_begruendungabschnitt, begruendungstexte::json) j
), '{}') AS begruendungstexte,
beschreibung,
kommentar,
json_populate_record(NULL::"${schema}".xp_plangeber, plangeber::json)::"${schema}".xp_plangeber AS plangeber,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_gemeinde
FROM json_populate_recordset(NULL::"${schema}".xp_gemeinde, planaufstellendegemeinde::json) j
), '{}') AS planaufstellendegemeinde,
internalid,
(status::json->>'id') AS status,
entwurfsbeschlussdatum::date,
aufstellungsbeschlussdatum::date,
technherstelldatum::date,
traegerbeteiligungsstartdatum::date[],
traegerbeteiligungsenddatum::date[],
auslegungsstartdatum::date[],
auslegungsenddatum::date[],
aenderungenbisdatum::date,
planbeschlussdatum::date,
genehmigungsdatum::date,
wirksamkeitsdatum::date,
untergangsdatum::date,
erstellungsmassstab::int,
bezugshoehe::numeric,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_verfahrensmerkmal
FROM json_populate_recordset(NULL::"${schema}".xp_verfahrensmerkmal, verfahrensmerkmale::json) j
), '{}') AS verfahrensmerkmale,
sachgebiet,
nullif(ARRAY(
SELECT ROW(j.*)::"${schema}".xp_generattribut
FROM json_populate_recordset(NULL::"${schema}".xp_generattribut, hatgenerattribut::json) j
), '{}') AS hatgenerattribut,
nullif(ARRAY(
SELECT ROW(tp.name, v.rechtscharakter, tp.nummer, tp.gml_id)::"${schema}".xp_verbundenerplan
FROM fp_verbundenerplan v
JOIN fp_plan_13073 tp
ON tp.gml_id = v.verbundenerplan
WHERE v.gml_id = p.gml_id
), '{}') AS aendert,
nullif(ARRAY(
SELECT ROW(tp.name, v.rechtscharakter, tp.nummer, tp.gml_id)::"${schema}".xp_verbundenerplan
FROM fp_verbundenerplan v
JOIN fp_plan_13073 tp
ON tp.gml_id = v.gml_id
WHERE v.verbundenerplan = p.gml_id
), '{}') AS wurdegeaendertvon,
ST_Multi(wkb_geometry)::geometry('MultiPolygon', 25833) AS raeumlichergeltungsbereich
FROM fp_plan_13073 p
ORDER BY gml_id;
DROP TABLE "${schema}".fp_plan_tmp;
CREATE UNIQUE INDEX fp_plan_gml_id
ON "${schema}".fp_plan
USING btree (gml_id);
CREATE INDEX fp_plan_raeumlichergeltungsbereich
ON "${schema}".fp_plan
USING gist (raeumlichergeltungsbereich);
-- Tabellen analysieren
SELECT format('ANALYZE %I.%I', relnamespace::regnamespace, relname)
FROM pg_catalog.pg_class
WHERE (relnamespace, relkind) = ('${schema}'::regnamespace, 'r')
\gexec
COMMENT ON SCHEMA "${schema}" IS
\$\$Wird über ${0} gefüllt. Zuletzt am $(date +%d.%m.%Y)\$\$;
COMMIT;
SQL
echo $SECONDS
exit
