Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Spatial?

• Coordinate System Support Based on EPSG Model
• New SDO_GEOMETRY Methods and Constructors
• New min_resolution and max_resolution Keywords
• New sdo_dml_batch_size Parameter
• New Geocoding Subprograms
• New Utility Subprograms
• U.S. National Grid Support
• Unknown and NaC Coordinate Reference Systems
• Spatial Routing Engine
• LRS_INTERSECTION Function

Part I Conceptual and Usage Information

1 Spatial Concepts

• 1.1 What Is Oracle Spatial?
• 1.2 Object-Relational Model
• 1.3 Introduction to Spatial Data
• 1.4 Geometry Types
• 1.5 Data Model
  • 1.5.1 Element
  • 1.5.2 Geometry
  • 1.5.3 Layer
  • 1.5.4 Coordinate System
  • 1.5.5 Tolerance
    • 1.5.5.1 Tolerance in the Geometry Metadata for a Layer
    • 1.5.5.2 Tolerance as an Input Parameter
• 1.6 Query Model
• 1.7 Indexing of Spatial Data
  • 1.7.1 R-Tree Indexing
  • 1.7.2 R-Tree Quality
• 1.8 Spatial Relationships and Filtering
• 1.9 Spatial Operators, Procedures, and Functions
• 1.10 Spatial Aggregate Functions
  • 1.10.1 SDOAGGRTYPE Object Type
• 1.11 Geocoding
• 1.12 Spatial Java Application Programming Interface
• 1.13 MDDATA Schema
• 1.14 Performance and Tuning Information
• 1.15 Open Geospatial Consortium (OGC) Conformance
• 1.16 Spatial Release (Version) Number
• 1.17 Spatial Application Hardware Requirement Considerations
• 1.18 Spatial Error Messages
• 1.19 Spatial Examples
• 1.20 README File for Spatial and Related Features

2 Spatial Data Types and Metadata

• 2.1 Simple Example: Inserting, Indexing, and Querying Spatial Data
• 2.2 SDO_GEOMETRY Object Type
  • 2.2.1 SDO_GTYPE
  • 2.2.2 SDO_SRID
  • 2.2.3 SDO_POINT
  • 2.2.4 SDO_ELEM_INFO
  • 2.2.5 SDO_ORDINATES
  • 2.2.6 Usage Considerations
• 2.3 SDO_GEOMETRY Methods
• 2.4 SDO_GEOMETRY Constructors
• 2.5 Geometry Examples
  • 2.5.1 Rectangle
  • 2.5.2 Polygon with a Hole
  • 2.5.3 Compound Line String
  • 2.5.4 Compound Polygon
  • 2.5.5 Point
  • 2.5.6 Oriented Point
  • 2.5.7 Type 0 (Zero) Element
  • 2.5.8 Several Geometry Types
• 2.6 Geometry Metadata Views
  • 2.6.1 TABLE_NAME
  • 2.6.2 COLUMN_NAME
  • 2.6.3 DIMINFO
  • 2.6.4 SRID
• 2.7 Spatial Index-Related Structures
  • 2.7.1 Spatial Index Views
    • 2.7.1.1 xxx_SDO_INDEX_INFO Views
    • 2.7.1.2 xxx_SDO_INDEX_METADATA Views
  • 2.7.2 Spatial Index Table Definition
  • 2.7.3 R-Tree Index Sequence Object
• 2.8 Unit of Measurement Support

3 Loading Spatial Data

• 3.1 Bulk Loading
  • 3.1.1 Bulk Loading SDO_GEOMETRY Objects
  • 3.1.2 Bulk Loading Point-Only Data in SDO_GEOMETRY Objects
• 3.2 Transactional Insert Operations Using SQL

4 Indexing and Querying Spatial Data

