Getting Started With Oracle Spatial PDF Free Download

2y ago

72 Views

1 Downloads

1.08 MB

51 Pages

Report/dmca

Download PDF

Transcription

Oracle Spatial 10g PlatformAnyDevicePartnerSpatial SolutionCustomerBuilt SolutionTelemetryServicesOracle & PartnerBusinessApplicationsMiddle Tier3rd Party ToolsWireless MidwareMapViewerWeather3rd Party ToolsLBS APIsWeb ServicesOracle Application Server 10gPositioningSOAP, WSDLSensorsSpatial Data ServerSpatialField Obs.Oracle10gOracle Location TechnologyOracle Core TechnologiesCopyright Oracle Corporation, 2006. All rights reserved.

What is a Spatial Database?Spatial AnalysisSpatial IndexingSpatial Data TypesFast Access toSpatial DataAll Spatial DataStored in the DatabaseSpatial Access Through SQLCopyright Oracle Corporation, 2006. All rights reserved.

Vector Map Data in Oracle TablesFisher CircleCoop Court85th St.RoadROAD IDNAMESURFACELANES1Pine Cir.Asphalt422nd St.Asphalt233rd St.Asphalt2Copyright Oracle Corporation, 2006. All rights reserved.LOCATION

The MDSYS Schemay When Oracle Locator or Spatial is installed, theMDSYS user is created–––Owner of Spatial types, packages, functions, procedures,metadataSimilar to user SYSPrivileged user- With ADMIN optiony This account is locked by default–––Be careful with this administrative accountYou should never need to log in as MDSYSNever create any data as user MDSYSCopyright Oracle Corporation, 2006. All rights reserved.

SDO GEOMETRY Objecty SDO GEOMETRY ObjectSDO GTYPESDO SRIDSDO POINTSDO ELEM INFOSDO ORDINATESNUMBERNUMBERSDO POINT TYPESDO ELEM INFO ARRAYSDO ORDINATE ARRAYy ExampleSQL CREATE TABLE states (2stateVARCHAR2(30),3totpopNUMBER(9),4geomSDO GEOMETRY);Copyright Oracle Corporation, 2006. All rights reserved.

SDO GEOMETRY Objecty SDO POINT TYPExyzNUMBERNUMBERNUMBERy SDO ELEM INFO ARRAYVARRAY (1048576) OF NUMBERy SDO ORDINATE ARRAYVARRAY (1048576) OF NUMBERCopyright Oracle Corporation, 2006. All rights reserved.

SDO GEOMETRY Objecty SDO GTYPE - Defines the type of geometrystored in the objectGTYPEExplanation1 POINT2 LINESTRING3 POLYGON4 HETEROGENEOUS COLLECTIONGeometry contains one pointGeometry contains one line stringGeometry contains one polygonGeometry is a collection ofelements of different types:points, lines, polygonsGeometry has multiple pointsGeometry has multiple line stringsGeometry has multiple polygons5 MULTIPOINT6 MULTILINESTRING7 MULTIPOLYGONCopyright Oracle Corporation, 2006. All rights reserved.

Constructing GeometriesSQL INSERT INTO LINES VALUES (2 attribute 1, . attribute n,3 SDO GEOMETRY (4 2002, null, null,5 SDO ELEM INFO ARRAY (1,2,1),6 SDO ORDINATE ARRAY (7 10,10, 20,25, 30,10, 40,10))8 );(20,25)(10,10)(30,10)(40,10)Copyright Oracle Corporation, 2006. All rights reserved.

Spatial Metadatay The spatial routines require you to populatea view that contains metadata aboutSDO GEOMETRY columnsy The metadata view is created for all OracleSpatial users when Oracle Spatial is installedy The metadata view is calledUSER SDO GEOM METADATAy For every SDO GEOMETRY column, insert arow in the USER SDO GEOM METADATAviewCopyright Oracle Corporation, 2006. All rights reserved.

