Sem PostGIS Roger: Unterschied zwischen den Versionen

Aktuelle Version vom 22. Januar 2009, 01:26 Uhr

Wiki-Seite zum Selbststudium im GISpunkt-Seminar PostGIS. Zurück zu Sem PostGIS Selbststudium.

Daten: Amtliche Vermessung Kanton Basel-Stadt

Verwendete Daten

Als Übungsdaten werden zwei Datensätze der amtlichen Vermessung des Kantons Basel-Stadt verwendet. Der eine Datensatz enthält die Rohrleitungen (INTERLIS-Topic), der zweite die Bodenbedeckung (INTERLIS-Topic), aus der im Laufe der Übung die Tankanlagen (Bodenbedeckungsart 17) extrahiert werden.

Die Datensätze liegen als Shapefiles Bodenbedeckung_merged.shp und Rohrleitungen.shp vor.

Metadaten-Records

Bemerkung: Die Angabe beim DC-Element modified bezieht sich auf das Datum des Datenbezugs.

Rohrleitung

 dc:title           = Rohrleitung
 dct:abstract       = Bestandteil des Datenmodells des Bundes. Beschreibt die geometrische Form und 
                      Lage der Rohrleitungen, die von übergeordneter Bedeutung für die 
                      Eigentumsverhältnisse sind.
 dc:format          = INTERLIS, Shapefile
 dct:spatial        = name=Kanton Basel-Stadt; northlimit=47.60200; southlimit==47.51858; 
                      eastlimit=7.69527; westlimit=7.55281
 dct:modified       = 2007-10-19
 dc:publisher       = Grundbuch- und Vermessungsamt Basel-Stadt
 dc:language        = de
 dc:rights          = Abgabebedingungen: Berechtigter Interessennachweis. Die Daten können aufgrund 
                      einer Benützungsbewilligung bezogen werden. Entsprechende Bezugsformulare sind 
                      auf www.gva.bs.ch erhältlich. Für die Verwendung der Daten gelten die allgemeinen 
                      Bedingungen für die Benützung von Geodaten des Grundbuch- und Vermessungsamtes 
                      Basel-Stadt.

Bodenbedeckung (enthält Art Tankanlagen)

 dc:title           = Bodenbedeckung
 dct:abstract       = Bestandteil des Datenmodells des Bundes. Beschreibt die geometrische Form und Lage 
                      der Flächenarten mit einer Mindestfläche von in der Regel 100 m2 (Gebäude, befestigt, 
                      humusiert, bestockt, Gewässer, vegetationslos, etc.).
 dc:format          = INTERLIS, Shapefile
 dct:spatial        = name=Kanton Basel-Stadt; northlimit=47.60200; southlimit==47.51858; 
                      eastlimit=7.69527; westlimit=7.55281
 dct:modified       = 2007-10-19
 dc:publisher       = Grundbuch- und Vermessungsamt Basel-Stadt
 dc:language        = de
 dc:rights          = Abgabebedingungen: Die Daten können aufgrund einer Benützungsbewilligung bezogen 
                      werden. Entsprechende Bezugsformulare sind auf www.gva.bs.ch erhältlich. Für die 
                      Verwendung der Daten gelten die allgemeinen Bedingungen für die Benützung von 
                      Geodaten des Grundbuch- und Vermessungsamtes Basel-Stadt.

Laden des Datensatzes

Die vorliegenden Shapefiles Bodenbedeckung_merged.shp und Rohrleitungen.shp wurden mit shp2pgsql.exe in .sql-Files konvertiert, diese wiederum mit psql.exe in die PostGIS-Datenbank geladen.

Die Shapefiles wurden mit Hilfe zweier Batchfiles automatisiert in die Datenbank importiert: Das erste Batchfile übergibt die zu importierenden Files als Parameter an das zweite Batchfile:

 echo ==============================
 echo  Shapefile in PostGIS-DB laden
 echo ==============================
 echo.
 echo  Übergibt Shapefilenamen (ohne
 echo  Endung) an Hauptbatchfile.
 echo.
  
 set shp2pg_main="_shp2pg_main.bat"
  
 call %shp2pg_main% Bodenbedeckung_merged
 call %shp2pg_main% Rohrleitungen
  
 pause

