SpatialDB Advisor

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.