USER SDO GEOM METADATASQL DESCRIBE USER SDO GEOM METADATANameNull?Type-------------- ---------- -----------------TABLE NAMENOT NULLVARCHAR2(32)COLUMN NAMENOT NULLVARCHAR2(1024)DIMINFOSDO DIM ARRAYSRIDNUMBERy MDSYS.SDO DIM ARRAYVARRAY(4) OF SDO DIM ELEMENTy MDSYS.SDO DIM ELEMENT objectSDO DIMNAMEVARCHAR2(64)SDO LBNUMBERSDO UBNUMBERSDO TOLERANCENUMBERCopyright Oracle Corporation, 2006. All rights reserved.

Populating theUSER SDO GEOM METADATAViewSQL INSERT INTO USER SDO GEOM METADATA2 (TABLE NAME, COLUMN NAME, DIMINFO, SRID)3 VALUES (4 'ROADS',5 'GEOMETRY',6 SDO DIM ARRAY (7 SDO DIM ELEMENT('Long', -180, 180, 0.5),8 SDO DIM ELEMENT('Lat',-90, 90, 0.5)),9 8307);Note: For geodetic data, the x axis bounds must be –180 to 180, and yaxis bounds –90 to 90.Copyright Oracle Corporation, 2006. All rights reserved.

Loading Spatial Datay Categories of loading:–Bulk loading of datay SQL*Loadery Import–Transactional insertsy INSERT statement–Loading using Partner Toolsy Example – SAFE Software’s FMECopyright Oracle Corporation, 2006. All rights reserved.

Validating Geometriesy Oracle Spatial validation routines ensure spatialdata in Oracle Spatial is valid––SDO GEOM.VALIDATE GEOMETRY WITH CONTEXT- Determines if a geometry is validSDO GEOM.VALIDATE LAYER WITH CONTEXT- Determines if all geometries in a layer are validy If data is invalid, both routines return why andwhere the geometry is invalidCopyright Oracle Corporation, 2006. All rights reserved.

Spatial Indexingy Used to optimize spatial query performancey R-tree Indexing––Based on minimum bounding rectangles (MBRs) for 2Ddata or minimum bounding volumes (MBVs) for 3DdataIndexes two, three, or four dimensionsy Provides an exclusive and exhaustive coverage ofspatial objectsy Indexes all elements within a geometry includingpoints, lines, and polygonsCopyright Oracle Corporation, 2006. All rights reserved.

A Look at R-tree IndexStructurescreate index GEOD STATES SIDXon GEOD STATES (GEOM)indextype is MDSYS.SPATIAL INDEX;Index InformationTable MDRT 7B50 Copyright Oracle Corporation, 2006. All rights reserved.

Spatial Operatorsy Full range of spatial operators–Implemented as functional extensions in SQLTopological ointCovered ByOverlap BoundaryINSIDEDistance Operatorsy Within Distancey Nearest NeighborHospital #2X DistancetreetFirst SHospital #1Main Street–Copyright Oracle Corporation, 2006. All rights reserved.

Spatial Operatorsy Operators––––SDO FILTERy Performs a primary filter onlySDO RELATE and SDO relationship y Performs a primary and secondary filterSDO WITHIN DISTANCEy Generates a buffer around a geometry andperforms a primary and optionally a secondaryfilterSDO NNy Returns nearest neighborsCopyright Oracle Corporation, 2006. All rights reserved.

SDO FILTER Example––Find all the cities in a selected rectangular areaResult is approximateSELECT c.city, c.pop90FROM proj cities cWHERE sdo filter (c.location,sdo geometry (2003, 32775, null,sdo elem info array (1,1003,3),sdo ordinate array (1720300,1805461,1831559, 2207250))) 'TRUE';Hint 1: All Spatial operators return TRUE or FALSE. When writing spatialqueries always test with 'TRUE', never 'FALSE' or 'true'.Copyright Oracle Corporation, 2006. All rights reserved.

SDO RELATE Exampley Find all counties in the state of New HampshireSELECT c.county, c.state abrvFROM geod counties c,geod states sWHERE s.state 'New Hampshire'AND sdo relate (c.geom,s.geom,'mask INSIDE COVEREDBY') 'TRUE';Note: For optimal performance, don’t forget to indexGEOD STATES(state)Copyright Oracle Corporation, 2006. All rights reserved.

