Oracle Spatial, often referred to as Spatial, includes advanced features for spatial data and analysis and for physical, logical, network, and social applications.

The spatial features provide a schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial features in an Oracle database. Spatial consists of the following:

• A schema (MDSYS) that prescribes the storage, syntax, and semantics of supported geometric data types

• A spatial indexing mechanism

• Operators, functions, and procedures for performing area-of-interest queries, spatial join queries, and other spatial analysis operations

• Functions and procedures for utility and tuning operations

• Topology data model for working with data about nodes, edges, and faces in a topology (described in Oracle Spatial Topology and Network Data Model Developer's Guide).

• Network data model for representing capabilities or objects that are modeled as nodes and links (vertices and edges) in a graph (described in Oracle Spatial Topology and Network Data Model Developer's Guide).

• GeoRaster, a feature that lets you store, index, query, analyze, and deliver GeoRaster data, that is, raster image and gridded data and its associated metadata (described in Oracle Spatial GeoRaster Developer's Guide).

The spatial component of a spatial feature is the geometric representation of its shape in some coordinate space. This is referred to as its geometry.

Oracle Spatial supports the object-relational model for representing geometries. This model stores an entire geometry in the Oracle native spatial data type for vector data, SDO_GEOMETRY.

An Oracle table can contain one or more SDO_GEOMETRY columns. The object-relational model corresponds to a "SQL with Geometry Types" implementation of spatial feature tables in the Open GIS ODBC/SQL specification for geospatial features.

The benefits provided by the object-relational model include:

• Support for many geometry types, including arcs, circles, compound polygons, compound line strings, and optimized rectangles

• Ease of use in creating and maintaining indexes and in performing spatial queries

• Index maintenance by the Oracle database

• Geometries modeled in a single column

• Optimal performance

Oracle Spatial is designed to make spatial data management easier and more natural to users of location-enabled applications and geographic information system (GIS) applications. Once spatial data is stored in an Oracle database, it can be easily manipulated, retrieved, and related to all other data stored in the database.

A common example of spatial data can be seen in a road map. A road map is a two-dimensional object that contains points, lines, and polygons that can represent cities, roads, and political boundaries such as states or provinces. A road map is a visualization of geographic information. The location of cities, roads, and political boundaries that exist on the surface of the Earth are projected onto a two-dimensional display or piece of paper, preserving the relative positions and relative distances of the rendered objects.

The data that indicates the Earth location (such as longitude and latitude) of these rendered objects is the spatial data. When the map is rendered, this spatial data is used to project the locations of the objects on a two-dimensional piece of paper. A GIS is often used to store, retrieve, and render this Earth-relative spatial data.

Types of spatial data (other than GIS data) that can be stored using Spatial include data from computer-aided design (CAD) and computer-aided manufacturing (CAM) systems. Instead of operating on objects on a geographic scale, CAD/CAM systems work on a smaller scale, such as for an automobile engine or printed circuit boards.

The differences among these systems are in the size and precision of the data, not the data's complexity. The systems might all involve the same number of data points. On a geographic scale, the location of a bridge can vary by a few tenths of an inch without causing any noticeable problems to the road builders, whereas if the diameter of an engine's pistons is off by a few tenths of an inch, the engine will not run.

In addition, the complexity of data is independent of the absolute scale of the area being represented. For example, a printed circuit board is likely to have many thousands of objects etched on its surface, containing in its small area information that may be more complex than the details shown on a road builder's blueprints.

These applications all store, retrieve, update, or query some collection of features that have both nonspatial and spatial attributes. Examples of nonspatial attributes are name, soil_type, landuse_classification, and part_number. The spatial attribute is a coordinate geometry, or vector-based representation of the shape of the feature.

A geometry is an ordered sequence of vertices that are connected by straight line segments or circular arcs.

The semantics of the geometry are determined by its type. Spatial supports several primitive types, and geometries composed of collections of these types, including two-dimensional:

• Points and point clusters

• Line strings

• n-point polygons

• Arc line strings (All arcs are generated as circular arcs.)

• Arc polygons

• Compound polygons

• Compound line strings

• Circles

• Optimized rectangles

Two-dimensional points are elements composed of two ordinates, X and Y, often corresponding to longitude and latitude. Line strings are composed of one or more pairs of points that define line segments. Polygons are composed of connected line strings that form a closed ring, and the area of the polygon is implied. For example, a point might represent a building location, a line string might represent a road or flight path, and a polygon might represent a state, city, zoning district, or city block.

Self-crossing polygons are not supported, although self-crossing line strings are supported. If a line string crosses itself, it does not become a polygon. A self-crossing line string does not have any implied area.

The following figure illustrates the geometric types.

Figure 1-1 Geometric Types

Description of "Figure 1-1 Geometric Types"

Spatial also supports the storage and indexing of three-dimensional and four-dimensional geometric types, where three or four coordinates are used to define each vertex of the object being defined. For information about support for three-dimensional geometries, see Three-Dimensional Spatial Objects.

The spatial data model in Oracle Spatial is a hierarchical structure consisting of elements, geometries, and layers. Layers are composed of geometries, which in turn are made up of elements.

• Element
  
• Geometry
  
• Layer
  
• Coordinate System
  
• Tolerance
  

Spatial uses a two-tier query model to resolve spatial queries and spatial joins.

The term is used to indicate that two distinct operations are performed to resolve queries. The output of the two combined operations yields the exact result set.

The two operations are referred to as primary and secondary filter operations.

• The primary filter permits fast selection of candidate records to pass along to the secondary filter. The primary filter compares geometry approximations to reduce computation complexity and is considered a lower-cost filter. Because the primary filter compares geometric approximations, it returns a superset of the exact result set.

• The secondary filter applies exact computations to geometries that result from the primary filter. The secondary filter yields an accurate answer to a spatial query. The secondary filter operation is computationally expensive, but it is only applied to the primary filter results, not the entire data set.

Figure 1-2 illustrates the relationship between the primary and secondary filters.

Figure 1-2 Query Model

Description of "Figure 1-2 Query Model"

As shown in Figure 1-2, the primary filter operation on a large input data set produces a smaller candidate set, which contains at least the exact result set and may contain more records. The secondary filter operation on the smaller candidate set produces the exact result set.

Spatial uses a spatial index to implement the primary filter. Spatial does not require the use of both the primary and secondary filters. In some cases, just using the primary filter is sufficient. For example, a zoom feature in a mapping application queries for data that has any interaction with a rectangle representing visible boundaries. The primary filter very quickly returns a superset of the query. The mapping application can then apply clipping routines to display the target area.

The purpose of the primary filter is to quickly create a subset of the data and reduce the processing burden on the secondary filter. The primary filter, therefore, should be as efficient (that is, selective yet fast) as possible. This is determined by the characteristics of the spatial index on the data.

The integration of spatial indexing capabilities into the Oracle Database engine is a key feature of the Spatial product.

A spatial index, like any other index, provides a mechanism to limit searches, but in this case the mechanism is based on spatial criteria such as intersection and containment. For example, a spatial index is used to:

• Find objects within an indexed data space that interact with a given point or area of interest (window query)

• Find pairs of objects from within two indexed data spaces that interact spatially with each other (spatial join)

Effective with Release 12.2 and later, creating and using a spatial index is not mandatory for the use of any Oracle Spatial features (except for the SDO_NN operator). However, spatial indexes are highly recommended, and not using them can negatively affect performance in some cases.

Testing of spatial indexes with many workloads and operators is ongoing, and further results and recommendations will be documented as they become available.

The following sections explain the concepts and options associated with R-tree indexing.

• R-Tree Indexing
  
• R-Tree Quality
  

Spatial uses secondary filters to determine the spatial relationship between entities in the database. The spatial relationship is based on geometry locations.

The most common spatial relationships are based on topology and distance. For example, the boundary of an area consists of a set of curves that separates the area from the rest of the coordinate space. The interior of an area consists of all points in the area that are not on its boundary. Given this, two areas are said to be adjacent if they share part of a boundary but do not share any points in their interior.

The distance between two spatial objects is the minimum distance between any points in them. Two objects are said to be within a given distance of one another if their distance is less than the given distance.

To determine spatial relationships, Spatial has several secondary filter methods:

• The SDO_RELATE operator evaluates topological criteria.

• The SDO_WITHIN_DISTANCE operator determines if two spatial objects are within a specified distance of each other.

• The SDO_NN operator identifies the nearest neighbors for a spatial object.

The SDO_RELATE operator implements a nine-intersection model for categorizing binary topological relationships between points, lines, and polygons. Each spatial object has an interior, a boundary, and an exterior. The boundary consists of points or lines that separate the interior from the exterior. The boundary of a line string consists of its end points; however, if the end points overlap (that is, if they are the same point), the line string has no boundary. The boundaries of a multiline string are the end points of each of the component line strings; however, if the end points overlap, only the end points that overlap an odd number of times are boundaries. The boundary of a polygon is the line that describes its perimeter. The interior consists of points that are in the object but not on its boundary, and the exterior consists of those points that are not in the object and are not on its boundary.

Given that an object A has three components (a boundary Ab, an interior Ai, and an exterior Ae), any pair of objects has nine possible interactions between their components. Pairs of components have an empty (0) or not empty (1) set intersection. The set of interactions between two geometries is represented by a nine-intersection matrix that specifies which pairs of components intersect and which do not. Figure 1-5 shows the nine-intersection matrix for two polygons that are adjacent to one another. This matrix yields the following bit mask, generated in row-major form: "101001111".

Figure 1-5 The Nine-Intersection Model

Description of "Figure 1-5 The Nine-Intersection Model"

Some of the topological relationships identified in the seminal work by Professor Max Egenhofer (University of Maine, Orono) and colleagues have names associated with them. Spatial uses the following names:

• DISJOINT: The boundaries and interiors do not intersect.

• TOUCH: The boundaries intersect but the interiors do not intersect.

• OVERLAPBDYDISJOINT: The interior of one object intersects the boundary and interior of the other object, but the two boundaries do not intersect. This relationship occurs, for example, when a line originates outside a polygon and ends inside that polygon.

• OVERLAPBDYINTERSECT: The boundaries and interiors of the two objects intersect.

• EQUAL: The two objects have the same boundary and interior.

• CONTAINS: The interior and boundary of one object is completely contained in the interior of the other object.

• COVERS: The boundary and interior of one object is completely contained in the interior or the boundary of the other object, their interiors intersect, and the boundary or the interior of one object and the boundary of the other object intersect.

• INSIDE: The opposite of CONTAINS. A INSIDE B implies B CONTAINS A.

• COVEREDBY: The opposite of COVERS. A COVEREDBY B implies B COVERS A.

• ON: The interior and boundary of one object is on the boundary of the other object. This relationship occurs, for example, when a line is on the boundary of a polygon.

• ANYINTERACT: The objects are non-disjoint.

Figure 1-6 illustrates these topological relationships.

Figure 1-6 Topological Relationships

Description of "Figure 1-6 Topological Relationships"

The SDO_WITHIN_DISTANCE operator determines if two spatial objects, A and B, are within a specified distance of one another. This operator first constructs a distance buffer, D_b, around the reference object B. It then checks that A and D_b are non-disjoint. The distance buffer of an object consists of all points within the given distance from that object. Figure 1-7 shows the distance buffers for a point, a line, and a polygon.

Figure 1-7 Distance Buffers for Points, Lines, and Polygons

Description of "Figure 1-7 Distance Buffers for Points, Lines, and Polygons"

In the point, line, and polygon geometries shown in Figure 1-7:

• The dashed lines represent distance buffers. Notice how the buffer is rounded near the corners of the objects.

• The geometry on the right is a polygon with a hole: the large rectangle is the exterior polygon ring and the small rectangle is the interior polygon ring (the hole). The dashed line outside the large rectangle is the buffer for the exterior ring, and the dashed line inside the small rectangle is the buffer for the interior ring.

The SDO_NN operator returns a specified number of objects from a geometry column that are closest to a specified geometry (for example, the five closest restaurants to a city park). In determining how close two geometry objects are, the shortest possible distance between any two points on the surface of each object is used.

The Spatial PL/SQL application programming interface (API) includes several operators and many procedures and functions.

Spatial operators, such as SDO_FILTER and SDO_RELATE, provide optimum performance when they use a spatial index. (Spatial operators perform most efficiently when the geometry column in the first parameter has a spatial index defined on it.) Spatial operators must be used in the WHERE clause of a query. The first parameter of any operator specifies the geometry column to be searched, and the second parameter specifies a query window. If the query window does not have the same coordinate system as the geometry column, Spatial performs an implicit coordinate system transformation. For detailed information about the spatial operators, see Spatial Operators .

Spatial procedures and functions are provided as subprograms in PL/SQL packages, such as SDO_GEOM, SDO_CS, and SDO_LRS. These subprograms do not require that a spatial index be defined, and they do not use a spatial index if it is defined. These subprograms can be used in the WHERE clause or in a subquery. If two geometries are input parameters to a spatial procedure or function, both must have the same coordinate system.

The following performance-related guidelines apply to the use of spatial operators, procedures, and functions:

• If an operator and a procedure or function perform comparable operations, and if the operator satisfies your requirements, use the operator. For example, unless you need to do otherwise, use SDO_RELATE instead of SDO_GEOM.RELATE, and use SDO_WITHIN_DISTANCE instead of SDO_GEOM.WITHIN_DISTANCE.

• With operators, always specify TRUE in uppercase. That is, specify = 'TRUE', and do not specify <> 'FALSE' or = 'true'.

• With operators, use the /*+ ORDERED */ optimizer hint if the query window comes from a table. (You must use this hint if multiple windows come from a table.) See the Usage Notes and Examples for specific operators for more information.

For information about using operators with topologies, see Oracle Spatial Topology and Network Data Model Developer's Guide.

SQL has long had aggregate functions, which are used to aggregate the results of a SQL query.

The following example uses the SUM aggregate function to aggregate employee salaries by department:

SELECT SUM(salary), dept
   FROM employees
   GROUP BY dept;

Spatial aggregate functions aggregate the results of SQL queries involving geometry objects. Spatial aggregate functions return a geometry object of type SDO_GEOMETRY. For example, the following statement returns the minimum bounding rectangle of all geometries in a table (using the definitions and data from Simple Example: Inserting, Indexing, and Querying Spatial Data):

SELECT SDO_AGGR_MBR(shape) FROM cola_markets;

The following example returns the union of all geometries except cola_d:

SELECT SDO_AGGR_UNION(SDOAGGRTYPE(c.shape, 0.005))
  FROM cola_markets c WHERE c.name <> 'cola_d';

For reference information about the spatial aggregate functions and examples of their use, see the Spatial Aggregate Functions reference chapter.

• SDOAGGRTYPE Object Type
  

Oracle Spatial supports the storage and retrieval of three-dimensional spatial data, which can include points, point clouds (collections of points), lines, polygons, surfaces, and solids.

Table 1-1 shows the SDO_GTYPE and element-related attributes of the SDO_GEOMETRY type that are relevant to three-dimensional geometries. (The SDO_GEOMETRY type is explained in SDO_GEOMETRY Object Type.)

The following spatial operators consider all three dimensions in their computations:

• SDO_ANYINTERACT

• SDO_FILTER

• SDO_INSIDE (for solid geometries only)

• SDO_NN

• SDO_WITHIN_DISTANCE

The other operators consider only the first two dimensions. For some of preceding operators the height information is ignored when dealing with geodetic data, as explained later in this section. (Spatial operators are described in Spatial Operators .)

The SDO_GEOM.SDO_VOLUME function applies only to solid geometries, which are by definition three-dimensional; however, this function cannot be used with geodetic data. For information about support for three-dimensional geometries with other SDO_GEOM subprograms, see the usage information in SDO_GEOM Package (Geometry).

For distance computations with three-dimensional geometries:

• If the data is geodetic (geographic 3D), the distance computations are done on the geodetic surface.

• If the data is non-geodetic (projected or local), the distance computations are valid only if the unit of measure is the same for all three dimensions.

To have any functions, procedures, or operators consider all three dimensions, you must specify PARAMETERS ('sdo_indx_dims=3') in the CREATE INDEX statement when you create the spatial index on a spatial table containing Geographic3D data (longitude, latitude, ellipsoidal height). If you do not specify that parameter in the CREATE INDEX statement, a two-dimensional index is created.

For spatial functions, procedures, and operators that consider all three dimensions, distance and length computations correctly factor in the height or elevation. For example, consider two three-dimensional points, one at the origin of a Cartesian space (0,0,0), and the other at X=3 on the Y axis and a height (Z) of 4 (3,0,4).

• If the operation considers all three dimensions, the distance between the two points is 5. (Think of the hypotenuse of a 3-4-5 right triangle.)

• If the operation considers only two dimensions, the distance between the two points is 3. (That is, the third dimension, or height, is ignored.)

However, for the following operators and subprograms, when dealing with geodetic data, the distances with three-dimensional geometries are computed between the "ground" representations (for example, the longitude/latitude extent of the footprint of a building), and the height information is approximated:

• SDO_NN operator

• SDO_WITHIN_DISTANCE operator

• SDO_GEOM.SDO_DISTANCE function

• SDO_GEOM.WITHIN_DISTANCE function

For a two-dimensional query window with three-dimensional data, you can use the SDO_FILTER operator, but not any other spatial operators.

For examples of creating different types of three-dimensional spatial geometries, see Three-Dimensional Geometry Types. That section also includes an example showing how to update the spatial metadata and create spatial indexes for three-dimensional geometries.

For information about support for three-dimensional coordinate reference systems, see Three-Dimensional Coordinate Reference System Support.

Three-dimensional support does not apply to many spatial aggregate functions and PL/SQL packages and subprograms. The following are supported for two-dimensional geometries only:

• Spatial aggregate functions, except for SDO_AGGR_MBR, which is supported for both two-dimensional and three-dimensional geometries.

• SDO_GEOM (geometry) subprograms, except for the following, which are supported for both two-dimensional and three-dimensional geometries:

  • SDO_GEOM.RELATE with the ANYINTERACT mask

  • SDO_GEOM.SDO_AREA

  • SDO_GEOM.SDO_DISTANCE

  • SDO_GEOM.SDO_LENGTH

  • SDO_GEOM.SDO_MAX_MBR_ORDINATE

  • SDO_GEOM.SDO_MBR

  • SDO_GEOM.SDO_MIN_MBR_ORDINATE

  • SDO_GEOM.SDO_VOLUME

  • SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT

  • SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT

  • SDO_GEOM.WITHIN_DISTANCE

• SDO_SAM (spatial analysis and mining) subprograms

• SDO_MIGRATE.TO_CURRENT procedure

Table 1-2 describes how Oracle Spatial internally performs certain geodetic three-dimensional calculations.

• Modeling Surfaces
  
• Modeling Solids
  
• Three-Dimensional Optimized Rectangles
  
• Using Texture Data
  
• Validation Checks for Three-Dimensional Geometries
  

Geocoding is the process of converting tables of address data into standardized address, location, and possibly other data.

The result of a geocoding operation includes the pair of longitude and latitude coordinates that correspond with the input address or location. For example, if the input address is 22 Monument Square, Concord, MA 01742, the longitude and latitude coordinates in the result of the geocoding operation may be (depending on the geocoding data provider) -71.34937 and 42.46101, respectively.

Given a geocoded address, you can perform proximity or location queries using a spatial engine, such as Oracle Spatial, or demographic analysis using tools and data from Oracle's business partners. In addition, you can use geocoded data with other spatial data such as block group, postal code, and county code for association with demographic information. Results of analyses or queries can be presented as maps, in addition to tabular formats, using third-party software integrated with Oracle Spatial.

For conceptual and usage information about the geocoding capabilities of Oracle Spatial, see Geocoding Address Data. For reference information about the MDSYS.SDO_GCDR PL/SQL package, see SDO_GCDR Package (Geocoding) .

Oracle Spatial includes a place name data set, with hierarchical geographical data from HERE, that you can load into the database.

You can then then search this place name data set using the SDO_UTIL.GEO_SEARCH function. The data set includes commonly used textual location data such as place names, addresses and partial addresses, and latitude and longitude information.

Location tags are extracted from text data, and are matched with well known place names using Oracle Text and enhanced with other geographic information associated with the well known place names.

The results can be stored as additional attributes with the original data.

This feature enables you to process less structured geographic and location data so that the information can be categorized, compared, filtered, and associated with other data. For example, data with only partial names can be enriched to include city, county, state, and country, allowing it to be joined or analyzed with other data sets that may have state level information. This is especially useful when comparing Big Data results with structured information in operational systems and data warehouses.

• ELOC_ADMIN_AREA_SEARCH Table
  
• Adding User Data to the Geographic Name Hierarchy
  

Spatial supports the use of JSON and GeoJSON objects to store, index, and manage geographic data that is in JSON (JavaScript Object Notation) format.

JSON support, introduced in Release 18.1, substantially expands the limited GeoJSON support available in the previous release, in that it supports a larger range of geometries, including 2D and 3D, solid, surface, and LRS geometries. While the Spatial GeoJSON-specific APIs are still supported, you are encouraged to use the more comprehensive JSON support.

• JSON Support in Oracle Spatial
  Spatial supports the use of JSON objects to store, index, and manage geographic data that is in JSON (JavaScript Object Notation) format.
• GeoJSON Support in Oracle Spatial
  Oracle Spatial supports the use of GeoJSON objects to store, index, and manage geographic data that is in JSON (JavaScript Object Notation) format.
• JSON Schema for Spatial Geometry Objects
  Spatial uses an internal schema for storing spatial data in JSON (JavaScript Object Notation) format.

Spatial supports non-uniform rational B-spline (NURBS) curve geometries.

NURBS curves allow representation of free-form shapes with arbitrary shapes. NURBS representation allows control over the shape of the curve because control points and knots guide the shape of the curve, and they allow complex shapes to be represented with little data.

Support for NURBS curves in Spatial includes the following:

• WKT/WKB and GML functions for loading and storing of NURBS curve geometries in Oracle Spatial.

• Validation of NURBS curve geometries.

• Spatial indexing of NURBS curve geometries along with the SDO_FILTER, SDO_RELATE, and other operators. Spatial operators use an approximation of the curve for computation.

A NURBS representation requires specification of the control points and the basis functions. The basis functions, in turn, are defined using the knot vector and the degree of the curve. The control points are used to determine the shape of the NURBS curve. The knot vector does not directly control the shape of the curve, but is used to control the exact placement of end points. The knot vector is also used to create curves with kinks and corners. Non-uniform knot vectors are used for manipulating the curve.

To represent a NURBS curve, the following data must be stored: the control points, the knot vector, and the degree of the curve. The set of control points can be represented in either the Euclidean form as (x, y, z, w) where w represents the weight of the control point or in the homogeneous form as (wx, wy, wz, w). If wi=1 for all i, the curve is non-rational. The control points are specified in the weighted Euclidean form. Basis functions can be uniform or non-uniform based on the knot vector. A non-uniform knot vector is useful for placement of end points and creating kinks or corners. A normalized knot vector must be specified, that is, the first knot in the knot vector must be zero and the last knot in the knot vector must be one. It is also required that the knot vector is "clamped" at the end points. This requirement is enforced by ensuring that the first d+1 values in the knot vector are all zeros and the last d+1 values are all ones, where d represents the degree of the NURBS curve.

The implementation of NURBS curves in Oracle Spatial follows the SQL/MM standards. The SQL/MM standards for NURBS curves are used to represent splines, polynomial splines, cubic splines, B-splines, and Bezier curves. In Oracle Spatial, the SDO_GEOMETRY object type is used for NURBS representation. NURBS curves can be included in the Line, Multiline, and Collection type geometry objects. In these geometries, the simple line string and compound line string type elements can contain NURBS.

For compound line strings containing at least one NURBS segment, the last point of the previous segment is the same as the "clamped" first control point of a NURBS segment, and the last "clamped" control point of a NURBS segment is the same as the first point of the next segment. That is, the vertices will be repeated.

For geometry elements with element type value 2 representing a line string, the interpretation value of 3 is used to represent a NURBS curve; interpretation values of 1 and 2 represent linear segments and arcs. The SDO_ELEM_INFO_ARRAY for a NURBS curve is stored as (offset, 2, 3), which represents the offset, element type, and the interpretation value.

The SDO_ORDINATE_ARRAY stores the degree of the curve d, the set of m control points and a knot vector of size n. So, the ordinate array is stored as a sequence of values (d, m, x1, y1, z1, w1…. xm, ym, zm, wm, n, k1….kn). The control points are stored in the Euclidean form as specified in the SQL/MM standards. Note that for a NURBS curve the number of knots is equal to the sum of the degree, the number of control points, and 1. Therefore, n=d+m+1, an equation which is useful for validating NURBS curve geometries.

The following considerations apply to defining a NURBS curve:

• The degree of the curve should be greater than 1, because a curve of degree 1 represents polylines.

• The number of control points must be greater than or equal to 3, and must be greater than the degree.

• The number of knots must be equal to the (number of control points + degree + 1).

• The weight component of each control point must be positive.

• Control points are represented in "weighted Euclidean" form [wx, wy, (wz), w].

• Knot values should be specified in non-decreasing order, and the knot vector must be a normalized knot vector [0, .. ……, 1].

• If d is the degree of the curve, there must be d+1 consecutive equal knots at the beginning of the curve (value 0) and d+1 consecutive equal knots at the end of the curve (value 1). This is to ensure that the curve is clamped at the end points.

• If d is the degree of the curve, there must not be more than d consecutive equal knots except at the beginning or end of the curve where d+1 knots must be present.

Be sure to validate geometries with NURBS segments before creating the spatial index or performing any spatial operations on them. (This recommendation applies to all geometry types, NURBS or otherwise.)

For examples that specify NURBS curve geometries, see NURBS Curve.

To get a line string geometry that is an approximation of an input NURBS curve geometry, use the SDO_UTIL.GETNURBSAPPROX function.

Spatial supports the use of sharded database technology.

You create a shaded spatial table in the usual way, but specify CREATE SHARDED TABLE and appropriate partitioning. For example:

CREATE SHARDED TABLE departments
(  department_id  NUMBER(4),
   geojson        VARCHAR2(4000) CHECK (geojson IS JSON),
   geoloc         mdsys.sdo_geometry, 
   CONSTRAINT dept_id_pk PRIMARY KEY(department_id)
)
PARTITION BY CONSISTENT HASH (department_id) 
PARTITIONS AUTO 
TABLESPACE SET ts1;

Create the special index on this table in the usual way. For example:

CREATE INDEX sidx on departments(geoloc) indextype is mdsys.spatial_index_v2 local;

However, the following index-related considerations apply:

• A global index is not supported on a sharded special table. The CREATE INDEX statement must include the keyword LOCAL.
• The spatial index on a sharded spatial table must be system-managed (INDEXTYPE=MDSYS.SPATIAL_INDEX_V2).

Functional spatial indexes are supported. For example:

CREATE INDEX sidx on departments(json_value(geojson, ‘$’, returning sdo_geometry)) indextype is mdsys.spatial_index_v2 local;

In addition, other requirements and guidelines for application development with sharded databases apply, including the following:

• The major advantage of a sharded database is the partitioning of data into semi-autonomous "regions" called shards. (In a spatial application, the regions might be cities or states.) Using shards means that applications running on a particular region get all the performance benefits of a single database without the interference from users in other shards.

  However, a potential downside is that applications where data moves from partition to partition may not work well in the sharded database environment. For example, applications like truck movement tracking are ideal if the truck remains within a single shard (region), but not ideal if the truck moves from region to region.

  You need to know "where" the data to be manipulated (through DML statements and queries) resides. In an application, accessing data in other shards must be done from the "coordinator" instance. In addition, it can be difficult to migrate data from one shard to another without performing a delete/insert operation because an update may not work as expected.

• Partitioned Management Operations (PMO), such as MERGE PARTITION and SPLIT PARTITION, are not supported by Spatial.

When Spatial performs operations on sharded spatial data, the following actions occur automatically as needed:

• A USER_SDO_GEOM_METADATA view is created on each shard separately.
• All DDL operations (such as CREATE INDEX and ALTER INDEX) are performed on the coordinator and they are automatically propagated to the shards.
• All queries that need to "cross-shards" are performed on the coordinator and are automatically aggregated, and shard-specific queries are performed on the individual shards.

For an overview of Oracle sharding, see Using Oracle Sharding.

Spatial supports the use of Oracle Database In-Memory technology.

You can enable a spatial table for use with Database In-Memory by adding virtual columns, and then use operators such as SDO_FILTER to query that table without using a spatial index.

You create a spatial table in the usual way. Assume an existing 2D geodetic table named vz_test1 with spatial column geoloc, which has been inserted into the USER_SDO_GEOM_METADATA view as follows:

INSERT INTO user_sdo_geom_metadata
     VALUES(
     'vz_test1',
     'geoloc',
     SDO_DIM_ARRAY(
        SDO_DIM_ELEMENT('X', -180, 180, .00000005),
        SDO_DIM_ELEMENT('Y',  -90,  90, .00000005)),
        8307);
COMMIT;

Modify the table to enable the in-memory spatial feature and specify INMEMORY for the spatial table. For example, for the preceding 2D table:

ALTER TABLE vz_test1 INMEMORY INMEMORY SPATIAL(geoloc);

Then, users can run queries on the table. For example:

SELECT * FROM vz_test1 WHERE SDO_FILTER(geoloc, :x) = 'TRUE';

This approach does not need a spatial index because the table is in Oracle Database In-Memory.

Oracle Spatial provides a Java application programming interface (API) .

This API includes the following packages:

• oracle.spatial.geometry provides support for the Spatial SQL SDO_GEOMETRY data type, which is documented in this guide.

• oracle.spatial.georaster provides support for the core GeoRaster features, which are documented in Oracle Spatial GeoRaster Developer's Guide.

• oracle.spatial.georaster.image provides support for generating Java images from a GeoRaster object or subset of a GeoRaster object, and for processing the images. These features are documented in Oracle Spatial GeoRaster Developer's Guide.

• oracle.spatial.georaster.sql provides support for wrapping the GeoRaster PL/SQL API, which is documented in Oracle Spatial GeoRaster Developer's Guide.

• oracle.spatial.network provides support for the Oracle Spatial Network Data Model, which is documented in Oracle Spatial Topology and Network Data Model Developer's Guide.

• oracle.spatial.network.lod provides support for the load-on-demand (LOD) approach of network analysis in the Oracle Spatial Network Data Model, which is documented in Oracle Spatial Topology and Network Data Model Developer's Guide.

• oracle.spatial.network.lod.config provides support for the configuration of load-on-demand (LOD) network analysis in the Oracle Spatial Network Data Model, which is documented in Oracle Spatial Topology and Network Data Model Developer's Guide.

• oracle.spatial.topo provides support for the Oracle Spatial topology data model, which is documented in Oracle Spatial Topology and Network Data Model Developer's Guide.

• oracle.spatial.util provides classes that perform miscellaneous operations.

For detailed reference information about the classes and interfaces in these packages, see Oracle Spatial Java API Reference (Javadoc).

The Spatial Java class libraries are in .jar files under the <ORACLE_HOME>/md/jlib/ directory.

During installation, Spatial creates user accounts that have the minimum privileges needed to perform their jobs.

These accounts are created locked and expired; so if you need to use the accounts, you must unlock them. Table 1-4 lists the predefined user accounts created by Spatial.

Many factors can affect the performance of Oracle Spatial applications, such as the use of optimizer hints to influence the plan for query execution.

This guide contains some information about performance and tuning where it is relevant to a particular topic. For example, R-Tree Quality discusses R-tree quality and its possible effect on query performance, and Spatial Operators_ Procedures_ and Functions explains why spatial operators provide better performance than procedures and functions.

In addition, more spatial performance and tuning information is available in one or more white papers through the Oracle Technology Network (OTN). That information is often more detailed than what is in this guide, and it is periodically updated as a result of internal testing and consultations with Spatial users. To find that information on the OTN, go to

http://www.oracle.com/technetwork/database/options/spatialandgraph/

Look for material relevant to spatial performance and tuning.

Oracle Spatial is conformant with Open Geospatial Consortium (OGC) Simple Features Specification 1.1.1 (Document 99-049), starting with Oracle Database release 10g (version 10.1.0.4).

Conformance with the Geometry Types Implementation means that Oracle Spatial supports all the types, functions, and language constructs detailed in Section 3.2 of the specification.

Synonyms are created to match all OGC function names except for X(p Point) and Y(p Point). For these functions, you must use the names OGC_X and OGC_Y instead of just X and Y.

Oracle Spatial is conformant with the following International Organization for Standardization (ISO) standards:

• ISO 13249-3 SQL Multimedia and Application Packages - Part 3: Spatial

• ISO 19101: Geographic information - Reference model (definition of terms and approach)

• ISO 19109: Geographic information - Rules for application schema (called the General Feature Model)

• ISO 19111: Geographic information - Spatial referencing by coordinates (also OGC Abstract specification for coordinate reference systems)

• ISO 19118: Geographic information - Encoding (GML 2.1 and GML 3.1.1)

• ISO 19107: Geographic information - Spatial schema (also OGC Abstract specification for Geometry)

However, standards compliance testing for Oracle Spatial is ongoing, and compliance with more recent versions of standards or with new standards might be announced at any time. For current information about compliance with standards, see http://www.oracle.com/technetwork/database/options/spatialandgraph/documentation/.

To check which release of Spatial you are running, use the SDO_VERSION function.

For example:

SELECT SDO_VERSION FROM DUAL;

SDO_VERSION
--------------------------------------------------------------------------------
20.3.0.0.0

To optimize the performance of spatial operators, the SPATIAL_VECTOR_ACCELERATION database system parameter value must be TRUE.

The Vector Performance Accelerator (VPA) feature, which accelerates the performance of spatial operators, is controlled by the value of the SPATIAL_VECTOR_ACCELERATION database system parameter. Effective with Release 21c, the default value for this parameter is TRUE.

The benefits of having the SPATIAL_VECTOR_ACCELERATION parameter be TRUE include:

• Improved spatial algorithms for spatial operators and functions

• Metadata caching for all spatial operators and functions, which improves their overall performance

• Metadata caching for all DML operations, which makes insert, update, and delete operations on spatial tables run faster

You should not set this parameter to FALSE unless you have a very good reason to do so. If you need to explicitly set the value to TRUE or FALSE (such as to change it to TRUE after setting it to FALSE), you can set the value for the whole system or for a single session. For example, to set the value for the whole system, do either of the following:

• Enter the following statement from a suitably privileged account:

  ALTER SYSTEM SET SPATIAL_VECTOR_ACCELERATION = TRUE;
  

• Add the following to the database initialization file (xxxinit.ora):

  SPATIAL_VECTOR_ACCELERATION = TRUE;
  

To set the value for the current session, enter the following statement from a suitably privileged account. For example:

ALTER SESSION SET SPATIAL_VECTOR_ACCELERATION = TRUE;

If you have a regular Oracle table without an SDO_GEOMETRY column, but containing location-related information (such as latitude/longitude values for points), you can spatially enable the table by adding an SDO_GEOMETRY column and using existing (and future) location-related information in records to populate the SDO_GEOMETRY column values.

1. Alter the table to add a geometry (SDO_GEOMETRY) column.
2. Update the table to populate the SDO_GEOMETRY objects using existing location-related data values.
3. Update the spatial metadata (USER_SDO_GEOM_METADATA).
4. Create the spatial index on the table.

-- Original table without a spatial geometry column.
CREATE TABLE city_points (
  city_id NUMBER PRIMARY KEY,
  city_name VARCHAR2(25),
  latitude NUMBER,
  longitude NUMBER);
 
-- Original data for the table.
-- (The sample coordinates are for a random point in or near the city.)
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (1, 'Boston', 42.207905, -71.015625);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (2, 'Raleigh', 35.634679, -78.618164);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (3, 'San Francisco', 37.661791, -122.453613);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (4, 'Memphis', 35.097140, -90.065918);
 
-- Add a spatial geometry column.
ALTER TABLE city_points ADD (shape SDO_GEOMETRY);
 
-- Update the table to populate geometry objects using existing
-- latutide and longitude coordinates.
UPDATE city_points SET shape = 
  SDO_GEOMETRY(
    2001,
    8307,
    SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL),
    NULL,
    NULL
   );
 
-- Update the spatial metadata.
INSERT INTO user_sdo_geom_metadata VALUES (
  'city_points',
  'SHAPE', 
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude',-180,180,0.5), 
    SDO_DIM_ELEMENT('Latitude',-90,90,0.5)
  ), 
  8307
);
 
-- Create the spatial index.
CREATE INDEX city_points_spatial_idx on city_points(SHAPE) 
  INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;
 
-- Later, add new records to the table, using original INSERT format
-- (latitude and longitude, no spatial geometry object data).
-- Then update to include spatial geometry object information.
-- Tip: For efficiency, keep track of existing and new records, and use 
-- a WHERE clause to restrict the UPDATE to new records (not shown here).
 
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (5, 'Chicago', 41.848832, -87.648926);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (6, 'Miami', 25.755043, -80.200195);
 
UPDATE city_points SET shape = 
  SDO_GEOMETRY(
    2001,
    8307,
    SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL),
    NULL,
    NULL
   );