Das zweite Batchfile lädt das so übergebene Shapefile in die Datenbank:

 echo ==============================
 echo  Shapefile in PostGIS-DB laden
 echo ==============================
 echo.
 echo  Erwartet Shapefilename (ohne
 echo  Endung) als Parameter (%1).
 echo.
  
 set shp2pgsql_exe="C:\Programme\PostgreSQL\8.2\bin\shp2pgsql.exe"
 set psql_exe="C:\Programme\PostgreSQL\8.2\bin\psql.exe"
  
 set srid="21781"
 set schema="public"
 set server="localhost"
 set dbname="postgis-seminar"
 set dbuser="postgres"
  
 echo --------------------
 echo  SQL-File erstellen
 echo --------------------
 %shp2pgsql_exe% -s %srid% -I %1 %schema%.%1 > %1.sql
  
 echo -------------------
 echo  Daten in DB laden
 echo -------------------
 %psql_exe% -h %server% -d %dbname% -U %dbuser% -f %1.sql

Unter Verwendung der UNIX-Pipes können Konvertierung und Laden in die Datenbank auch in einem Schritt durchgeführt werden (s. S. 62 in [1]).

Queries

Query 1: Aufbereiten des Übungsdatensatzes Tankanlagen

Tankanlagen sind als Teil der Bodenbedeckung in der Tabelle bodenbedeckung_merged vorhanden. Für eine einfachere Handhabung werden Sie in eine separate Tabelle tankanlagen ausgelagert. (Die Rohrleitungen sind bereits in Tabelle rohrleitungen aus dem Ursprungsdatensatz rohrleitungen.shp vorhanden.)

 -- Neue Tabelle tankanlagen mit Primary Key erstellen
 CREATE TABLE tankanlagen
 (
   gid integer PRIMARY KEY,
   art_txt text,
   art text
 );
 
 -- Geometriespalte the_geom hinzufügen
 SELECT AddGeometryColumn('public', 'tankanlagen', 'the_geom', 21781, 'MULTIPOLYGON', 2);
 
 -- Daten von Tabelle bodenbedeckung_merged in Tabelle tankanlagen importieren (Tankanlagen sind art = 17)
 INSERT INTO tankanlagen
 (
   SELECT gid, art_txt, art, the_geom 
   FROM bodenbedeckung_merged
   WHERE art = '17'
 );
 
 -- Räumlichen Index für Tabelle tankanlagen erstellen
 CREATE INDEX tankanlagen_the_geom_gist
   ON tankanlagen 
   USING gist (the_geom GIST_GEOMETRY_OPS);
 
 -- Statistiken für optimierte Abfragen anlegen
 VACUUM ANALYZE tankanlagen;

Query 2: Umprojizieren (CH03->WGS84)

Die Daten sind ursprünglich im Koordinatensystem CH03/LV03 (EPSG-Code oder SRID 21781) vorhanden. Für den anschliessenden KML-Export müssen sie jedoch in geografische Koordinaten (WGS84, EPSG-Code 4326) transformiert werden. Zu Übungszwecken wird auf zwei Arten projiziert, einmal anhand einer zweiten Geometriespalte (Rohrleitungen), und einmal on-the-fly (Tankanlagen; s. Abschn. "Als KML speichern").

Query 2a: Rohrleitungen (mit zusätzlicher Geometriespalte)