• 4.1 Creating a Spatial Index
  • 4.1.1 Indexing Geodetic Data
  • 4.1.2 Constraining Data to a Geometry Type
  • 4.1.3 Creating a Cross-Schema Index
  • 4.1.4 Using Partitioned Spatial Indexes
  • 4.1.5 Exchanging Partitions Including Indexes
  • 4.1.6 Export and Import Considerations with Spatial Indexes and Data
  • 4.1.7 Distributed Transactions and Spatial Index Consistency
• 4.2 Querying Spatial Data
  • 4.2.1 Spatial Query
    • 4.2.1.1 Primary Filter Operator
    • 4.2.1.2 Primary and Secondary Filter Operator
    • 4.2.1.3 Within-Distance Operator
    • 4.2.1.4 Nearest Neighbor Operator
    • 4.2.1.5 Spatial Functions
  • 4.2.2 Spatial Join
  • 4.2.3 Cross-Schema Operator Invocation

5 Geocoding Address Data

• 5.1 Concepts for Geocoding
  • 5.1.1 Address Representation
  • 5.1.2 Match Modes
  • 5.1.3 Match Codes
  • 5.1.4 Error Messages for Output Geocoded Addresses
• 5.2 Data Types for Geocoding
  • 5.2.1 SDO_GEO_ADDR Type
  • 5.2.2 SDO_ADDR_ARRAY Type
  • 5.2.3 SDO_KEYWORDARRAY Type
• 5.3 Using the Geocoding Capabilities
• 5.4 Geocoding from a Place Name
• 5.5 Data Structures for Geocoding
  • 5.5.1 GC_AREA_<suffix> Table
  • 5.5.2 GC_COUNTRY_PROFILE Table
  • 5.5.3 GC_INTERSECTION_<suffix> Table
  • 5.5.4 GC_POI_<suffix> Table
  • 5.5.5 GC_POSTAL_CODE_<suffix> Table
  • 5.5.6 GC_ROAD_<suffix> Table
  • 5.5.7 GC_ROAD_SEGMENT_<suffix> Table

6 Coordinate Systems (Spatial Reference Systems)

• 6.1 Terms and Concepts
  • 6.1.1 Coordinate System (Spatial Reference System)
  • 6.1.2 Cartesian Coordinates
  • 6.1.3 Geodetic Coordinates (Geographic Coordinates)
  • 6.1.4 Projected Coordinates
  • 6.1.5 Local Coordinates
  • 6.1.6 Geodetic Datum
  • 6.1.7 Transformation
• 6.2 Geodetic Coordinate Support
  • 6.2.1 Geodesy and Two-Dimensional Geometry
  • 6.2.2 Choosing a Geodetic or Projected Coordinate System
  • 6.2.3 Geodetic MBRs
  • 6.2.4 Other Considerations and Requirements with Geodetic Data
• 6.3 Local Coordinate Support
• 6.4 EPSG Model and Spatial
• 6.5 TFM_PLAN Object Type
• 6.6 Coordinate Systems Data Structures
  • 6.6.1 SDO_COORD_AXES Table
  • 6.6.2 SDO_COORD_AXIS_NAMES Table
  • 6.6.3 SDO_COORD_OP_METHODS Table
  • 6.6.4 SDO_COORD_OP_PARAM_USE Table
  • 6.6.5 SDO_COORD_OP_PARAM_VALS Table
  • 6.6.6 SDO_COORD_OP_PARAMS Table
  • 6.6.7 SDO_COORD_OP_PATHS Table
  • 6.6.8 SDO_COORD_OPS Table
  • 6.6.9 SDO_COORD_REF_SYS Table
  • 6.6.10 SDO_COORD_REF_SYSTEM View
  • 6.6.11 SDO_COORD_SYS Table
  • 6.6.12 SDO_CRS_COMPOUND View
  • 6.6.13 SDO_CRS_ENGINEERING View
  • 6.6.14 SDO_CRS_GEOCENTRIC View
  • 6.6.15 SDO_CRS_GEOGRAPHIC2D View
  • 6.6.16 SDO_CRS_GEOGRAPHIC3D View
  • 6.6.17 SDO_CRS_PROJECTED View
  • 6.6.18 SDO_CRS_VERTICAL View
  • 6.6.19 SDO_DATUM_ENGINEERING View
  • 6.6.20 SDO_DATUM_GEODETIC View
  • 6.6.21 SDO_DATUM_VERTICAL View
  • 6.6.22 SDO_DATUMS Table
  • 6.6.23 SDO_ELLIPSOIDS Table
  • 6.6.24 SDO_PREFERRED_OPS_SYSTEM Table
  • 6.6.25 SDO_PREFERRED_OPS_USER Table
  • 6.6.26 SDO_PRIME_MERIDIANS Table
  • 6.6.27 SDO_UNITS_OF_MEASURE Table