Database administrators (DBAs) can use the MDSYS.MOVE_SDO procedure to move all Oracle Spatial metadata tables to a specified target tablespace.

By default, the spatial metadata tables are created in the SYSAUX tablespace in Release 11.1 and later releases, and in the SYSTEM tablespace in releases before 11.1.

The MDSYS.MOVE_SDO procedure has the following syntax:

MDSYS.MOVE_SDO(
   target_tablespace_name IN VARCHAR2);

The required target_tablespace_name parameter specifies the name of the tablespace to which to move the spatial metadata tables.

This procedure should be used only by DBAs.

During the move operation, all other Oracle Spatial capabilities are disabled.

The following example moves the spatial metadata tables to the SYSAUX tablespace.

EXECUTE MDSYS.MOVE_SDO('SYSAUX');

This topic discusses some general guidelines that affect the amount of disk storage space and CPU power needed for applications that use Oracle Spatial.

These guidelines are intended to supplement, not replace, any other guidelines you use for general application sizing.

The following characteristics of spatial applications can affect the need for storage space and CPU power:

• Data volumes: The amount of storage space needed for spatial objects depends on their complexity (precision of representation and number of points for each object). For example, storing one million point objects takes less space than storing one million road segments or land parcels. Complex natural features such as coastlines, seismic fault lines, rivers, and land types can require significant storage space if they are stored at a high precision.

