12
The Relational Schema

Before release 8.1, the Spatial product always used four database tables to store and index spatial data. This database structure is modeled on the first of three OpenGIS Features for SQL Implementation options, namely, using numeric SQL types for geometry storage. This schema is different from the spatial objects model introduced in Spatial release 8.1 and described in Part II of this guide. However, there are still some advantages, discussed in Section 1.2.2, to using this model.

Note:
In the next release of Oracle Spatial, the relational model will no longer be documented in this guide, but will instead be documented in a separate document whose title and location will be announced.
Spatial applications using the relational model will continue to work; however, if you are not already using the object-relational model for all Spatial applications, you are encouraged to do so before the next release.

12.1 Database Structures for the Relational Implementation

The four tables, used to store and index geometries, are collectively referred to as a layer. A template SQL script is provided to facilitate the creation of these tables. See Section A.2.2 for details.

Table 12-1 through Table 12-4 describe the schema of a Spatial layer.

SDO_ORDCNT	SDO_LEVEL	SDO_NUMTILES	SDO_MAXLEVEL^{Foot 1}	SDO_COORDSYS^{Foot 2}
<number>	<number>	<number>	<number>	<varchar>

*SDO_DIMNUM*	*SDO_LB*	*SDO_UB*	*SDO_TOLERANCE*	*SDO_DIMNAME*
<number>	<number>	<number>	<number>	<varchar>

*SDO_GID*	*SDO_ESEQ*	*SDO_ETYPE*	*SDO_SEQ*	*SDO_X1*	*SDO_Y1*	*...*	*SDO_Xn*	*SDO_Yn*
<number>	<number>	<number>	<number>	<number>	<number>	...	<number>	<number>

*SDO_GID*	*SDO_CODE*	SDO_MAXCODE ^{Foot 1}	SDO_GROUPCODE ^{Foot 2}	SDO_META
<number>	<raw>	<raw>	<raw>	<raw>

Table 12-1 <layername>_SDOLAYER Table

SDO_ORDCNT SDO_LEVEL SDO_NUMTILES SDO_MAXLEVEL^{Foot 1} SDO_COORDSYS^{Foot 2}

<number>

<number>

<number>

<number>

<varchar>

^{Foot 1} SDO_MAXLEVEL is an optional column.
^{Foot 2} SDO_COORDSYS is an optional column.

Table 12-2 <layername>_SDODIM Table or View

SDO_DIMNUM SDO_LB SDO_UB SDO_TOLERANCE SDO_DIMNAME

<number> <number> <number> <number> <varchar>

Table 12-3 <layername>_SDOGEOM Table or View

SDO_GID SDO_ESEQ SDO_ETYPE SDO_SEQ SDO_X1 SDO_Y1 ... SDO_Xn SDO_Yn

<number> <number> <number> <number> <number> <number> ... <number> <number>

Table 12-4 <layername>_SDOINDEX Table

SDO_GID SDO_CODE SDO_MAXCODE ^{Foot 1} SDO_GROUPCODE ^{Foot 2} SDO_META

<number> <raw> <raw> <raw> <raw>

^{Foot 1} SDO_MAXCODE is not required for the recommended fixed-size tile indexing algorithm.
^{Foot 2} SDO_GROUPCODE is not required for the recommended fixed-size tile indexing algorithm.

The columns of each table are defined as follows:

<layername>_SDOLAYER

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 tiles that interact with a geometry should be decomposed. It is the termination criterion for fixed tiling. Use the SDO_TUNE.ESTIMATE_TILING_LEVEL procedure to determine an appropriate tiling level for your data.
SDO_NUMTILES: The SDO_NUMTILES column is the number of variable-sized tiles used to tessellate each object in the <layername>_SDOGEOM table. This column must be set to NULL when using fixed-size tiles.
SDO_MAXLEVEL: The SDO_MAXLEVEL column indicates the maximum level to which a variable-sized tile can be decomposed. It is the termination criterion for the variable component of hybrid tiling.
SDO_COORDSYS: The SDO_COORDSYS column is optional; you can indicate the name of the coordinate system, using a standard such as POSC or OGIS.

<layername>_SDODIM

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 latitude, the upper bound would be 90.
SDO_TOLERANCE: The SDO_TOLERANCE column is the distance two points can be apart and still be considered the same due to round-off errors. Tolerance must be greater than zero. If you want zero tolerance, enter a number such as 0.00005, where the number of zeros to the right of the decimal point matches the precision of your data. The extra 5 will round up to the 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.

<layername>_SDOGEOM

