Oracle Spatial

Oracle Spatial 2003 10

Oracle Spatial... 3 1.0... 3 2.0 ORDBMS... 5 2.1 ORDBMS... 5 2.2... 5 2.2.1... 6 2.2.2... 6 2.2.3... 6 2.2.4... 6 2.3... 7 2.3.1... 7 2.3.2... 7 2.3.3... 8 2.3.4... 8 2.3.5... 8 3.0 Oracle Spatial... 9 3.1... 9 3.2... 12 3.3... 14 3.4... 15 4.0 Spatial 8i Spatial 9i... 17 4.1... 17 4.2... 18 4.3... 18 4.4... 19 4.5... 19 4.6 Oracle9i Release 2... 19 4.7 Oracle9i 9i Release 2... 20 5.0... 21 Oracle Spatial 2

Oracle Spatial (GIS) 2 i ii Oracle Spatial SDO_GEOMETRY SDO_GEOMETRY / Oracle9i Oracle9i Java 1.0 Web / Oracle Spatial 3

GIS 2 CAD CAM 10 1 10 1 1 1: Oracle Spatial 4

2.0 ORDBMS 2.1 ORDBMS 3 ORDBMS ORDBMS SQL (GIS) GIS SQL E-Business 2.2 4 Oracle Spatial 5

2.2.1 2 2 10 2.2.2 I-95 2.2.3 A B A B 2.2.4 GIS NASA Oracle Spatial 6

2.3 GIS 1. 2. SQL 3. 5 2.3.1 Taxicab Manhattan A B C 3 A C Manhattan A B B C 2.3.2 ADT ADT adjacent() Oracle Spatial 7

2.3.3 SQL SQL OGC SQL 2.3.4 Filter and Refine A B A B A B A B Filter and Refine 1 2 2 i ii 1 2 2 2.3.5 SQL I/O 1 2 1 B Oracle Spatial 8

2 1 B 1 3.0 Oracle Spatial Oracle Spatial Oracle Spatial SQL n Java Oracle 3.1 Oracle Spatial 3 3 2 N 2 X Y 2 2 2: Oracle Spatial 9

2 1 1 3 MDSYS.SDO_GEOMETRY MDSYS.SDO_GEOMETRY 2 3 2 3 3: SDO_GEOMETRY Oracle Spatial 10

MDSYS.SDO_GEOMETRY ROADS CREATE TABLE ROADS ( Name Classification Geometry Varchar2(64), Varchar2(64), MDSYS.SDO_GEOMETRY); SQL INSERT INSERT INTO ROADS VALUES('Short Street', 'Bylane', MDSYS.SDO_GEOMETRY(2002, 8307, null, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), MDSYS.SDO_ORDINATE_ARRAY(10, 10, 10, 15, 15, 15))); 2002 2 2 8307 8307 WGS 84 3 null 4 SDO_ORDINATES 1 2 5 {(10, 10), (10, 15), (15, 15)} 4: Oracle Spatial Oracle Spatial 11

VIEW USER_SDO_GEOM_METADATA ( TABLE_NAME VARCHAR2(32), COLUMN_NAME VARCHAR2(32), DIMINFO MDSYS.SDO_DIM_ARRAY), SRID NUMBER); SDO_DIM_ARRAY MDSYS.SDO_DIM_ELEMENT VARRAY(4) CREATE TYPE MDSYS.SDO_DIM_ELEMENT AS OBJECT ( SDO_DIMNAME VARCHAR2(32), SDO_LB NUMBER, -- Lower bound SDO_UB NUMBER, -- Upper bound SDO_TOLERANCE NUMBER); MDSYS.SDO_GEOMETRY 1 USER_SDO_GEOM_METADATA SRID ROADS.GEOMETRY INSERT INSERT INTO SDO_GEOM_METADATA VALUES ('ROADS', 'GEOMETRY', MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.05), MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.05)); USER_SDO_GEOM_METADATA DIMINFO SQL SELECT COUNT(*) FROM THE(SELECT DIMINFO FROM SDO_GEOM_METADATA WHERE TABLE_NAME = 'ROADS'); MDSYS.SDO_GEOMETRY Oracle Spatial User s Guide and Reference 3.2 A B 2 A B 2 2 9 Intersection 3 3 A B 2 9 =512 2 2 8 A B contain coveredby covers Oracle Spatial 12

