GEOMETRY_COLUMNS for Oracle Spatial

Recently, I had cause to look at the implementation of the OGC/SQLMM Geometry_Columns and Spatial_Ref_Sys metadata views in Oracle.

ISO/TC 211, 6.2 Architecture � SQL implementation using Geometry Types, 6.2.1 Overview says:

“This standard defines a schema for the management of feature table, Geometry, and Spatial Reference System information in an SQL-implementation with a Geometry Type extension.”

Visually, this is:

The definition of the Geometry_Columns table is as follows:

 CREATE TABLE GEOMETRY_COLUMNS (
   F_TABLE_CATALOG   CHARACTER VARYING NOT NULL,
   F_TABLE_SCHEMA    CHARACTER VARYING NOT NULL,
   F_TABLE_NAME      CHARACTER VARYING NOT NULL,
   F_GEOMETRY_COLUMN CHARACTER VARYING NOT NULL,
   G_TABLE_CATALOG   CHARACTER VARYING NOT NULL,
   G_TABLE_SCHEMA    CHARACTER VARYING NOT NULL,
   G_TABLE_NAME      CHARACTER VARYING NOT NULL,
   STORAGE_TYPE      INTEGER,
   GEOMETRY_TYPE     INTEGER,
   COORD_DIMENSION   INTEGER,
   MAX_PPR           INTEGER,
   SRID              INTEGER NOT NULL
   REFERENCES SPATIAL_REF_SYS,
   CONSTRAINT GC_PK PRIMARY KEY
    (F_TABLE_CATALOG, F_TABLE_SCHEMA, 
     F_TABLE_NAME, F_GEOMETRY_COLUMN)
 )

Oracle implements this table via a table called MDSYS.OGC_GEOMETRY_COLUMNS as follows:

 CREATE TABLE MDSYS.OGIS_GEOMETRY_COLUMNS (
   F_TABLE_SCHEMA    VARCHAR2(64), 
   F_TABLE_NAME      VARCHAR2(64), 
   F_GEOMETRY_COLUMN VARCHAR2(64), 
   G_TABLE_SCHEMA    VARCHAR2(64), 
   G_TABLE_NAME      VARCHAR2(64), 
   STORAGE_TYPE      NUMBER, 
   GEOMETRY_TYPE     NUMBER, 
   COORD_DIMENSION   NUMBER, 
   MAX_PPR           NUMBER, 
   SRID              NUMBER, 
   CONSTRAINT FK_SRID FOREIGN KEY (SRID) REFERENCES
     MDSYS.OGIS_SPATIAL_REFERENCE_SYSTEMS (SRID) 
 )

A few things must be noted:

• There is no global GEOMETRY_COLUMNS view only Oracle-specific USER_GEOMETRY_COLUMNS and ALL_GEOMETRY_COLUMNS public views based on MDSYS.OGC_GEOMETRY_COLUMNS table.
• The MAX_PPR and G_TABLE_SCHEMA/G_TABLE_NAME columns are no longer of any use as Oracle’s implementation of the Normalised model has long been dropped.
• Oracle does not have concept of a CATALOG so F_TABLE_CATALOG was never supported.
• STORAGE_TYPE should always be NULL = geometry types implementation (OGC SFS SQL 1.2)
• Geometry_Type column is declared as a Number/Integer
• For the GEOMETRY_TYPE column, the “use of a non-leaf Geometry class name from the Geometry Object Model for a geometry column implies that domain of the column corresponds to instances of the class and all of its subclasses” OGC 06-104r3, 7.1.3.3 Field description, Page 29

On top of this, no Oracle functions know of, or use, MDSYS.OGC_GEOMETRY_COLUMNS or the related views. Rather, all Oracle Spatial’s functionality uses the Oracle-specific metadata tables, the most basic being:

 CREATE TABLE mdsys.sdo_geom_metadata_table (
    owner 	varchar2(32),
    table_name	varchar2(32),
    column_name	varchar2(32),
    diminfo 	mdsys.sdo_dim_array,
    srid 	number );

This table is needed mainly for creation of indexes. It is populated by user or client software: there is nothing automatic such that if a table with an sdo_geometry column (or more) is created, records are created in XXX_SDO_GEOM_METADATA>

In addition, Oracle does not automatically synchronise GEOMETRY_COLUMNS as DML is executed against the XXXX_SDO_GEOM_METADATA views. With manual DML executed against actual OGC_GEOMETRY_COLUMNS table or views generating the following error:

 Error starting at line 1 in command:
 insert into user_geometry_columns ( 
 F_TABLE_SCHEMA,F_TABLE_NAME,F_GEOMETRY_COLUMN,STORAGE_TYPE,GEOMETRY_TYPE,COORD_DIMENSION,SRID
 )
 values('CODESYS','MORTON_P','GEOM',NULL,1,2,4326)
 Error report:
 SQL Error: ORA-02291: integrity constraint (MDSYS.FK_SRID) violated - parent key not found
 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
 *Cause:    A foreign key value has no matching primary key value.
 *Action:   Delete the foreign key or add a matching primary key.

