Print

## Mapping Geometric Data with Oracle Spatial

by Matt Bauer
11/10/2003

Mobile applications and technologies have created an increased need for storing, mapping, and analyzing spatial data. Traditional database techniques have stored this spatial data in X and Y columns to form various geometries. This technique allows only limited indexing and querying, making data access and manipulation difficult. Oracle Spatial attempts to make up for these limitations with its various data models, indexes, and queries. This article explains the core concepts of Oracle Spatial, particularly in its role as a way to store and analyze spatial data.

### Data Model

Oracle Spatial can store and analyze four-dimensional data; however, for the sake of clarity, this article discusses only two-dimensional. The basic building block in Oracle Spatial is an element: a `Point`, a `LineString`, or a `Polygon`. Each element is described by an array of its vertices. For a two-dimensional element, the array is read as { X1, Y1, X2, Y2, X3, Y3, ... }. Examples for each element are below.

ElementNameOrdinates (Vertices Array)
`Point`{ 1,2 }
`LineString`{ 2,2, 5,1, 3,0 }
`Polygon`{ 0,0, 1,0, 1,1, 0,1, 0,0 }

The `Polygon` element includes a repeat of the first point, which instructs Oracle Spatial that the element is a polygon. Without the repeat it would be impossible to distinguish a `LineString` that ends where it started from a `Polygon`. Ordered combinations of these elements are used to create `Geometries`. Multiple `LineString`s are placed together to describe a roadway or two polygons are combined to describe a lake with an island. The lake would have ordinates (vertices array) of { 0,0, 10,0, 10,10, 0,10, 0,0 } and the island ordinates of { 4,4, 6,4, 6,6, 4,6, 4,4 }.

Figure 1. Lake with island

It's important to note that the order of vertices is important. Oracle Spatial expects a clockwise order for polygon elements, with the last vertex being the first in order to close the polygon.

These ordinates are not enough to map the lake with an island though. What's missing is a coordinate system and tolerance. Looking at the lake ordinates it's impossible to tell if it's ten miles or ten kilometers wide. It's also not clear if the lake is on a plane or on some projection of the earth. What's needed is a coordinate or spatial reference system. Oracle Spatial defaults to a Cartesian coordinate system with dimensionless units. For large geodetic data sets (such as maps of the earth's surface), a geodetic coordinate system like NAD83 is needed. This coordinate system uses longitude and latitude for the ordinates and automatically handles issues with the curvature of the earth's surface. For the lake example, a Cartesian coordinate system is sufficient. Another piece of information needed to map is the accuracy of ordinates. Spatial data almost always has an associated tolerance or error associated with it. However, Oracle Spatial defaults to a tolerance of zero. With a coordinate system and tolerance, it's possible to query and map spatial data correctly.

### Putting It in Tables

To create a table called `mylake` with an indexed spatial column, the following SQL statements are needed.

``````CREATE TABLE mylake (
feature_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);

'mylake',
'shape',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.05),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.05)),
NULL);