• Query complexity: The CPU requirements for simple mapping queries, such as Select all features in this rectangle, are lower than for more complex queries, such as Find all seismic fault lines that cross this coastline.

Oracle Spatial Studio, also referred to as Spatial Studio, is a free tool that lets you connect with, visualize, explore, and analyze geospatial data stored in and managed by Oracle Spatial.

Before you can use Spatial Studio, you must download the kit from Oracle Technical Resources (formerly called Oracle Technology Network), install the software, and perform certain administrative actions like creating database users that are authorized to use the tool, and managing those users.

Spatial has a set of error messages.

The Spatial error messages are documented in Oracle Database Error Messages.

Oracle error message documentation is only available in HTML. You can browse the error messages by range; and once you find the specific range, use your browser's "find in page" feature to locate the specific message. You can also search for a specific error message using the error message search feature of the Oracle online documentation.

Oracle Spatial provides examples that you can use to reinforce your learning and to create models for coding certain operations.

If you installed the demo files from the Oracle Database Examples media (see Oracle Database Examples Installation Guide), several examples are provided in the following directory:

$ORACLE_HOME/md/demo/examples

The following files in that directory are helpful for applications that use the Oracle Call Interface (OCI):

• readgeom.c and readgeom.h

• writegeom.c and writegeom.h

