Tweet
Tweet

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

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“

Slide 3

Slide 3 text

Agenda •Spatial Information Management •Overview of Oracle Spatial •Further Reading

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

Spatial Information Management

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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)

Slide 8

Slide 8 text

Storage of Spatial Data •Database supports a geometry type • points, lines, polygons • networks, • raster images • 3D/4D •Create, Update, Delete (CRUD)

Slide 9

Slide 9 text

Analyzing Spatial Data •Within-distance •Contains •Nearest-Neighbor •Distance •Buffer •Visualization (Mapping & Overlay)

Slide 10

Slide 10 text

Example

Slide 11

Slide 11 text

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, ...

Slide 12

Slide 12 text

Overview of Oracle Spatial

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

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.

Slide 15

Slide 15 text

Location Enabling Create tables SQL> CREATE TABLE restaurants ( id NUMBER, poi_name VARCHAR2(32), location SDO_GEOMETRY -- New column to store locations );

Slide 16

Slide 16 text

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 ) );

Slide 17

Slide 17 text

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 ;

Slide 18

Slide 18 text

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;

Slide 19

Slide 19 text

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;

Slide 20

Slide 20 text

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' ;

Slide 21

Slide 21 text

Visualizing Spatial Data MapViewer & Oracle Maps → Need to specify Maps, Themes & Styles in USER_SDO_MAPS, USER_SDO_THEMES, and USER_SDO_STYLES

Slide 22

Slide 22 text

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“

Slide 23

Slide 23 text

Oracle Spatial Licensing

Slide 24

Slide 24 text

Locator • Data model (SDO_GEOMETRY, 2D only) • Query & Analysis (Index Engine only) • spatial indexes & operators such as SDO_NN •

Slide 25

Slide 25 text

No content

Slide 26

Slide 26 text

Spatial Option • Data model (+3D → buildings, CAD) • Query & Analysis (Index & Geometry Engine) • length, area, volume • Geocoder • Advanced Spatial Engine • routing and network analysis

Slide 27

Slide 27 text

No content

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

Upgrades

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Understanding Spatial Install • Checking Version of Spatial SQL> SELECT SDO_VERSION FROM DUAL; → Locator returns NULL

Slide 33

Slide 33 text

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)

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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%';

Slide 37

Slide 37 text

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%';

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

Understanding Tolerance

Slide 40

Slide 40 text

Understanding Tolerance

Slide 41

Slide 41 text

Further Reading •Pro Oracle Spatial for Oracle Database 11g R.Kothuri, A.Godfriend & Euro Beinat (2007)

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

Further Reading •Python Geospatial Analysis Cookbook Michael Diener

Slide 44

Slide 44 text

Further Reading •Python Geospatial Development Erik Westra (2010)

Slide 45

Slide 45 text

Further Reading •QGIS Python Programming Cookbook Joel Lawhead

Slide 46

Slide 46 text

No content