Wichtig

Übersetzen ist eine Gemeinschaftsleistung Sie können mitmachen. Diese Seite ist aktuell zu 71.67% übersetzt.

16.5. Lesson: Geometry Construction

In diesem Kapitel gehen wir näher darauf ein, wie einfach Geometrien in SQL erstellt werden. In der Realität wird man wahrscheinlich ein GIS wie QGIS mit seinen Digitalisierungshilfen verwenden, um komplexe Geometrien zu erstellen; trotzdem ist das Verständnis für die Erstellung von Abfragen und für die Nachvollziehbarkeit wie die Daten aufgebaut ist nützlich.

The goal of this lesson: To better understand how to create spatial entities directly in PostgreSQL.

16.5.1. Erstellen von Linien

Going back to our address database, let’s get our streets table matching the others; i.e., having a constraint on the geometry, an index and an entry in the geometry_columns table.

16.5.2. Try Yourself: ★★☆

  • Modify the streets table so that it has a geometry column of type ST_LineString.

  • Vergessen Sie dabei nicht, die geometry_columns Tabelle zu aktualisieren!

  • Fügen Sie auch eine Einschränkung ein, die verhindert, dass andere Geometrien als Linien oder NULL hinzugefügt werden.

  • Erstellen Sie einen räumlichen Index über die neue Geometriespalte

Antwort

alter table streets add column geom geometry;
alter table streets add constraint streets_geom_point_chk check
     (st_geometrytype(geom) = 'ST_LineString'::text OR geom IS NULL);
insert into geometry_columns values ('','public','streets','geom',2,4326,
     'LINESTRING');
create index streets_geo_idx
  on streets
  using gist
  (geom);

Lassen Sie uns nun eine Linie in unsere Tabelle streets einfügen. In diesem Fall aktualisieren wir einen vorhandenen Straßen-Datensatz:

update streets
set geom = 'SRID=4326;LINESTRING(20 -33, 21 -34, 24 -33)'
where streets.id=2;

Sehen Sie sich die Ergebnisse in QGIS an. (Sie müssen unter Umständen mit der rechten Maustaste auf den streets-Layer im ‚Layer‘ Bereich klicken und dann ‚Auf den Layer zoomen‘ auswählen.)

Erstellen Sie noch ein Paar Einträge für Straßen - einige in QGIS und einige aus der Kommandozeile.

16.5.3. Erstellung von Polygonen

Die Erstellung von Polygonen ist genauso einfach. Dabei muss man daran denken, dass Polygone definitionsgemäß mindestens vier Stützpunkte haben, wobei der erste und letzte übereinander liegen:

insert into cities (name, geom)
values ('Tokyo', 'SRID=4326;POLYGON((10 -10, 5 -32, 30 -27, 10 -10))');

Bemerkung

Ein Polygon erfordert doppelte Klammern um seine Koordinatenliste. Dies erlaubt es, komplexe Polygone mit mehreren unverbundenen Flächen einzufügen. Zum Beispiel

insert into cities (name, geom)
values ('Tokyo Outer Wards',
        'SRID=4326;POLYGON((20 10, 20 20, 35 20, 20 10),
                           (-10 -30, -5 0, -15 -15, -10 -30))'
        );

Nach dem Abschlusses dieses Schrittes, können Sie die Städte in QGIS einladen und nachvollziehen, was sich verändert hat. Öffnen Sie dazu die Attributtabelle und wählen den neuen Eintrag aus. Beachten Sie, wie die neuen Polygone sich wie ein einziges Polygon verhalten.

16.5.4. Übernung: Anbindung Städte an Personen

Gehen Sie für diese Übung wie folgt vor:

  • Löschen Sie alle Daten aus der Tabelle people.

  • Fügen Sie eine Spalte mit einem Fremdschlüssel in der Tabelle people ein, der eine Referenz zum Primärschlüssel der Tabelle cities herstellt.

  • Nutzen Sie QGIS, um einige Städte zu erfassen.

  • Verwenden Sie SQL um einige neue Datensätze zu people hinzuzufügen. stellen Sie sicher, dass jeder Datensatz eine zugehörige Straße und Stadt enthält.

Ihr aktualsiertes Schema zu people sollte in etwa so aussehen:

\d people

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     |
  geom      | geometry              |
  city_id   | integer               | not null
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(geom) =
                       'ST_Point'::text OR geom IS NULL)
Foreign-key constraints:
  "people_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(id)
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

Antwort

delete from people;
alter table people add column city_id int not null references cities(id);

(capture cities in QGIS)

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('Faulty Towers',
           34,
           3,
           '072 812 31 28',
           1,
           'SRID=4326;POINT(13 -15)');

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('IP Knightly',
           32,
           1,
           '071 812 31 28',
           1,
           'SRID=4326;POINT(18 -24)');

insert into people (name,house_no, street_id, phone_no, city_id, geom)
   values ('Rusty Bedsprings',
           39,
           1,
           '071 822 31 28',
           1,
           'SRID=4326;POINT(22 -25)');

If you’re getting the following error message:

ERROR:  insert or update on table "people" violates foreign key constraint
        "people_city_id_fkey"
DETAIL: Key (city_id)=(1) is not present in table "cities".

