16.4. Lesson: Räumliche Abfragen

Räumliche Abfragen unterscheiden sich nicht von anderen Datenbankabfragen. Man kann die Geometriespalte wie jede andere Spalte in der Datenbank verwenden. Durch die Installation von PostGIS in unserer Datenbank kommen weitere zusätzliche Funktionen zur Abfrage der Datenbank hinzu.

Ziel dieser Lektion: Verständnis über die Implementierung von räumlichen Funktionen im Vergleich mit „normalen“ nicht räumlichen Funktionen.

16.4.1. Räumliche Operationen

Wenn man wissen möchte, welche Punkte innerhalb eines Abstandes von 2 Grad zu einem Punkt (X,Y) sind, kann man folgende Abfrage verwenden:

select *
from people
where st_distance(the_geom,'SRID=4326;POINT(33 -34)') < 2;

Ergebnis:

 id |     name     | house_no | street_id |   phone_no    |   the_geom
----+--------------+----------+-----------+---------------+---------------
  6 | Fault Towers |       34 |         3 | 072 812 31 28 | 01010008040C0
(1 row)

Bemerkung

Der obige Wert von the_geom wurde aus Platzgründen abgeschnitten. Wenn man den Punkt in vom Menschen lesbaren Koordinaten sehen möchte, kann man so ähnlich vorgehen wie oben unter „Einen Punkt als WKT anzeigen“.

Woher wissen wir, das die obige Abfrage alle Punkte innhalb von 2 Grad ausgibt? Warum nicht 2 Meter oder irgendeine andere Einheit?

Answer

The units being used by the example query are degrees, because the CRS that the layer is using is WGS 84. This is a Geographic CRS, which means that its units are in degrees. A Projected CRS, like the UTM projections, is in meters.

Remember that when you write a query, you need to know which units the layer’s CRS is in. This will allow you to write a query that will return the results that you expect.

16.4.2. Räumliche Indexe

Wir können auch räumliche Indexe definieren. Ein räumlicher Index beschleunigt Ihre räumlichen Abfragen stark. Um einen räumlichen Index über eine Geometriespalte zu erstellen, geht man wie folgt vor:

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (the_geom);

\d people

Ergebnis:

Table "public.people"
   Column   |         Type          |                Modifiers
 -----------+-----------------------+----------------------------------------
  id        | integer               | not null default
            |                       | nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  the_geom  | geometry              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (the_geom)  <-- new spatial key added
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(the_geom) = 'ST_Point'::text
  OR the_geom IS NULL)
Foreign-key constraints:
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

16.4.3. Try Yourself 

Verändern Sie die Tabelle cities, so dass die Geometriespalte einen räumlichen Index erhält.

Answer

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (the_geom);

16.4.4. Demonstration der räumlichen Funktionen von PostGIS

Um die räumlichen Funktionen von PostGIS zu demonstrieren, werden wir eine neue Datenbank mit (fiktionalen) Daten erstellen.

Erstellen Sie zu Beginn eine neue Datenbank (verlassen Sie die psql Konsole zuerst):

createdb postgis_demo

Denken Sie daran, die PostGIS Erweiterungen zu installieren:

psql -d postgis_demo -c "CREATE EXTENSION postgis;"

Importieren Sie als Nächstes die Daten aus dem Verzeichnis exercise_data/postgis/. Sehen Sie sich dazu die Anleitung in der vorherigen Lektion an, aber denken Sie daran, dass Sie eine neue PostGIS Verbindung zu der neuen Datenbank erstellen müssen. Sie können mit Hilfe des Terminals oder des DB Managers importieren. Importieren Sie die Dateien in die folgenden Datenbanktabellen:

points.shp nach building
lines.shp nach road
polygons.shp nach region

Laden Sie diese drei Datenbanklayer wie gewohnt mit Hilfe des PostGIS-Layer hinzufügen Dialogs in QGIS ein. Beim Öffnen der Attributtabellen sehen wir, dass beide ein Feld id und ein Feld gid haben, die beim PostGIS Import erzeugt wurden.

Nachdem die Tabellen importiert wurden, können wir PostGIS zur Abfrage der Daten verwenden. Gehen Sie zurück zur Konsole (Kommandozeile) und geben folgendes Kommando ein:

psql postgis_demo

Wir werden einige dieser Auswahlanweisungen durch die Erstellung von Sichten demonstrieren, so dass Sie sie in QGIS öffnen und die Ergebnisse sehen können.

Auswahl nach der Lage

Wir erhalten alle Bauwerke in der Region KwaZulu:

SELECT a.id, a.name, st_astext(a.the_geom) as point
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_geom)
    AND b.name = 'KwaZulu';

Ergebnis:

 id | name |                  point
----+------+------------------------------------------
 30 | York | POINT(1622345.23785063 6940490.65844485)
 33 | York | POINT(1622495.65620524 6940403.87862489)
 35 | York | POINT(1622403.09106394 6940212.96302097)
 36 | York | POINT(1622287.38463732 6940357.59605424)
 40 | York | POINT(1621888.19746548 6940508.01440885)
