GeODin zu PostGIS, aber wie?

Probleme, Fragen die mit der Installation und Anwendung der PostGIS-Erweiterung zu tun haben.
schweitzerili
Benutzer
Beiträge: 56
Registriert: Di 13. Okt 2015, 09:35

GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Di 28. Jun 2016, 15:15

Hallo,

die folgende Frage betrifft eine GeODin-DB welche per C/S-Modul als PostgrSQL-DB vorliegt.

Die Koordinaten der einzelnen Punkte werden hier in 2 Spalten xcoord und ycoord (bzw. 3 mit Höhe Z) gespeichert.

Dies sieht folgendermaßen aus:

Code: Alles auswählen

-- Table: public.geodin_loc_ssgkrzt1

-- DROP TABLE public.geodin_loc_ssgkrzt1;

CREATE TABLE public.geodin_loc_ssgkrzt1
(
  prj_id character varying(6),
  locid smallint,
  recid smallint,
  xcoord double precision,
  ycoord double precision,
  zcoordb double precision,
  zcoorde double precision,
  shortname character varying(15),
  longname character varying(40),
  kommentar character varying(76),
  tk25 smallint,
  brgtyp character varying(2),
  arnummer smallint,
  koordsys smallint,
  koordfin character varying(1),
  messverf smallint,
  brgort character varying(40),
  tk25name character varying(40),
  auftrag character varying(40),
  projekt character varying(40),
  autor character varying(18),
  brgzweck character varying(40),
  bohrz_von date,
  bohrz_bis date,
  bearb_dat date,
  bearbeiter character varying(18),
  bohrfirma character varying(40),
  lp_symnr smallint,
  lp_symcol smallint,
  lp_symwide double precision
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.geodin_loc_ssgkrzt1
  OWNER TO geodin;

-- Index: public.geodin_loc_ssgkrzt1_i1

-- DROP INDEX public.geodin_loc_ssgkrzt1_i1;

CREATE INDEX geodin_loc_ssgkrzt1_i1
  ON public.geodin_loc_ssgkrzt1
  USING btree
  (prj_id COLLATE pg_catalog."default");

-- Index: public.geodin_loc_ssgkrzt1_i2

-- DROP INDEX public.geodin_loc_ssgkrzt1_i2;

CREATE INDEX geodin_loc_ssgkrzt1_i2
  ON public.geodin_loc_ssgkrzt1
  USING btree
  (locid);

Nun möchte habe ich eine zweite DB angelegt mit PostGIS-Aufsatz, welche später für eine GIS-Anwendung (WEBGIS etc.) verwendet werden soll.

Nun zu den eigentlichen Fragen:

1. Kopiere ich die entsprechenden Spalten mit dem copy-Befehl aus der einen in die ander DB? Wenn ja in welcher Abfolge (siehe auch Frage 4)?

2. Wie muss die Geometrie-Spalte aussehen? Wir arbeiten mit Gauß-Krüger-Koordinaten 3 und 4 (also verschiedene EPSG-Codes) und würden diese eventuell gleich per Funktion in das UTM-System überführen. Ist es also möglich unterschiedliche Codes eingehen zu lassen und sie direkt als UTM-Koordinaten zu speichern in der geom-Spalte? Wenn ja, wie könnte oder sollte das aussehen?

3. Mit welchen ID's sollte ich am sinnvollsten arbeiten?

4.Zusatzfrage (für die Cracks =P): Wie müsste ein Trigger aussehen, welcher der GeODin-DB sagt: Sobald etwas Neues aus GeODin in meine DB kommt soll dies automatisch an die richtige Stelle in der PostGIS-DB kopiert werden?

Das wären erstmal alle Fragen ;) Für etwas Hilfe wäre ich dankbar.

Gruß Schweitzer

Gisman
Erfahrener Benutzer
Beiträge: 1094
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von Gisman » Di 28. Jun 2016, 15:46

schweitzerili hat geschrieben:Hallo,

die folgende Frage betrifft eine GeODin-DB welche per C/S-Modul als PostgrSQL-DB vorliegt.

Die Koordinaten der einzelnen Punkte werden hier in 2 Spalten xcoord und ycoord (bzw. 3 mit Höhe Z) gespeichert.

Dies sieht folgendermaßen aus:

Code: Alles auswählen

-- Table: public.geodin_loc_ssgkrzt1

-- DROP TABLE public.geodin_loc_ssgkrzt1;

