Creating SDO_GEOMETRIES with an Oracle Non-Spatial Writer

Summary

This post (actually it’s not a question) describes how to create SDO_GEOMETRIES when writing features with the Oracle Non-Spatial Writer, by post processing the Oracle table with SQL statements.

All functionality is contained in a few simple steps inside a single workspace.

This post provides both a procedure to renew an entire table each time the workspace is run, and a procedure to append features to an existing table each time the workspace is run.

Finally, this post shows how to spatially enable the table by maintaining a record for the table in USER_SDO_GEOM_METADATA, and by optionally adding a spatial index to the table.

I hope this is of use to someone.

Preliminary Remarks

• In this post the Oracle table is called TEST_GEOM, the GML field is called GML, and the SDO_GEOMETRY field is called GEOMETRY. These names can be changed as required. It is possible to put these names in Private Parameters, and use those parameters wherever a name needs to be provided (except for the field names in the Oracle Non-Spatial Writer); this prevents typing errors.
• All SQL commands in this post are available under the Oracle 11g and 12c Locator license.
• Most SQL statements in this post start with a dash ‘-’. A dash in front of a(n) SQL statement tells FME to ignore SQL errors that result from that statement. This functionality is (ab)used here to make the entire process as compact as possible, to have one workspace which can be used for both the initial run and all subsequent runs.

Steps when renewing the entire table

The steps below are used when renewing the entire table TEST_GEOM each time the workspace is run.

1. Add a GeometryExtractor Transformer to the workspace. Convert the geometries to GML format in the GML field, and remove the geometry from the features.
2. Add an Oracle Non-Spatial Writer to the workspace. Set the Feature Operation to ‘Insert’, set Table Handling to ‘Drop and Create’. Change the Attribute Definition of the User Attributes to ‘Manual’, then set the data type of the GML field to CLOB.
3. Add these SQL commands to the ‘SQL To Run After Write’ parameter:

FME_SQL_DELIMITER ;
- ALTER TABLE TEST_GEOM ADD GEOMETRY SDO_GEOMETRY ;
- UPDATE TEST_GEOM 
    SET GEOMETRY = SDO_UTIL.FROM_GMLGEOMETRY(GML) ;
- ALTER TABLE TEST_GEOM DROP COLUMN GML ;

Steps when appending features to an existing table (or when initially creating a table for this use)

With the steps below new features can be appended to an existing table TEST_GEOM, and a table TEST_GEOM intended for appending features can be created.

 

1. Add a GeometryExtractor Transformer to the workspace. Convert the geometries to GML format in the GML field, and remove the geometry from the features.
2. Add an Oracle Non-Spatial Writer to the workspace. Set the Feature Operation to ‘Insert’, set Table Handling to ‘Create If Needed’. Change the Attribute Definition of the User Attributes to ‘Manual’, then set the data type of the GML field to CLOB.
3. Add these SQL commands to the ‘SQL To Run After Write’ parameter:

FME_SQL_DELIMITER ;
- ALTER TABLE TEST_GEOM ADD GEOMETRY SDO_GEOMETRY ;
- UPDATE TEST_GEOM 
    SET GEOMETRY = SDO_UTIL.FROM_GMLGEOMETRY(GML) 
    WHERE GEOMETRY IS NULL AND GML IS NOT NULL ;
- ALTER TABLE TEST_GEOM DROP COLUMN GML ;.

1. Add the following SQL command to the ‘SQL To Run Before Write’ parameter:

- ALTER TABLE TEST_GEOM ADD GML CLOB;

Spatially enabling the table

The steps above do not spatially enable the table.

To spatially enable the table a record for this table needs to be present in table USER_SDO_GEOM_METADATA. This record can be created by adding these SQL statements to the end of ‘SQL To Run After Write’ parameter:

- DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_GEOM' ;
- INSERT 
    INTO USER_SDO_GEOM_METADATA
      (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
    VALUES 
      (    
        'TEST_GEOM', 
        'GEOMETRY', 
        ( 
            SELECT MDSYS.SDO_DIM_ARRAY
           (
                MDSYS.SDO_DIM_ELEMENT('X', MINX, MAXX, 0.001),
                MDSYS.SDO_DIM_ELEMENT('Y', MINY, MAXY, 0.001)
            ) AS DIMINFO
            FROM 
            ( 
                SELECT TRUNC( MIN( V.X ) - 1,0) AS MINX,
                    ROUND( MAX( V.X ) + 1,0) AS MAXX,
                    TRUNC( MIN( V.Y ) - 1,0) AS MINY,
                    ROUND( MAX( V.Y ) + 1,0) AS MAXY
                FROM 
                (SELECT SDO_AGGR_MBR(G.GEOMETRY) AS MBR FROM TEST_GEOM G) T,
                TABLE(MDSYS.SDO_UTIL.GETVERTICES(T.MBR)) V
            )
        ),
        (
            SELECT G.GEOMETRY.SDO_SRID 
            FROM TEST_GEOM G 
            WHERE ROWNUM = 1
        )
    )
;

Credits: the SQL statement to create DIMINFO comes from SpatialDB Advisor’s excellent website: 

https://spatialdbadvisor.com/oracle_spatial_tips_tricks/154/making-sdo-geometry-metadata-update-generic-code

Creating a Spatial Index

A Spatial Index enables efficient query performance on the Spatial geometries. A record in table USER_SDO_GEOM_METADATA, as created above, is a prerequisite for creating a Spatial Index.

Add these SQL statements to the end of ‘SQL To Run After Write’ parameter, after the statement inserting a record for the table in USER_SDO_GEOM_METADATA.

 

When the workspace renews an entire table each time it is run:

- CREATE INDEX TEST_GEOM_SP_IDX ON TEST_GEOM(GEOMETRY) 
    INDEXTYPE IS MDSYS.SPATIAL_INDEX ;

 

When the workspace appends data to an existing table when it is run:

- DROP INDEX TEST_GEOM_SP_IDX FORCE ;
- CREATE INDEX TEST_GEOM_SP_IDX ON TEST_GEOM(GEOMETRY) 
    INDEXTYPE IS MDSYS.SPATIAL_INDEX ;