Transcription

6. Dezember 2010PostGIS - das Wo?in der DatenbankStefan Keller und Andreas NeumannS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20101

Einleitung Die Ergänzung von (bestehenden)Informationen mit Ortsbezug erschliesstvöllig neue Möglichkeiten z.B. Suche nach Bars in der Nähe Geodaten sind die Basis dafür! Diese Präsentation soll Informatiker in dieWelt der Geodaten einführenS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20102

Geodaten Def. Geodaten: einfach Daten mit Raumbezug – und trotzdemunterschätzt! Raumbezug: Punkt, Linie, Fläche mit (gemeinsamem) Koord.Referenzsystem, z.B. Lat./Lon. 48.72,9.16 Adresse: Plieninger Str. 100, D-70567 Stuttgart Def. Geoinformationssysteme (GIS): Geo-DB und weitere SW-Komponenten (EVAP)S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20103

What‘s so special about spacial? 5 Gründe warum Geodaten “komplex” sind:1. Komplexe Datenstrukturen da nicht-relational, siehe Datentypen gleich1. Umfangreiche Metadaten u.a. Koordinaten-Referenzsysteme, siehe Theorie1. Komplexe Konsistenzbedingungen und Grafik siehe gleich1. Grosse Datenmengen (d.h. komplex in derVerarbeitung) OpenStreetMap zurzeit 171 GB (XML ausgepackt)1. Teure Datenerfassung und -nachführungS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20104

Geschichte von IT, DBMS undPostGIS Erste kommerzielle Computer Ende 50er Jahre (LEO I, UNIVAC) erste GIS ab 1960 (Symap Uni. Harvard) Erste DBMS (Oracle) 1978 erstes (Server-)GIS 1982 (ARC/INFO) Postgres 1986 (Stonebreaker) Erste Desktop CAD (AutoCAD) 1982 1986 Desktop-GIS (Mapinfo) Erste kommerz. DBMS mit Geodaten-Typen 1986 (Oracle 7.3) 1996: PostgreSQL Open Source und 2001: PostGIS Erste Webapplikationen (u.a. E-Mail) frühe 90er; 'Geo-Web' ab Mitte 2000 (Google Maps); HTML5 ab 2010Fazit: Zehn Jahre Rückstand von IT-Mainstream zu GIS(Credits: Muki Haklay, University College London)S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20105

Moral von der Geschicht‘ Sie dokumentiert, dass Geodaten “komplex” sind Der „Rückstand“ erklärt, warum GIS seit 20 Jahren als"Emerging Technology" bezeichnet werden Alles das hat (hatte) zur Folge, dass viele Firmen im GIS-Business aufgeben mussten (z.B.Location-Based Services) GIS-Firmen ca. 10 Jahre durchhalten müssen Womit alle vor Selbstüberschätzung gewarnt seien!S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20106

Etwas Theorie Vektorund Rastersowie 'mixed‘ Koordinaten-Referenz-Systeme (SRS, CRS), z.B.EPSG:4326 für ‚GPS‘ Koordinaten mit gleichenSRS Layer-Prinzip Wirken wie„Fremdschlüssel“S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20107

Geodatentypen: Grundlagen Geometriedatentypen in PostGIS: Point, LineString/Curve, Polygon, etc. MultiPoint, MultiLineString, MultiPolygon, etc. Dazu 3D-Datentyp Geography Standards OpenGIS Consortium, ISO 19000, SQL/MM 2D-Daten: POINT(8.55 47.37) -- Zürich LINESTRING(0 0, 1 1, 1 2) POLYGON( (0 0,4 0,4 4,0 4,0 0), (1 1, 2 1, 2 2, 1 2,1 1) ) 3D-Daten: POINT(8.55 47.37 410) -- Zürich auf 410m HöheS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20108

Geodatentypen: Funktionen ST Distance(geom, geom) - units ST DWithin(geom, geom, distance) - boolean ST Intersect(geom, geom) - geom ST Transform(geom, srid) - geom – z.B. SRID 4326z.B. POINT(683946.7 246796.8) zu POINT(8.55 47.36) Alle mit Präfix „ST “ (Spatial Type) und noch ca. 300 Funktionen mehr S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 20109