Neben der schon vorhandenen Geometriespalte the_geom mit SRID 21781, wird für den anschliessenden KLM-Export eine zweite Geometriespalte mit dem SRID 4326 hinzugefügt. Doppeltes Ablegen von Geometrien kann bei häufigen Abfragen Sinn machen.

 -- Zweite Geometriespalte mit EPSG-Code 4326 (WGS84) hinzufügen
 SELECT AddGeometryColumn('public', 'rohrleitungen', 'the_geom_4326', 4326, 'MULTILINESTRING', 2)
 
 -- Neue Geometriespalte the_geom_4326 füllen
 UPDATE rohrleitungen
 SET the_geom 4326 = transform(the_geom, 4326);
 
 -- Räumlichen Index für Tabelle rohrleitungen (the_geom_4326) erstellen
 CREATE INDEX rohrleitungen_the_geom_4326_gist
   ON rohrleitungen 
   USING gist (the_geom_4326 GIST_GEOMETRY_OPS);
 
 -- Statistiken für optimierte Abfragen anlegen
 VACUUM ANALYZE rohrleitungen;

Query 2b: Tankanlagen (on-the-fly)

Die Daten in der Tabelle tankanlagen werden bei der KML-Generierung on-the-fly in WGS84 umprojiziert (s. Abschn. "Als KML speichern").

Query 3: Als KML speichern

Die KML-Dateien wurden mit der Funktion AsKML(text, text, geometrie) gemäss Anleitung auf PostGIS_-_Tipps_und_Tricks#Von_PostGIS_direkt_nach_Google_Earth erstellt. Mit dieser Funktion wird die Ausgabe KML-konform formatiert.

Wichtig ist, dass beim SELECT AsKML(...) mehrere Geometrien zusammengefasst werden, z.b. mit geomunion(), sonst wird der Inhalt mehrerer KML-Dateien (eine für jeweils eine Geometre) in eine einzige geschrieben (d.h. mehrere <?xml>-Tags).

Query 3a: Tankanlagen

Die Tankanlagen wurden mit folgendem SELECT on-the-fly in WGS84-Koordinaten transformiert und in KLM-Form zurückgegeben:

 SELECT AsKML(
   'Tankanlagen', 
   'Amtliche Vermessung Basel-Stadt, Bodenbedeckung Art 17', 
   geomunion( Transform( the_geom, 4326 ) ) )
FROM tankanlagen;

Ergebnis: tankanlagen.kml

Query 3b: Rohrleitungen

Die Geometrien sind nicht nur in der Spalte the_geom in Schweizer Landeskoordinaten ab-gelegt (EPSG-Code 21781), sondern auch in einer zweiten Spalte the_geom_4326 in geografischen Koordinaten, die für den KML-Export benötigt werden. Hier ist somit keine on-the-fly-Transformation nötig.

 SELECT AsKML( 
   'Rohrleitungen', 
   'Amtliche Vermessung Basel-Stadt', 
   geomunion( the_geom_4326 ) )
 FROM rohrleitungen;

Ergebnis: rohrleitungen.kml

Query 4: Ausschnitt (Perimeter, definiert mittels BBox)

Query 4a: Bounding Box generieren

Im Folgenden wird aus den Eckkoordinaten 610750/269000 und 612600/271100 eine Bounding Box generiert.

 SELECT ST_MakeBox3D(
   ST_GeomFromText( 'POINT( 610750 269000 )', 21781 ),
   ST_GeomFromText( 'POINT( 612600 271100 )', 21781 ) 
 );

Ausgabe der Query:

 BOX3D( 610750 269000, 612600 271100 )

Query 4b: Geometrien mit Bounding Box ausschneiden

Die in der oben erstellten Bounding Box enthaltenen Objekte aus der Tabelle Tankanlagen sollen in einer separaten Tabelle tankanlagen_aoi abgelegt werden. Dies kann mit dem Bounding-Box-basierten Overlap && untersucht werden; der Vorteil von Bounding-Box-Operatoren ist, dass der räumliche Index verwendet wird.

 -- Tabelle tankanlagen_aoi aus BBox-Abfrage erstellen und füllen.
 CREATE TABLE "public"."tankanlagen_aoi" AS
 (
   SELECT *
   FROM tankanlagen 
   WHERE the_geom
         && SetSRID( 'BOX3D( 610750 269000, 612600 271100 )'::box3d, 21781 )
  );
 
 -- Primärschlüssel hinzufügen
 ALTER TABLE tankanlagen_aoi ADD PRIMARY KEY( gid );
 
 -- Räumlichen Index kreieren
 CREATE INDEX "tankanlagen_aoi_the_geom_gist" 
   ON "public"."tankanlagen_aoi" using gist ("the_geom" gist_geometry_ops);
 
 - Statistiken für optimierte Abfragen anlegen
 VACUUM analyze tankanlagen_aoi;

