Oracle8(TM) Server Spatial Cartridge User's Guide and Reference Release 8.0.3 A53264_01 

Oracle8 Spatial Cartridge is an integrated set of functions and procedures that enables spatial data to be stored, accessed, and analyzed quickly and efficiently in an Oracle8 database.
Spatial data represents the essential location characteristics of real or conceptual objects as those objects relate to the real or conceptual space in which they exist.
Spatial Cartridge is designed to make the storage, retrieval, and manipulation of spatial data easier and more natural to users such as a Geographic Information System (GIS). Once this data is stored in an Oracle8 relational database, it can be easily and meaningfully manipulated and retrieved as it relates to all the other data stored in the database.
A common example of spatial data can be seen in a road map. A road map is a twodimensional 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 twodimensional display or piece of paper, preserving the relative positions and relative distances of the rendered objects.
The data that indicates the Earth location (latitude and longitude, or height and depth) 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 twodimensional piece of paper. A GIS is often used to store, retrieve, and render this Earthrelative spatial data.
Other types of spatial data that can be stored using Spatial Cartridge besides GIS data include data from computeraided design (CAD) and computeraided 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 much smaller scale as for printed circuit boards.
The differences among these three systems are only in the scale of the data, not its complexity. They might all actually 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 are off by a few tenths of an inch, the engine will not run. A printed circuit board is likely to have many thousands of objects etched on its surface that are no bigger than the smallest detail shown on a roadbuilder's blueprints.
Spatial Cartridge supports three geometric primitive types and geometries composed of collections of these types. The three primitive types are:
2D 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 interior of the polygon is implied. Figure 11 illustrates the supported geometric primitives:
Selfcrossing polygons are not supported although selfcrossing line strings are. If a line string crosses itself it does not become a polygon. A selfcrossing line string does not have any implied interior.
The Spatial Cartridge data model is a hierarchical structure consisting of elements, geometries, and layers, which correspond to representations of spatial data. Layers are composed of geometries, which in turn are made up of elements.
For example, a point might represent a building location, a line string might be a road or flight path, and a polygon could be a state, city, zoning district, or city block.
An element is the basic building block of a geometric feature for Spatial Cartridge. The supported spatial element types are points, line strings, and polygons. For example, elements might model star constellations (point clusters), roads (line strings), and county boundaries (polygons). Each coordinate in an element is stored as an X,Y pair.
Point data^{1} consists of one coordinate. Line data consists of two coordinates representing a line segment of the element. Polygon data consists of coordinate pair values, one vertex pair for each line segment of the polygon. Coordinates are defined in either a clockwise or counterclockwise order around the polygon. Each layer's geometric objects and their associated spatial index are stored in the database in tables.
If an element spans more than one row, an incremental sequence number (starting at zero) orders the rows.
A geometry, or geometric object, is the representation of a user's spatial feature, modeled as an ordered set of primitive elements. Each geometric object is required to be uniquely identified by a numeric geometry identifier (GID), associating the object with its corresponding attribute set.
A complex geometric feature such as a polygon with holes would be stored as a sequence of polygon elements. In a multielement polygonal geometry, all subelements are wholly contained within the outermost element, thus building a more complex geometry from simpler pieces.
For example, a geometry might describe the buildable land in a town. This could be represented as a polygon with holes where water or zoning prevents construction.
A layer is a heterogeneous collection of geometries having the same attribute set. For example, one layer in a GIS might include topographical features, while another describes population density, and a third describes the network of roads and bridges in the area (lines and points).
Spatial Cartridge uses four database tables to store and index spatial data. These four tables are collectively referred to as a "layer". A template SQL script is provided to facilitate the creation of these tables. See Section A.3, "crlayer.sql Script" for details.
Figure 12 describes the schema of a Spatial Cartridge layer.
The columns of each table are defined as follows:
SDO_ORDCNT

The SDO_ORDCNT column is the total number of ordinates per row in the <layername>_SDOGEOM table. That is, the total number of data value columns, and not the number of points or coordinates. SDO_ORDCNT should not be multiplied by the total number of dimensions per coordinate as it is already a total.
SDO_LEVEL
 The SDO_LEVEL column stores the number of times the layer was tessellated during the indexing stage. This column is not used or updated by any of the Spatial Cartridge functions. It is maintained by the user and is only necessary if fixedsize tiles are used.
SDO_DIMNUM
 The SDO_DIMNUM column is the dimension to which this row refers, starting with 1 and increasing.
SDO_LB
 The SDO_LB column is the lower bound of the ordinate in this dimension. For example, if the dimension is latitude, the lower bound would be 90.
SDO_UB
 The SDO_UB column is the upper bound of the ordinate in this dimension. For example, if the dimension is longitude, the upper bound would be 180.
SDO_TOLERANCE
 The SDO_TOLERANCE column is the distance two points can be apart and still be considered the same due to roundoff errors. Tolerance must be greater than zero. If you want zero tolerance, enter a number such as 0.00005, where the number of zeroes to the right of the decimal point matches the precision of your data. The extra "5" will round up to your last decimal digit.
SDO_DIMNAME
 The SDO_DIMNAME column is used for the usual name applied to this dimension, such as "longitude," "latitude," "X" or "Y."
SDO_GID
 A Geometry IDentifier is a unique numeric identifier for each geometry in a layer.
SDO_ESEQ
 A geometry is composed of one or more primitive types called elements. The column SDO_ESEQ enumerates each element in a geometry, that is, the Element SEQuence number.