(There is no SRID in Spatial_Ref_Sys, which, itself, generates an error if you try and insert a correct spatial reference into it.)

So, how can one create a populated GEOMETRY_COLUMNS table such that open source or standards based software will be able to correctly interrogate the table/view?

One approach is to build a public view called GEOMETRY_COLUMNS over the existing Oracle metadata that is maintained as part of the normal course of events as follows:

 CREATE VIEW GEOMETRY_COLUMNS 
 AS
 SELECT asgm.owner        as F_TABLE_SCHEMA,
        asgm.table_name   as F_TABLE_NAME,
        asgm.column_name  as F_GEOMETRY_COLUMN,
        NULL              as STORAGE_TYPE,
        Codesys.Get_Geometry_Type(asgm.owner,
                          asgm.table_name,
                          asgm.column_name)
                          as GEOMETRY_TYPE,
        (SELECT count(*) 
           FROM TABLE(asgm.diminfo)
        )                 as COORD_DIMENSION,
        asgm.SRID         as SRID
   FROM ALL_SDO_GEOM_METADATA asgm; 

 CREATE PUBLIC SYNONYM geometry_columns 
 FOR codesys.geometry_columns;

(For performance reasons, this view would be better implemented as an “on demand” refreshable materialized view.)

The function Get_Geometry_Type() returns the correct OGC Geometry_Type. This function is as follows:

 create or replace
 Function Get_Geometry_Type( p_owner    in varchar2,
                              p_object   in varchar2,
                              p_geometry in varchar2,
                              p_text     in integer := 1)
  return varchar2 deterministic
 AUTHID CURRENT_USER                       
   /* See:
      7.1.3 Geometry columns information
      7.1.3.3 Field description
      Table 4: Geometry type codes
      OGC SFS SQL 1.2 Document 
   */
 As
   v_geometryType       integer;
   v_GeometryTypeString varchar2(100);
   v_exists             integer;
   v_sql                varchar2(4000);
 Begin
   IF ( ' || p_geometry || ' is null ) Then
     Return 0;
   End If;
   BEGIN
     SELECT 1
       INTO v_exists
       FROM all_tab_columns a
      WHERE a.owner       = UPPER(p_owner)
        AND a.table_name  = UPPER(p_object)
        AND a.column_name = UPPER(p_geometry)
        AND a.data_type = 'SDO_GEOMETRY';
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
            -- raise_application_error(-20001,'No table or view (' || p_owner || '.' || p_object || ') exists with sdo_geometry object called ' || p_geometry,true);
            return 0;
   END;
   v_sql := '
 SELECT SUBSTR(GeometryType,1,100),
        OGC_GeometryType 
   FROM (
   SELECT b.GeometryType,
          CASE b.GeometryType
               WHEN ''GEOMETRY'' THEN 0
               WHEN ''POINT'' THEN 1
               WHEN ''LINESTRING'' THEN 2
               WHEN ''POLYGON'' THEN 3
               WHEN ''MULTIPOINT'' THEN 4
               WHEN ''MULTILINESTRING'' THEN 5
               WHEN ''MULTIPOLYGON'' THEN 6
               WHEN ''GEOMCOLLECTION'' THEN 7
               WHEN ''CURVE'' THEN 13
               WHEN ''SURFACE'' THEN 14
               WHEN ''POLYHEDRALSURFACE'' THEN 15
               WHEN ''GEOMETRYZ'' THEN 1000
               WHEN ''POINTZ'' THEN 1001
               WHEN ''LINESTRINGZ'' THEN 1002
               WHEN ''POLYGONZ'' THEN 1003
               WHEN ''MULTIPOINTZ'' THEN 1004
               WHEN ''MULTILINESTRINGZ'' THEN 1005
               WHEN ''MULTIPOLYGONZ'' THEN 1006
               WHEN ''GEOMCOLLECTIONZ'' THEN 1007
               WHEN ''CURVEZ'' THEN 1013
               WHEN ''SURFACEZ'' THEN 1014
               WHEN ''POLYHEDRALSURFACEZ'' THEN 1015
               WHEN ''GEOMETRY'' THEN 2000
               WHEN ''POINTM'' THEN 2001
               WHEN ''LINESTRINGM'' THEN 2002
               WHEN ''POLYGONM'' THEN 2003
               WHEN ''MULTIPOINTM'' THEN 2004
               WHEN ''MULTILINESTRINGM'' THEN 2005
               WHEN ''MULTIPOLYGONM'' THEN 2006
               WHEN ''GEOMCOLLECTIONM'' THEN 2007
               WHEN ''CURVEM'' THEN 2013
               WHEN ''SURFACEM'' THEN 2014
               WHEN ''POLYHEDRALSURFACEM'' THEN 2015
               WHEN ''GEOMETRYZM'' THEN 3000
               WHEN ''POINTZM'' THEN 3001
               WHEN ''LINESTRINGZM'' THEN 3002
               WHEN ''POLYGONZM'' THEN 3003
               WHEN ''MULTIPOINTZM'' THEN 3004
               WHEN ''MULTILINESTRINGZM'' THEN 3005
               WHEN ''MULTIPOLYGONZM'' THEN 3006
               WHEN ''GEOMCOLLECTIONZM'' THEN 3007
               WHEN ''CURVEZM'' THEN 3013
               WHEN ''SURFACEZM'' THEN 3014
               WHEN ''POLYHEDRALSURFACEZM'' THEN 3015
               ELSE 0
           END as OGC_GeometryType
      FROM (SELECT DISTINCT
                  CASE MOD(a.' || p_geometry || '.sdo_gtype,10) 
                       WHEN 0 THEN NULL
                       WHEN 1 THEN ''POINT'' 
                       WHEN 5 THEN ''MULTIPOINT'' 
                       WHEN 2 THEN CASE WHEN codesys.isCompound(a.' || p_geometry || '.sdo_elem_info) = 1 THEN ''CURVE''             ELSE ''LINESTRING''      END 
                       WHEN 6 THEN CASE WHEN codesys.isCompound(a.' || p_geometry || '.sdo_elem_info) = 1 THEN ''MULTICURVE''        ELSE ''MULTILINESTRING'' END
                       WHEN 3 THEN CASE WHEN codesys.isCompound(a.' || p_geometry || '.sdo_elem_info) = 1 THEN ''SURFACE''           ELSE ''POLYGON''         END
                       WHEN 7 THEN CASE WHEN codesys.isCompound(a.' || p_geometry || '.sdo_elem_info) = 1 THEN ''POLYHEDRALSURFACE'' ELSE ''MULTIPOLYGON''    END
                       WHEN 4 THEN ''GEOMETRYCOLLECTION''
                       ELSE NULL
                   END || 
                   CASE WHEN a.' || p_geometry || '.get_dims() > 2 
                             AND 
                             ( a.' || p_geometry || '.get_dims() = MOD(trunc(a.' || p_geometry || '.sdo_gtype/100),10) )
                        THEN ''Z'' 
                        ELSE CASE WHEN MOD(trunc(a.' || p_geometry || '.sdo_gtype/100),10) > 0 
                                  THEN ''M'' ELSE NULL 
                               END 
                    END as GeometryType
              FROM ' || p_owner || '.' || p_object || ' a
          ) b
     ORDER BY 1 DESC
     )
     WHERE rownum < 2';
 dbms_output.put_line(v_sql);
     EXECUTE IMMEDIATE v_sql INTO v_GeometryTypeString, v_geometryType;
     RETURN CASE WHEN p_text = 1 
                 THEN CASE WHEN v_geometryType = 0 THEN 'GEOMETRY' ElSE v_GeometryTypeString END
                 ELSE TO_CHAR(v_geometryType) 
             END;
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         RETURN CASE WHEN p_text = 1 
                     THEN CASE WHEN v_geometryType = 0 or v_geometryType is NULL THEN 'GEOMETRY' ELSE v_GeometryTypeString END
                     ELSE TO_CHAR(v_geometryType) 
                 END;
 END Get_Geometry_Type;

Note that this function implements the OGC requirement for the GEOMETRY_TYPE column’s value to be “[…] a non-leaf Geometry class name from the Geometry Object Model for a geometry column implies that domain of the column corresponds to instances of the class and all of its subclasses” OGC 06-104r3, 7.1.3.3 Field description, Page 29

In other words, if the geometry_type is MULTIPOLYGON it includes the sub-type POLYGON.

Selecting on this table shows that it works:

 select * 
   from geometry_columns gc
  where gc.f_table_schema = 'CODESYS';

Note that, geometry_type is presented as text when the standard says INTEGER, recompiling the view using:

        CAST(Codesys.Get_Geometry_Type(asgm.owner,
                          asgm.table_name,
                          asgm.column_name,
                          0) AS INTEGER) 
                          as GEOMETRY_TYPE,

Will present the data correctly as an INTEGER.

After having done this work on a view I now have something that is:

• Sustainable in that it is “automatic”, a part of the normal metadata management within Oracle.
• And presents the right information to open source and OGC compliant software.

I hope this is of use to someone.