CREATE TABLE public.geodin_loc_ssgkrzt1
(
  prj_id character varying(6),
  locid smallint,
  recid smallint,
  xcoord double precision,
  ycoord double precision,
  zcoordb double precision,
  zcoorde double precision,
  shortname character varying(15),
  longname character varying(40),
  kommentar character varying(76),
  tk25 smallint,
  brgtyp character varying(2),
  arnummer smallint,
  koordsys smallint,
  koordfin character varying(1),
  messverf smallint,
  brgort character varying(40),
  tk25name character varying(40),
  auftrag character varying(40),
  projekt character varying(40),
  autor character varying(18),
  brgzweck character varying(40),
  bohrz_von date,
  bohrz_bis date,
  bearb_dat date,
  bearbeiter character varying(18),
  bohrfirma character varying(40),
  lp_symnr smallint,
  lp_symcol smallint,
  lp_symwide double precision
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.geodin_loc_ssgkrzt1
  OWNER TO geodin;

-- Index: public.geodin_loc_ssgkrzt1_i1

-- DROP INDEX public.geodin_loc_ssgkrzt1_i1;

CREATE INDEX geodin_loc_ssgkrzt1_i1
  ON public.geodin_loc_ssgkrzt1
  USING btree
  (prj_id COLLATE pg_catalog."default");

-- Index: public.geodin_loc_ssgkrzt1_i2

-- DROP INDEX public.geodin_loc_ssgkrzt1_i2;

CREATE INDEX geodin_loc_ssgkrzt1_i2
  ON public.geodin_loc_ssgkrzt1
  USING btree
  (locid);

Nun möchte habe ich eine zweite DB angelegt mit PostGIS-Aufsatz, welche später für eine GIS-Anwendung (WEBGIS etc.) verwendet werden soll.

Nun zu den eigentlichen Fragen:

1. Kopiere ich die entsprechenden Spalten mit dem copy-Befehl aus der einen in die ander DB? Wenn ja in welcher Abfolge (siehe auch Frage 4)?

2. Wie muss die Geometrie-Spalte aussehen? Wir arbeiten mit Gauß-Krüger-Koordinaten 3 und 4 (also verschiedene EPSG-Codes) und würden diese eventuell gleich per Funktion in das UTM-System überführen. Ist es also möglich unterschiedliche Codes eingehen zu lassen und sie direkt als UTM-Koordinaten zu speichern in der geom-Spalte? Wenn ja, wie könnte oder sollte das aussehen?

3. Mit welchen ID's sollte ich am sinnvollsten arbeiten?

4.Zusatzfrage (für die Cracks =P): Wie müsste ein Trigger aussehen, welcher der GeODin-DB sagt: Sobald etwas Neues aus GeODin in meine DB kommt soll dies automatisch an die richtige Stelle in der PostGIS-DB kopiert werden?

Das wären erstmal alle Fragen ;) Für etwas Hilfe wäre ich dankbar.

Gruß Schweitzer
Juchhei, mein Spezialgebiet :)

Hallo!

Wir haben sowas gerade umgesetzt bzw bauen es weiter aus (SEP3 unter PostgreSQL/PostGIS mit Webfrontend und als Clients QGIS, ArcGIS und Geoserver/UMN).

zu 1.)
Kommt auf dein Design an. Wenn du eine eigene Datenbank einsetzen willst würde ich die notwendigen Daten mit Hilfe eines FDW dort einbinden. Man hat dann keinen Aufwand mit der Aktualisierung usw.

zu 2.)
Ich verwalte alle Geometrien unabhängig von den eingegebenen Koordinaten mit UTM32N weil wir auch eine spezielle Transforation benutzen, die auch in der DB integriert ist. Gemanagt wird das von einem Insert/Update-Trigger, der bei Änderungen an den Koordinaten/Geometrie die jeweils andere Seite anpasst. So ganz allgemein sieht die Geometrieerzeugung etwa so aus:

Code: Alles auswählen

select st_transform(st_setsrid(st_makepoint(xcoord, ycoord[, zcoordb]), <Eingabesystem>), 25832) from <Stammdatentabelle>
zu 3.)
Ich habe für die Aufschlußdaten die vorgegeben Schlüsselfelder prjid, locid,recid,... verwendet also einen mehrspaltigen Schlüssel erzeugt. Für die Weboberfläche und die Webservices gibt es noch eine zusätzliche Serial-Spalte mit Unique un NotNull, falls die nicht mit den PKs umgehen können. Das hat den Vorteil das ich in jeder Relation Zugriff auf alle übergeordneten Einträge habe ohne große Join-Orgien erzeugen zu müssen.