SDO_ETYPE
 The type of each element is recorded by the Element TYPE column. For this release of Spatial Cartridge, the valid values are SDO_GEOM.POINT_TYPE, SDO_GEOM.LINESTRING_TYPE, or SDO_GEOM.POLYGON_TYPE (ETYPE values 1, 2, and 3, respectively).
SDO_SEQ
 The SDO_SEQ column records the order (the SEQuence number) of each row of data making up the element.
SDO_X1
 X value of the first coordinate.
SDO_Y1
 Y value of the first coordinate.
SDO_Xn
 X value of the Nth coordinate.
SDO_Yn
 Y value of the Nth coordinate.
SDO_GID
 A Geometry IDentifier is a unique numeric identifier for each geometry in a layer. This can be thought of as a foreign key back to the <layername>_SDOGEOM table.
SDO_CODE
 The SDO_CODE column is the bit interleaved ID of a tile that covers SDO_GID. The number of bytes needed for the SDO_CODE and SDO_MAXCODE columns depends on the length of the HHCODE used for tiling. Use the SDO_ADMIN.SDO_CODE_SIZE()
function to determine the size required for a given layer. The maximum number of bytes possible in an HHCODE is 255.
SDO_MAXCODE
 The SDO_MAXCODE column is SDO_CODE padded out one place farther than the longest allowable code name for this index. The SDO_MAXCODE column describes a logical tile which is infinitesimally smaller than the next cell ID. This column is optional if using fixedsize tiles.
Spatial Cartridge provides stored procedures that assume the existence of the layer schema as described in this section. While layer objects may contain additional columns, they are required to contain at least the columns described here with the same column names and data types. The SDO_GID column always needs to be specified when loading or inserting geometries.
Figure 13 illustrates how a geometry is stored in the database using Spatial Cartridge. The geometry to be stored is a complex polygon with a hole in it.
<layername>_SDOLAYER
SDO_ORDCNT (number)


4 
<layername>_SDODIM
SDO_DIMNUM (number)

SDO_LB (number)

SDO_UB (number)

SDO_TOLERANCE (number)

SDO_DIMNAME (varchar)


1 
0 
100 
.05 
X axis 
2 
0 
100 
.05 
Y axis 
<layername>_SDOGEOM
In this example, the <layername>_SDOGEOM table is shown as a 4wide table. In actual usage, Spatial Cartridge supports Nwide^{2} tables. The coordinates for each ESEQ in this example table could have been loaded into a single, 18wide row containing values for SDO_X1 and SDO_Y1 through SDO_X9 and SDO_Y9.
The data in the <layername>_SDOINDEX table is described in Section 1.5, "Indexing Method".
Spatial Cartridge uses HHCODEs to build a spatial index.
HHCODEs represent a quad tree decomposition of the object space and are used to define an exclusive and exhaustive cover of every element stored in a Spatial Cartridge layer. Such HHCODEs are sometimes referred to as "tiles." Spatial Cartridge can use either fixed or variablesized tiles to cover a geometry.
The number of tiles used to cover an element is a usertunable parameter. Using either smaller fixedsize tiles or more variablesized tiles provides a better fit of the tiles to the element. The fewer the number of tiles or the larger the tiles, the coarser the fit. For primary filter operations, a small number of tiles should be used because it is more desirable to have less tiles to search.
The process of determining which tiles cover a given element is called tessellation. The results of a tessellation process on an element are stored in the <layername>_SDOINDEX table. See Section 2.3, "Index Creation" for more information on tessellation.
Figure 14 illustrates geometry 1013 decomposed to a maximum of four cover tiles. The cover tiles are then shown stored in the <layername>_SDOINDEX table.
Only three of the four tiles generated by the first tessellation interact with the geometry. Tile T0 shows a further tessellation into four smaller tiles, two of which interact with the geometry. Only those tiles that interact with the geometry are stored in the <layername>_SDOINDEX table, as shown in Table 11. In this example, four variablesized tiles are used.
With fixedsize tiling, size 1 would create the same three large cover tiles shown. Setting the size to 2 would cause two subdivisions, creating 8 cover tiles (the same two in Tile T0, four in T2, and two in T3) for this geometry.
Table 11<layername>_SDOINDEX
or Using VariableSized Tiles
SDO_GID <NUMBER>  SDO_CODE <RAW>  SDO_MAXCODE <RAW> 

1013 
T0_02 
T0_02FF...F 
1013 
T0_03 
T0_03FF...F 
1013 
T2 
T2FF...F 
1013 
T3 
T3FF...F 
1013 
T2 
T2FF...F 
All elements in a geometry are tessellated. In a multielement polygon like 1013, Element 1 is covered by redundant tiles from the tessellation of Element 0.
Spatial Cartridge has undergone an architectural change for this release. A reliance on partitioned tables has changed to utilize improved spatial indexing capable of handling complex geometries. However, for handling very large amounts (tens of gigabytes) of purely point data, keeping that data in partitioned tables may be more efficient than using the new spatial indexing scheme.
Table partitioning and spatial indexing are two very different techniques. While both are important to their respective users, this manual emphasizes the spatial indexing capabilities of Spatial Cartridge.
See Chapter 4, "Partitioned Point Data" for a brief overview of partitioned point data.
^{1}
Point data can also be stored in a partitioned table. See Chapter 4, "Partitioned Point Data" for details.
^{2}
A <layername>_SDOGEOM table can have up to 255 columns. The maximum number of data columns is 255, minus 4 for the other required spatial attributes, and minus any other userdefined columns.