Oracle Spatial 101 - An Introduction

An introductory talk about Oracle Spatial

Transcript

2. About Me •Agile Coach & Consultant (2012) •Enterprise Architect (2010) •Spatial Engineer (in GIS field since 2007) •Programming since 1993 •Open Source Enthusiast & Life long learner •Loves Lean, Agile, TTD, XP & doing things „right“
3. Agenda •Spatial Information Management •Overview of Oracle Spatial •Further Reading
4. About you You are... •an application developer • familiar with database design, PL/SQL, Java, ... • unfamiliar with spatial data or GIS •a technical user of Oracle • interested in advanced features of database technology •a GIS programmer
5. Spatial Information Management
6. From G[is] to g[IS] •GIS • Specialized applications for storing, processing, analyzing, and displaying spatial data • Isolated from application data •Spatial Databases • application data + spatial data
7. Sources of Spatial Data •„Static“ data • address lists, cadastral, land-use, road networks, administrative boundaries, rivers & lakes, ... • distributed by public bodies or companies •Real-Time data... • GPS, WiFi, RFID (location/sensor-based services)
8. Storage of Spatial Data •Database supports a geometry type • points, lines, polygons • networks, • raster images • 3D/4D •Create, Update, Delete (CRUD)
9. Analyzing Spatial Data •Within-distance •Contains •Nearest-Neighbor •Distance •Buffer •Visualization (Mapping & Overlay)
10. Example
11. Benefits of Oracle Spatial •Unified data storage (app + spatial) • GIS integrated, not separated •Use standard SQL •Supported by world‘s leading „Spatial-ists“ • NAVTEQ, Tele Atlas, Digital Globe, ESRI, ...
12. Overview of Oracle Spatial
14. Data Model (SDO_GEOMETRY) Location • specifies where the data is located with respect to a two-, three-, or four-dimensional coordinate space. Shape • specifies the geometric structure of the data, e.g. point, line, polygon, … →San Francisco is located at (–122.436, 37.719) in 2d “latitude, longitude” space.
15. Location Enabling Create tables SQL> CREATE TABLE restaurants ( id NUMBER, poi_name VARCHAR2(32), location SDO_GEOMETRY -- New column to store locations );
16. Location Enabling Populate tables SQL> INSERT INTO restaurants VALUES ( 1, 'PIZZA HUT', SDO_GEOMETRY ( 2001, -- SDO_GTYPE attribute: "2" in 2001 means 2d. NULL, -- other fields are set to NULL. SDO_POINT_TYPE( -87, 38, NULL), NULL, NULL ) );
17. Location Enabling Geocoding SQL> SELECT SDO_GCDR.GEOCODE_AS_GEOMETRY ( 'SPATIAL', -- Spatial schema storing the geocoder data SDO_KEYWORDARRAY -- address components ( '3746 CONNECTICUT AVE NW', 'WASHINGTON, DC 20008' ), 'US' -- Name of the country ) geom FROM DUAL ;
18. Query & Analysis Geometry Engine SQL> SELECT poi_name FROM ( SELECT poi_name, SDO_GEOM.SDO_DISTANCE(P.location, I.geom, 0.5) distance FROM interstates I, restaurants P WHERE I.interstate = 'I795' ORDER BY distance ) WHERE ROWNUM <= 5;
19. Query & Analysis Index Engine SQL> CREATE INDEX restaurants_sidx ON restaurants(location) INDEXTYPE IS mdsys.spatial_index; SQL> SELECT poi_name FROM interstates I, restaurants P WHERE I.interstate = 'I795' AND SDO_NN(P.location, I.geom) ='TRUE' AND ROWNUM <= 5;
20. Query & Analysis All restaurants within 50km SQL> SELECT POI_NAME FROM interstates I, restaurants P WHERE SDO_ANYINTERACT ( P.location, SDO_GEOM.SDO_BUFFER(I.geom, 50, 0.5, 'UNIT=KM') ) ='TRUE' AND I.interstate='I795' ;
21. Visualizing Spatial Data MapViewer & Oracle Maps → Need to specify Maps, Themes & Styles in USER_SDO_MAPS, USER_SDO_THEMES, and USER_SDO_STYLES
22. Advanced Spatial Engine •Network data model („routing“) •Linear Referencing System (LRS, „measures“) •GeoRaster •Topological Data Model • Nodes & edges, rules for implicit updates • „shared road segment“
23. Oracle Spatial Licensing
24. Locator • Data model (SDO_GEOMETRY, 2D only) • Query & Analysis (Index Engine only) • spatial indexes & operators such as SDO_NN •
26. Spatial Option • Data model (+3D → buildings, CAD) • Query & Analysis (Index & Geometry Engine) • length, area, volume • Geocoder • Advanced Spatial Engine • routing and network analysis
28. Installing Spatial • Oracle Standard or Enterprise includes Spatial • Installed into schema MDSYS • Check Spatial Option installed SQL> SELECT COMP_NAME, STATUS FROM DBA_REGISTRY WHERE COMP_NAME = 'Spatial'; →Status should be VALID or LOADED
29. Upgrades
30. Understanding Spatial Install • All spatial files: $ORACLE_HOME/md/admin • Package definitions → sdoxxxx.sql • Package bodies → prvtxxxx.plb (binary) • Installation creates MDSYS • mdinst.sql, mdprivs.sql • catmd.sql catmdloc.sql for Locator
31. Understanding Spatial Install • Checking for invalid objects SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM ALL_OBJECTS WHERE OWNER='MDSYS' AND STATUS <> 'VALID' ORDER BY OBJECT_NAME; → Contact Oracle Support if any invalid
32. Understanding Spatial Install • Checking Version of Spatial SQL> SELECT SDO_VERSION FROM DUAL; → Locator returns NULL
33. Metadata for Spatial Tables • Layer • All objects in a single SDO_GEOMETRY column of a table • Metadata for a layer • coordinate system • number of dimensions, bounds, tolerance (epsilon)
34. Metadata for Spatial Tables USER_SDO_GEOM_METADATA dictionary view SQL> DESCRIBE USER_SDO_GEOM_METADATA; Name Null? Type ------------ -------- ------------------ TABLE_NAME NOT NULL VARCHAR2(32) COLUMN_NAME NOT NULL VARCHAR2(1024) DIMINFO MDSYS.SDO_DIM_ARRAY SRID NUMBER
35. Metadata for Spatial Tables USER_SDO_GEOM_METADATA dictionary view SQL> DESCRIBE USER_SDO_GEOM_METADATA; Name Null? Type ------------ -------- ------------------ TABLE_NAME NOT NULL VARCHAR2(32) COLUMN_NAME NOT NULL VARCHAR2(1024) DIMINFO MDSYS.SDO_DIM_ARRAY SRID NUMBER
36. Coordinate System • Specified by SRID attribute • Types of coordinate systems • Geodetic → lat/lon 'GEOGCS%' • Projected → UTM, Gauss-Krüger 'PROJCS%' • Local → cartesian, CAD 'LOCAL_CS%' SQL> SELECT SRID FROM MDSYS.CS_SRS WHERE WKTEXT LIKE 'PROJCS%';
37. Coordinate System • Select geodetic CRS SQL> SELECT SRID FROM MDSYS.CS_SRS WHERE WKTEXT LIKE 'GEOGCS%'; • Select projected CRS SQL> SELECT SRID FROM MDSYS.CS_SRS WHERE WKTEXT LIKE ‘PROJCS%';
38. Layer Bounds Specified by DIMINFO attribute SQL> DESCRIBE SDO_DIM_ARRAY; SDO_DIM_ARRAY VARRAY(4) OF MDSYS.SDO_DIM_ELEMENT Name Type ------------- ----------- SDO_DIMNAME VARCHAR2(64) -- e.g. ‘X’, ‘lon’, … SDO_LB NUMBER -- lower bound SDO_UB NUMBER -- upper bound SDO_TOLERANCE NUMBER -- epsilon
39. Understanding Tolerance
40. Understanding Tolerance
41. Further Reading •Pro Oracle Spatial for Oracle Database 11g R.Kothuri, A.Godfriend & Euro Beinat (2007)
42. Further Reading •Oracle Spatial User’s Guide and Reference •Oracle Application Server, MapViewer User’s Guide •Oracle Spatial Topology and Network Data Models Developer’s Guide •Oracle Spatial GeoRaster Developer’s Guide •Oracle Spatial Java API Reference
43. Further Reading •Python Geospatial Analysis Cookbook Michael Diener
44. Further Reading •Python Geospatial Development Erik Westra (2010)
45. Further Reading •QGIS Python Programming Cookbook Joel Lawhead