zu 4.)
Schau die mal FDW (Foreign Data Wrapper) an. Oder prüfe, ob du du deine eigenen Daten in einem eigenen Schema statt einer anderen Datenbank unterbringen kannst. Weitere Möglichkeiten wären da auch sicher Materialized Views, die einfacher zu händeln sind als Trigger wenn es nur ums Abbilden einer Urtabelle sind. Wie auch immer, hängt auch davon ab was du alles anstellen willst. Und Trigger gehen immer auf die Performance, allerdings macht sich dass hier für diese Anwendung eher nicht bemerkbar.

Soweit erstmal für den Anfang. Falls du noch Fragen hast, stell sie einfach ;)

Grüße Uwe

schweitzerili
Benutzer
Beiträge: 56
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Di 28. Jun 2016, 16:14

Hi Gisman,

wusste ich doch dass ich da bei dir richtig bin ;)

Die Geschichte mit der Transformationsfunktion dachte ich mir auch schon so. Nur hab ich keinen blassen Schimmer wie der Insert/Update-Trigger aussehen muss um 2 bzw 3 verschiedene Spalten von einer DB in eine einzige Spalte einer anderen (Postgis) DB zu bekommen. Plus einer direkten Transformation. Und das sobald sich etwas an der Ausgangs-DB ändert. ^^

Ich bin mit Triggern absolut nicht bewandert.

Unsere DB hat zur Zeit nur ca. 45000 Objekte, so dass die Performance keine Rolle spielen sollte zwecks Trigger bzw copy oder dem FDW, oder was meinst du?

Gisman
Erfahrener Benutzer
Beiträge: 1094
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von Gisman » Di 28. Jun 2016, 16:45

Hoi!

Für einen Trigger such ich morgen mal ein Beispiel zusammen, jetzt mach ich erst mal Feierabend. Ansonsten geht die Performance in die Knie wenn du sehr schnell viel einfügen willst. Ist aber für diesen Anwendungsfall so nicht gegeben glaube ich, es sei den ihr habt sehr schnelle Finger und bohrt wie die Raketenwürmer ;)

Grüßle

Uwe

schweitzerili
Benutzer
Beiträge: 56
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Mi 29. Jun 2016, 08:27

Nein, also bei uns wird jedes Projekt von Hand in Ruhe eingegeben... Und das sind maximal 10-20 Rammkernsondierungen am Tag... Unsere bisherige mdb hat das auch gerade so verkraftet ;)

Aber im Ernst: Ich bin immer wieder erstaunt wie gut die Performance der Postgre-DB ist. Einmal in GeODin geladen und es gibt quasi keine Verzögerung mehr beim Anzeigen, Aufrufen oder Editieren. Ich bin gespannt wie sich die Performance in Zukunft mit einem Firmeninternen GIS ist.

Ich bin gespannt auf den Trigger ^^

Bis dahin

Gisman
Erfahrener Benutzer
Beiträge: 1094
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von Gisman » Mi 29. Jun 2016, 08:39

Guten Morgen!

Mal ein Beispiel:
Zuerst bauen wir eine Stammtabelle, dies entspricht deiner GeoDyn-Tabelle:

Code: Alles auswählen

create table if not exists koordinaten(
  id integer,
  xcoord numeric,
  ycoord numeric,
  srid integer)
Jetzt wird eine neue Datenbank erstellt, darin muß als extension der postgres_fdw aktiviert sein, Dann führen wir folgendes aus:

Code: Alles auswählen

CREATE SERVER demo
   FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'localhost',port '5432',dbname 'GeoDyn-DB');
ALTER SERVER demo
  OWNER TO postgres;
GRANT USAGE ON FOREIGN SERVER demo TO postgres;
GRANT USAGE ON FOREIGN SERVER demo TO "public";
 
--Hier muß der lokale Nutzer auf einen Nutzer in der Fremddatenbank gemappt werden
create user mapping for postgres server demo options (user 'postgres', password 'xxxxxx');

--Hier können afair auch Aliase für die Felder verwendet werden, näheres im AHndbuch
create foreign table if not exists koordinaten(
  id integer,
  xcoord numeric,
  ycoord numeric,
  srid integer)
  server demo;