Ein-/Ausgabe-Formate Vektor-Daten KML, Shapefile, GeoJSON, etc. ogr2ogrMapInfo, DGN, CSV, GPX, SQLite, PostGIS Raster-Daten gif, png, jpg, tif, etc. gdalinfo --formatsVRT, GeoTIFF, HDF, HGT, RLE, XPM, SGI, ERS, S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201010

Konsistenzbedingungen,Ebene/Layer und Grafik „Statische Konsistenzbedingungen“ Was ist eine gültige Linie?2D-Fläche? 3D-Objekt? Ebene/Layer Gruppe von Entitäten mit Beziehungen Die Geometrie (und zugeordnetes SRID) ist dieBeziehung! IT „UML-Package“; CAD farbige Vektoren mit IDs Grafik Generierung von Grafik aus Koordinaten (-listen) (istetwa so, wie aus XML-Rohtext sowohl Abstract alsauch Buch generieren) Kunst der Visualisierung: Chronische Platznot aufBildschirm und PapierS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201011

Gut zu wissen Abhilfe zum Platzmangel: Multirepräsentation! Kartogr. Zoom (Zoom versus Skalierung) Drei Stolpersteine (aus Sicht Informatiker) Viele Begriffe, Formate, Implementationen Metadaten (SRS), Konsistenzbedingungen Was erscheint wo? (Zoom-Level)S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201012

Beispiel mit SQL (1)-- Gegeben Staedte-Tabelle der Schweiz:CREATE TABLE staedte(gid integer PRIMARY KEY,name character varying,geom geometry);-- Räumliche Geometrie-Spalte und -Index dazufügen:SELECT AddGeometryColumn('public', 'staedte', 'geom', 21781, 'POINT',2);CREATE INDEX staedte gist ON staedte USING gist (geom);-- Ausgabe 1: Lokales SRS:SELECT name, ST AsText(geom), ST SRID(geom)FROMstaedte AS staWHERE name 'Zürich'"Zürich";"POINT(683946.7 246796.8)";21781S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201013

Beispiel mit SQL (2)-- Ausgabe 2: SRS mit lon/latSELECT name, ST AsText(ST Transform(geom,4326))FROMstaedte AS staWHEREname 'Zürich' LIMIT 1;"Zürich";"POINT(8.55 47.36)"-- Ausgabe 3SELECT 'http://maps.google.com/?q ' ST Y(ST Transform(geom,4326))::text ',' ST X(ST Transform(geom,4326))::text as lon latFROMstaedte AS staWHEREname 'Zürich' LIMIT 1;"http://maps.google.com/?q 47.36,8.55"S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201014

Beispiel mit SQL (3)-- Die 5 nächsten Orte von Zürich im Umkreis von 5000m:SELECT name as stadt,round((ST Distance(sta.geom, sta2.geom)/1000)::numeric, 3)as dist kmFROMstaedte sta,(SELECT (ST Transform(ST GeomFromText('POINT(8.55 47.37)',4326),21781))WHEREORDERas geom) sta2ST DWithin(sta.geom,sta2.geom,10000)AND sta.name ! 'Zürich'BY 2 LIMIT Witikon";2.51788786503948S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201015

Beispiel mit SQL (4)-- oder:SELECT name as stadt,round((ST Distance(sta.geom, sta2.geom)/1000)::numeric, 3) as dist kmFROM staedte sta,(SELECT geom FROM staedte WHERE name 'Zürich') sta2WHERE ST DWithin(sta.geom,sta2.geom,10000)AND sta.name ! 'Zürich'ORDER BY 2 LIMIT 5;-- oder:SELECT sta.name as stadt,round((ST Distance(sta.geom, sta2.geom)/1000)::numeric, 3) as dist kmFROM staedte sta, staedte sta2WHERE ST DWithin(sta.geom, sta2.geom, 10000)AND sta2.name 'Zürich'AND sta.name ! 'Zürich'ORDER BY 2 LIMIT 5;S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201016