(5 rows)

Oder wir erstellen eine Sicht daraus:

CREATE VIEW vw_select_location AS
  SELECT a.gid, a.name, a.the_geom
    FROM building a, region b
      WHERE st_within(a.the_geom, b.the_geom)
      AND b.name = 'KwaZulu';

Fügen Sie die Sicht als Layer in QGIS ein:

../../../_images/kwazulu_view_result.png

Nachbarn auswählen

Geben Sie eine Liste aller Namen von Regionen aus, die an die Hokkaido Region angrenzen:

SELECT b.name
  FROM region a, region b
    WHERE st_touches(a.the_geom, b.the_geom)
    AND a.name = 'Hokkaido';

Ergebnis:

    name
--------------
 Missouri
 Saskatchewan
 Wales
(3 rows)

Als Sicht:

CREATE VIEW vw_regions_adjoining_hokkaido AS
  SELECT b.gid, b.name, b.the_geom
    FROM region a, region b
      WHERE TOUCHES(a.the_geom, b.the_geom)
      AND a.name = 'Hokkaido';

In QGIS:

Achten Sie auf die fehlende Region (Queensland). Das kann an einem Topologiefehler liegen. Solche Artefakte können auf potentielle Fehler in den Daten hinweisen. Um das Problem zu lösen ohne sich in möglichen Problemen des Datensatzes zu verzetteln, können wir einen Puffer verwenden:

CREATE VIEW vw_hokkaido_buffer AS
  SELECT gid, ST_BUFFER(the_geom, 100) as the_geom
    FROM region
      WHERE name = 'Hokkaido';

Damit wird ein Puffer von 100 Metern um die Region Hokkaido erstellt.

Die dunklere Fläche ist der Puffer:

Auswahl mit Hilfe des Puffers:

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.the_geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.the_geom, b.the_geom)
    AND b.name != 'Hokkaido';

In dieser Abfrage wird die Sicht mit Puffer genauso wie eine normale Tabelle verwendet. Die Sicht erhält den Alias a und ihr Geometriefeld a.the_geom wird verwendet, um alle Polygone in der Tabelle region (Alias b) zu selektieren die sie überschneiden. Hokkaido ist dagegen aus der Auswahl ausgeschlossen, da wir nur die angrenzenden Regionen selektieren wollen.

In QGIS:

../../../_images/hokkaido_buffer_select.png

Es ist auch möglich, alle Objekte innerhalb eines vorgegebenen Abstandes auszuwählen, ohne vorher einen Puffer zu erstellen:

CREATE VIEW vw_hokkaido_distance_select AS
  SELECT b.gid, b.name, b.the_geom
    FROM region a, region b
      WHERE ST_DISTANCE (a.the_geom, b.the_geom) < 100
      AND a.name = 'Hokkaido'
      AND b.name != 'Hokkaido';

Das Ergebnis ist dasselbe und der Zwischenschritt der Pufferbildung entfällt:

../../../_images/hokkaido_distance_select.png

Auswahl eindeutiger Werte

Anzeige einer Liste der eindeutigen Stadtnamen für alle Gebäude in der Region Queensland:

SELECT DISTINCT a.name
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_geom)
    AND b.name = 'Queensland';

Ergebnis:

  name
---------
 Beijing
 Berlin
 Atlanta
(3 rows)

Weitere Beispiele …

CREATE VIEW vw_shortestline AS
  SELECT b.gid AS gid,
        ST_ASTEXT(ST_SHORTESTLINE(a.the_geom, b.the_geom)) as text,
        ST_SHORTESTLINE(a.the_geom, b.the_geom) AS the_geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_longestline AS
  SELECT b.gid AS gid,
         ST_ASTEXT(ST_LONGESTLINE(a.the_geom, b.the_geom)) as text,
         ST_LONGESTLINE(a.the_geom, b.the_geom) AS the_geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_road_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.the_geom) as the_geom
    FROM road a
      WHERE a.id = 1;

CREATE VIEW vw_region_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.the_geom) as the_geom
    FROM region a
      WHERE a.name = 'Saskatchewan';

SELECT ST_PERIMETER(a.the_geom)
  FROM region a
    WHERE a.name='Queensland';

SELECT ST_AREA(a.the_geom)
  FROM region a
    WHERE a.name='Queensland';

CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(the_geom, 20) AS the_geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(the_geom, 50) AS the_geom
    FROM road;

CREATE VIEW vw_convex_hull AS
  SELECT
    ROW_NUMBER() over (order by a.name) as id,
    a.name as town,
    ST_CONVEXHULL(ST_COLLECT(a.the_geom)) AS the_geom
    FROM building a
    GROUP BY a.name;

16.4.5. In Conclusion

Wir haben gesehen, wie man räumliche Objekte mit Hilfe der neuen Datenbankfunktionen in QGIS abfragen kann.

16.4.6. What’s Next?

Als Nächstes werden wir die Strukturen komplexerer Geometrien untersuchen und wie man sie mit Hilfe von PostGIS erstellt.