GK3-Koordinaten aus einzelnen Spalten in Geometriespalte übernehmen

Hallo!
Ich habe eine bestehende Tabelle in der es jeweils Spalten für die X-, Y-, und Z-Koordinate gibt.
Die Tabelle war nicht für die PostGis-Verwendung vorgesehen.
Das konnte ich ändern, indem ich eine Geometriespalte (geom (POINT))) hinzugefügt habe und einen Spatial Index erstellt habe.
Wie kann ich den Inhalt der X- und Y-Spalte automatisch an die Geometreispalte inform von (‘POINT ( X Y )’, 31467) weiter geben?
Weiss mir da vieleicht Jemand Rat???

Automatisch? Also wenn da Daten eingegeben oder verändert werden? Via TRIGGER, PostGIS hat Funktionen wie ST_GeomFromText() und weitere. Ich würde aber NICHT das in den bisherigen 3 Spalten UND in einer Geometry-Spalte speichern, das ist redundant.


Andreas

Hallo und schonmal Danke.
Ja, ich weiß das es redunant ist, aber der Programmierer der Datenbank war leider so schlau schon Postgres zu benutzen und die Daten über eine eigene Programmoberfläche einzugeben.
Nur Postgis für die Koordinaten zu nutzen um sie dann auch noch im GIS nutzen können war leider aus.
Da ich die Daten aber auch über QGIS benötige, dachte ich es wäre die eleganteste Lösung einfach eine weitere Spalte mit den Geometriedaten anzulegen und diese dann automatisch mit der Eingabe in die X- und- Y-Spalte an die Geometriespalte zu übergeben.
Eine Script welches man nach der Eingabe durchlaufen lässt wäre da evtl auch noch eine Lösung.
Nur leider sind meine Programmierkenntnisse in Gostgres/Psotgis rel beschränkt.
Die Funktion ST_GeomFromText() habe ich auch schon entdeckt, nur noch nicht zum laufen bringen können, da so wie ich es verstehe, diese Funktion quasi ja auf bestehende Datensätze zugreift und nicht die Daten bei Eingabe automatisch im gewünschten Format in die Letzte Zeile mit einträgt (Geometriespalte). Oder habe ich da etwas falsch verstanden?

Ich habe hier nochmal den bereits modifizierten Create-Abschnitt der Tabelle mal eingefügt:

CREATE TABLE messdaten (
messdaten_id uuid DEFAULT ‘00000000-0000-0000-0000-000000000000’::uuid NOT NULL,
vorgang_id uuid DEFAULT ‘00000000-0000-0000-0000-000000000000’::uuid,
dokuschritt_id uuid DEFAULT ‘00000000-0000-0000-0000-000000000000’::uuid,
x character varying(20) DEFAULT ‘kein Wert’::character varying,
y character varying(20) DEFAULT ‘kein Wert’::character varying
,
z character varying(20) DEFAULT ‘kein Wert’::character varying,
code character varying(20) DEFAULT ‘kein Wert’::character varying,
punktkennung character varying(20) DEFAULT ‘kein Wert’::character varying,
“timestamp” timestamp with time zone DEFAULT now(),
grabungsleiter character varying(50) DEFAULT ‘kein Wert’::character varying,
version_pool integer DEFAULT 0,
version_lokal integer DEFAULT 1,
deprecated integer DEFAULT 0,
geom GEOMETRY(Point, 31467),
);
ALTER TABLE public.messdaten OWNER TO postgres;
REVOKE ALL ON TABLE messdaten FROM PUBLIC;
REVOKE ALL ON TABLE messdaten FROM postgres;
GRANT ALL ON TABLE messdaten TO postgres;
GRANT ALL ON TABLE messdaten TO PUBLIC;