• 6.7 Legacy Tables and Views
  • 6.7.1 MDSYS.CS_SRS Table
    • 6.7.1.1 Well-Known Text (WKT)
    • 6.7.1.2 Procedures for Updating the Well-Known Text
  • 6.7.2 MDSYS.SDO_ANGLE_UNITS View
  • 6.7.3 MDSYS.SDO_AREA_UNITS View
  • 6.7.4 MDSYS.SDO_DATUMS_OLD_FORMAT and SDO_DATUMS_OLD_SNAPSHOT Tables
  • 6.7.5 MDSYS.SDO_DIST_UNITS View
  • 6.7.6 MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT and SDO_ELLIPSOIDS_OLD_SNAPSHOT Tables
  • 6.7.7 MDSYS.SDO_PROJECTIONS_OLD_FORMAT and SDO_PROJECTIONS_OLD_SNAPSHOT Tables
• 6.8 Creating a User-Defined Coordinate Reference System
  • 6.8.1 Creating a Geodetic CRS
  • 6.8.2 Creating a Projected CRS
• 6.9 Notes and Restrictions with Coordinate Systems Support
  • 6.9.1 Different Coordinate Systems for Geometries with Operators and Functions
  • 6.9.2 3D LRS Functions Not Supported with Geodetic Data
  • 6.9.3 Functions Supported by Approximations with Geodetic Data
  • 6.9.4 Unknown CRS and NaC Coordinate Reference Systems
• 6.10 U.S. National Grid Support
• 6.11 Example of Coordinate System Transformation

7 Linear Referencing System

• 7.1 Terms and Concepts
  • 7.1.1 Geometric Segments (LRS Segments)
  • 7.1.2 Shape Points
  • 7.1.3 Direction of a Geometric Segment
  • 7.1.4 Measure (Linear Measure)
  • 7.1.5 Offset
  • 7.1.6 Measure Populating
  • 7.1.7 Measure Range of a Geometric Segment
  • 7.1.8 Projection
  • 7.1.9 LRS Point
  • 7.1.10 Linear Features
  • 7.1.11 Measures with Multiline Strings and Polygons with Holes
• 7.2 LRS Data Model
• 7.3 Indexing of LRS Data
• 7.4 3D Formats of LRS Functions
• 7.5 LRS Operations
  • 7.5.1 Defining a Geometric Segment
  • 7.5.2 Redefining a Geometric Segment
  • 7.5.3 Clipping a Geometric Segment
  • 7.5.4 Splitting a Geometric Segment
  • 7.5.5 Concatenating Geometric Segments
  • 7.5.6 Scaling a Geometric Segment
  • 7.5.7 Offsetting a Geometric Segment
  • 7.5.8 Locating a Point on a Geometric Segment
  • 7.5.9 Projecting a Point onto a Geometric Segment
  • 7.5.10 Converting LRS Geometries
• 7.6 Tolerance Values with LRS Functions
• 7.7 Example of LRS Functions

8 Spatial Analysis and Mining

• 8.1 Spatial Information and Data Mining Applications
• 8.2 Spatial Binning for Detection of Regional Patterns
• 8.3 Materializing Spatial Correlation
• 8.4 Colocation Mining
• 8.5 Spatial Clustering
• 8.6 Location Prospecting