Relationship OperatorsExampley Find all the counties around Passaic county in NewJersey:SELECT /* ordered */ a.countyFROM geod counties b,geod counties aWHERE b.county 'Passaic'AND b.state 'New Jersey'AND SDO TOUCH(a.geom,b.geom) 'TRUE';y Previously:. . .AND SDO RELATE(a.geom,b.geom,'MASK TOUCH') 'TRUE';Copyright Oracle Corporation, 2006. All rights reserved.

SDO WITHIN DISTANCEExamplesy Find all cities within a distance from an interstateSELECT /* ordered */ c.cityFROM geod interstates i, geod cities cWHERE i.highway 'I170'AND sdo within distance (c.location, i.geom,'distance 15 unit mile') 'TRUE';y Find interstates within a distance from a citySELECT /* ordered */ i.highwayFROM geod cities c, geod interstates iWHERE c.city 'Tampa'AND sdo within distance (i.geom, c.location,'distance 15 unit mile') 'TRUE';Copyright Oracle Corporation, 2006. All rights reserved.

SDO NN Exampley Find the five cities nearest to Interstate I170,ordered by distanceSELECT /* ordered */c.city, c.state abrv,sdo nn distance (1) distance in milesFROM geod interstates i,geod cities cWHERE i.highway 'I170'AND sdo nn(c.location, i.geom,'sdo num res 5 unit mile', 1) 'TRUE'ORDER by distance in miles;yNote: Make sure you have an index on GEOD INTERSTATES(HIGHWAY).Copyright Oracle Corporation, 2006. All rights reserved.

Oracle Spatial 10g PlatformAnyDevicePartnerSpatial SolutionCustomerSpatialSolutionBuilt SolutionTelemetryServicesOracle & PartnerBusinessApplicationsMiddle Tier3rd Party ToolsWireless MidwareMapViewerWeather3rd Party ToolsLBS APIsWeb ServicesOracle Application Server 10gPositioningSOAP, WSDLSensorsSpatial Data ServerSpatialField Obs.Oracle10gOracle Location TechnologyOracle Core TechnologiesCopyright Oracle Corporation, 2006. All rights reserved.

MapViewer OverviewTitley A map rendering service in OracleApplication Server 10g. It is aserver component (not a clientviewer!)y It visualizes data managed byOracle Spatial.y Provides a comprehensive set ofAPIs( XML and Java-based),using which client viewers can beeasily developed and OGC WMSAPIsy Provides an enterprise-levelsolution to mapping metadatamanagement.Copyright Oracle Corporation, 2006. All rights reserved.Earthquakes

MapViewer QueryMapping ClientA map request consistsof: Base map name Center of map Width and height of map Optional tags– map name– jdbc query– othersA map response consistsof: A streamed map imageor A URL to the map imagealong with the map MBRMapViewerOracle Spatial/LocatorCopyright Oracle Corporation, 2006. All rights reserved.

MapViewer APIs MapViewer supports 3 API flavors–––XML-basedy Native language to MapViewerJava thin libraryy a mapping “bean” (without UI)JSP custom tagsy a subset of functionsy To be used as a ‘fast start’ for beginnersy The JSP taglib can be easily added to Oracle JDeveloper’scomponent palettey A JDeveloper extension that lets you browse the current list ofexisting maps/themes/styles in a data sourceCopyright Oracle Corporation, 2006. All rights reserved.

MapViewer Welcome Pagehttp://localhost:8888/mapviewer––Icon to go to/from the Admin page (see key icon in upper left)Several other hyperlinks, including DemosCopyright Oracle Corporation, 2006. All rights reserved.

Copyright Oracle Corporation, 2006. All rights reserved. SDO_GEOMETRYObject ySDO_GTYPE - Defines the type of geometry stored in the object GTYPE Explanation 1 POINT Geometry contains one point 2 LINESTRING Geometry contains one line string 3 POLYGON Geometry contains one polygon 4 HETEROGENEOUS COLLECTION Geometry is a collection ofFile Size: 1MBPage Count: 51