This guide also includes many examples in SQL and PL/SQL. One or more examples are usually provided with the reference information for each function or procedure, and several simplified examples are provided that illustrate table and index creation, combinations of functions and procedures, and advanced features:

• Inserting, indexing, and querying spatial data (Simple Example: Inserting_ Indexing_ and Querying Spatial Data)

• Coordinate systems (spatial reference systems) (Example of Coordinate System Transformation)

• Linear referencing system (LRS) (Example of LRS Functions)

• SDO_GEOMETRY objects in function-based indexes (SDO_GEOMETRY Objects in Function-Based Indexes)

• Complex queries ( Complex Spatial Queries: Examples)

This topic presents a simple example that creates spatial data using the WGS 84 (longitude/latitude) coordinate system.

It refers to concepts that are explained in SDO_GEOMETRY Object Type and Coordinate Systems (Spatial Reference Systems).

-- Create the table.

CREATE TABLE polygons_long_lat (
  geom_id NUMBER PRIMARY KEY,
  geom_name VARCHAR2(32),
  shape SDO_GEOMETRY);

-- The geometries are simple polgons using the 
-- WGS 84 (longitude/latitude) coordinate system.
-- The geometries are three simple polygons. The first and third have 4 sides; 
-- the second has 3 sides (triangle). These geeometries happen to
-- be in or around Concord in the US state of Massachusetts, but they
-- do not represent any actual identifiable places or areas of interest.

