Ein Leitfaden für PostGIS: Beispiele für grundlegende Geodatenabfragen

Geodaten werden in vielen Bereichen, von der Stadtplanung bis zur Umweltwissenschaft, immer wichtiger. In diesem Artikel werden grundlegende PostgreSQL PostGIS-Abfragen für die Arbeit mit dieser Art von Daten demonstriert.

Geodaten, die Informationen über Standorte auf der Erde enthalten, erfordern spezielle Werkzeuge für eine effektive Nutzung. PostGIS ist eine leistungsstarke PostgreSQL-Erweiterung, die eine Postgres-Datenbank in ein vollwertiges Geografisches Informationssystem (GIS) verwandelt. Mit PostGIS können Sie geografische Objekte speichern, räumliche Abfragen ausführen und erweiterte Analysen direkt in SQL durchführen. Das macht es zu einem unverzichtbaren Werkzeug für jeden, der mit ortsbezogenen Daten arbeitet.

Durch die Installation von PostGIS können Sie räumliche Operationen durchführen, für die normalerweise spezielle GIS-Software erforderlich ist. Egal, ob Sie Entfernungen berechnen, Flächen messen oder räumliche Beziehungen analysieren müssen, PostGIS gibt Ihnen die nötigen Werkzeuge an die Hand, um komplexe räumliche Daten effizient zu verarbeiten.

Wenn Sie Ihre PostGIS-Kenntnisse vertiefen möchten, sollten Sie sich für unseren PostGIS-Kurs anmelden. Er führt Sie von den GIS-Grundlagen zu fortgeschrittenen PostGIS-Techniken. Wenn Sie den Kurs abgeschlossen haben, wissen Sie, wie Sie die Möglichkeiten von PostGIS in Ihren Projekten nutzen können.

In diesem Leitfaden werden wir Sie in die Grundlagen von PostGIS einführen. Wir beginnen mit einfachen räumlichen Abfragen und gehen dann zu fortgeschrittenen Techniken wie Näherungsanalyse und Netzwerkanalyse über. Wenn Sie noch mehr über Geodaten erfahren möchten, lesen Sie unseren Artikel Eine Einführung in Geodatenbanken und Geodaten.

Unser Beispiel für Geodaten

In diesem Artikel werden wir mit einem Geodatensatz aus San Francisco, Kalifornien, arbeiten. Wir werden mehrere Tabellen verwenden:

• sf_tram_stops: Diese Tabelle enthält die Standorte der Straßenbahnhaltestellen in der Stadt; sie speichert auch die Standortkoordinaten und ob an dieser Haltestelle ein Umsteigen zwischen den Linien möglich ist. Sie hat die folgenden Spalten:
  • id - Einen eindeutigen Bezeichner für jede Haltestelle.
  • coordinates - Die Standortkoordinaten der Haltestelle.
  • transfer_possible - Ob ein Fahrgast an dieser Haltestelle in eine andere Straßenbahn umsteigen kann.
    
• sf_planning_districts: Diese Tabelle definiert die Grenzen der Planungsbezirke von San Francisco; diese Information ist für städtebauliche Analysen unerlässlich. Sie enthält die folgenden Spalten:
  • id - Ein eindeutiger Identifikator für jeden Planungsbezirk.
  • name - Der Name des Bezirks.
  • boundaries - Die Grenzen des Bezirks (als Ortskoordinaten).
• sf_bicycle_routes: In dieser Tabelle werden Informationen über die Fahrradrouten der Stadt gespeichert, die Sie für die Analyse der Fahrradinfrastruktur von SanFran benötigen. Sie hat die folgenden Spalten:
  • id - Eine eindeutige Kennung für jede Fahrradroute.
  • course - Der Streckenverlauf der Route.
  • condition_rating - Die Bewertung der Route (basierend auf ihren Bedingungen).
