Storing geometry in a relational database

Standards have been developed by industry, government bodies, and the ISO for managing geometric objects in a relational database. These standards include:

OpenGIS Consortium - Simple Features Specification for SQL

ISO19125-1, Geographic Information - Simple feature access - Part 1: Common architecture

ISO19125-2, Geographic Information - Simple feature access - Part 2: SQL option

ISO 19107, Geographic information - Spatial schema

ISO 13249-3, Information technology Database languages SQL Multimedia and Application Packages Part 3: Spatial.  

Taken together, these standards define the behavior of the geometric object, well-known exchange formats for the geometric object, and three different schemas for storing the geometric object as a value in a relational database. The different storage schemas are:

• The Normalized geometry storage schema
• The Binary geometry storage schema
• The Geometry Type where the SQL type system is extended

These different storage schema are defined in abstract form by the standards organizations, and may take advantage of DBMS specific data types. For example, the Binary storage schema may be implemented using any DBMS supported binary data type, and the specification defines the binary type in the abstract.

ArcSDE manages the physical storage of geometry for features using standard data types provided by the host DBMS. Some DBMS' have spatial data types, while others provide standard binary or binary large object (BLOB) storage types. ArcSDE geometry storage is dependent on the capabilities of your DBMS. No matter what geometry storage type you use, an ArcSDE client application does not need to know what the geometry storage type is. In the case of Oracle, you can use any combination of available storage methods. You may choose to store a point layer as Oracle Spatial geometry types and a polygon layer as binary. The decision on how to store your geometry will be based on the DBMS you use and other requirements specific to your implementation.

The Configuration and Tuning Guides for each DBMS describe how to define geometry storage types offered by the DBMS. The ESRI product documentation folder contains a pdf file, config_tuning_guide_[your DBMS].pdf  

This table summarizes the DBMS and geometry storage options available.

*ST_GEOMETRY is a super class of several subclasses (e.g.: ST_polygon)

Geometry Metadata

The different geometry storage schemas have associated metadata for discovering the existence and properties of a geometry column. In the standardized specification, there is overlap between the different metadata schema. ArcSDE has implemented the metadata schema to allow any storage schema.

An ArcSDE geometry column is called a LAYER. Layers have a number of specialized properties that are maintained by ArcSDE. These properties include:

• Layer owner, table, and column name
• Coordinate reference
• Geometry type
• Indexing parameters
• Table creation parameters
• Layer description

This information is managed in three DBMS tables, called the LAYERS, SPATIAL_REF_SYS, and GEOMETRY_COLUMNS tables. Three separate tables are used so that data can be normalized (in the case of spatial references) and for ease of integration with the OpenGIS Simple Features in SQL Specification.

The LAYERS table stores a row for each spatial column in the database. Applications use the layer properties to discover available spatial data sources. The layer properties are used by ArcSDE to constrain and validate the contents of the spatial column, to index geometry values, and to properly create and manage the associated DBMS tables. The entries found in the LAYERS table vary slightly depending on the underlying DBMS.

The GEOMETRY_COLUMNS table stores a row for each column of type Geometry in the database that complies with the OpenGIS SQL specification. The ArcSDE application server treats this table as "write only"—the only time it is accessed by the ArcSDE server is when a layer is added or deleted that uses an OpenGIS SQL data format. This table is defined by the OpenGIS SQL specification, and may be updated by other applications, with geometry columns not managed by ArcSDE. When a new Geometry column is created in an OpenGIS compliant format, the fully qualified table, column name, and spatial reference ID (SRID) is added to the GEOMETRY_COLUMNS table.

Each Geometry column is associated with a Spatial Reference System. ArcSDE stores information on each Spatial Reference System in the SPATIAL_REF_SYS table. The columns of this table are those defined by the OpenGIS SQL Specification (SRID, SRTEXT, AUTH_NAME, and AUTH_SRID), and those required by ArcSDE for internal coordinate transformation. The Spatial Reference System identifies the coordinate system for a geometry and gives meaning to the numeric coordinate values for the Geometry.

Layers Table Schema (example)

The LAYERS table schema:

CREATE TABLE layers ( layer_id            INTEGER NOT NULL,
                      description         VARCHAR2(65) NULL,
                      table_name          VARCHAR2(160) NOT NULL,
                      owner               VARCHAR2(32) NOT NULL,
                      spatial_column      VARCHAR2(32) NOT NULL,
                      srid                INTEGER NOT NULL,
                      storage_type        INTEGER NOT NULL,
                      eflags              INTEGER NOT NULL,
                      gsize               FLOAT(64) NOT NULL,
                      gsize2              FLOAT(64) NOT NULL,
                      gsize3              FLOAT(64) NOT NULL,
                      minx                FLOAT(64) NOT NULL,
                      miny                FLOAT(64) NOT NULL,
                      maxx                FLOAT(64) NOT NULL,
                      maxy                FLOAT(64) NOT NULL,
                      cdate               INTEGER NOT NULL,
                      layer_config        VARCHAR2(32) NULL,
                      optimal_array_size  INTEGER NULL,
                      stats_date          INTEGER NULL,
                      minimum_id          INTEGER NULL,
                      CONSTRAINT layers_uk1 UNIQUE (layer_id),
                      CONSTRAINT layers_uk2 UNIQUE(table_name,owner));

• LAYER_ID—The unique layer ID number assigned by ArcSDE.
• DESCRIPTION—An optional layer description string.
• TABLE_NAME—The name of the business table containing the geometry column.
• OWNER—The name of the user that owns the business table.
• SPATIAL_COLUMN—The name of the column in the feature table that is the geometry column.
• SRID—The ID of the spatial reference system used for the coordinate geometry in this table. It is a foreign key reference to the SPATIAL_REFERENCES table.
• STORAGE_TYPE—the type of storage used for this geometry column.

                   -1 = SDE Compressed Binary implementation
                    0 = Normalized geometry OGIS SQL 92 implementation
                    1 = binary geometry OGIS SQL 92 implementation
                    2 = SQL Spatial Type Implementation

• EFLAGS—The ArcSDE column type mask defining legal entity types in the spatial column. This column is mirrored as GEOMETRY_TYPE with different values for OGIS views of the data.
• GSIZE,GSIZ2, GSIZE3—Geometry grid index values.
• MINX, MINY, MAXX, MAXY—The extents of the spatial data.
• CDATE—The date the layer was created or registered in ArcSDE.
• OPTIMAL_ARRAY_SIZE—The buffer array size to use when transmitting data from this layer to the client application.
• LAYER_CONFIG—Table creation parameter keyword.
• MINIMUM_ID—Minimum feature ID for the layer.

SPATIAL_REF_SYS Table Schema

The SPATIAL_REF_SYS schema:

CREATE TABLE spatial_ref_sys ( srid               INTEGER NOT NULL,
                               description        VARCHAR(65),
                               auth_name          VARCHAR(1024),
                               auth_srid          INTEGER,
                               falsex             FLOAT NOT NULL,
                               falsey             FLOAT NOT NULL,
                               xyunits            FLOAT NOT NULL,
                               falsez             FLOAT NOT NULL,
                               zunits             FLOAT NOT NULL,
                               falsem             FLOAT NOT NULL,
                               munits             FLOAT NOT NULL,
                               srtext             VARCHAR(2048),
                               PRIMARY KEY (srid));

• SRID—An integer value that uniquely identifies each Spatial Reference System within a database.
• DESCRIPTION—Short textual description of the Spatial Reference (e.g. UTM Zone 17).
• AUTH_NAME—The name of the standard or standards body that is being cited for this reference system. For example, POSC would be a valid AUTH_NAME.
• AUTH_SRID—The ID of the Spatial Reference System as defined by the Authority cited in AUTH_NAME.
• FALSEX, FALSEY—The x and y offsets to use when transforming ground coordinates to internal system coordinates.
• XYUNITS—The scale factor to apply when transforming ground coordinates to internal system coordinates.
• FALSEZ—The z offset to use when transforming z values to internal system coordinates.
• ZUNITS—The scale factor to use when transforming z values to internal system coordinates.
• FALSEM—The measure offset to use when transforming measure values to internal system coordinates.
• MUNITS—The scale factor to use when transforming measure values to internal system coordinates.
• SRTEXT—The Well-Known Text representation of the Spatial Reference System.

Geometry_Columns Table Schema

