Verwendung der PostGIS-Erweiterung in PostgreSQL

Einführung

In diesem Lab lernen Sie, wie Sie die PostGIS-Erweiterung verwenden, um einer PostgreSQL-Datenbank räumliche Fähigkeiten hinzuzufügen. PostGIS verwandelt Ihre Datenbank in eine räumliche Datenbank und ermöglicht die Speicherung und Abfrage von Daten, die in einem geometrischen Raum definiert sind.

Sie beginnen mit der Erstellung einer neuen Datenbank und der Aktivierung der PostGIS-Erweiterung. Anschließend erstellen Sie eine Tabelle mit einer GEOMETRY-Spalte zur Speicherung von Standortdaten. Abschließend fügen Sie Daten für mehrere Städte ein und führen räumliche Abfragen durch, um Entfernungen zu berechnen und Daten innerhalb eines bestimmten Bereichs zu finden.

Räumliche Datenbank erstellen und PostGIS aktivieren

In diesem Schritt erstellen Sie eine neue Datenbank und aktivieren die PostGIS-Erweiterung darin. Es ist eine gute Praxis, eine dedizierte Datenbank für räumliche Daten zu verwenden, um diese organisiert zu halten.

Öffnen Sie zunächst ein Terminal und verbinden Sie sich mit dem PostgreSQL-Server über das interaktive Terminal psql. Sie werden sich als der Standard-Superuser postgres verbinden.

sudo -u postgres psql

Sie sehen die psql-Eingabeaufforderung, die wie postgres=# aussieht.

Erstellen Sie als Nächstes eine neue Datenbank namens spatial_db. Alle SQL-Befehle in psql müssen mit einem Semikolon (;) enden.

CREATE DATABASE spatial_db;

Sie sollten die folgende Bestätigungsnachricht sehen:

CREATE DATABASE

Verbinden Sie sich nun mit Ihrer neu erstellten Datenbank. Der Befehl \c wird verwendet, um sich mit einer anderen Datenbank zu verbinden.

\c spatial_db

Ihre Eingabeaufforderung ändert sich, um anzuzeigen, dass Sie nun mit spatial_db verbunden sind:

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

Nachdem die Datenbank bereit ist, aktivieren Sie die PostGIS-Erweiterung. Dieser Befehl lädt alle räumlichen Typen, Funktionen und Tabellen von PostGIS in Ihre Datenbank.

CREATE EXTENSION postgis;

Die Ausgabe bestätigt, dass die Erweiterung erstellt wurde:

CREATE EXTENSION

Um zu überprüfen, ob PostGIS korrekt installiert ist, können Sie dessen Version abfragen.

SELECT PostGIS_full_version();

Dies zeigt detaillierte Versionsinformationen für PostGIS und seine zugehörigen Bibliotheken an und bestätigt eine erfolgreiche Einrichtung. Sie können nun mit dem nächsten Schritt fortfahren. Beenden Sie die psql-Shell nicht.

Räumliche Tabelle erstellen und Daten einfügen

Nachdem Sie nun eine räumliche Datenbank haben, können Sie Tabellen zum Speichern geografischer Daten erstellen. In diesem Schritt erstellen Sie eine cities-Tabelle und fügen die Koordinaten einiger wichtiger Städte ein.

Sie sollten sich immer noch in der psql-Shell befinden, verbunden mit der Datenbank spatial_db.

Erstellen Sie zunächst eine Tabelle namens cities. Diese Tabelle wird eine Standard-id und einen name sowie eine location-Spalte vom Typ GEOMETRY enthalten.

Verständnis von GEOMETRY(Point, 4326)

• GEOMETRY: Dies ist der grundlegende PostGIS-Datentyp zum Speichern räumlicher Merkmale.
• Point: Dies gibt an, dass die Geometriespalte nur Punkte speichert. Andere Typen sind LINESTRING und POLYGON.
• 4326: Dies ist der Spatial Reference System Identifier (SRID). SRID 4326 entspricht WGS 84, dem Standardkoordinatensystem, das von GPS und Google Maps verwendet wird.

Führen Sie den folgenden Befehl aus, um die Tabelle zu erstellen:

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

Sie sehen die Bestätigung:

CREATE TABLE

Fügen Sie als Nächstes Daten in die cities-Tabelle ein. Sie verwenden die Funktion ST_GeomFromText, um Well-Known Text (WKT)-Darstellungen von Punkten in Geometrieobjekte umzuwandeln. Das Format ist POINT(Längengrad Breitengrad).