• sf_restaurants: Diese Tabelle enthält Informationen über die Restaurants in San Francisco, einschließlich ihrer Namen, Standorte, Bewertungen und Arten von Speisen. Sie hat die folgenden Spalten:
  • id - Eine eindeutige Kennung für jedes Restaurant.
  • name - Der Name des Restaurants.
  • food_type - Die Art der Küche, die in diesem Restaurant serviert wird.
  • rating - Die Bewertung des Restaurants.
  • coordinates - Die Standortkoordinaten des Restaurants.
• sf_sights: In dieser Tabelle werden Informationen über wichtige Sehenswürdigkeiten und Points of Interest (POI) in der Stadt gespeichert. Sie enthält die folgenden Spalten:
  • id - Eine eindeutige Kennung für jedes Sonderziel.
  • name - Der Name des POI.
  • coordinates - Den Standort des POI.
• sf_atms: In dieser Tabelle werden Details zu den Geldautomaten von SanFran gespeichert, einschließlich des Standorts jedes Geldautomaten und der Gesellschaft, die ihn betreibt. Sie enthält die folgenden Spalten:
  • id - Eine eindeutige Kennung für jeden Geldautomaten.
  • company - Das Unternehmen, das diesen Geldautomaten betreibt.
  • coordinates - Der Standort des Geldautomaten.

Diese Daten dienen als Grundlage für die räumlichen Abfragen, die wir erforschen werden. Wir werden diese Abfragen verwenden, um die Nähe zwischen Wahrzeichen und Einrichtungen zu analysieren, Flächen innerhalb von Planungsgebieten zu berechnen und vieles mehr. Jede Abfrage zeigt, wie PostGIS Ihnen hilft, sinnvolle Erkenntnisse aus räumlichen Daten zu gewinnen.

Grundlegende raumbezogene Abfragen mit PostGIS

Sobald Ihre Geodaten in PostgreSQL mit PostGIS gespeichert sind, können Sie damit beginnen, sie abzufragen! Räumliche Abfragen ermöglichen es Ihnen, Daten auf der Grundlage ihres Standorts, ihrer Form und ihrer räumlichen Beziehungen auszuwählen, zu filtern und zu manipulieren. Beginnen wir also mit der Arbeit mit räumlichen Daten.

Visualisierung von Geodaten

Schauen wir uns zunächst an, wie Geodaten aussehen. Hier wählen wir die Haltestellen-IDs und die Koordinaten der Haltestellen aus, an denen die Fahrgäste umsteigen können:

SELECT
  id, 
  coordinates
FROM sf_tram_stops
WHERE transfer_possible = true;

So sieht das Ergebnis aus:

Wie Sie sehen können, sind die Geodaten in der Koordinatenspalte nicht wirklich lesbar. Um sie wirklich nutzen zu können, bräuchte man eine spezielle Software, um sie auf der Karte darzustellen. Glücklicherweise verfügt unser PostGIS-Kurs über eine integrierte Karte; jetzt können Sie die Lage dieser Straßenbahnhaltestellen sehen:

Konvertierung von Geodaten in Text

Vielleicht möchten Sie die Standortkoordinaten der oben genannten Straßenbahnhaltestellen ohne Karte sehen. Sie können die folgende Abfrage verwenden, um die Daten in für Menschen lesbare Koordinaten umzuwandeln:

SELECT
  id, 
  ST_AsText(coordinates),
  ST_Y(coordinates),
  ST_X(coordinates)
FROM sf_tram_stops
WHERE transfer_possible = true;

Diese Abfrage verwendet die PostGIS-Funktion ST_AsText, um die Koordinaten in ein lesbares Format zu bringen. Sie verwendet ST_Y und ST_X, um die Werte für Breiten- und Längengrad zu extrahieren. Hier ist ein Teilergebnis:

Räumliche Beziehungen

PostGIS bietet mehrere Funktionen zur Untersuchung der räumlichen Beziehungen von Geo-Objekten. Schauen wir sie uns kurz an.