9 Extending Spatial Indexing Capabilities

• 9.1 SDO_GEOMETRY Objects in User-Defined Type Definitions
• 9.2 SDO_GEOMETRY Objects in Function-Based Indexes
  • 9.2.1 Example: Function with Standard Types
  • 9.2.2 Example: Function with a User-Defined Object Type

Part II Reference Information

10 SQL Statements for Indexing Spatial Data

• ALTER INDEX
• ALTER INDEX REBUILD
• ALTER INDEX RENAME TO
• CREATE INDEX
• DROP INDEX

11 Spatial Operators

• SDO_ANYINTERACT
• SDO_CONTAINS
• SDO_COVEREDBY
• SDO_COVERS
• SDO_EQUAL
• SDO_FILTER
• SDO_INSIDE
• SDO_JOIN
• SDO_NN
• SDO_NN_DISTANCE
• SDO_ON
• SDO_OVERLAPBDYDISJOINT
• SDO_OVERLAPBDYINTERSECT
• SDO_OVERLAPS
• SDO_RELATE
• SDO_TOUCH
• SDO_WITHIN_DISTANCE

12 Spatial Aggregate Functions

• SDO_AGGR_CENTROID
• SDO_AGGR_CONCAT_LINES
• SDO_AGGR_CONVEXHULL
• SDO_AGGR_LRS_CONCAT
• SDO_AGGR_MBR
• SDO_AGGR_UNION

13 SDO_CS Package (Coordinate System Transformation)

• SDO_CS.ADD_PREFERENCE_FOR_OP
• SDO_CS.CONVERT_NADCON_TO_XML
• SDO_CS.CONVERT_NTV2_TO_XML
• SDO_CS.CONVERT_XML_TO_NADCON
• SDO_CS.CONVERT_XML_TO_NTV2
• SDO_CS.CREATE_CONCATENATED_OP
• SDO_CS.CREATE_OBVIOUS_EPSG_RULES
• SDO_CS.CREATE_PREF_CONCATENATED_OP
• SDO_CS.DELETE_ALL_EPSG_RULES
• SDO_CS.DELETE_OP
• SDO_CS.DETERMINE_CHAIN
• SDO_CS.DETERMINE_DEFAULT_CHAIN
• SDO_CS.FIND_GEOG_CRS
• SDO_CS.FIND_PROJ_CRS
• SDO_CS.FROM_OGC_SIMPLEFEATURE_SRS
• SDO_CS.FROM_USNG
• SDO_CS.MAP_EPSG_SRID_TO_ORACLE
• SDO_CS.MAP_ORACLE_SRID_TO_EPSG
• SDO_CS.REVOKE_PREFERENCE_FOR_OP
• SDO_CS.TO_OGC_SIMPLEFEATURE_SRS
• SDO_CS.TO_USNG
• SDO_CS.TRANSFORM
• SDO_CS.TRANSFORM_LAYER
• SDO_CS.UPDATE_WKTS_FOR_ALL_EPSG_CRS
• SDO_CS.UPDATE_WKTS_FOR_EPSG_CRS
• SDO_CS.UPDATE_WKTS_FOR_EPSG_DATUM
• SDO_CS.UPDATE_WKTS_FOR_EPSG_ELLIPS
• SDO_CS.UPDATE_WKTS_FOR_EPSG_OP
• SDO_CS.UPDATE_WKTS_FOR_EPSG_PARAM
• SDO_CS.UPDATE_WKTS_FOR_EPSG_PM
• SDO_CS.VALIDATE_WKT
• SDO_CS.VIEWPORT_TRANSFORM

14 SDO_GCDR Package (Geocoding)

• SDO_GCDR.GEOCODE
• SDO_GCDR.GEOCODE_ADDR
• SDO_GCDR.GEOCODE_ADDR_ALL
• SDO_GCDR.GEOCODE_ALL
• SDO_GCDR.GEOCODE_AS_GEOMETRY
• SDO_GCDR.REVERSE_GEOCODE