ALTER FOREIGN TABLE koordinaten
  OWNER TO postgres;
Und das wars vorerst auch schon, die Namen etc mußt du an deine Umgebung anpassen. Daten, die in der Geodyntabelle eingefügt werden, erscheinen jetzt auch in der anderen Datenbank.

Man kann auch einfach Daten an die Fremdtabelle anfügen:

Code: Alles auswählen

insert into koordinaten(id, xcoord, ycoord, srid) values(3,2567986, 5567892,31466);
Ein Update überlasse ich dem Kandidaten zur Übung ;).
Hier stehen also erstmal nur Koordinaten, die in verschiedenen Systemen dargestellt sein können. Diese sollen jedoch in einem einheitlichen Bezug hier UTM32N) dargestellt werden. Dazu benötigen wir einen View:

Code: Alles auswählen

create or replace view koordinaten_geom as
  select id, xcoord, ycoord, srid, st_transform(st_setsrid(st_makepoint(xcoord, ycoord), srid), 25832) as geom
    from koordinaten


Diesen können wir jetzt z.B. in QGIS anzeigen.
Soweit erstmal, wie man Daten z.B. über QGIS an den View abfügt probiere ich nach dem Kaffee ;)

Gruß Uwe

Gisman
Erfahrener Benutzer
Beiträge: 1094
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von Gisman » Do 30. Jun 2016, 07:45

Noch zu Trigger: Auf den hab ich heir verzichtet, weil er nicht nötig ist. In meiner Umgebung hab ich einen gebaut der mir beim Anlegen oder Ändern von Stammdaten bestimmte Felder z.B. tk25 oder die Gemeinde füllen soll oder die IDs hochzählt. Da ich die Geometrie in der Stammdatentabelle mitführe wird auch getestet ob sich die Koordinaten oder Geometrie geändert haben und das entsprechende nachgeführt. Da hier ein View genutzt wird ist das nicht notwendig.

Hier der Trigger zur Ansicht (das Aktualisieren von tk25 funktioniert hier im Moment noch nicht so wie es soll):

Code: Alles auswählen

CREATE OR REPLACE FUNCTION sep3.sync_geometry()
  RETURNS trigger AS
$BODY$
  declare
    srid integer := 25832;
  begin
  --check operation
  if TG_OP = 'INSERT' then
    raise notice 'insert';
    --check if names set
    if new.shortname is null then
      new.shortname := coalesce(new.longname, 'NEU');
    end if;
    if new.longname is null then
      new.longname := new.bver||'-'||coalesce(new.shortname, 'NEU');
    end if; 

    --check keys
    raise notice 'prj: %s, loc: %s, rec: %s', new.prjid, new.locid, new.recid;
    if new.prjid is null then
      new.prjid := 1;
    end if;
    if new.locid is null then
      select coalesce(max(locid), 0) + 1 into new.locid from sep3.s3stamm where prjid = new.prjid;
    end if;
    if new.recid is null then
      select coalesce(max(recid), 0) + 1 into new.recid from sep3.s3stamm where prjid = new.prjid and locid = new.locid;
    end if;
    raise notice 'prj: %s, loc: %s, rec: %s', new.prjid, new.locid, new.recid;
    -- check if a geometry can be made
    if new.geom is null and -500 = any(array[new.xcoord, new.ycoord, new.ksys]) is not null then
      new.geom := st_transform(st_setsrid(st_makepoint(new.xcoord, new.ycoord), new.ksys), srid);
    elsif new.geom is not null and st_srid(new.geom) > 0 then
      new.xcoord := st_x(new.geom);
      new.ycoord := st_y(new.geom);
      new.ksys := st_srid(new.geom);
      new.geom := st_transform(new.geom, srid);
    else
      raise notice 'no geometry possible!';
      return null;   
    end if;
  else 
    raise notice 'update';
    if (new.shortname <> old.shortname or new.bver <> old.bver) and old.longname is null then
      new.longname := new.bver||'-'||coalesce(new.shortname, 'NEU');
    end if; 
    if not new.geom = old.geom then
      raise notice 'geometry altered';
      new.xcoord := st_x(st_transform(new.geom, new.ksys));
      new.ycoord := st_y(st_transform(new.geom, new.ksys));
    elsif new.xcoord <> old.xcoord 
       or new.ycoord <> old.ycoord 
       or new.zcoordb <> old.zcoordb 
       or new.ksys <> old.ksys then
      raise notice 'coords altered';
      new.geom := st_transform(st_setsrid(st_makepoint(new.xcoord, new.ycoord),new.ksys), srid);
    end if;
  end if;
  -- get the tk