INSERT INTO polygons_long_lat VALUES(
  1,
  'geom_1',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    8307,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    SDO_ORDINATE_ARRAY(
                      -71.373742, 42.475827,
                      -71.369622, 42.455059,
                      -71.344903, 42.472788,
                      -71.357949, 42.480638,
                      -71.373742, 42.475827)
              )
);

INSERT INTO polygons_long_lat VALUES(
  2,
  'geom_2',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    8307,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    SDO_ORDINATE_ARRAY(
                      -71.358120, 42.464937,
                      -71.352971, 42.454046,
                      -71.357777, 42.475827,
                      -71.358120, 42.464937)
              )
);

INSERT INTO polygons_long_lat VALUES(
  3,
  'geom_3',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    8307,  -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1), -- polygon
    SDO_ORDINATE_ARRAY(
                      -71.394341, 42.435552,
                      -71.405671, 42.429977,
                      -71.390564, 42.428203,
                      -71.383698, 42.434285,
                      -71.394341, 42.435552)
              )
);


-- Update the USER_SDO_GEOM_METADATA view. 

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'polygons_long_lat',
  'shape',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),  -- 10 meters tolerance
    SDO_DIM_ELEMENT('Latitude', -90, 90, 10)  -- 10 meters tolerance
     ),
  8307   -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

