GLE-Postgis-BLPS
Aus kvwmap
#!/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