Transcription
Preparing for the Oracle Spatial Essentials ExamSession OverviewOVERVIEW The Oracle “Spatial Essentials Certification” measures skillsin a number of technical areas Organizations interested in obtaining OraclePartnerNetwork “Oracle Spatial 11g Specialized” statusmust have at least one individual who holds this certificationCHALLENGES / OPPORTUNITIES The exam covers a wide range of topics—many of whichusers might not have had experience with Preparation can appear difficultSOLUTIONS Learn about how the exam is structured and what types ofquestions are included Review the topics included and the general level of detailcovered for each See demonstrations and use cases on the specializedconcepts and topics on the exam Learn about classroom courses and online resourcesExcerpt of topic list from Oracle University siteRESULTS Gain a compact, comprehensive overview of all keyOracle Spatial technical capabilities, even if you don’tplan to register for the exam Learn how to prepare for the certification examefficiently and effectively Prepare yourself to pass the exam and become arecognized expert in Oracle Spatial implementations1
May 2014Oracle Spatial Summit2
May 21, 2014Walter E. Washington Convention CenterWashington, DC USA
Olivier DuboisCEO, OSCARSAlbert GodfrindSpatial Solutions Architect, OracleSteve PierceCEO, Think Huddle
Preparing for theOracle SpatialEssentials Exam:Topic Review and Strategies
OverviewCertification vs Specialization Individual Certification“The Oracle Spatial 11g Certified Implementation Specialist certification is designed for individuals who possess astrong technical background and exposure to Oracle Spatial 11g implementation. This certification exam coverstopics such as: Oracle Spatial Concepts and Data Structures; Loading and Validating Spatial Data; Indexing SpatialData; Performing Spatial Queries; Performing Spatial Processing; Oracle Fusion Middleware MapViewer; Geocoding,Routing, Spatial Analysis and Mining; Web Services; Advanced Indexing (Partitioning, Parallelism, Function-BasedIndexes); Using Linear Referencing and Network and Topology Models; GeoRaster; Managing 3D and Lidar Data;Performance and Tuning / Exadata; and Workspace Manager. This certification differentiates candidates in themarketplace by providing a competitive edge through proven expertise. Up-to-date training and field experience arerecommended.This certification is available to all candidates but is geared toward members of the Oracle PartnerNetwork. OPNmembers earning this certification will be recognized as OPN Certified Specialists. This certification qualifies ascompetency criteria for the Oracle Spatial 11g Specialization.”
OverviewCertification vs Specialization Company Specialization“The Oracle Spatial 11g Specialization recognizes partner organizations that are proficient in selling,implementing and/or developing Oracle Spatial 11g solutions. Topics covered in this Specialization include: OracleSpatial Concepts and Data Structures; Loading and Validating Spatial Data; Indexing Spatial Data; Performing SpatialQueries; Performing Spatial Processing; Oracle Fusion Middleware MapViewer; Geocoding, Routing, Spatial Analysisand Mining; Web Services; Advanced Indexing (Partitioning, Parallelism, Function-Based Indexes); Using LinearReferencing and Network and Topology Models; GeoRaster; Managing 3D and Lidar Data; Performance and Tuning /Exadata; and Workspace Manager.”7
OverviewHow to Get Certified 2-Step Certification Process– Preparation Oracle Spatial: Essentials Oracle Spatial: Advanced Experience– Exam Oracle Spatial 11g Essentials 1Z0-5958
OverviewWhere To Begin with Certification? There are a number ofchallenges when preparing forthe exam:– The exam covers dozens oftopics– Many of those topics aren’tencountered frequently by spatialusers– Varying levels of detail arecovered for each topicExcerpt of topic list from Oracle University site9
OverviewIncluded in this Presentation Overview of exam topics For each topic:– Subtopics covered– Examples of the type of knowledge to be tested, to include: Demonstrations Sample questions Strategies for preparing and taking the exam
Section 1:Exam Topics
OverviewExam Topics1.2.3.4.Oracle Spatial Concepts and Data StructuresLoading and Validating Spatial DataIndexing Spatial DataPerforming Spatial Queries5. Performing Spatial Processing6. Oracle Fusion Middleware MapViewer7. Geocoding, Routing, Spatial Analysis and Mining12
OverviewExam Topics (continued)8. Web Services9. Advanced Indexing10. Using Linear Referencing / Network & Topology Models11. GeoRaster12. Managing 3D and Lidar Data13. Performance and Tuning / Exadata14. Workspace Manager13
Spatial Concepts & Data StructuresTopic 1 What you need to know:– Describe the data types, data models, coordinate systems,indexing structure, query model, and types of spatial queries– Describe the Oracle Spatial schema and how spatial data isrepresented in the database– Explain spatial metadata structures and views– Explain coordinate systems and unit support, including Oracledata structures14
Geometric Primitive TypesPointPolygonOptimizedpolygonsLine stringPolygon withone or more holesArc linestringArcpolygonSelf-crossingline stringsvalidCompoundline stringCompoundpolygonSelf-crossingpolygons not valid15
Spatial Data ModelSpatial layerGeometry GeometryGeometry ElementElementPointLine stringElementPolygon16
Spatial Metadata & CoordinateSystems The spatial routines require you to populate a view that containsmetadata about the SDO GEOMETRY columns. For every SDO GEOMETRY column, insert a row in theUSER SDO GEOM METADATA view. A coordinate system (CS) is a means of assigning coordinates to alocation.– It establishes relationships between sets of coordinates. All spatial data has an associated coordinate system.17
Spatial Query ModelAnatomy of a spatial exIndex retrievesarea of onsExactResultSpatial function determinesexact result18
Loading and Validating Spatial DataTopic 2 What you need to know:– Load spatial data from text files using SQL Loader and external tables– Load spatial data from GIS files using Oracle Map Builder, Oracleshapefile loader and GDAL/OGR– Perform transactional inserts– Describe data validation and correction routines in Oracle Spatial– Use export and import utilities to move spatial data between databases– Move spatial data between databases by using transportabletablespaces19
SQL*Loader sampleLOAD DATACONTINUEIF NEXT(1:1) '#'INTO TABLE us countiesFIELDS TERMINATED BY ';' (county,state,poppsqmi,geom COLUMN OBJECT (sdo gtypeINTEGER EXTERNAL,sdo sridCONSTANT 8307,sdo elem info VARRAY TERMINATED BY '/' (e FLOAT EXTERNAL),sdo ordinates VARRAY TERMINATED BY '/' (o FLOAT EXTERNAL) 60429000;31.297289000;-87.759232000;./20
Using GDAL To setup– Add \gdal xxxx \bin to your path– Set GDAL DATA to \gdal xxxx \data (optional) Main commands for vector data– ogrinfo get information about a file (or spatial table)– ogr2ogr copy from one format to another (import/export) Documentation– http://www.gdal.org/ogr/– http://www.gdal.org/ogr/ogr formats.html– http://www.gdal.org/ogr/drv oci.html21
Using GDALogr2ogr -f OCI OCI:scott/tiger@orcl112 world countries.shp-lco DIM 2 -lco SRID 8307 -lco GEOMETRY NAME geometry -lco INDEX NO-f OCIUse Oracle as outputOCI:scott/tiger@orcl112Database connectionworld countries.shpInput shape file-lco DIM 2Dimension (2D)-lco SRID 8307SRID-lco GEOMETRY NAME geometryName of geometry column-lco INDEX NODo not create a spatial index22
Validating Geometries Oracle Spatial validation routines ensure that spatialdata in Oracle Spatial is valid.– SDO GEOM.VALIDATE GEOMETRY WITH CONTEXT Determines whether a single geometry is valid– SDO GEOM.VALIDATE LAYER WITH CONTEXT Determines whether all geometries in a layer are valid If data is invalid, both routines return why and wherethe geometry is invalid.23
Indexing Spatial DataTopic 3 What you need to know:– Explain R-tree indexing concepts, how R-tree indexes are built,and index structures– Create indexes, set index parameters, and create indexes inparallel– Describe index metadata and find index size24
R-tree Indexing R-tree indexing is used to index spatial data.– Requires almost no configuration– Indexes two or three dimensions Primary filter (index only query) can operate on two or three dimensions Secondary filters are two dimensional or three dimensional Each index entry approximates geometry using Minimum BoundingRectangle (MBR) for 2-D and Minimum Bounding Volume (MBV) for 3D. MBRs and MBVs are indexed internally using a tree structure.25
R-tree Indexing ConceptR-treeindexMBRGeometryLeaf nodes of R-tree store MBR, geometry pointer 26
Building the R-TreeR-treeArootRBSCRADSBCDrootMBRs, pointers to geometries– Fanout is the number of branches that comes out of eachnode.– Oracle Spatial R-tree has the same fanout for all nodes.27
A Look at R-tree IndexStructuresCREATE INDEX us states sxON us states (geom)INDEXTYPE IS mdsys.spatial index;Index information Index tables are “opaque” structures Do not change them in any wayTable MDRT 7B50 28
CREATE INDEXCREATE INDEX index-name ON table-name ( column-name )INDEXTYPE IS MDSYS.SPATIAL INDEX[PARAMETERS (' parameter value parameter value ') ][PARALLEL [ parallel degree ]]; Parameters–––––LAYER GTYPEPARALLELSDO INDX DIMSSDO RTR PCTFREE 29
Parallel Index Creation PARALLEL [ parallel degree ]– This parameter breaks the work of index creation into smaller piecesthat can be performed in parallel.– parallel degree is an optional parameter that specifies the degree ofparallelism.– If the degree of parallelism is not specified, Oracle chooses a defaultbased on the number of CPUs.CREATE INDEX us counties sxON us counties(geom)INDEXTYPE IS MDSYS.SPATIAL INDEXPARALLEL 4;30
Spatial Index Dictionary Views USER SDO INDEX INFO– Summary information USER SDO INDEX METADATA– Detailed information Also “ALL” variants31
USER SDO INDEX INFOINDEX NAMETABLE NAMECOLUMN NAMESDO INDEX TYPESDO INDEX 2(32)VARCHAR2(32) Useful to relate a spatial index to its physical MDRTtable Allows you to find out the size of the index32
USER SDO INDEX METADATASDO INDEX OWNERSDO INDEX TYPESDO INDEX NAMESDO INDEX TABLESDO INDEX PRIMARYSDO INDEX PARTITIONSDO PARTITIONEDSDO TSNAMESDO COLUMN NAMESDO INDEX DIMSSDO RTREE HEIGHTSDO RTREE NUMNODESSDO RTREE DIMENSIONALITYSDO RTREE FANOUTSDO RTREE ROOTSDO RTREE SEQ NAMESDO RTREE PCTFREESDO INDEX STATUSSDO LAYER )VARCHAR2(32)NUMBERVARCHAR2(32)VARCHAR2(32)SDO LEVELSDO NUMTILESSDO MAXLEVELSDO COMMIT INTERVALSDO FIXED METASDO TABLESPACESDO INITIAL EXTENTSDO NEXT EXTENTSDO PCTINCREASESDO MIN EXTENTSSDO MAX EXTENTSSDO RTREE QUALITYSDO INDEX VERSIONSDO INDEX GEODETICSDO NL INDEX TABLESDO DML BATCH SIZESDO RTREE ENT XPNDSDO ROOT )NUMBERVARCHAR2(8)VARCHAR2(32)NUMBERNUMBERSDO GEOMETRY33
Find Index Sizeselect i.table name, i.index name, si.column name,si.sdo index table, s.bytesfromuser indexes i,user sdo index info si,user segments swhere i.index type 'DOMAIN'andi.ityp name 'SPATIAL INDEX'andi.index name si.index nameands.segment name si.sdo index tableorder by i.table name, i.index name;TABLE NAME---------------US CITIESUS COUNTIESUS INTERSTATESUS PARKSUS RIVERSUS STATESWORLD CONTINENTSWORLD COUNTRIESINDEX NAME-------------------US CITIES SXUS COUNTIES SXUS INTERSTATES SXUS PARKS SXUS RIVERS SXUS STATES SXWORLD CONTINENTS SXWORLD COUNTRIES SXCOLUMN N-------LOCATIONGEOMGEOMGEOMGEOMGEOMGEOMGEOMSDO INDEX TABYTES------------ ---------MDRT 1ABA5 65536MDRT 1ABAE 327680MDRT 1ABC1 65536MDRT 1ABCA 655360MDRT 1ABD4 65536MDRT 1ABB8 65536MDRT 1ABDD 65536MDRT 1ABE6 6553634
Demo35
Performing Spatial QueriesTopic 4 What you need to know:– Explain the differences between spatial operators and spatialfunctions– Write queries to determine spatial relationships and returngeometries using SDO RELATE and SDO FILTER operators– Write queries returning results within a specific distance andnearest neighbors– Write queries finding correlations between two spatial layers– Write queries combining spatial and non-spatial criteria36
Spatial Operators vs. Functions Spatial operators:––––Require a spatial index on the first geometry specified in the operatorTake advantage of spatial indexesAppear only in the WHERE clauseImplicitly transform the coordinate system of the window, if required Spatial functions:––––Do not take advantage of spatial indexes !Can be used on small tables that are not spatially indexedCan be used in the SELECT list and the WHERE clauseBoth geometries must exist in the same coordinate system.37
SDO RELATE exampleWhich parks fully inside the state of Wyoming ?SELECT p.id, p.nameFROM us parks p, us states sWHERE s.state 'Wyoming'AND SDO INSIDE (p.geom, s.geom) 'TRUE';38
SDO WITHIN DISTANCE exampleHow many customers are within 10 km of a sales region ?SELECTFROMWHEREANDcount(*)sales regions r, customers cr.region id 'R1'sdo within distance (c.location, r.geom,'distance 10 unit km') 'TRUE';SELECTFROMWHEREANDcount(*)sales regions r, customers cr.region id 'R1'sdo within distance (c.location, r.geom,'distance 10 unit km') 'TRUE‘AND SDO GEOM.SDO DISTANCE (c.location, r.geom, 0.5) 0;39
SDO JOIN exampleWhich cities are within 10 miles of an interstate ?SELECT c.city, i.interstateFROM us cities c,us interstates i,TABLE(SDO JOIN('US CITIES', 'LOCATION','US INTERSTATES', 'GEOM','DISTANCE 10 UNIT MILE')) jWHERE j.rowid1 c.rowidAND j.rowid2 i.rowidORDER BY c.city;40
Performing Spatial ProcessingTopic 5 What you need to know:–––––––––Perform area, length, and distance calculations using spatial functionsCreate buffers, combine geometries, and derive geometriesUse spatial aggregate functionsDescribe the functions for arc densification/coordinate transformationUse spatial utility functionsDescribe the functions for conversion from/to OGC formatsGenerate GML documents from spatial objectsModify geometries using PL/SQLUse the Java API to work with geometries41
The SDO AREA Function geometry – SDO GEOMETRY that defines a polygon Can be a variable or table column tolerance – Number used as the tolerance unit – A quoted string with the units for the result Returned value– A number that is the area of the input polygonarea : SDO GEOM.SDO AREA( geometry , tolerance [, unit ])42
The SDO LENGTH Function geometry : SDO GEOMETRY that defines a polygonor line– Can be a variable or table column tolerance : Number used as the tolerance unit : Quoted string with the units for the result returned value : Numeric lengthlength : SDO GEOM.SDO LENGTH( geometry , tolerance [, unit ])43
The SDO DISTANCE Function geometry-1 geometry-2 : SDO GEOMETRYobjects– Can be variables or table columns tolerance : Numeric tolerance for the function unit : Quoted string with the units for the result returned value : A number (the minimum distancebetween the geometries)distance : SDO GEOM.SDO DISTANCE( geometry-1 , geometry-2 , tolerance [, unit ])44
SDO BUFFER FunctionSDO GEOM.SDO BUFFER Generates a buffer polygon around a geometry Takes an SDO GEOMETRY object as input– Any kind (point, line, polygon, compound)– Can buffer geodetic geometries Returns an SDO GEOMETRY object containing thebuffer (polygon)45
Buffer Examples Simple geometries Collection geometries46
The SDO BUFFER Function geometry :SDO GEOMETRY object to bufferDEMO– Can be a variable or table column distance : The buffer distance tolerance : A number used as the toleranceSDO GEOMETRY : SDO GEOM.SDO BUFFER( geometry , distance , tolerance [, ' params '])SELECT SDO GEOM.SDO BUFFER(location, 100, .05, 'unit km') FROM cities47
Geometric Intersection SDO GEOM.SDO UNION SDO GEOM.SDO INTERSECTION SDO GEOM.SDO DIFFERENCE SDO GEOM.SDO XOR48
SDO AGGR UNION Example UNION all the county boundaries for the states of NewYork and New Jersey, and generate a geometry foreach state:SELECT sdo aggr union(sdoaggrtype(geom, 0.5)),stateFROM us countiesWHERE state abrv in ('NY', 'NJ')GROUP by state; Note: SDO AGGR SET UNION49
Coordinate Transformation Functions SDO CS.TRANSFORM: Transforms a geometry fromone coordinate system to another SDO CS.TRANSFORM LAYER: Transforms a layerfrom one coordinate system to another50
The SDO CS.TRANSFORM Function geom – Geometry of type SDO GEOMETRY– Can be a variable or a table column to srid – Spatial reference system ID to transform to Return value– Geometry of type SDO GEOMETRYSDO GEOMETRY : SDO CS.TRANSFORM ( geom , to srid )51
SDO CS.TRANSFORM: Example Single geometry transformed, for example,Hillsborough County in New Hampshire:DEMOSELECT sdo cs.transform (geom, 82151)FROM us counties pWHERE county 'Hillsborough'AND state 'New Hampshire'; Note: All transformations require valid SDO SRID fieldset in source geometry. Note: 82151 “New Hampshire 2800 (1983, meters)” State Plane CS 198352
Package SDO UTIL simplify() getNumElem circle polygon() getNumVertices ellipse polygon() convert unit polygonToLine() point at bearing remove duplicate vertices AffineTransforms append() concat lines reverseLinestring()53
OGC Methods GET WKT– Returns Well Known Text format of SDO GEOMETRY GET WKB– Returns Well Known Binary format of SDO GEOMETRY Extract Geometries to WKTSELECT c.geom.get wkt()FROM us counties cWHERE county 'Denver';POLYGON ((-105.052597 39.791199, -105.064606 39.789928, . -105.024757 39.790947,-105.052597 39.791199),(-104.933578 39.698139, -104.936104 39.698299, . -104.9338 39.696701, -104.933578 39.698139))54
Exporting Geometries to GMLSELECT sdo util.to gmlgeometry(location)FROM us citiesWHERE state abrv 'CO'; gml:Point srsName "SDO:8307“xmlns:gml "http://www.opengis.net/gml" gml:coordinates decimal "." cs "," ts " " -104.872655,39.768035 /gml:coordinates /gml:Point 55
Oracle Fusion Middleware MapViewerTopic 6 What you need to know:– Explain the use of Oracle FusionMiddleware MapViewer– Describe the architecture ofMapViewer– Install, configure, and administerMapViewer using OC4J andWebLogic Server– Define maps using styles,themes, and maps*Resource: MapViewer Primer– Define and manage tile caches– Build applications using theOracle Maps tutorial– Integrate external data– Define and access OGC WebMapping Service and WebFeature Services56
Oracle Application Server MapViewer A standard feature of all versions of Fusion Middleware– Oracle Application Server– Weblogic Server XML, Java and JSP APIs Map definition tool Map definitions and symbology stored in the database Thematic mapping Produces PNG, GIF, JPEG, SVG AJAX API for rich user interfaces High performance map cache Integration with Business Intelligence tools57
MapViewer "AJAX" Architecture:Oracle MapsApplicationClient(browser)JavaScript Map APIHTTPApplicationServerTileCacheTile Cache ServerFOI ServerMap Rendering ilder58
Map Definitions Style: defines how shapes should be rendered– Areas: color, transparency, boundary thickness and color, – Lines: color, thickness, center lines, wing lines, hash marks, – Points: symbols, graphics, StylesThemes– Texts: font, styling, color, size, halo, Theme: associates a spatial table and a style– Graphic style and text style (for labels) Map: groups a number of themes– Visibility of themes based on scaleSpatialTablesMapsTile Layer Map Cache: makes a base map available to webapplications59
Updating Map Definitions Definitions are stored in the database– USER SDO STYLES– USER SDO THEMES– USER SDO MAPS– USER SDO CACHED MAPS Updated using MapBuilderMapsThemesStylesSpatialTables60
USER SDO STYLES Style information stored in the database– Text, colors, line styles, area and fill information, markers– Advanced styles for thematic mapping based on a column value Includes XML definition of each style Can add styles easily using MapbuilderSQL describe user sdo stylesNameType---------------------- METRYMDSYS.SDO GEOMETRY61
USER SDO THEMES Stores user-defined themes User specifies:––––Theme name and descriptionTable nameGeometry column nameStyling rules in XML based on values in USER SDO STYLESSQL describe user sdo themesNameType------------------ 4000)BASE TABLEVARCHAR2(32)GEOMETRY COLUMNVARCHAR2(2048)STYLING RULESCLOB62
What Are Themes? A theme applies one or more styles to a geometry column– Feature drawing information (color, fill, marker, and so on)– Text and label font, color, sizeLancaster Thematic mapping is supported by associating an advanced style to atheme. Themes can be based on any kind of spatial data:– Vector (SDO GEOMETRY)– Raster (SDO GEORASTER)– Network, topology , – WMS or WFS servers Possible to preview the results 63
USER SDO MAPS A map is a collection of themes User specifies a map name and (optionally) adescription User specifies an XML definition:– Theme names to display– Zoom scale information to specify when themes displaySQL describe user sdo mapsNameType------------------- AR2(4000)DEFINITIONCLOB64
What Are Maps? A map groups one or more themes Can specify “min scale” and/or “max scale” values tocontrol the visibility of each theme depending on mapscale. Can also control label visibility Use ratio-scales (not “mapviewer native”) Can control the order in which themes are rendered A theme can be used in many different maps65
Updating Definitions Changes are only applied to the database tables– USER SDO STYLES, THEMES and MAPS Existing applications will not see the changes– Definitions are cached in memory– Clear the cache: Map Tile Caches are also invalid now!– Rebuild the cache!66
Integrating External Data SourcesOracle eLocationApplicationGoogle MapsMicrosoft Bing MapsJavaScript Map APICustom Tile ServersMap CacheHTTPMap Cache ServerFOI ServerMap Rendering der67
Integrating External Data Sources From the browser– Use an eLocation background (« Oracle Maps ») Only for Oracle tools and applications!– Use a Google Maps or Microsoft Bing Maps background– Access any other tile server Custom map tile layer From the server– Use WMS or WFS servers– Data will be cached in the map cache68
Using an Elocation Background Saves you all the effort of buying, loading andmaintaining your own spatial data for the base maps. Also saves you the effort of designing a base map fromscratch. Free for use by applications that use Oracle. Check out terms and conditions mapview.addMapTileLayer(new MVMapTileLayer("elocation mercator.world map",elocation.oracle.com/mapviewer/mcserver") );"http://69
Using a Google or Bing MapsBackground Saves you all the effort of buying, loading andmaintaining your own spatial data for the base maps. Also saves you the effort of designing a base map fromscratch. Google or Bing APIs automatically loaded Free for private use, not for commercial use!– Check out access restrictionsvar basemap new MVBingTileLayer({key: your bing maps key ) ;mapview.addMapTileLayer(basemap);70
WMS ThemesChoose layersChoose coordinatesystem71
Oracle Maps: the Javascript APILoad the Oracle Maps clientlibrary html head script language "Javascript" src "/mapviewer/fsmc/jslib/oraclemaps.js" /script script language JavaScript var baseURL "http://" document.location.host "/mapviewer";function display map() {var mapview new MVMapView (document.getElementById(« MAP DIV"), baseURL);mapview. addMapTileLayer(new MVMapTileLayer ("mvdemo.demo map"));mapview.setCenter(-122.45, 37.75);Initialize andconfigure the nel("east");var foiLayer new MVThemeBasedFOI ('customers', 'mvdemo.customers') ;mapview.addThemeBasedFOI (foiLayer); Launch themapmapview.display();} /script Add a dynamicthemeAutomaticallyproduce the mapwhen the HTML pageis loaded /head body onload display map() div id “MAP DIV" style "left:10; top:60;width: 600px; height: 500px" /div /body /html 72
Geocoding, Routing, Spatial Analysisand MiningTopic 7 What you need to know:– Describe geocoding concepts, process, and functionality providedin Oracle Spatial– Describe the geocoding data model– Describe geocoding functions and structure of address results– Explain point addressing, structured address geocoding, andreverse geocoding– Install, configure, and use geocoding services with the XML API– Describe where to obtain data to use with the geocoding androuting engines ( )73
Geocoding, Routing, Spatial Analysisand MiningTopic 7 What you need to know (continued):––––Describe the capabilities and architecture of the routing engineDescribe the routing engine tablesInstall and configure the routing engineFormulate XML route requests and describe the structure of routeresponses– Explain the concept of network partitioning– Partition the network used by the routing engine– Describe the uses and capabilities of the spatial analysis andmining functions74
The Geocoding ProcessAddress to MatchGeocoderAddress ParsingSearching andCleansingCoordinateGenerationCoordinates Corrected AddressReference Datafor GeocodingAddressStructureStreet andplace namesGeometries75
DatatablesGeocoding Tables Metadatatables Describetheorganiza1onofthegeocodingdata lesetsofdata Onepercountry,ormul1plecountriesperset Typicallyonesetpercountry,withacountrysuffixData country XXGC ROAD xxGC ROAD SEGMENT xxMetadataGC AREA xxGC COUNTRY PROFILEGC POSTAL CODE xxGC PARSER PROFILEGC POI xxGC PARSER PROFILEAFSGC INTERSECTION xxGC ADDRESS POINT xx76
PL/SQL API ExampleSELECT SDO GCDR.GEOCODE('SCOTT',SDO KEYWORDARRAY('Clay Street', 'San Francisco, CA'),'US', 'DEFAULT') GEO ADDRFROM DUAL;SDO GEO ADDR(0, SDO KEYWORDARRAY(), NULL, 'CLAY ST', NULL,NULL, 'SAN FRANCISCO', 'SAN FRANCISCO', 'CA', 'US','94108', NULL,'94108', NULL, '998', 'CLAY', 'ST', 'F', 'F',NULL, NULL, 'L', 0, 198728956, '?#ENUT?B281CP?', 1,'DEFAULT', -122.40953, 37.79388, '?14101010?004?')MATCHCODE 1 exact match with provided input77
Deploy & use Geocoding web service Deploy GEOCODER.EAR in your application server– File is in ORACLE HOME/md/jlib– The initial startup fails because the default configuration has nodatabase connction– Geocoder demonstration Update geocoder configuration– Using geocoder console: http:// server /geocoder/admin.jsp– Or manually update file WEB-INF/config/geocodercfg.xml– Set proper database connection to database user that owns thegeocoding tables78– Restart the geocoder application
Routing ArchitectureRouting tablesInstallation and configurationPartitioning the network How to formulate route requests Structure of the route responses79
Routing Web Service HTTPJava EnvironmentRouting EngineJDBCDatabaseRouting Tables80
Routing tablesNODESIGN POSTEDGEROUTER TRANSPORTPARTITIONSDO ROUTER DATA VERSION81
A route requestDemoAn XML request for a single route includes: A numeric route ID One start and one end location Optionally, one or more attributes:–––––––Route preference: fastest or shortestRoad preference: highway or localVehicle type and parameters (Oracle Database 11g Release 2)Whether to return the geometry of the routeTime and distance unitsLanguage for driving directionsAnd more 82
Spatial Analysis and MiningCorrelate data based on location (spatial correlation).Neighborhood analysisto determine specific information about an area of interestProportion of theme layer geometries overlapping geometry of interest isapplied to aggregate analysisSpatial binningto classify data based on locationSpatial clusteringto determine patterns based on locationCo-location analysisto determine how the location of one thing correlates to the location ofsomething elseAll functions are in the SDO SAM package.83
Spatial Analysis and Mining Find how large a population lives in a radius of 3 milesaround a pointSELECT sdo sam.aggregates for geometry('US COUNTIES', 'GEOM','sum', 'totpop',SDO GEOMETRY(2001, 8307,SDO POINT TYPE(-73.943849, 40.6698,NULL),NULL,NULL),'distance 3 unit mile')FROM DUAL;724464.31984
Spatial Analysis and Mining The area covers counties in NYCOUNTYST TOTPOP--------- -- -----QueensKingsNew York% AREACOVERED----------NY 1951598 00.5437756NY 2300664 31.0430579NY 1487536 00.018878585
Web ServicesTopic 8 What you need to know:– Describe the four major Open Geospatial Consortium standards forspatial web services and the services they enable– Describe the architecture, configuration steps, and example queriesfor Web Mapping Service for Oracle Database– Describe the architecture, configuration steps, and example queriesfor Oracle Database for Web Feature Service– Use WMS/WFS th
This certification is available to all candidates but is geared toward members of the Oracle PartnerNetwork. OPN members earning this certification will be recognized as OPN Certified Specialists. This certification qualifies as competency criteria for the Oracle Spatial 11g Specialization." Individual Certification