-- Create the spatial index

CREATE INDEX polygons_long_lat_spatial_idx
   ON polygons_long_lat(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

-----------------
-- Miscellaneous other operations

-- Is a specific geometry (geom_3) valid?
SELECT geom_name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(shape, 10)
   FROM polygons_long_lat WHERE geom_name = 'geom_3';

-- Is a layer valid? (First, create the results table.)
CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('POLYGONS_LONG_LAT', 'SHAPE', 
  'VAL_RESULTS');
-- Next SELECT should process 3 rows and return null (no errors).
SELECT * from val_results;

-- Do two geometries (geom_1 and geom_2) have any spatial relationship?
SELECT SDO_GEOM.RELATE(p_a.shape, 'anyinteract', p_b.shape, 10)
  FROM polygons_long_lat p_a, polygons_long_lat p_b
  WHERE p_a.geom_name = 'geom_1' AND p_b.geom_name = 'geom_2';

-- Return the areas of all geometries.
SELECT geom_name, SDO_GEOM.SDO_AREA(shape, 10) FROM polygons_long_lat;

SQL> -- Miscellaneous other operations
SQL> 
SQL> -- Is a specific geometry (geom_3) valid?
SQL> SELECT geom_name, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(shape, 10)
  2  	FROM polygons_long_lat WHERE geom_name = 'geom_3';

GEOM_NAME                                                                       
--------------------------------                                                
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(SHAPE,10)                               
--------------------------------------------------------------------------------
geom_3                                                                          
TRUE                                                                            
                                                                                

SQL> 
SQL> -- Is a layer valid? (First, create the results table.)
SQL> CREATE TABLE val_results (sdo_rowid ROWID, result VARCHAR2(2000));

Table created.

SQL> CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('POLYGONS_LONG_LAT', 'SHAPE',
  2    'VAL_RESULTS');

Call completed.

SQL> -- Next SELECT should process 3 rows and return null (no errors).
SQL> SELECT * from val_results;

SDO_ROWID                                                                       
------------------                                                              
RESULT                                                                          
--------------------------------------------------------------------------------
                                                                                
Rows Processed <3>                                                              
                                                                                

SQL> 
SQL> -- Do two geometries (geom_1 and geom_2) have any spatial relationship?
SQL> SELECT SDO_GEOM.RELATE(p_a.shape, 'anyinteract', p_b.shape, 10)
  2    FROM polygons_long_lat p_a, polygons_long_lat p_b
  3    WHERE p_a.geom_name = 'geom_1' AND p_b.geom_name = 'geom_2';

SDO_GEOM.RELATE(P_A.SHAPE,'ANYINTERACT',P_B.SHAPE,10)                           
--------------------------------------------------------------------------------
TRUE                                                                            

SQL> 
SQL> -- Return the areas of all geometries.
SQL> SELECT geom_name, SDO_GEOM.SDO_AREA(shape, 10) FROM polygons_long_lat;

GEOM_NAME                        SDO_GEOM.SDO_AREA(SHAPE,10)                    
-------------------------------- ---------------------------                    
geom_1                                            3531176.58                    
geom_2                                            273244.085                    
geom_3                                            812379.389                    

SQL> 

A README.txt file supplements the information in several manuals.

These manuals are Oracle Spatial Developer's Guide (this manual), Oracle Spatial GeoRaster Developer's Guide, and Oracle Spatial Topology and Network Data Model Developer's Guide. This file is located at:

$ORACLE_HOME/md/doc/README.txt