15 SDO_GEOM Package (Geometry)

• SDO_GEOM.RELATE
• SDO_GEOM.SDO_ARC_DENSIFY
• SDO_GEOM.SDO_AREA
• SDO_GEOM.SDO_BUFFER
• SDO_GEOM.SDO_CENTROID
• SDO_GEOM.SDO_CONVEXHULL
• SDO_GEOM.SDO_DIFFERENCE
• SDO_GEOM.SDO_DISTANCE
• SDO_GEOM.SDO_INTERSECTION
• SDO_GEOM.SDO_LENGTH
• SDO_GEOM.SDO_MAX_MBR_ORDINATE
• SDO_GEOM.SDO_MBR
• SDO_GEOM.SDO_MIN_MBR_ORDINATE
• SDO_GEOM.SDO_POINTONSURFACE
• SDO_GEOM.SDO_UNION
• SDO_GEOM.SDO_XOR
• SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT
• SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT
• SDO_GEOM.WITHIN_DISTANCE

16 SDO_LRS Package (Linear Referencing System)

• SDO_LRS.CLIP_GEOM_SEGMENT
• SDO_LRS.CONCATENATE_GEOM_SEGMENTS
• SDO_LRS.CONNECTED_GEOM_SEGMENTS
• SDO_LRS.CONVERT_TO_LRS_DIM_ARRAY
• SDO_LRS.CONVERT_TO_LRS_GEOM
• SDO_LRS.CONVERT_TO_LRS_LAYER
• SDO_LRS.CONVERT_TO_STD_DIM_ARRAY
• SDO_LRS.CONVERT_TO_STD_GEOM
• SDO_LRS.CONVERT_TO_STD_LAYER
• SDO_LRS.DEFINE_GEOM_SEGMENT
• SDO_LRS.DYNAMIC_SEGMENT
• SDO_LRS.FIND_LRS_DIM_POS
• SDO_LRS.FIND_MEASURE
• SDO_LRS.FIND_OFFSET
• SDO_LRS.GEOM_SEGMENT_END_MEASURE
• SDO_LRS.GEOM_SEGMENT_END_PT
• SDO_LRS.GEOM_SEGMENT_LENGTH
• SDO_LRS.GEOM_SEGMENT_START_MEASURE
• SDO_LRS.GEOM_SEGMENT_START_PT
• SDO_LRS.GET_MEASURE
• SDO_LRS.GET_NEXT_SHAPE_PT
• SDO_LRS.GET_NEXT_SHAPE_PT_MEASURE
• SDO_LRS.GET_PREV_SHAPE_PT
• SDO_LRS.GET_PREV_SHAPE_PT_MEASURE
• SDO_LRS.IS_GEOM_SEGMENT_DEFINED
• SDO_LRS.IS_MEASURE_DECREASING
• SDO_LRS.IS_MEASURE_INCREASING
• SDO_LRS.IS_SHAPE_PT_MEASURE
• SDO_LRS.LOCATE_PT
• SDO_LRS.LRS_INTERSECTION
• SDO_LRS.MEASURE_RANGE
• SDO_LRS.MEASURE_TO_PERCENTAGE
• SDO_LRS.OFFSET_GEOM_SEGMENT
• SDO_LRS.PERCENTAGE_TO_MEASURE
• SDO_LRS.PROJECT_PT
• SDO_LRS.REDEFINE_GEOM_SEGMENT
• SDO_LRS.RESET_MEASURE
• SDO_LRS.REVERSE_GEOMETRY
• SDO_LRS.REVERSE_MEASURE
• SDO_LRS.SET_PT_MEASURE
• SDO_LRS.SPLIT_GEOM_SEGMENT
• SDO_LRS.TRANSLATE_MEASURE
• SDO_LRS.VALID_GEOM_SEGMENT
• SDO_LRS.VALID_LRS_PT
• SDO_LRS.VALID_MEASURE
• SDO_LRS.VALIDATE_LRS_GEOMETRY

