Oracle Spatial

Size: px

Start display at page:

Download "Oracle Spatial"

あつのむこやま
5 years ago
Views:

1 Oracle Spatial

2 Oracle Spatial ORDBMS ORDBMS Oracle Spatial Spatial 8i Spatial 9i Oracle9i Release Oracle9i 9i Release Oracle Spatial 2

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

4 GIS 2 CAD CAM : Oracle Spatial 4

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

6 I A B A B GIS NASA Oracle Spatial 6

7 2.3 GIS SQL Taxicab Manhattan A B C 3 A C Manhattan A B B C ADT ADT adjacent() Oracle Spatial 7

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

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

10 MDSYS.SDO_GEOMETRY MDSYS.SDO_GEOMETRY : SDO_GEOMETRY Oracle Spatial 10

11 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))); WGS 84 3 null 4 SDO_ORDINATES {(10, 10), (10, 15), (15, 15)} 4: Oracle Spatial Oracle Spatial 11

12 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 Intersection 3 3 A B 2 9 = A B contain coveredby covers Oracle Spatial 12

13 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

14 Oracle Spatial 3.3 Oracle Spatial 2 Oracle Spatial R : 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.

15 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

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

17 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

18 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

19 4.4 Oracle9i I/O I/O / 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

20 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 ( ) Oracle Spatial 20

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

22 Oracle Spatial : Dr. Jayant Sharma : Dr. John Herring, Siva Ravada Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A. : : : Oracle Copyright 2003 Oracle Corporation All rights reserved.

Oracle Locator Oracle Database 10g Standard Edition Standard Edition One Oracle Database 10g Release 1 Oracle Locator : Enterprise Edition Oracle Loca

ORACLE SPATIAL OPTION ORACLE LOCATOR Oracle Database 10g Oracle Database 10g Release 2 Oracle Locator : Oracle Spatial Oracle Locator Oracle Locator Oracle Database 10g Standard Edition EPSG Standard Edition