Erfahrungen mit PostGIS Schnell jedenfalls schneller als z.B. MS SQL Server(vgl. "HSR Benchmark“) Einfacher zu warten (als z.B. Oracle) Stabil und produktionsreif genügt Enterprise-Anforderungen Gute Dokumentation und guter Support Gute Implementierung der Standards (OGC etc.)S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201017

PostGIS: Schwächen und Potential Schwächen ST Extent (ähnlich wie Count) zu laaangsaaam! Geodaten-Benchmarks sind nötig! Fehlende Features 3D-Index (siehe Roadmap OpenGeo) Weitere Geometrie-Datentypen, z.B.– Point Cloud (Laser Radar; siehe Roadmap OpenGeo) Neue Features siehe Release 2.0 Potential Noch bessere Performance: Extent, Nearest-neighbour Suche Backend von GIS: zuwenig genutzt (nur Speicherung)S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201018

Verbreitung von PostGIS IGN Paris (France) Verkehrsministerium (Niederlande) Swisstopo Bern, Kantone Solothurn undThurgau, Gemeinden (Schweiz) Im Hochschulunterricht. OpenStreetMap.org (VGI): Migriert im April2009 von MySQL zu PostgreSQL; (baldPostGIS?) Vermutlich in versch. Verwaltungen vonDeutschland, Kanada, Brasilien, Südafrika,etc. (bitte melden :- )S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201019

Stadt Uster: Erfahrungen mitPostGIS Zentrales Geodata-Warehouse Guter Support in verschiedener GIS-Software Sehr stabil, gute Performanz Einfache Wartung Einfach zu lernen (gute Dokumentation) Gutes Ökosystem (Behörden, Hochschulen, Firmen) Gut erweiterbar Gute und schnelle WeiterentwicklungS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201020

Anwendungen von PostGIS:OpenStreetMap-in-a-Boxosminabox - A Ready-Made Map and GeodataServer Including a Highly ConfigurableConverter Which Synchronizes OpenStreetMapDataS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201021

osminabox Website (Showcases)22GeoServer GeoExt/OpenLayersS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201022

osminabox ArchitekturHTTPTomcat 6XMLGeoServer2.0PostgreSQL8.4PostGIS 1.5JavaJDBC, Bzip2, JASP, Quartz,Log4j, JUnitS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201023

osminabox: Erfahrungen mitPostGIS Robust Schnell Umfangreiche FunktionenS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201024

Vektor-Raster Analyse in PostgisDas wktraster-ProjektS. Keller und A. Neumann25

Ziele Hybride Speicherung/Datenverarbeitung Gleiche/ähnliche SQL-Befehle wie beiVektordatenverarbeitung Wachsende Anzahl von Analysemöglichkeiten Sehr flexible Speichermöglichkeiten Innerhalb/ausserhalb DB Eine Zelle per Record, ein Tile per Record,gesamter Rasterdatensatz per Record Jeder Record kann andere Georeferenzierunghaben Metadaten können direkt vom Rasterdatentypabgeleitet werdenS. Keller und A. Neumann26

Mögliche Anwendungen Backend für Desktop-GIS Backend für ModelBuilder Backend für Reports/Berichte Backend für Web-GIS Backend für WPS Toolbox für EntwicklerS. Keller und A. Neumann27

Datenspeicherung in DBVorteile: Alles in einer DB/Dump Schnellere Analyse Rasterbearbeitung kann Teil von DB-Transaktionsein Besser getestet als Raster ausserhalb DBNachteile: Bläst Dumpfile auf (Backup) Raster können nicht von beliebigen anderenTools bearbeitet werdenS. Keller und A. Neumann28

Datenspeicherung ausserhalb DBVorteile: Auch andere Applikationen können auf Rasterzugreifen Einfacheres Backup (kein riesiger DB-Dump)Nachteile: Derzeit ”read-only” Langsamerer Zugriff für Analysen Weniger getestet Nicht im DB-Dump drinnen (Problem derverwaisten Referenzen) Kann nicht Teil von DB-Transaktionen seinS. Keller und A. Neumann29

Arten der SpeicherungVerschiedene Raster könnenunterschiedliche:Zellgrössen Anzahl Bänder Ursprünge/Grids Projektionen haben und können trotzdemin einem gemeinsamen SQLStatement verwendet werdenS. Keller und A. Neumann30

MetadatenKeine separaten Metadatentabellen wie bei Oracle

Format, Bänder und Pyramiden Speicherformat: wkb Beliebige Anzahl Bänder pro Raster Pyramiden werden vor dem Einlesen generiert (gdal)Müssen im Vorhinein festgelegt werden Pyramiden sind in separaten Tabellen gespeichertS. Keller und A. Neumann32

Import Import über gdal2wktraster (resp. Raster2pgsql) Alle Formate die gdal unterstützt ( 100 Formate) Import in 2 Schritten: Erzeugung .sql-File, danneinlesen des SQL-Filesgdal2wktraster.py -t dtm.dtmav -M -r dtm av.txt -odtmav.sql -s 21781 -k 200x200 Optional können beim Import auch Pyramiden generiertwerden Geplant: direkter Import ohne Umweg über SQL-FileS. Keller und A. Neumann33

Export Über gdal (gdal translate, gdal warp) – wie alle anderenRasterdaten auch ST AsBinary() In Implementierung: ST AsText(), ST AsJPEG, ST AsTiff,ST AsPNG Vektorobjekte werden beim Export automatischrasterisiertS. Keller und A. Neumann34

Befehlsgruppen Raster Management Functions Raster and Raster Band Accessors and Constructors Raster Pixel Accessors and Setters Raster Editors and Band Editors Raster Outputs Raster Processing Functions Raster Operators Raster Spatial Relationship OperatorsS. Keller und A. Neumann35

In Implementierung ST Resample(raster): nearest neighbour, bilinear,bicubic ST Reclass(rast geom, expr text) zum Reklassifizierenvon Rasterdaten ST SelectByValue(raster geometry, ‘expression’)PixelSelektionen über Bedingungen/ranges ST MapAlgebra(raster geometry, [raster geometry,.],‘mathematical expression’,‘raster’ ’geometry’) - MapAlgebra über 2 verschiedene DatensätzeS. Keller und A. Neumann36

Ideen für SQL-Abfragen Rasterwerte abfragen für Punkte/Flächen/Linien (z.B.Gebäudehöhen, Baumhöhen (DOM/DTMAV)) Reklassifizierungen Map-Algebra Geländemodellierung Kürzeste Wege finden mit Raster die Gewichte als”Hindernisse” beinhalten Raster ausschneiden aufgrund von Vektorgeometrien(z.B. Alle Hausdächer aus Orthofoto)S. Keller und A. Neumann37

Beispiel: Gebäudehöhen extrahierenSELECT MIN((int result).val), MAX((int result).val), AVG((int result).val)FROM(SELECT ST Intersection(dom.rast, ST BUFFER(geb.the geom,-0.5)) AS int resultFROM dtm.dom dom, av.gebaeude gebWHERE geb.nummer 1884 AND ST Intersects(dom.rast, geb.the geom)) foo;S. Keller und A. Neumann38

Beispiel: DTM-Höhe entlang einerStrasse extrahierenSELECT ST Value(rast,(SELECTST Line Interpolate Point(ST GeometryN(the geom,1),0.1)FROM av user.strassenstuecke WHERE text 'Florastrasse' AND ordnung 1))FROM dtm.dtmavWHERE (SELECT ST Line Interpolate Point(ST GeometryN(the geom,1),0.1)FROM av user.strassenstuecke WHERE text 'Florastrasse' AND ordnung 1)&& rast;S. Keller und A. Neumann39

Tools/GUI Desktop-GIS: QGIS OpenJump gvSig Mapserver: UMN Mapserver Geoserver QGIS Server Libraries Gdal/ogrS. Keller und A. Neumann40

PostGIS-Raster Fazit Vektor/Rasteranalyse neu?Nein, aber einzigartige Realisierung Existierendes SQL-Knowhow kann auch fürRasteranalyse eingesetzt werden Transparente SQL-Funktionen/Operatoren für Vektorund Raster Berechnungen nur dann wenn nötig und nur für dengerade nötigen geografischen Ausschnitt Umfassender als Oracle GeorasterS. Keller und A. Neumann41

Mögliche Probleme Einige Funktionen noch nicht implementiert Tilegrösse und Pyramidenparameter können nur beimImport definiert werden Out of DB-Raster noch nicht genügend getestet Noch zuwenige Clients/ApplikationenS. Keller und A. Neumann42

PostGIS-Raster Infos/Credits Credits:– Talks at FOSS4G2010 (http://2010.foss4g.org): – Pierre Racine et al.: „Introducing Postgis WKT Raster “, id 3221Jorge Arévalo: „PostGIS WKT Raster, an Open Source Alternative “ id 3814Marc Jansen: „What you type is what you see “, id 3630Obe & Hsu, „PostGIS in Action“, Book by Manning (geplant 1. Quartal 2011,www.manning.com/obe/)Weblinks:– PostGIS Raster:– Postgis Raster Tutorial:– PostGIS:– PostGIS ‚stuff‘:– GISpunkt Wiki:S. Keller und A. hsr.ch/wiki43

AusblickPostGIS Version 2Support- und BuchhinweiseS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201044

PostGIS Release 2.0 (ca. März2011) Neuer Datentyp Raster: siehe oben Neue Funktionen auf 3D Points, z.B. ST 3DDWithin, ST 3DDistance,ST 3DIntersects Geländedaten (topologische 3D-Typen)S. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201045

Support, Bücher und Web Support: Users & Developers: www.postgis.org/support/ Entscheider D-A-CH: www.postgres-support.ch Bücher: „PostGIS in Action“ von Obe & Hsu (geplant ab 1. Quartal2011), Manning (siehe unten) Web: PostGIS:www.postgis.org PostGIS Blog:www.postgresonline.org GISpunkt Wiki:www.gis.hsr.ch/wiki/PostGISS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201046

Noch nicht genug von PostGIS? Mo. 6. September (heute): 14:00-14:50, "PostGIS 1.5 and beyond: a technicalperspective", Mark Cave-Ayland and Olivier Courtin 15:20-16:10, "Openstreetmap - PostGIS - OpenLayers:Mit offenen Karten ins Web", Hartmut Holzgraefe Di. 7. September (morgen): 13:10-14:00, "Discover PostGIS: GIS for PostgreSQL",Vincent Picavet 17:00 (nach Closing): Birds-of-a-feather Session zu„Benchmarking PostgreSQL and other DBMS”? Mi. 8. September (übermorgen): Workshops von Mark Cave-Ayland zu Introduction undAdvanced PostGISS. Keller und A. NeumannPostGIS - das Wo? in der Datenbank, PGDay.EU 201047

Dank / Credits Talks at FOSS4G2010 from Pierre Racine et al.sowie von Jorge Arévalo:http://2010.foss4g.org/presentations.php Obe & Hsu, „PostGIS in Action“, Manning(www.manning.com/obe/)Stefan KellerHSR Hochschule Rapperswilwww.gis.hsr.chsfkeller(ät)hsr(dot)chS. Keller und A. NeumannAndreas NeumannGIS-Kompetenzzentrum S - das Wo? in der Datenbank, PGDay.EU 201048

Einfach zu lernen (gute Dokumentation) . Gleiche/ähnliche SQL-Befehle wie bei Vektordatenverarbeitung . Import in 2 Schritten: Erzeugung .sql-File, dann einlesen des SQL-Files gdal2wktraster.py -t dtm.dtmav -M -r dtm