INSERT INTO cities (name, location) VALUES
    ('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
    ('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
    ('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));

Die Ausgabe zeigt an, dass drei Zeilen eingefügt wurden:

INSERT 0 3

Um zu überprüfen, ob die Daten korrekt eingefügt wurden, können Sie die Tabelle abfragen. Verwenden Sie die Funktion ST_AsText, um das Geometrieobjekt wieder in ein für Menschen lesbares Textformat umzuwandeln.

SELECT id, name, ST_AsText(location) FROM cities;

Die Ausgabe sollte die gerade eingefügten Daten anzeigen:

 id |   name   |          st_astext
----+----------+-----------------------------
  1 | New York | POINT(-74.006 40.7128)
  2 | London   | POINT(-0.1278 51.5074)
  3 | Tokyo    | POINT(139.6917 35.6895)
(3 rows)

Sie haben erfolgreich eine räumliche Tabelle erstellt und diese mit Daten gefüllt.

Räumliche Abfragen und Analysen durchführen

Die wahre Stärke von PostGIS liegt in seiner Fähigkeit, räumliche Abfragen durchzuführen. In diesem Schritt verwenden Sie PostGIS-Funktionen, um die Entfernung zwischen zwei Punkten zu berechnen und herauszufinden, welche Punkte sich innerhalb eines bestimmten Bereichs befinden.

Sie sollten sich immer noch in der psql-Shell befinden, verbunden mit der Datenbank spatial_db.

Berechnen wir zunächst die Entfernung zwischen New York und London. Für genaue Entfernungsberechnungen auf der Erdoberfläche ist es am besten, den geometry-Typ in den geography-Typ umzuwandeln. Der geography-Typ berücksichtigt die Krümmung der Erde, und ST_Distance gibt das Ergebnis in Metern zurück.

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

Die Abfrage gibt die Großkreisentfernung in Metern zurück. Das Ergebnis wird eine große Zahl sein, ungefähr 5,57 Millionen Meter.

   st_distance
-----------------
 5570299.7888681
(1 row)

Führen wir als Nächstes eine Analyse durch, um herauszufinden, welche unserer Städte sich innerhalb von 200 Kilometern von London befinden. Dazu erstellen Sie zunächst einen kreisförmigen Puffer um Londons Standort und prüfen dann, welche Standorte der Städte mit diesem Puffer übereinstimmen.

• ST_Buffer: Erstellt eine Geometrie, die alle Punkte darstellt, deren Entfernung von der Quellgeometrie kleiner oder gleich einer angegebenen Entfernung ist. Die Entfernung wird in Metern angegeben, wenn sie mit dem geography-Typ verwendet wird.
• ST_Intersects: Gibt true zurück, wenn sich zwei Geometrien "räumlich überschneiden" – wenn sie einen gemeinsamen Bereich haben.

WITH london_buffer AS (
  SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);

Diese Abfrage verwendet einen Common Table Expression (CTE), um einen Puffer von 200.000 Metern (200 km) um London zu erstellen. Anschließend werden alle Städte gefunden, die mit diesem Puffer übereinstimmen. Wie erwartet, befindet sich nur London selbst in diesem Bereich.

  name
--------
 London
(1 row)

Sie haben nun eine grundlegende räumliche Analyse durchgeführt. Sie können die psql-Shell durch Eingabe von \q beenden.

\q

Datenbank bereinigen

Es ist gute Praxis, Ihre Umgebung nach Abschluss Ihrer Arbeit zu bereinigen. In diesem Schritt werden Sie die Datenbank spatial_db löschen. Das Löschen einer Datenbank entfernt die Datenbank selbst zusammen mit allen darin enthaltenen Objekten wie Tabellen und Erweiterungen.

Sie sollten sich wieder an der normalen Terminalaufforderung (labex@vm:~$) befinden.

Um eine Datenbank zu löschen, müssen Sie mit einer anderen Datenbank verbunden sein. Stellen Sie eine Verbindung zur Standarddatenbank postgres her.

sudo -u postgres psql

Führen Sie nun den Befehl DROP DATABASE aus.

DROP DATABASE spatial_db;

Sie sehen eine Bestätigungsnachricht:

DROP DATABASE

Sie können überprüfen, ob die Datenbank entfernt wurde, indem Sie alle verfügbaren Datenbanken mit dem Befehl \l auflisten.

\l

Sie werden sehen, dass spatial_db nicht mehr in der Liste enthalten ist.

Beenden Sie schließlich die psql-Shell.

\q

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie die PostGIS-Erweiterung verwenden, um PostgreSQL um räumliche Datenunterstützung zu erweitern. Sie haben erfolgreich eine dedizierte Datenbank erstellt, die PostGIS-Erweiterung aktiviert, eine Tabelle mit einer Geometrie-Spalte definiert, Standortdaten eingefügt und räumliche Abfragen durchgeführt, um Entfernungen zu berechnen und Schnittpunkte zu finden. Diese Fähigkeiten bilden eine solide Grundlage für die Entwicklung von standortbezogenen Anwendungen mit PostgreSQL.