Oracle Spatial: Tipps, Tricks, Best practice Carsten Czarski Business Unit Database ORACLE Deutschland GmbH Oracle Spatial Offene Geodaten ... Offene Zugriffe mit SQL, Java, .NET, ... Geo-Operationen mit SQL durchführbar Datennahe Operationen: Beste Performance direkt in der Datenbank Viele Utility-Funktionen GIS 1 SQL SQL GIS 2 “Spatial enabled” Business Intelligence Eigene Abfragen Generell ... Offenes Datenmodell in Oracle Spatial Name ----------------SDO_GTYPE SDO_SRID SDO_POINT SDO_ELEM_INFO SDO_ORDINATES Type -------------------NUMBER NUMBER SDO_POINT_TYPE SDO_ELEM_INFO_ARRAY SDO_ORDINATE_ARRAY Einfacher Umgang direkt mit SQL – – Informationen abrufen Geometrien bearbeiten Informationen über Geodaten PL/SQL-Paket SDO_UTIL Geometrien anzeigen lassen ... select id, x, y from de_hiway e, table(sdo_util.getvertices(e.geoloc)) where rownum < 20 ID ---------1 2 3 : X Y ---------- ---------12.0115001 47.8214 11.9985998 47.8229 11.9702999 47.8228 : : Oracle Spatial Operationen mit Geodaten Spatial-Operationen in der Datenbank (Beispiele) – – – – – SDO_GEOM.SDO_INTERSECTION SDO_GEOM.SDO_CENTROID SDO_GEOM.SDO_DISTANCE SDO_CS.TRANSFORM ... Empfehlung: – – Datenintensive Operationen mit Massendaten in der Datenbank durchführen (SQL, PL/SQL) Gründe: Netzwerkverkehr, Datennähe Oracle Spatial Ein Beispiel: Datenintegrität mit RDBMS-Trigger Nur gültige Geometrien werden gespeichert create or replace trigger tr_valid_geom before insert or update on [geom_table] for each row is v_result varchar2(32767); begin v_result := sdo_geom.validate_geometry_with_context( [:new.geom_col], [tolerance] ); if not v_result = 'TRUE' then raise_application_error(-20000,'...'); end if; end; Umgang mit Geometrien Toleranzen Höhere Toleranzen = weniger Rechenbedarf Achtung: – – – Zu hohe Toleranzen: Ungültige Geometrien Toleranzen müssen zu Geometrien passen Bei Bedarf Geometrien vereinfachen (SIMPLIFY) Geometrie ungültige Geometrie vereinfachte Geometrie Tipps & Tricks mit SDO_GEOMETRY Wie groß sind meine Geometrien ...? select * from ( select sdo_util.getnumvertices(geoloc) from [table] order by sdo_util.getnumvertices(geoloc) desc ) where rownum<6 SDO_UTIL.GETNUMVERTICES(GEOLOC) ------------------------------4873 1769 1642 1553 1220 Spatial Index Aufbau des R-Tree-Index a root R b S c R a d S b c d root Zeiger auf Geometrien Nutzung des Spatial-Index Primär- und Sekundärfilter Optimizer-"Shortcut": SDO_FILTER Primary Primary Filter Filter Geodaten Geodaten Spatial Spatial Index Index Index-Lookup Reduced Reduced Data Data Set Set Secondary Secondary Filter Filter Spatial Spatial Functions Functions Exact Exact Result Result Set Set Berechnungen (CPU!) R-Tree-Indizes "Blattknoten" in eigener Tabelle SDO_NON_LEAF_TBL = TRUE root R a MDNT$.... S b c d • MDNT$-Tabelle im Cache halten • ... STORAGE (BUFFER_POOL KEEP) • Bei großen Datenmengen sinnvoll Zeiger auf Geometrien MDRT$.... R-Tree-Indizes Weitere Parameter ... LAYER_GTYPE = POINT – – – Index nur für Punkte Auch andere Geometrie-Arten möglich Speziell für POINT: Abfrage-Optimierung GEODETIC = FALSE – – Nicht-Geodätischer Index auf geodätische Daten Nur in Ausnahmefällen empfohlen PARALLEL SDO_RTR_PCTFREE = [integer] – – Frei-Bereich eines R-Tree-"Slots" (default 10) Je mehr INSERT-Aktivität, desto höher ... R-Tree Indizes Große Datenmengen laden ... Wichtig bei Massen-Inserts Vorgehensweise – Index-Status auf "deferred" setzen ALTER INDEX ... PARAMETERS ('index_status=deferred'); – – Daten laden Index synchronisieren ALTER INDEX ... PARAMETERS ('index_status=synchronize sdo_batch_size=500'); R-Tree Indizes Indexqualität – Index Rebuild Index neu erstellen ALTER INDEX ... REBUILD Sinnvoll bei "Quality Degradation" – – SDO_TUNE.QUALITY_DEGRADATION Performance-Nachteil ... ... im Vergleich zu neu erstellten Index Tipp: "Benutzerdefinierte Metrik" im Enterprise Manager R-Tree Indizes Indexqualität - Monitoring Oracle Enterprise Manager (DB Console) Einrichten als Benutzerdefinierte Metrik Benachrichtigung via Alert ACHTUNG: Signifikanz beachten – SGA-Maßnahmen (Buffer Cache) bringen mehr ... Tabellen partitionieren Prinzip – – – Aufteilung in mehrere physikalische Einheiten Nach außen eine normale Tabelle Oracle TEXT unterstützt partitionierte Tabellen Nutzen: Performance – – – Abfragen können auf einzelne Partitionen beschränkt werden (Optimizer) Partitionen können als Einheit gelöscht oder verschoben werden Parallele Index-Operationen Rasterdaten Datenformate ... Unterstützung "out-of-the-box" für ... – – – – ESRI World Files JPEG GIF TIF/GeoTiff BMP ESRI World File (.tfw) 62.50000000000000 – 0.00000000000000 0.00000000000000 ESRI World Files werden georeferenziert -62.50000000000000 – Koordinatensystem (SRID) muss 32760031.25000000000000 5364968.75000000000000 jedoch angegeben werden ... Rasterdaten Server-gestützter Ladevorgang PL/SQL Prozedur SDO_GEOR.importFrom() init() importFrom() ESRI World Files: SRID angeben georeference() generate SpatialExtent() generate Pyramid() Rasterdaten Datenspeicherung ... Raster besteht aus X Blöcken (SDO_RASTER) GEORASTER-Tabelle (Metadaten) Raster SDO_GEORASTER Rasterdaten-Tabelle (Daten) Rasterblock Rasterblock Rasterblock Rasterblock SDO_RASTER Rasterdaten Datenspeicherung ... Tabellen – – – Metadaten: Rasterdaten: Rasterblock: Typ SDO_GEORASTER Table of SDO_RASTER SDO_RASTER.RASTERBLOCK Speicherung als BLOB – Storage Parameter sind wichtig Eigenes Tablespace (große Blöcke) Extent-Verwaltung (große Extents) Logging-Einstellungen (NOLOGGING) Buffer Cache-Verhalten (CACHE, CACHE READS) MapViewer Performance Faktoren ... Anzahl der Geometrien und Layer Detaillierungsgrad der Geometrien Qualität der JDBC-Abfragen MapViewer-Caching – – Metadata cache Spatial data cache Oracle MapViewer Aufbau einer Karte ... Schritte – – Parsing des XML Map Requests JDBC Queries an die Datenbank Einschränkung mit Query Window • Parallelisierung HIER Vorsicht mit dem "as-is"-Attribut • Attribut "number-of-mappers" – – Rendering / Labeling Erzeugung des Images (GIF/PNG/JPEG/...) Große Geometrien und Visualisierung Situation – – Große und sehr detaillierte Geometrien Stark herausgezoomte Karte Problem – – Geometrien werden vollständig dargestellt Details sind wg. Zoom-Level ohnehin nicht sichtbar Lösungsansatz: – – – Verschiedene Map-Layer je nach Zoom-Level Geometrien vereinfachen SDO_UTIL.SIMPLIFY Zusätzliche Tabellen anlegen Tipps & Tricks mit SDO_GEOMETRY Wie groß sind meine Geometrien ...? select * from ( select sdo_util.getnumvertices(geoloc) from [table] order by sdo_util.getnumvertices(geoloc) desc ) where rownum<6 SDO_UTIL.GETNUMVERTICES(GEOLOC) ------------------------------4873 1769 1642 1553 1220 Große Geometrien und Visualisierung Geometrien vereinfachen (SDO_UTIL.SIMPLIFY) select * from ( select sdo_util.getnumvertices(geoloc) from [simplified_table] order by sdo_util.getnumvertices(geoloc) desc ) where rownum<6 SDO_UTIL.GETNUMVERTICES(GEOLOC) ------------------------------1618 1287 1025 : Geometrien vereinfachen Stark herausgezoomte Karten Original-Geometrien Vereinfachte Geometrien Oracle MapViewer Caching Reines in-Memory-Caching Metadaten-Cache – – Style, Theme und Map-Definitionen Stets aktiv – Löschen via Map Request Spatial Data Cache – – Per Default aktiv Abschalten mit max_cache_size=0 Oracle MapViewer Spatial Data Cache Theme-basiertes Caching ... – – – NONE NORMAL ALL Kein Caching Objektdefinitionen (Fast unpickling) Objektdefinitionen und Daten Kein Caching für ... – – JDBC Themes Dynamische Themes Oracle MapViewer und Rasterdaten Wissenswertes ... Java Advanced Imaging (JAI) Libraries – – Einbindung in den CLASSPATH jai_core.jar, jai_codec.jar Datenbank oder App.-Server Installation Rasterdaten müssen georeferenziert sein – – ESRI World Files (.tfw) SDO_GEOR.georeference() Indizierung der Spatial Footprints – SDO_GEORASTER.SPATIALEXTENT Auflösungspyramiden (Performance) Oracle Spatial 10gR2 Unterstützung für EPSG Koordinatensysteme – – – De-Facto-Standard Noch mehr Koordinatensysteme Mehr Flexibilität beim Umrechnen ORA_SRID --------82337 82340 82343 82346 82349 : CS_NAME EPSG_SRID ------------------------ ----------UTM Zone 30 (ED 50) 23030 UTM Zone 31 (ED 50) 23031 UTM Zone 32 (ED 50) 23032 UTM Zone 33 (ED 50) 23033 UTM Zone 34 (ED 50) 23034 : : Oracle Spatial 10gR2 Unterstützung für Well-Known Text GET_WKT()-Funktion select e.geoloc.get_wkt() from deflusse e E.GEOLOC.GET_WKT() ---------------------------------------------LINESTRING (10.7313 52.4531, 10.7261 52.4569, 10.7242 52.4605, 10.7196 52.4628, ... Konstruktoren WKT SDO_GEOMETRY – – SDO_GEOMETRY (wkt CLOB, srid NUMBER); SDO_GEOMETRY (wkt VARCHAR2, srid NUMBER); Oracle Spatial 10gR2 Weitere Neuigkeiten ... Utility-Funktionen ... – – SDO_UTIL.RECTIFY_GEOMETRY (Automatische Korrektur von Polygonen) IS_VALID-Funktion Geocoding – Reverse Geocoding ... GeoRaster – – Kompression Verbesserte Werkzeuge Spatial GeoRaster 10gR2 Verbesserte Werkzeuge ... Weitere Informationen Oracle Dokumentation – Oracle Spatial User's Guide and Reference http://download-uk.oracle.com/docs/cd/B19306_01/ appdev.102/b14255/toc.htm – Oracle Spatial GeoRaster http://download-uk.oracle.com/docs/cd/B19306_01/ appdev.102/b14254/toc.htm Oracle Technology Network (OTN) – Database Location Services http://www.oracle.com/technology/products/ spatial/index.html Q& A Q U E S T I O N S A N S W E R S