Software • 03. Februar 2025
Tipps & Tricks für die Geodatenverarbeitung
PostGIS ist die räumliche Erweiterung zur Verarbeitung von Geodaten im relationalen Datenbankmanagementsystem PostgreSQL. Die Extension stellt Datentypen und Funktionen bereit, welche es erlauben, vektorbasierte Geodaten zu speichern und mittels SQL zu verarbeiten. PostGIS ist Open Source und konkurriert mit der ESRI ArcSDE und Oracle Spatial, die ähnliche Funktionalitäten bereitstellen.
Wenn Sie bereits PostGIS einsetzen, gibt Ihnen dieser Beitrag Tipps und liefert Ideen, wie Sie damit effektiver arbeiten können.
Wenn Sie noch nicht mit PostGIS arbeiten, erhalten Sie in diesem Beitrag einen Einblick, was über das einfache Speichern von Geodaten in einer PostGIS-Datenbank hinaus möglich ist.
DBeaver als Alternative für pgAdmin
Dass sich PostgreSQL-Datenbanken mit pgAdmin verwalten lassen, leuchtet ein und wird oft auch so praktiziert. Für die Administration von Datenbanken funktioniert es als Werkzeug sehr gut. Wenn Sie jedoch mehr Abfragen schreiben und die Ergebnisse visualisiert haben möchten, empfehle ich DBeaver als Software.
DBeaver unterstützt eine Vielzahl von relationalen Datenbanksystemen (MSSQL, Oracle, MySQL/MariaDB, SQLite …). Das Schreiben von Abfragen wird durch eine hervorragende Autovervollständigung unterstützt, einzelne Abfragen lassen sich per Tastatur ausführen und räumliche Daten auf verschiedenen Karten darstellen.
Die Vorteile:
- Autovervollständigung und -formatierung für Abfragen
- Einfache Ausführung einzelner Statements, die mit Strg + Enter durch Leerzeichen getrennt sind
- Unterstütztung verschiedener Arbeitsbereiche „Workspaces“ mit jeweils eigenen Verbindungen zu Datenbanken
- Automatische Vorschau auf Geodaten als WKT in den Abfrageergebnissen
- Visualisierung der Geodaten auf interaktiven Webkarten - mit der Möglichkeit, eigene Karten hinzuzufügen
- Visualisierung der Beziehungen zwischen Tabellen
- Die Nutzung der pg_service Files über die Angabe der Verbindungs-URL ist ebenfalls möglich: jdbc:postgresql://?service=<pg_service_name>
Mein Tipp: Probieren Sie DBeaver einfach mal aus. Ich persönlich möchte die Software für meine tägliche Arbeit nicht mehr missen. Die (kostenlose) Community-Version reicht dabei vollkommen aus und beschleunigt das Schreiben von Abfragen deutlich. Mit der Visualisierung der Geodaten auf Karten benötigt man kein zusätzliches GIS zur Anzeige der Daten mehr und kann in nur einem Programm arbeiten.
Kommen wir nun zu den Möglichkeiten in PostGIS-Datenbanken zu arbeiten, unabhängig davon, welchen Client Sie benutzen.
Aufteilen und Zusammenfassen von Geometrien
Die sogenannten “Multipart-Features” bestehen aus mehreren Teilgeometrien, die zusammengehören,und können ein Multipunkt, Multipolygon oder eine Multilinie sein. Anders ausgedrückt: Eine Multilinie ist eine Linie, die aus zwei Linien besteht, die zusammengehören, und ein Objekt mit gemeinsamen Attributen und einer ID bilden. Für verschiedene Verarbeitungsschritte muss man diese aufteilen und wieder zusammenführen. Hier möchte ich Ihnen einen Überblick dazu geben, wie das Aufteilen mit räumlichem SQL möglich ist.
Die Umwandlung von Multi- zu Single-Geometrien ist mittels “(ST_Dump(geom)).geom” ausführbar. Im ersten Schritt wird ein Set von Zeilen mit den einzelnen Teilen einer Geometrie zurückgegeben, mit den Feldern “geom” und “path” welches die jeweiligen Teile nummeriert. Ergänzt man die äußeren Klammern, so kann man direkt die Geometrien aus der Spalte “geom” abrufen:
SELECT fid, name, (ST_Dump(geom)).geom AS geom
FROM osm.forest f
Die gegenteilige Operation ist das Zusammenführen von Objekten. Die Aggregation mit ST_Collect() “sammelt” Geometrien in Multigeometrien oder Geometriesammlungen, je nachdem ob die Geometrietypen zusammenpassen oder nicht. In Verbindung mit der GROUP BY-Anweisung von SQL erfolgt eine Veränderung der Geometrien dabei nicht. ST_Collect() ist dabei die Aggregatfunktion, welche die Geometrien auflistet vergleichbar mit ARRAY_AGG() als SQL-Funktionen.
SELECT fid, ST_Collect(geom) AS geom
FROM (
SELECT fid, name, (ST_Dump(geom)).geom AS geom
FROM osm.forest ) s
GROUP BY fid
Die Funktion ST_Union fasst Geometrien zu Multigeometrien zusammen und vereinigt dabei sich überlappende Flächen zu neuen Geometrien. Wenn es nur eine Geometrie gibt, so wird diese nicht verändert. Welche Geometrien zusammengeführt werden, wird wieder über GROUP BY definiert. Es handelt sich dabei erneut um eine Aggregatfunktion. Möchten Sie keine Vermischung von Single- und Multi-Geometrien, so bietet sich die Umwandlung aller Geometrien in Multigeometrien mit ST_Multi() an.
SELECT fid, ST_Multi(ST_Union(geom)) AS geom
FROM (
SELECT fid, name, (ST_Dump(geom)).geom AS geom
FROM osm.forest ) s
GROUP BY fid
Außerdem möchte ich auf die Zerlegung von großen Polygonen in Teilpolygone mittels ST_Subdivide() hinweisen.
Ähnlich wie ST_Dump() handelt es sich dabei um eine Funktion, die ein Set zurückgibt. Weil dabei nur eine Spalte ausgegeben wird, ist die Syntax jedoch einfacher. Als Parameter kann man die maximale Anzahl von Stützpunkten angeben, die ein Teilpolygon nach der Verarbeitung haben soll. Die Funktion ist besonders für die Speicherung und Verarbeitung sehr großer Polygone interessant, damit diese besser mit einem Index funktionieren und schneller laden. Ansonsten muss möglicherweise, obwohl nur wenige Stützpunkte betroffen sind, ein Polygon mit tausenden Stützpunkten verarbeitet werden, was viel Rechenzeit benötigt. Speichert man das Ergebnis als neue Tabelle mit Index auf die Geometrie ab, kann die Geodatenverarbeitung sehr schnell erfolgen.
SELECT fid, ST_Subdivide(geom, 150) AS geom
FROM osm.forest f
WHERE name = 'Dresdner Heide'
Umkreissuche und nächster Nachbar mit SQL
Häufig ist die Suche nach bestimmten Objekten in einem definierten Umkreis notwendig. Dafür sollten Sie nicht die Geometrie puffern und anschließend eine Verschneidung durchführen. Schnellere Ergebnisse unter Nutzung der Indexe auf Geometrien erhalten Sie, wenn Sie ST_Dwithin() in PostGIS verwenden.Als Parameter müssen Sie dazu eine Geometriespalte, eine Geometrie (es muss nicht unbedingt ein Punkt sein) und zuletzt die Distanz angeben. Zu beachten ist, dass die Geometrien die gleiche SRID haben müssen. Ist dies nicht der Fall, ist eine Projizierung der Koordinaten des Startpunktes möglich und beeinflusst die Ausführungsgeschwindigkeit nicht negativ. So erhält man alle Shops im Umkreis von 200 Metern:
SELECT fid, name, shop, geom
FROM osm.poi p
WHERE ST_Dwithin(p.geom, ST_Transform(ST_SetSRID(ST_MakePoint(13.746209, 51.062890), 4326), 32633), 200)
AND shop IS NOT NULLEine verwandte Art der Abfrage ist die Suche nach dem einen nächstgelegenen Punkt und nicht nach der Menge an möglichen Ergebnissen. Diese Art der Suche wird auch als nächste Nachbarsuche (Nearest neighbor search) bezeichnet. Mit räumlichem SQL lässt sich das selbstverständlich auch umsetzen. Dafür müssen Sie zwei Abfragen ineinander verschachteln.
Die äußere Abfrage liefert zum einen die Ausgangspunkte für die Suche, zum anderen auch die Ergebnisse. Die innere Abfrage sucht jeweils das nächstgelegene Ergebnis heraus über das ORDER BY und liefert nur ein Ergebnis durch das LIMIT 1. Dafür wird das Konstrukt eines CROSS JOIN LATERAL genutzt. Ein CROSS JOIN betrachtet dabei alle Ergebnisse aus beiden Tabellen. Das Schlüsselwort LATERAL erlaubt die Verwendung von Werten aus der äußeren Abfrage in der inneren Abfrage. Sie erhalten folglich ein Konstrukt, das es erlaubt, für jedes Ergebnis der äußeren Abfrage eine neue Abfrage zu starten.
SELECT p1.fid, p1.name AS name1, q.name AS name2,
ST_Distance(p1.geom, q.geom) AS distance, p1.geom::geometry(point, 32633) AS geom
FROM osm.poi p1
CROSS JOIN LATERAL (
SELECT p2.name, geom
FROM osm.poi p2
WHERE shop = 'supermarket'
ORDER BY ST_Distance(p1.geom, p2.geom)
LIMIT 1
) q
WHERE shop = 'bakery';Abfrage Ergebnisse mit SQL weiterverarbeiten
Abschließend möchte ich Ihnen bespielhaft noch ein komplexes SQL-Konstrukt vorstellen, das sich für eine ganze Reihe von Fragestellungen eignet. Sie können damit, wie im Beispiel gezeigt, Daten in mehrere verknüpfte Tabellen einfügen, die Daten für weitere Abfragen verwenden, Verzweigungen einbauen oder daraus SQL erzeugen.
Als Beispiel nutze ich hier zwei Tabellen mit einer Fremdschlüsselbeziehung, die Besuche von Attraktionen speichern sollen. Wenn man eine neue Attraktion und einen Besuch dieser hinzufügen möchte, so kann man das nicht in einer Abfrage erledigen.
CREATE SCHEMA demo;
CREATE TABLE demo.poi (
fid serial NOT NULL,
"name" text NULL,
geom geometry(point, 4326) NULL,
CONSTRAINT poi_pkey PRIMARY KEY (fid)
);
CREATE TABLE demo.poi_visit (
fid serial NOT NULL,
poi_fid int REFERENCES demo.poi(fid),
visit timestamp NULL,
CONSTRAINT poi_visit_pkey PRIMARY KEY (fid)
);Das folgende Konstrukt nutzt die prozedurale Sprache PL/pgSQL als Erweiterung von PostgreSQL, welche Schleifen und Bedingungen erlaubt.
In einem ersten Schritt wird eine Abfrage ausgeführt, deren Ergebnisse in einer Schleife Zeile für Zeile verarbeitet werden. Dabei stehen die Ergebnisse der Abfrage als Variablen r.<Spaltenname> bereit und können mehrfach verwendet werden. In meinem Beispiel nutze ich das, um die Daten in zwei über einen Fremdschlüssel verknüpfte Tabellen einzufügen. Damit die Schlüssel zueinander passen, verwende ich die Funktion nextval(), um aus der Sequenz für den Primärschlüssel hochzuzählen und rufe dann den aktuellen Wert mit currval() ab. Damit kann ich auf die jeweils passenden Werte verweisen und die Daten in einem Durchgang in beide Tabellen einfügen:
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN (
SELECT name, ST_Transform(geom, 4326) as geom
FROM osm.poi p
WHERE shop = 'supermarket'
LIMIT 20
)
LOOP
INSERT INTO demo.poi (fid, "name", geom)
VALUES(nextval('demo.poi_fid_seq'), r.name, r.geom);
INSERT INTO demo.poi_visit (fid, poi_fid, visit)
VALUES(nextval('demo.poi_visit_fid_seq'), currval('demo.poi_fid_seq'), current_timestamp);
END LOOP;
END;
$$ language plpgsql;Zusammenfassung
SQL ist ein mächtiges Werkzeug zur Speicherung und Verarbeitung von Geodaten. Besonders bei der Verarbeitung großer Datenmengen lohnt sich die Einarbeitung in die Syntax und Denkweise zur effektiven Verarbeitung von Geodaten. Testen Sie gerne mal DBeaver zum Schreiben von Abfragen oder das SQL-Schleifenkonstrukt zur direkten Verarbeitung von Abfrageergebnissen. Das alles und noch viel mehr lernen Sie in unserer FOSS Academy Schulung “PostGIS für Fortgeschrittene”. Bei weiteren Fragen und Ideen steht Ihnen die WhereGroup gerne zur Seite.