SDO_GID: The SDO_GID column is a unique numeric identifier for each geometry in a layer.
SDO_ESEQ: The SDO_ESEQ column enumerates each element in a geometry, that is, the Element SEQuence number.
SDO_ETYPE: The SDO_ETYPE column is the geometric primitive type of the element. For this release of Spatial, the valid values are SDO_GEOM.POINT_TYPE, SDO_GEOM.LINESTRING_TYPE, or SDO_GEOM.POLYGON_TYPE (ETYPE values 1, 2, and 3, respectively). The SDO_ETYPE values 4 and 5, supported in the object-relational schema, are not supported. Setting the ETYPE to zero indicates that this element should be ignored. See Section A.1.9 for information on ETYPE=0.
SDO_SEQ: The SDO_SEQ column records the order (the SEQuence number) of each row of data making up the element.
SDO_X1: The X value of the first coordinate.
SDO_Y1: The Y value of the first coordinate.
SDO_Xn: The X value of the nth coordinate.
SDO_Yn: The Y value of the nth coordinate.

<layername>_SDOINDEX

SDO_GID: The SDO_GID column 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. This column should be created as type RAW(255).
SDO_MAXCODE: The SDO_MAXCODE column describes a variable-sized logical tile, which is the smallest tile (with the longest tile ID) in the current quadrant. The SDO_MAXCODE column is SDO_CODE padded out one place farther than the longest allowable code name for this index. This column is not used for fixed-size tiles.
SDO_GROUPCODE: The SDO_GROUPCODE column is a prefix of SDO_CODE. It represents a variable-sized tile at level <layername>_SDOLAYER.SDO_LEVEL that contains or is equal to the tile represented by SDO_CODE. This column is not used for fixed-size tiles.
SDO_META: The SDO_META column is not required for spatial queries. It provides information necessary to find the bounds of a tile. See Section A.1.5 for one possible use of this column.

Spatial provides stored procedures that assume the existence of the layer schema as described in this section. While layer tables may contain additional columns, they are required to contain at least the columns described in this section with the same column names and data types.

Figure 12-1 illustrates how a geometry is stored in the database using Spatial and the OGIS V1 schema model. The geometry to be stored is a complex polygon with a hole in it.

Figure 12-1 Complex Polygon

<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

SDO_GID (number)	SDO_ESEQ (number)	SDO_ETYPE (number)	SDO_SEQ (number)	SDO_X1 (number)	SDO_Y1 (number)	SDO_X2 (number)	SDO_Y2 (number)
1013	0	3	0	P1(X)	P1(Y)	P2(X)	P2(Y)
1013	0	3	1	P2(X)	P2(Y)	P3(X)	P3(Y)
1013	0	3	2	P3(X)	P3(Y)	P4(X)	P4(Y)
1013	0	3	3	P4(X)	P4(Y)	P5(X)	P5(Y)
1013	0	3	4	P5(X)	P5(Y)	P6(X)	P6(Y)
1013	0	3	5	P6(X)	P6(Y)	P7(X)	P7(Y)
1013	0	3	6	P7(X)	P7(Y)	P8(X)	P8(Y)
1013	0	3	7	P8(X)	P8(Y)	P1(X)	P1(Y)
1013	1	3	0	G1(X)	G1(Y)	G2(X)	G2(Y)
1013	1	3	1	G2(X)	G2(Y)	G3(X)	G3(Y)
1013	1	3	2	G3(X)	G3(Y)	G4(X)	G4(Y)
1013	1	3	3	G4(X)	G4(Y)	G1(X)	G1(Y)

In this example, the <layername>_SDOGEOM table is shown as an 8-column table with 4 ordinates per row. In actual usage, Spatial supports n-wide^{Foot 1} tables. The coordinates for the outer polygon in this example could have been loaded into a single row containing values for coordinates P1 to P8, and then repeating P1 to close the polygon. The coordinates would be stored in the SDO_X1 and SDO_Y1 through SDO_X9 and SDO_Y9 columns.

The data in the <layername>_SDOINDEX table is described in further detail in Section 1.7. The SDOINDEX table contains entries of the form [SDO_GID, SDO_CODE] where each SDO_CODE represents a tile that interacts with a geometry identified by SDO_GID. For a given SDO_GID value, there may be one or more SDO_CODE values. Each SDO_CODE value may be associated with one or more SDO_GID values.

^{Foot 1} A <layername>_SDOGEOM table can have up to 1000 columns. The maximum number of data columns is 1000, minus 4 for the other required spatial columns, and minus any other user-defined columns. For polygons and line strings, storing 16 to 20 ordinates per row is suggested for performance reasons, but not required. The objective is to minimize the number of null values stored in the <layername>_SDOGEOM table.

12 The Relational Schema