CREATE INDEX mylake_idx ON mylake(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX``````

The first statement creates the desired table with a column named `shape` to hold the spatial data. We'll discuss the `MDSYS.SDO_GEOMETRY` data type later. The second statement tells Oracle Spatial about the spatial data in the `mylake` table. The `user_sdo_geom_metadata` table describes the coordinate system and tolerance for each spatial column that a user owns. The table takes four values: the table name that has a spatial column, the column name for the spatial data, an array describing the minimum, maximum, and tolerance value for each dimension, and a number stating the coordinate system. `mylake` uses two-dimensional data that can range from 0 to 100 units with a tolerance of 0.05. The null value for the coordinate system tells Oracle Spatial to use the default Cartesian coordinate system.

The last statement creates the spatial index, which is required for spatial queries. Oracle uses two types of spatial indexing: R-tree and Quadtree. If the spatial data is geodetic (map data), R-Tree must be used to take full advantage of Oracle Spatial's functions. In the index statement above the spatial type wasn't specified, causing Oracle Spatial to default to an R-Tree index. Details about spatial indexing may be found in the reference section, but both types follow the same concept. Indexes are made by placing a grid over the spatial data and then noting which grid square or grid squares the shape lies in.

Figure 2. Grid overlaid on various shapes

(We'll discuss indexing in further detail below in the explanation of spatial querying.)

Having created an indexed table to store spatial data, all that's left is to populate it with data. Oracle Spatial uses the `MDSYS.SDO_GEOMETRY` type to store spatial data, which is defined as:

``````CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);``````

`SDO_GTYPE` is a number that defines the overall shape; that is, a line, a sphere, and so forth. It describes the end result of the ordered combination of elements. `SDO_GTYPE` is a four-digit integer. The first digit represents the number of dimensions. The second digit represents the linear representation, which is important for a three- or four-dimensional shape. In a two-dimensional shape the value is zero. The last two digits represent the shape and range from 00 to 07. Each value describes a particular geometry, noted below.

ValueGeometryDescription
00`UNKNOWN_GEOMETRY`Spatial ignores this value
01`POINT`A single point element
02`LINE` or `CURVE`Contains one line string element that may be linear, curved or both
03`POLYGON`Contains one polygon element with or without other polygon elements in it
04`COLLECTION`A heterogeneous collection of elements
05`MULTIPOINT`Contains one or more points
06`MULTILINE` or `MULTICURVE`Contains one or more line string elements
07`MULTIPOLYGON`Contains multiple polygon elements that maybe disjoint

Examples of `SDO_GTYPE` are a rectangle, 2003, and a collection of roadways, 2006.

The `SDO_SRID` number describes the coordinate system to use. This field is used to guarantee that all geometries within the table column use the same coordinate system. This number is the same as that used in the `USER_SDO_GEOM_METADATA` table. It also defaults to the Cartesian coordinate system if set to null.

Spatial also allows for the definition of a single point within a geometry. Think of this as a point within the lake. This point could be used for label placement, measurement determinations, and so on. An `SDO_POINT` is defined as:

``````CREATE TYPE sdo_point_type AS OBJECT (
X NUMBER,
Y NUMBER,
Z NUMBER);``````

If a two-dimensional geometry is used, `Z` may be left null. Alternately, the entire `SDO_POINT` value may be set to null.

Skipping ahead, the `SDO_ORDINATES_ARRAY` is a list of all the vertices that define the geometry. It's defined below.

``CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;``

For a lake with an island, the `SDO_ORDINATES_ARRAY` value is (0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4). Mentioned above, the values are read in pairs, with the first value being `X` and the second value being `Y`. If this were a three-dimensional geometry, the values would be read in triplets, with the last value being `Z`.

The `SDO_ELEM_INFO_ARRAY` describes the multiple elements within the `SDO_ORDINATES_ARRAY` and is defined below.

``CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;``

An `SDO_ELEM_INFO_ARRAY` is understood as three values at a time. Each set of three values describes an element of the geometry. The lake example is composed of two polygon elements and would have an `SDO_ELEM_INFO_ARRAY` containing six numbers: { 1,1003,1, 6, 2003, 1 }. The first triplet of 1, 1003, 1 states that the first vertex of the first element (the lake) starts at position 1 in the `SDO_ORDINATES_ARRAY`. The value of 1003 defines the element (the lake) as a single exterior polygon and the final 1 tells Oracle Spatial to connect the vertices with straight lines. A value of 2 would connect the vertices with arcs. The second triple of 6, 2003, 1 states the first vertex of the second element (the island) starts at position 6 in the `SDO_ORDINATES_ARRAY`. The value of 2003 defines the element (the island) as an interior polygon and it too has its vertices connected with straight lines. Possible values besides 1003 and 2003 are listed below. More information about `SDO_ELEM_INFO_ARRAY` may be found in the reference section at the end of this article.

ValueMeaning
1`Point` element
2`LineString` element
1003`Exterior` polygon element
2003`Interior` polygon element

The SQL needed to insert a lake with an island into the `mylake` table is listed below.

``````INSERT INTO mylake VALUES(
10, -- feature_id
'Lake Calhoun', -- name
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4)
));``````

The SQL needed to insert a couple of boats into the `mylake` table is listed below.

``````INSERT INTO mylake VALUES(
12, -- feature_id
'The Windswept', -- name
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(2,2, 3,2, 3,2, 2,3, 2,2)
)
);

INSERT INTO mylake VALUES(
12, -- feature_id
'Blue Crest', -- name
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(7,7, 8,7, 8,7, 7,8, 7,7)
)
);``````
 Pages: 1, 2