--   select dtknr 
--     into new.tk25 
--     from public.tk_tiles t1
--     where t1.m_id = '25' 
--       and t1.type = 'TK' 
--       and new.geom @ st_transform(t1.geom, srid);
  return new;     
  end; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Gisman
Erfahrener Benutzer
Beiträge: 1094
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von Gisman » Do 30. Jun 2016, 10:35

Noch zum Erstellen von Geometrien z.B. in QGIS. Bei uns trat die Anwendung auf von georeferenzierten Plänen direkt die Punkte in das Datenmodell zu digitalisieren. Hierzu kann man in dem obigen Beispiel, das kein dezidiertes Geometriefeld enthält eine Rule erstellen, die ein Insert auf den View in ein Insert auf die Foreigntable und damit auf die Stammdatentabelle in de anderen Datenbank auslöst.

Code: Alles auswählen

create or replace rule insert_geom as 
  on insert to koordinaten_geom do instead
  insert into koordinaten (id, xcoord, ycoord, srid)
    values ((select max(id) from koordinaten)+1, st_x(new.geom), st_y(new.geom), st_srid(new.geom));
Man bindet den View als Layer in QGIS oder einem anderen GIS ein und startet die Bearbeitung. Beim speichern der erfassten Punkte wird ein Insert ausgelöst der anschliessend über die Rule modifiziert und umgeleitet wird. So kann man ohne real vorhandenes Geometriefeld im GIS digitalisieren.

Grüßle Uwe

schweitzerili
Benutzer
Beiträge: 56
Registriert: Di 13. Okt 2015, 09:35

Re: GeODin zu PostGIS, aber wie?

Beitrag von schweitzerili » Fr 1. Jul 2016, 08:51

Oha... ich habe gerade leider mal wieder andersweitig viel zu tun und bin nächste Woche im Urlaub.

Ich habe es mal kurz überflogen und äh habe keine Ahnung! =D das schau ich mir nach meinem Urlaub mal genauer an und danke dir an dieser Stelle schon einmal für deine Mühe.

Eine kleine Bemerkung/Frage habe ich aber schon nach dem Überfliegen, sorry also falls doch schon irgendwo drin steht ^^:

Mir ist es wichtig, dass unsere 2. DB (also die, in welche aus der Geodin-DB reinkopiert werden soll) eine wirklich bestehende DB mit Tabelle und drumherum ist. Das heißt ich möchte keine Schattenkopie oder eine Tabelle die mir Daten aus der originalen Geodin-DB anzeigt, ohne das die Daten wirklich in der neuen DB sind... Ich brauche quasi eine neue DB die physisch real auf dem Server existiert mit Daten die quasi geklont und transformiert sind ^^ Die Frage wäre nun: Macht dies dein Beispiel mit dem view und dem mapping? ^^

Ich habe nämlich vor aus einer zweiten zukünftigen DB (Laborversuche) zusätzliche Daten in die neu erstellte DB den einzelnen Projekten zu zuordnen ^^

Also soweit schon mal danke und bis nach meinem Urlaub ^^

Grüße

Gisman
Erfahrener Benutzer
Beiträge: 1094
Registriert: Fr 20. Jan 2006, 15:30
Wohnort: Pfalz
Kontaktdaten:

Re: GeODin zu PostGIS, aber wie?

Beitrag von Gisman » Fr 1. Jul 2016, 09:26

Hallo!

Dann mache aus der Fremdtabelle einen Materialized View statt eines normalen Views in der 2. Datenbank. Das funktioniert im wesentlichen wie eine Tabelle, ist physikalisch vorhanden und kann mit indices etc. versehen werden. Nur das Update ist deutlich einfacher und kann ab Postgres 9.5 auch ohne Unterbrechung während des Refreshs stattfinden. Vorteil ist hier, das du die Berechtigungen auf den MV individuell und unterschiedlich zur Originaltabelle erzeugen kannst während der View immer die Berechtigungen der Originaltabelle voraussetzt. Ist nur zu beachten wie mit gelöschten Einträgen der Originaltabelle umgegangen werden soll bzw. ob das vorkommen kann.
Wie auch immer, ich wünsch dir einen schönen Urlaub. Bei mir ist es übernächste Woche soweit ;)

Gruß Uwe

Antworten