Skip to main content
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.

0684Q00000ArDcMQAV.png

  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.

0684Q00000ArEKWQA3.png

 

  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 ;

 

But the question is: Why would you do all of this if FME has an Oracle Spatial Objects writer?

As far as I know that writer takes care of all of the additional steps, mentioned in this article.


Hi @erik_jan,

 

Thank you for your response.

 

You are absolutely right, FME does have an Oracle Spatial Objects writer which takes care of all the additional steps. The only trouble is, I do not have access to that writer, as it only becomes available in the FME Database Edition, whereas I have to make do with the FME Professional Edition.

 

That restriction was the base of a quest to find out whether it is possible to create SDO_GEOMETRIES with the FME Professional Edition. It is.

 

I started with writing the data to a PostGIS database, and then transferring it to Oracle with OGR2OGR, but that process proved to be quite tricky and error prone.

Next I thought of extracting the geometry to GML, export the GML to Oracle with an Oracle Non-Spatial writer (as I still do in the article), and convert the GML in Oracle with a Before Insert trigger. That worked, but in that case I had to do the post processing in Oracle, out of sight of FME.

When I found out I could make the ‘SQL To Run After Write’ parameter sit up and do tricks, I had found my solution: I now had a way of creating SDO_GEOMETRIES without an Oracle Spatial Objects writer, and I could contain it completely in an FME Workspace.

And the solution was surprisingly simple.

 

When I studied the forum for others who might have written about this solution, I found out that nobody had, although I hit upon some hints that others must have thought in the same direction.

I thought it would be nice to share my findings with the community, as there may be other users who face the same challenge.


Hi @erik_jan,

 

Thank you for your response.

 

You are absolutely right, FME does have an Oracle Spatial Objects writer which takes care of all the additional steps. The only trouble is, I do not have access to that writer, as it only becomes available in the FME Database Edition, whereas I have to make do with the FME Professional Edition.

 

That restriction was the base of a quest to find out whether it is possible to create SDO_GEOMETRIES with the FME Professional Edition. It is.

 

I started with writing the data to a PostGIS database, and then transferring it to Oracle with OGR2OGR, but that process proved to be quite tricky and error prone.

Next I thought of extracting the geometry to GML, export the GML to Oracle with an Oracle Non-Spatial writer (as I still do in the article), and convert the GML in Oracle with a Before Insert trigger. That worked, but in that case I had to do the post processing in Oracle, out of sight of FME.

When I found out I could make the ‘SQL To Run After Write’ parameter sit up and do tricks, I had found my solution: I now had a way of creating SDO_GEOMETRIES without an Oracle Spatial Objects writer, and I could contain it completely in an FME Workspace.

And the solution was surprisingly simple.

 

When I studied the forum for others who might have written about this solution, I found out that nobody had, although I hit upon some hints that others must have thought in the same direction.

I thought it would be nice to share my findings with the community, as there may be other users who face the same challenge.

Thank you for sharing!


Reply