Die Daten kommen über eine eigens programmierte Benutzerobefläche per INSERT in die Datenbank, die Geometriezeile habe ich hinzugefügt.
Die Tabelle kann ich so auch problemlos in QGIS einbinden, es fehlen halt leider noch die korrekten Koordinaten.
So muss ich leider die vorgegebene Struktur nutzen, aber kann sie ja erweitern.
Ich hatte bereits versucht über SELECT und ST_GeomFromText() über der Zeile mit Alter Table die Daten weiter zu geben, leider mit Fehlermeldung…

Also, mal als Beispiel:

test=*# select * from dresden;
 id |     name     |  laenge   |  breite   
----+--------------+-----------+-----------
  1 | Frauenlirche | 13.741522 | 51.051873
(1 row)

Nun noch die geometry-Spalte:

test=*# alter table dresden add column g geometry;
ALTER TABLE

Diese ist ja nun leer, also müssen wir sie mit dem, was wir haben, füllen. Dazu gibt es in SQL UPDATE:

test=*# update dresden set g = st_geomfromtext('POINT(' || laenge || ' ' || breite || ')');
UPDATE 1
test=*# select * from dresden;
 id |     name     |  laenge   |  breite   |                     g                      
----+--------------+-----------+-----------+--------------------------------------------
  1 | Frauenlirche | 13.741522 | 51.051873 | 01010000006E8786C5A87B2B40D04543C6A3864940
(1 row)

test=*#

Das war nur ein Datensatz. Frage an Dich: was würde mit 1 Million Datensätze passieren?

(PS.: ich bin jetzt mit den SRID’s etwas unsicher, ob das Beispiel da korrekt ist. Aber prinzipiell geht es so).


Für neu hinzukommende Datensätze kannst Du das via TRIGGER ausführen, dann ist das immer aktuell. Oder du läßt die Spalte weg in Deiner Tabelle und erstellst einen VIEW:

test=*# alter table dresden drop column g;
ALTER TABLE
test=*# create view dresden_with_g as select id, name, st_geomfromtext('POINT(' || laenge || ' ' || breite || ')') from dresden; 
CREATE VIEW
test=*# select * from dresden_with_g ;
 id |     name     |              st_geomfromtext               
----+--------------+--------------------------------------------
  1 | Frauenlirche | 01010000006E8786C5A87B2B40D04543C6A3864940
(1 row)

Wenn ich so drüber nachdenke, ist das vielleicht sogar die bessere Variante, zumindest solange Selects auf diesen VIEW nicht sehr viele Ergebnisse liefern, wegen der Umrechnung der Koordinaten.

Hallo Andreas,
ich muss dir erst mal sagen das du mein Held des Tages bist!!!
SUPER! Nochmal vielen lieben Dank!
So langsam verstehe ich auch ein bissl den Syntax und dann macht das gleich viel mehr Spaß, wenn man dann auch noch die richtigen Befehle weiß.
Hat mir schonmal sehr geholfen.
Habe das Update mit:

update public.messdaten set geom = st_geomfromtext(‘POINT(’ || x || ’ ’ || y || ‘)’,31467);

durchlaufen lassen, hat wunderbar funktioniert.
Habe den View auch mit:

CREATE VIEW messdaten_geom geometry AS SELECT dokuschritt_id, punktkennung, st_geomfromtext(‘POINT(’ || x || ’ ’ || y || ‘)’,31467) FROM “public”.“messdaten” ;

erstellen können, allerdings konnte QGIS nicht auf die Tabelle zugreifen.
Meine Vermutung ist das das View auch nochmal explizit auf die Projektion (SRID) eingestellt sein muss, hatte das mal mit:

CREATE VIEW messdaten_geom GEOMETRY(POINT, 31467) AS SELECT dokuschritt_id, punktkennung, st_geomfromtext(‘POINT(’ || x || ’ ’ || y || ‘)’,31467) FROM “public”.“messdaten” ;

versucht, allerdings ergab das einen Syntaxfehler.
Kann ich den View so überhaupt noch mal auf das SRID einstellen?

VIele Grüße Mirko