Query 4c: Bounding Box erstellen und darin enthaltene Objekte wählen (beides diesmal in einer einzigen Query)

 CREATE TABLE "public"."tankanlagen_aoi" AS
 (
   SELECT *
   FROM tankanlagen 
   WHERE the_geom
     && SetSRID( 
     (
       SELECT ST_MakeBox3D(
         ST_GeomFromText( 'POINT( 610750 269000 )', 21781 ),
         ST_GeomFromText( 'POINT( 612600 271100 )', 21781 ) 
       )
      )::box3d, 21781 )
 );

Die Funktion SetSRID() ordnet der BBox das entsprechende SRID zu; andernfalls würde es defaultmässig -1 betragen.

Nun kann, wie oben beschrieben, noch ein Primärschlüssel und ein räumlicher Index hinzugefügt werden.

Query 5: Buffer rund um einen Ausschnitt

Um die weiter oben erstellte Bounding Box soll ein Buffer berechnet werden. Die auf diese Weise ausgegebene Bounding Box (BOX3D(…)) soll gleich in die Buffer-Query integriert werden. Um die Bounding Box vom Type box3d in eine Geometrie umzuwandeln, stand früher die Funktion GeometryFromText() zur Verfügung; in neueren PostGIS-Versionen ist diese nicht mehr implementiert, stattdessen kann SetSRID() verwendet werden[2]. Als Buffer wurden 1000m gewählt.

 SELECT AsText(
   ST_Buffer( 
     SetSRID( 'BOX3D( 610750 269000, 612600 271100 )'::box3d, 
       21781 ),
     1000
   )
 );

Ergebnis: Ein Polygon, das ein Rechteck mit "abgerundeten Ecken" repräsentiert.

Query 6: Spatial Join (Overlay, Flächenverschnitt)

Es soll ermittelt werden, welche Teilstücke der Rohrleitungen wenigstens teilweise innerhalb eines Buffers von 50 Metern um die Tankanlagen liegen, diese Buffer also überlappen:

 SELECT r.gid, r.the_geom
   FROM rohrleitungen r, tankanlagen t
   WHERE r.the_geom && ( SELECT ST_Buffer( t.the_geom, 50 ) )
   GROUP BY r.gid, r.the_geom;

Ergebnis: rohrleitungen_nahe.kml (Erstellung: zuerst wurde aus obenstehendem SELECT analog dem Abschnitt "Geometrien mit Bounding Box ausschneiden" die Tabelle rohrleitungen_nahe kreiert und gefüllt, die dann analog dem Abschnitt "Als KML speichern" als .kml ausgegeben wurde).

Query 7: Kleinste Distanz (eigene Query)

Es soll ermittelt werden, wieviel die kürzeste Distanz zwischen den Tankanlagen und Rohrleitungen beträgt. Dies kann mit ST_Distance() in Kombination mit MIN() und der Klausel GROUP BY eruiert werden.

 SELECT r.gid AS r_gid, 
        t.gid AS t_gid, 
        MIN( ST_distance( r.the_geom, t.the_geom ) ) AS min_dist
 FROM rohrleitungen r, tankanlagen t
 GROUP BY r.gid, t.gid
 ORDER BY min_dist ASC
 LIMIT 1;

Ausgabe:

 r_gid; t_gid; min_dist
 ------------------------------
 15;    39111; 1.76061264778433

Die kürzeste Distanz beträgt demnach rund zwei Meter, und zwar zwischen dem Rohrleitungsteilstück mit gid=15 und der Tankanlage mit gid=39111.