ST_Intersection

Diese Funktion gibt den gemeinsamen Teil (d.h. den Schnittpunkt) zweier Geometrien zurück. Die folgende Abfrage zeigt alle Fahrradrouten (oder deren Teile) innerhalb der Grenzen des Bezirks Downtown:

SELECT
  sfbr.id,
  ST_Intersection(sfpd.boundaries, sfbr.course)
FROM sf_bicycle_routes sfbr
JOIN sf_planning_districts sfpd
  ON ST_Intersects(sfpd.boundaries, sfbr.course)
WHERE sfpd.name = 'Downtown';

Die Funktion ST_Intersection findet den Bereich, in dem sich zwei Geometrien überschneiden - in diesem Fall, wo Fahrradrouten die Grenzen des Bezirks "Downtown" kreuzen. Die Funktion ST_Intersects prüft, ob sich die Fahrradrouten und die Bezirksgrenzen berühren oder kreuzen, und stellt sicher, dass nur diejenigen einbezogen werden, die dies tun.

Und hier ist ein Teilergebnis als Text:

Und als Karte:

ST_Enthält

Die Funktion ST_Contains prüft, ob eine Geometrie eine andere vollständig enthält. Um Planungsbezirke aufzulisten, die Geldautomaten der Firma Crown Financial enthalten, können Sie diese Abfrage ausführen:

SELECT DISTINCT sfn.name
FROM sf_planning_districts sfn
JOIN sf_atms sfa
  ON ST_Contains(sfn.boundaries, sfa.coordinates)
WHERE sfa.company = 'Crown Financial Inc.';

Hier ist das Ergebnis:

ST_Within

Die Funktion ST_Within prüft auch, ob eine Geometrie vollständig innerhalb einer anderen liegt. Diese Abfrage findet Restaurants mit einer Bewertung über 4,0 innerhalb des Bezirks Nordost:

SELECT 
  sep.name,
  sep.coordinates
FROM sf_planning_districts spd
JOIN sf_restaurants sep
  ON ST_Within(sep.coordinates, spd.boundaries)
WHERE rating > 4.0
  AND spd.name = 'Northeast';

Diese Abfrage gibt die Namen und Koordinaten der Restaurants zurück, so dass sie leicht auf einer Karte angezeigt werden können.

Berechnungen von Entfernungen und Flächen

Die Berechnung von Entfernungen und Flächen ist ein weiterer grundlegender Aspekt der Arbeit mit Geodaten. Diese Berechnungen helfen bei der Beantwortung von Fragen wie "Wie weit ist dieser Geldautomat von einer bestimmten Sehenswürdigkeit entfernt?" oder "Wie groß ist die Fläche dieses Planungsbezirks?"

Entfernungsberechnungen

Die Funktion ST_Distance gibt die Entfernung zwischen zwei Geo-Objekten zurück. Sie benötigt zwei geometrische Argumente: ST_Distance(geometryA, geometryB).

Um alle Geldautomaten im Umkreis von 300 Metern von Fisherman's Wharf zu visualisieren, können Sie die folgende Abfrage verwenden:

SELECT   
  sa.id,
  sa.coordinates,
  ST_Distance(
    ST_Transform(sa.coordinates, 26910), 
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_atms sa
  ON ST_Distance(
     ST_Transform(sa.coordinates, 26910), 
     ST_Transform(ss.coordinates, 26910)) < 300
WHERE ss.name = 'Fisherman''s Wharf';

In dieser Abfrage wandelt die Funktion ST_Transform die angegebenen Koordinaten in ein spezifisches Koordinatensystem um, das für räumliche Berechnungen geeignet ist. Das Koordinatensystem stellt sicher, dass alle räumlichen Operationen (wie z. B. Entfernungsberechnungen) sowohl genau als auch relevant für die spezifischen Anforderungen des geografischen Gebiets und des Projekts sind.

In dieser Abfrage verwenden wir das durch den SRID (Spatial Reference System Identifier) 26910 definierte Koordinatensystem, das dem System NAD83 / UTM Zone 10N entspricht. Dieses System wird in der Regel für genaue Entfernungsberechnungen im Raum San Francisco verwendet.

Wie können Sie den richtigen SRID finden? Versuchen Sie es mit einer der folgenden Websites:

• Spatial Reference Website: Verwenden Sie die Registerkarte EPSG.
• io: Geben Sie den Namen des Landes, den Code oder den Namen des Koordinatensystems ein, um zu suchen und alle möglichen Koordinatenreferenzsysteme zu erhalten.

Durch die Umwandlung der Koordinaten stellt die Abfrage sicher, dass die Entfernungsberechnung zwischen dem Geldautomaten und Fisherman's Wharf genau ist. Hier ist das Ergebnis:

Flächenberechnungen

Um die Fläche eines Planungsbezirks zu berechnen, verwenden Sie die Funktion ST_Area. Diese nimmt ein Geometrieargument (ST_Area(geometry)) und gibt die Fläche der resultierenden Form zurück.

Hier ist die Abfrage. Beachten Sie, dass wir wieder SRID = 26910 als Koordinatensystem verwenden:

SELECT
  ST_Area(ST_Transform(boundaries, 26910))
FROM sf_planning_districts
WHERE name = 'Buena Vista';

Und das ist das Ergebnis:

Mit SRID = 26910 ist die Standardeinheit Meter. Daher wird das Ergebnis von ST_Area in Quadratmetern angezeigt.

Erweiterte räumliche Analyse

Mit zunehmender Vertrautheit mit PostGIS können Sie die erweiterten Funktionen nutzen, um tiefgreifende räumliche Analysen durchzuführen. Diese Techniken ermöglichen es Ihnen, räumliche Beziehungen zu untersuchen, Näherungsanalysen durchzuführen und sogar Netzwerkanalysen vorzunehmen, um die Erkenntnisse aus Ihren Geodaten besser umsetzen zu können.

Pufferung und Näherungsanalyse

Durch Pufferung werden Zonen um räumliche Merkmale herum geschaffen. Dies ist besonders nützlich bei der Näherungsanalyse, bei der Sie bestimmen müssen, wie nahe bestimmte Merkmale beieinander liegen. Wenn Sie zum Beispiel alle Restaurants im Umkreis von 3000 Metern um den "Palast der schönen Künste" ermitteln möchten, können Sie die Funktion ST_DWithin verwenden:

SELECT   
  sep.name,
  sep.rating,
  sep.food_type,
  sep.coordinates,
  ST_Distance(
    ST_Transform(sep.coordinates, 26910), 
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_restaurants sep
  ON ST_DWithin(
     ST_Transform(sep.coordinates, 26910), 
     ST_Transform(ss.coordinates, 26910), 
     3000)
WHERE ss.name = 'Palace of Fine Arts';

Und hier sind Ihre Ergebnisse in Tabellen- und Kartenform:

Überlagerungsoperationen

Mit Hilfe von Überlagerungsoperationen können wir sehen, wie verschiedene geografische Informationen zusammenpassen oder interagieren. Stellen Sie sich vor, Sie haben zwei Karten, eine mit allen Parks in einer Stadt und eine andere mit überschwemmungsgefährdeten Gebieten. Wenn Sie die eine Karte über die andere legen, können Sie herausfinden, welche Parks überschwemmungsgefährdet sind. Dieses Verfahren hilft uns zu verstehen und zu visualisieren, wo verschiedene geografische Merkmale zusammentreffen; es macht Planung, Analysen und Entscheidungsfindung effektiver.

Die folgende Abfrage verwendet die Funktion ST_Union, um die Grenzen aller "Freizeit"-Planungsbezirke zu einer einheitlichen geometrischen Form zusammenzuführen:

SELECT
  ST_Union(boundaries) 
FROM sf_planning_districts 
WHERE type = 'recreational';

In ähnlicher Weise könnten Sie ST_Intersection verwenden, um das Gleiche zu erreichen.

Netzwerkanalyse

Bei der Netzwerkanalyse geht es um die Untersuchung von Routen, die Ermittlung der kürzesten Wege und die Verbesserung der Reiseeffizienz innerhalb eines Netzwerks. Sie ist entscheidend für das Verständnis und die Optimierung von Verkehrssystemen und Konnektivität. PostGIS - in Verbindung mit pgRouting - bietet robuste Werkzeuge für diese Art von Analyse.

Die pgRouting-Erweiterung bietet fortschrittliche Algorithmen zur Routenoptimierung. Einer der bekanntesten ist die Funktion pgr_dijkstra; sie berechnet den kürzesten oder schnellsten Weg zwischen zwei Punkten unter Berücksichtigung von Faktoren wie Straßentypen, Verkehrsbedingungen und anderen Variablen. Diese Fähigkeit ist von unschätzbarem Wert für Navigationssysteme, Logistikplanung, Transportmanagement und vieles mehr. Durch den Einsatz solcher Erweiterungen können Benutzer die Routenplanung verbessern, Reisezeiten verkürzen und die Effizienz des gesamten Netzwerks steigern.

Weitere Erkundung von Geodaten mit PostGIS

PostGIS bietet mit seiner nahtlosen Integration in PostgreSQL eine leistungsstarke Plattform für die Verwaltung und Analyse von Geodaten. In diesem Artikel haben wir die Grundlagen räumlicher Abfragen behandelt, uns an fortgeschrittene räumliche Analysen herangewagt und gezeigt, wie PostGIS Ihnen helfen kann, komplexe geografische Fragen mit Präzision und Effizienz zu beantworten.

Das Erlernen von PostGIS kann Ihren Umgang mit räumlichen Daten verändern. Es wird es Ihnen erleichtern, wertvolle Erkenntnisse zu gewinnen, Arbeitsabläufe zu optimieren und datengestützte Entscheidungen zu treffen. Ganz gleich, ob Sie in der Stadtplanung, in der Umweltanalyse, in Verkehrsnetzen oder in einem anderen GIS-bezogenen Bereich tätig sind, PostGIS stattet Sie mit den Werkzeugen aus, die für einen effektiven Umgang mit raumbezogenen Daten erforderlich sind.

Wenn Sie die Möglichkeiten von PostGIS erkunden, werden Sie feststellen, dass es Ihre Fähigkeit, mit Geodaten zu arbeiten, verbessert und neue Wege für Innovationen und Entdeckungen eröffnet. Die Kombination aus den robusten Datenbankfähigkeiten von PostgreSQL und den Geodatenfunktionen von PostGIS ermöglicht es Ihnen, selbst die komplexesten räumlichen Herausforderungen mit Zuversicht anzugehen.

Bevor ich gehe, möchte ich noch einmal unseren umfassenden PostGIS-Kurs erwähnen; er ist wirklich eine gute Wahl, um praktische Erfahrungen mit räumlichen Daten zu sammeln. Er führt Sie durch jeden Aspekt von PostGIS, von den Grundlagen bis zu fortgeschrittenen Techniken. Ehe Sie sich versehen, werden Sie selbstbewusst Geodatenprojekte in Angriff nehmen.

PostGIS ist mehr als nur eine Erweiterung von PostgreSQL. Es ist ein Tor, um das volle Potenzial von Geodaten in Ihren Datenbanksystemen zu erschließen. Wenn Sie die Funktionen von PostGIS beherrschen, können Sie räumliche Daten in verwertbare Erkenntnisse umwandeln, die den Erfolg Ihrer Projekte und Ihres Unternehmens fördern.