10
The Relational Schema

Prior to 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 Open GIS Features for SQL Implementation options, namely, using numeric SQL types for geometry storage. This schema is different from the new 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 A.1, to using this model.

10.1 Database Structures for the Relational Implementation

The four tables, used to store and index geometry, are collectively referred to as a layer. A template SQL script is provided to facilitate the creation of these tables. See Section A.3.2, "crlayer.sql Script" for details.

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

Table 10-1 <layername>_SDOLAYER

SDO_ORDCNT	SDO_LEVEL	SDO_NUMTILES	SDO_MAXLEVEL1	SDO_COORDSYS2
<number>	<number>	<number>	<number>	<varchar>

1 SDO_MAXLEVEL is an optional column. 2 SDO_COORDSYS is an optional column.

Table 10-2 <layername>_SDODIM Table or View

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

Table 10-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 10-4 <layername>_SDOINDEX Table

SDO_GID	SDO_CODE	SDO_MAXCODE 1	SDO_GROUPCODE 2	SDO_META
<number>	<raw>	<raw>	<raw>	<raw>

1 SDO_MAXCODE is not required for the recommended fixed-size tile indexing algorithm. 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 criteria 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 criteria for the variable component of hybrid tiling.

• SDO_COORDSYS - The SDO_COORDSYS column is optional; where 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 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.

<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.2.8 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 - 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.

<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.2.4 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 10-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 10-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¹ 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 Section 1.6, "Indexing Methods". 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_CODEs. Each SDO_CODE value may be associated with one or more SDO_GIDs.

---

¹ 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 NULLs stored in the <layername>_SDOGEOM table.