then it means that while experimenting with creating polygons for the cities table, you must have deleted some of them and started over. Just check the entries in your cities table and use any id which exists.

16.5.5. Unser Schema

Aktuell sollte unser Schema wie folgt aussehen:

../../../_images/final_schema.png

16.5.6. Try Yourself: ★★★

Erstellen Sie Stadtgrenzen indem Sie die minimale konvexe Hülle um alle Adressen einer Stadt und einen Puffer darum ermitteln.

Antwort

  • Add some people in ‚Tokyo Outer Wards‘ city

INSERT INTO people (name, house_no, street_id, phone_no, city_id, geom)
   VALUES ('Bad Aboum',
           57,
           2,
           '073 712 31 21',
           2,
           'SRID=4326;POINT(22 18)');

INSERT INTO people (name, house_no, street_id, phone_no, city_id, geom)
   VALUES ('Pat Atra',
           59,
           2,
           '074 712 31 25',
           2,
           'SRID=4326;POINT(23 14)');

INSERT INTO people (name, house_no, street_id, phone_no, city_id, geom)
   VALUES ('Kat Herin',
           65,
           2,
           '074 722 31 28',
           2,
           'SRID=4326;POINT(29 18)');

  • Create myPolygonTable table

CREATE TABLE myPolygonTable (
  id serial NOT NULL PRIMARY KEY,
  city_id int NOT NULL REFERENCES cities(id),
  geom geometry NOT NULL
);

ALTER TABLE myPolygonTable
  ADD CONSTRAINT myPolygonTable_geom_polygon_chk
  CHECK (st_geometrytype(geom) = 'ST_Polygon'::text );

  • Create and load the convex hulls

INSERT INTO myPolygonTable (city_id, geom)
  SELECT * FROM
  (
    SELECT
      ROW_NUMBER() over (order by city_id)::integer AS city_id,
      ST_CONVEXHULL(ST_COLLECT(geom)) AS geom
        FROM people
        GROUP BY city_id
  ) convexHulls;

16.5.7. Zugriff auf Unter-Objekte

With the SFS-Model functions, you have a wide variety of options to access sub-objects of SFS Geometries. When you want to select the first vertex point of every polygon geometry in the table myPolygonTable, you have to do this in this way:

  • Umwandeln der Polygongrenze in eine Linie:

    select st_boundary(geom) from myPolygonTable;

  • Auswahl des ersten Stützpunktes der resultierenden Linie:

    select st_startpoint(myGeometry)
from (
    select st_boundary(geom) as myGeometry
    from myPolygonTable) as foo;

16.5.8. Datenverarbeitung

PostGIS unterstützt alle zum OGC SFS/MM Standard konforme Funktionen. Alle diese Funktionen starten mit ST_.

16.5.9. Ausschneiden

Um einen Teil unsrer Daten auszuschneiden, können wir die ST_INTERSECT() Funktion verwenden. To avoid empty geometries, use:

where not st_isempty(st_intersection(a.geom, b.geom))
../../../_images/qgis_001.png 
select st_intersection(a.geom, b.geom), b.*
from clip as a, road_lines as b
where not st_isempty(st_intersection(st_setsrid(a.geom,32734),
  b.geom));
../../../_images/qgis_002.png

16.5.10. Geometrien aus anderen Geometrien erstellen

From a given point table, you want to generate a linestring. The order of the points is defined by their id. Another ordering method could be a timestamp, such as the one you get when you capture waypoints with a GPS receiver.

../../../_images/qgis_006.png

Um eine Linie aus einem neuen Punktlayer ‚points‘ zu erstellen, kann man das folgende Kommando ausführen:

select ST_LineFromMultiPoint(st_collect(geom)), 1 as id
from (
  select geom
  from points
  order by id
) as foo;

Um die Arbeitsweise zu testen ohne einen neuen Layer zu erstellen, kann man das Kommando auf den ‚people‘ Layer anwenden, obwohl das kein wirklich sinnvolles Ergebnis liefert.

../../../_images/qgis_007.png

16.5.11. Geometriebereinigung

You can get more information for this topic in this blog entry.

16.5.12. Unterschiede zwischen Tabellen

Um den Unterschied zwischen zwei Tabellen mit derselben Struktur herauszufinden, kann man das PostgreSQL Schlüsselwort EXCEPT verwenden:

select * from table_a
except
select * from table_b;

As the result, you will get all records from table_a which are not stored in table_b.

16.5.13. Tablespaces

Man kann durch die Erstellung von Tablespaces vorgeben, wo Postgres seine Daten speichern soll:

CREATE TABLESPACE homespace LOCATION '/home/pg';

When you create a database, you can then specify which tablespace to use e.g.:

createdb --tablespace=homespace t4a

16.5.14. Zusammenfassung

Sie haben gelernt, wie man komplexere Geometrien mit Hilfe von PostGIS Anweisungen erstellt. Dies dient vor allem der Verbesserung Ihres Hintergrundwissens bei der Arbeit mit räumlichen Datenbanken über eine GIS Oberfläche. Normalerweise müssen Sie diese Anweisungen nicht manuell eingeben. Trotzdem hilft ein generelles Verständnis ihrer Struktur bei der Nutzung von GIS, insbesondere wenn Fehler auftreten, die ohne dieses Wissen unverständlich erscheinen.