The GEOMETRY_COLUMNS table schema:

CREATE TABLE geometry_columns ( f_table_catalog     VARCHAR(18),
                                f_table_schema      VARCHAR(18) NOT NULL,
                                f_table_name        VARCHAR(18) NOT NULL,
                                f_geometry_column   VARCHAR(18) NOT NULL,
                                srid                INTEGER NOT NULL,
                                storage_type        INTEGER NOT NULL,
                                geometry_type       INTEGER NOT NULL,
                                coord_dimension     INTEGER NOT NULL,
                                max_ppr             INTEGER NOT NULL,
                                primary key (f_table_catalog, f_table_schema,
                                             f_table_name, f_geometry_column) constraint geocol_pk,
                                foreign key (srid) references SPATIAL_REFERENCES
                                            (srid) constraint geocol_fk);

NOTE: The OpenGIS specification limits table and column names to 18 characters. ArcSDE supports 32 characters or less (based on DBMS).

• F_TABLE_CATALOG, F_TABLE_SCHEMA, F_TABLE_NAME—The fully qualified name of the feature table containing the geometry column.
• F_GEOMETRY_COLUMN—The name of the column in the feature table that is the geometry column.
• SRID—The ID of the spatial reference system used for the coordinate geometry in this table. It is a foreign key reference to the SPATIAL_REFERENCES table.
• STORAGE_TYPE—The type of storage used for this geometry column.

       0 = Normalized geometry OGIS SQL 92 implementation
       1 = binary geometry OGIS SQL 92 implementation
       2 = OGIS SQL Spatial Type Implementation

• GEOMETRY_TYPE—The type of geometry values stored in this column.

       0 = Geometry
       1 = point
       2 = curve
       3 = linestring
       4 = surface
       5 = polygon
       6 = collection
       7 = multipoint
       8 = multicurve
       9 = multilinestring
      10 = multisurface
      11 = multipolygon

• COORD_DIMENSION—The number of ordinates used in the complex, usually corresponds to the number of dimensions in the geometry. Note: applicable to normalized geometry schema only.
• MAX_PPR—Points per row; the number of points stored as ordinate columns in the geometry table. Note: applicable to normalized geometry schema only.

Geometry Storage

ArcSDE supports binary and SQL Geometry Type mechanisms for geometry storage. The geometry storage mechanism is identified when the layer is created. Not all storage mechanisms can be supported on all databases. The following table shows which storage schemas are available for each database:

Note: Oracle discontinued support for Normalized storage at the 9.2 release of their software. Consequently, ArcSDE 9.0 discontinued support for Normalized geometry schema on Oracle databases.

While the logical geometry model for all storage schemas is the same, not all ArcSDE functionality is available in all formats. For example, the OpenGIS specification does not define elevations and measures on coordinates. The following chart shows the functionality available for each storage mechanism:

*The specification for the SQL Spatial Type has been extended to support ArcSDE functionality. This is possible because the actual geometry type structure is hidden behind the SQL function interfaces.

Binary Geometry Schema

The OpenGIS Specification for Simple Features in SQL defines a Binary geometry schema as one of three schemas for managing spatial data. In the Binary geometry schema, the coordinate values for the shape are stored in a binary object (called a BLOB). The BLOB is managed in a separate Geometry Table. Access to the geometry from the Business Table is through a Foreign key called the Geometry ID, or GID.

The following tables illustrate the relationship between the Business Table and the Geometry Table in the Binary geometry schema:

The ArcSDE binary schema is the same geometry storage schema used in earlier ArcSDE releases. This schema implements the OpenGIS Binary schema, with additional functionality, and a compressed binary representation in place of the OpenGIS Well-Known Binary Representation for Geometry. The compressed binary representation provides support for geometry properties not defined by the OpenGIS specification, including: Elevations, Measures, Annotation, and CAD data. Functions are available in the C, JAVA, and SQL type API to convert this compressed binary representation into OpenGIS Well-Known Binary and Well-Known Text representations of Geometry.

The internal format of the ArcSDE binary is defined in ArcSDE compressed binary representation .

The schema for the ArcSDE binary schema geometry table is as follows:

CREATE TABLE F<layer#> ( fid            INTEGER NOT NULL,
                         numofpts       INTEGER NOT NULL,
                         entity         SMALLINT NOT NULL,
                         eminx          FLOAT(64) NOT NULL,
                         eminy          FLOAT(64) NOT NULL,
                         emaxx          FLOAT(64) NOT NULL,
                         emaxy          FLOAT(64) NOT NULL,
                         eminz          FLOAT(64) NOT NULL,
                         emaxz          FLOAT(64) NOT NULL,
                         min_measure    FLOAT(64) NOT NULL,
                         max_measure    FLOAT(64) NOT NULL,
                         area           FLOAT(64) NOT NULL,
                         len            FLOAT(64) NOT NULL,
                         points         <BLOB DATATYPE>
                         PRIMARY KEY (fid));

• FID—The ID of the geometry.
• NUMOFPTS—The number of coordinate values in the geometry.
• ENTITY—An accumulated mask of geometry and coordinate type flags.
• EMINX—Minimum X ordinate value.
• EMINY—Minimum Y ordinate value.
• EMAXX—Maximum X ordinate value.
• EMAXY—Maximum Y ordinate value.
• EMINZ—Minimum Z ordinate value (Initializes to NULL).
• EMAXZ—Maximum Z ordinate value (Initializes to NULL).
• MIN_MEASURE—Minimum M ordinate value (Initializes to NULL).
• MAX_MEASURE—Maximum M ordinate value (Initializes to NULL).
• AREA—Calculated area for the geometry.
• LEN—Calculated length or perimeter for the geometry.
• Points—Geometry coordinate values.

SQL Spatial Type

The OpenGIS Specification for Simple Features in SQL defines a SQL Spatial type with associated SQL functions as one of three schemas for managing spatial data. In the SQL Spatial Type implementation, the coordinate values for the shape are managed as an SQL object, accessible only through SQL function invocations.

The SQL Spatial Type is just another column in the DBMS table, with well-defined properties and behavior. The actual representation of the geometry and storage of the coordinate data is DBMS specific and never exposed to the application. This hidden implementation allows the extension of the OpenGIS Specification for Simple Features in SQL with Annotation, CAD, elevations, and measures.

The following example illustrates how a geometry type looks in the table. The index is not shown, because it is now managed by the DBMS as an index on the column:

Conformance to standards

The DB2 and Informix spatial type implementations are based on the OpenGIS and SQL/MM spatial type specifications. These implementations have been conformance tested by the OpenGIS. See www.opengis.org for further conformance information.

Consult Oracle documentation and www.opengis.org for Oracle Spatial conformance information.

SQL Geometry Subtypes

The OpenGIS specification for the SQL Type implementation defines the following subtype relationships for the geometry type:

Subtype relationships between Types

ST_Curve, ST_Surface, ST_MultiCurve, and ST_MultiSurface are defined to be non-instantiable types. No constructors are defined for these types.

SQL Geometry Functions

Functions may be associated with abstract data types; for example, a geometry type could have functions to return the area or length. Functions that operate on the type are also possible. For example, functions that compute intersections or the union of two geometries, returning a new geometry as the result such as returning all parcels that intersect a specified polygon (named MyPolygon):

SELECT Parcel.Name, Parcel.Id
FROM Parcels
WHERE Intersects(Parcels.Geometry, MyPolygon)

The spatial functions are fully integrated into the SQL language allowing them to be used in SELECT statements (as above) and any other place a SQL expression would be used, such as the following INSERT statement:

INSERT INTO Countries (Name, Geometry)
 VALUES ( Kenya, PolygonFromText(POLYGON ((x y, x y, x y, ..., x y)), 14))

SQL Spatial Types and Functions are only available on extensible DBMS products. At this time, support is limited to Informix Dynamic Server, and DB2 Data Joiner. As other extensible DBMS products become available, they will be evaluated to determine if the necessary functionality exists to provide spatial type support.

The following functions are defined in the OpenGIS specification for SQL for spatial types. Functions are identified with an ST_ prefix for conformance with the SQL/MM specification, but the function signatures are the same in both specifications. Functions prefixed with SE_ are not part of the SQL/MM or OpenGIS specifications, but have been added to ArcSDE implementations on IBM DB2 and Informix to support extended functionality.