17 SDO_MIGRATE Package (Upgrading)

• SDO_MIGRATE.TO_CURRENT

18 SDO_SAM Package (Spatial Analysis and Mining)

• SDO_SAM.AGGREGATES_FOR_GEOMETRY
• SDO_SAM.AGGREGATES_FOR_LAYER
• SDO_SAM.BIN_GEOMETRY
• SDO_SAM.BIN_LAYER
• SDO_SAM.COLOCATED_REFERENCE_FEATURES
• SDO_SAM.SIMPLIFY_GEOMETRY
• SDO_SAM.SIMPLIFY_LAYER
• SDO_SAM.SPATIAL_CLUSTERS
• SDO_SAM.TILED_AGGREGATES
• SDO_SAM.TILED_BINS

19 SDO_TUNE Package (Tuning)

• SDO_TUNE.AVERAGE_MBR
• SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE
• SDO_TUNE.EXTENT_OF
• SDO_TUNE.MIX_INFO
• SDO_TUNE.QUALITY_DEGRADATION

20 SDO_UTIL Package (Utility)

• SDO_UTIL.APPEND
• SDO_UTIL.CIRCLE_POLYGON
• SDO_UTIL.CONCAT_LINES
• SDO_UTIL.CONVERT_UNIT
• SDO_UTIL.ELLIPSE_POLYGON
• SDO_UTIL.EXTRACT
• SDO_UTIL.FROM_WKBGEOMETRY
• SDO_UTIL.FROM_WKTGEOMETRY
• SDO_UTIL.GETNUMELEM
• SDO_UTIL.GETNUMVERTICES
• SDO_UTIL.GETVERTICES
• SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS
• SDO_UTIL.POINT_AT_BEARING
• SDO_UTIL.POLYGONTOLINE
• SDO_UTIL.PREPARE_FOR_TTS
• SDO_UTIL.RECTIFY_GEOMETRY
• SDO_UTIL.REMOVE_DUPLICATE_VERTICES
• SDO_UTIL.REVERSE_LINESTRING
• SDO_UTIL.SIMPLIFY
• SDO_UTIL.TO_GMLGEOMETRY
• SDO_UTIL.TO_WKBGEOMETRY
• SDO_UTIL.TO_WKTGEOMETRY
• SDO_UTIL.VALIDATE_WKBGEOMETRY
• SDO_UTIL.VALIDATE_WKTGEOMETRY

Part III Supplementary Information

A Installation, Compatibility, and Upgrade

• A.1 Upgrading LRS Data
• A.2 Ensuring That GeoRaster Works Properly After an Upgrade

B Oracle Locator

C Routing Engine

• C.1 Deploying and Configuring the Routing Engine
• C.2 Routing Engine XML API
  • C.2.1 Route Request and Response Examples
  • C.2.2 Route Request DTD
    • C.2.2.1 route_request Element
    • C.2.2.2 route_request Attributes
    • C.2.2.3 input_location Element
    • C.2.2.4 pre_geocoded_location Element
    • C.2.2.5 longitude_latitude_location Element
  • C.2.3 Route Response DTD
  • C.2.4 Batch Route Request and Response Examples
  • C.2.5 Batch Route Request DTD
    • C.2.5.1 batch_route_request Element
    • C.2.5.2 batch_route_request Attributes
  • C.2.6 Batch Route Response DTD
  • C.2.7 Geocoding Request and Response DTDs
    • C.2.7.1 Geocoding Request DTD
    • C.2.7.2 Geocoding Response DTD
• C.3 Data Structures Used by the Routing Engine
  • C.3.1 EDGE Table
  • C.3.2 NODE Table
  • C.3.3 PARTITION Table
  • C.3.4 SIGN_POST Table

D Complex Spatial Queries: Examples

• D.1 Tables Used in the Examples
• D.2 SDO_WITHIN_DISTANCE Examples
• D.3 SDO_NN Examples
• D.4 SDO_AGGR_UNION Example

Glossary

Index