disjoint equal inside overlap touch Oracle Spatial 9 Intersection 2 8 OVERLAPBDYDISJOINT SDO_RELATE SDO_GEOM.RELATE() SDO_RELATE SDO_GEOM.RELATE 2 2 SQL SELECT parks.name FROM parks, rivers WHERE mdsys.sdo_relate(parks.geometry, rivers.geometry, 'mask = OVERLAPBDYINTERSECT') = 'TRUE'; SELECT parks.name FROM parks, rivers WHERE sdo_geom.relate(parks.geometry, rivers.geometry, 'OVERLAPBDYINTERSECT') = 'OVERLAPBDYINTERSECT'; SDO_RELATE 2 SDO_GEOM.RELATE 1 Oracle Spatial SDO_FILTER(Geometry1, Geometry2, otherparams): SDO_WITHIN_DISTANCE(Geometry1, Geometry2, 'distance = NN'): 2 1 NN 2 NN 1 1 SDO_NN(Geometry1, Geometry2, num_res=nn ): 1 2 NN SDO_GEOM.SDO_BUFFER UNION INTERSECTION DIFFERENCE XOR 2 A B A B UNION C Oracle Spatial 13

Oracle Spatial 3.3 Oracle Spatial 2 Oracle Spatial R : 2 2 4 4 4 z-ordering R : Oracle Spatial R R R 3 4 R MBR 1 1 R MBR R Oracle Spatial 14

Oracle Spatial SQL Oracle9i CREATE ALTER DROP MDSYS.SDO_GEOMETRY GEOMETRY ROADS CREATE INDEX ROADS_GEOMIDX ON ROADS(GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX; ALTER INDEX ROADS_GEOMIDX REBUILD PARAMETERS ('tablespace=dom_idx'); DROP INDEX ROADS_GEOMIDX; SQL Oracle Spatial 3.4 SQL Relate SELECT a.feature FROM ROADS a,windows b WHERE MDSYS.SDO_RELATE(a.geometry, b.geometry, 'mask=overlapbdyintersect'); ROAD ROAD ROADS B B B Oracle Spatial 15

Oracle9i 3 SQL Oracle9i : Oracle Oracle : SQL SELECT FROM Oracle Spatial 16

0 1 2 SQL Oracle Spatial SPATIAL_INDEX SDO_RELATE SDO_FILTER SDO_WITHIN_DISTANCE SDO_NN SELECT Parks.Name FROM Parks, Roads WHERE MDSYS.SDO_RELATE(Parks.Geometry, Roads.Geometry, 'MASK=ANYINTERACT') = 'TRUE' AND Roads.Name = 'I-93'; 2 Roads.Name = 'I-93' SDO_RELATE(...) = 'TRUE' Oracle Spatial 4.0 Spatial 8i Spatial 9i Oracle9i 4.1 SQL SQL SUM SELECT SUM(salary), dept FROM employees GROUP BY dept; Oracle9i SQL SDO_GEOMETRY Oracle Spatial 17

MBR SELECT SDO_AGGR_MBR(c.shape) FROM cola_markets c; Oracle 9i SQL PL/SQL Oracle 9i SDO_AGGR_UNION SDO_AGGR_CENTROID SDO_AGGR_CONVEXHULL Oracle Spatial 4.2 SDO_GEOMETRY SDO_GEOMETRY SDO_GEOMETRY SDO_GEOMETRY address_type 1 1 SDO_GEOMETRY address_type 4.3 Oracle9i Spatial Oracle 9i Spatial Oracle Spatial 18

4.4 Oracle9i I/O I/O 4.5 1 2 / x/y Oracle Spatial LRS API LRS Oracle Spatial Oracle Spatial LRS API 4.6 Oracle9i Release 2 Oracle9i Release 2 R CPU GIS Oracle Spatial 19

4.7 Oracle9i Release 1 Oracle9i Release 2 Oracle9i Release 1 R 20% SDO_GEOMETRY 2 INTERACT 200% WITHIN_DISTANCE R 40% VALIDATE_GEOMETRY 200% Oracle9i Release 2 Oracle Spatial GIS Oracle9i Release 1 R 50% R LBS GIS R 2 40% WITHIN_DISTANCE 30% / Oracle Technology Network ( ) http://otn.oracle.com/products/spatial Oracle Spatial 20

5.0 Oracle Spatial Oracle Spatial SQL n Java Oracle Oracle Spatial 21

Oracle Spatial 2003 10 : Dr. Jayant Sharma : Dr. John Herring, Siva Ravada Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. : : +1.650.506.7000 : +1.650.506.7200 www.oracle.com Oracle Copyright 2003 Oracle Corporation All rights reserved.