[PostNAS Suite] ALKIS NAS xml-File CRS wechseln

[Jürgen E. Fischer]

Moin Micha,

On Fri, 06. Sep 2024 at 16:06:43 +0200, Der Laie via NAS wrote:
> Am 22.07.24 um 18:17 schrieb Jürgen E. Fischer via NAS:
> > SELECT format(E'DROP TRIGGER IF EXISTS %I ON %I.%I;\nCREATE TRIGGER %I BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE PROCEDURE inplace_transform();',
> >          a.table_name || '_transform', a.table_schema, a.table_name,
> >          a.table_name || '_transform', a.table_schema, a.table_name)
> >      FROM information_schema.columns a
> >      JOIN information_schema.columns b ON a.table_schema=b.table_schema AND a.table_name=b.table_name AND b.column_name='wkb_geometry'
> >      WHERE a.table_schema=:'alkis_schema'
> >        AND substr(a.table_name,1,3) IN ('ax_','ap_','ln_','lb_','au_','aa_')
> >        AND a.column_name='gml_id';
> > \gexec

> 2024-09-06T14:52:07 >  CREATE TRIGGER ax_vegetationsmerkmal_transform BEFORE INSERT ON alkis_import.ax_vegetationsmerkmal FOR EACH ROW EXECUTE PROCEDURE inplace_transform();|
> 2024-09-06T14:52:07 > psql:preprocessing.d/1_transform.sql:23: NOTICE:  trigger "ax_ausfuehrendestellen_transform" for relation "alkis_import.ax_ausfuehrendestellen" does not exist, skipping|
> 2024-09-06T14:52:07 > psql:preprocessing.d/1_transform.sql:23: ERROR:  "ax_ausfuehrendestellen" is a view|
> 2024-09-06T14:52:07 > DETAIL:  Views cannot have row-level BEFORE or AFTER triggers.|

Achso, der View hat auch "gml_id" und "wkb_geometry".

Ergänze noch

    JOIN information_schema.tables c ON b.table_schema=c.table_schema AND b.table_name=c.table_name AND c.table_type='BASE TABLE'

damit nur die Tabellen mit Triggern versehen werden.


Jürgen

-- 
Jürgen E. Fischer
Dipl.-Inf. (FH)           norBIT GmbH               Tel. +49-4931-918175-31
Software Engineer         Rheinstraße 13            Fax. +49-4931-918175-50
Geschäftsführer           D-26506 Norden              https://www.norbit.de
-------------- nächster Teil --------------
\set alkis_transform false

\ir ../config.sql

\if :alkis_transform

SET search_path TO :"alkis_schema",public;

SELECT
  find_srid(:'alkis_schema', 'ax_flurstueck', 'wkb_geometry')=25832 AS utm32,
  find_srid(:'alkis_schema', 'ax_flurstueck', 'wkb_geometry')=25833 AS utm33;
\gset

\if :utm32
CREATE OR REPLACE FUNCTION inplace_transform() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF substr(NEW.gml_id, 3, 2) NOT IN ('BW','BY','HB','HE','HH','NW','RP','SH','SL','ST','TH') THEN
    NEW.wkb_geometry := st_transform(st_setsrid(NEW.wkb_geometry, 25833), 25832);
  END IF;
  RETURN NEW;
END;
$$ SET search_path TO :"alkis_schema";
\endif

\if :utm33
CREATE OR REPLACE FUNCTION inplace_transform() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF substr(NEW.gml_id, 3, 2) IN ('BW','BY','HB','HE','HH','NW','RP','SH','SL','ST','TH') THEN
    NEW.wkb_geometry := st_transform(st_setsrid(NEW.wkb_geometry, 25832), 25833);
  END IF;
  RETURN NEW;
END;
$$ SET search_path TO :"alkis_schema";
\endif

SELECT format(E'DROP TRIGGER IF EXISTS %I ON %I.%I;\nCREATE TRIGGER %I BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE PROCEDURE inplace_transform();',
        a.table_name || '_transform', a.table_schema, a.table_name,
        a.table_name || '_transform', a.table_schema, a.table_name)
    FROM information_schema.columns a
    JOIN information_schema.columns b ON a.table_schema=b.table_schema AND a.table_name=b.table_name AND b.column_name='wkb_geometry'
    JOIN information_schema.tables c ON b.table_schema=c.table_schema AND b.table_name=c.table_name AND c.table_type='BASE TABLE'
    WHERE a.table_schema=:'alkis_schema'
      AND substr(a.table_name,1,3) IN ('ax_','ap_','ln_','lb_','au_','aa_')
      AND a.column_name='gml_id';
\gexec

\endif
-------------- nächster Teil --------------
Ein Dateianhang mit Binärdaten wurde abgetrennt...
Dateiname   : signature.asc
Dateityp    : application/pgp-signature
Dateigröße  : 833 bytes
Beschreibung: nicht verfügbar
URL         : <http://lists.osgeo.org/pipermail/nas/attachments/20240906/65285198/attachment.sig>
-------------- nächster Teil --------------
Ein eingebundener Text mit undefiniertem Zeichensatz wurde abgetrennt.
Name: Pflichtangaben
URL: <http://lists.osgeo.org/pipermail/nas/attachments/20240906/65285198/attachment.ksh>