Question

Using Oracle Spatial instead of ESRI sde geodatabase writer?

  • 10 March 2016
  • 6 replies
  • 12 views

I have a large workspace needed to load tens of millions of spatial records into our ESRI SDE geodatabase (built on Oracle sdo_geometry). I have noticed a large performance advantage in using the Oracle Spatial writer compared to the ESRI SDE/geodatabase writers in FME but am wary if there are any concerns in using the Oracle Spatial writer as the tables are registered with the geodatabase.

As long as I creat the tables using ESRI tools and switch them into LOAD_ONLY mode would it be safe in using the Oracle Spatial writer or would I be bypassing certain SDE protocol that will break the tables for use in SDE?


6 replies

Userlevel 4

Hi

It might be possible, but I'm not sure I'd recommend it. Even if it works now, you risk that it does something subtly different down the road.

To improve the Geodatabase writer performance, have you looked at this article?

David

Userlevel 2
Badge +12

Yes, you can do it this way as long as you are using SDO_GEOMETRY and not the SDE GEOMETRY type.

You could even have FME create the tables, but then you will need to use the ESRI tools to register the tables afterwards.

I noticed this approach can be much faster than the direct SDE writing.

Obviously you can not use versioning on the SDE data if you use the Oracle Spatial writer.

Badge

I would also look at the spatiallite. It seems to act like the file geodatabase and is easily read by programs like QGIS. I have switched to them and really like it because it works well with ogr2ogr when sending to a postgres databases, i am sure it would work just as well going to a oracle database also.

Badge +3

As mentioned above you can amend SDO_GEOMETRY and it works fine in production. It is the the ESRI recommended way if data manipulation is required by non-ESRI 3rd party software.

However you have to take care that any geometry is valid for SDE.

SDE validation is stricter:

http://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/geometry-validation.htm

than SDO:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objgeom.htm#BGHFDDBF

If geometries may not be always valid it is worth to send them through a GeometryValidator. I don't know if there is any rule combo that is suitable for SDE validation (there is a request for that: https://knowledge.safe.com/content/idea/18827/geometryvalidator-3.html ) but the OGC rules should do the vast majority of the job.

The tool that can be used in FME to do real SDE validation is an ArcSDE command line tool:

http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/Support_files/datamgmt/sdelayer.htm

sdelayer -o feature_info -r invalid ...

It reveals the FID of invalid geometries only but no information about where the error is.

Thanks for everyones responses so far!

I have now tried this practically and am struggling when using the Oracle Spatial writer when writing to the same registered table using parallel processing (via the WorkspaceRunner). It seems that I have to manually enter a unique value for the 'OBJECTID' field for each record. This would be ok if running just a single workspace by using a Counter transformer. But when using the WorkspaceRunner which spawns multiple processes that are INSERTING at the same time, I cannot ensure that a unique numeric value is entered. Any ideas?

Userlevel 4

Hi

You should never define the value of OBJECTID yourself, it is bad practice that can lead to primary key collisions, as you've seen. If you use the ESRI family of writers they will automatically use the appropriate SDE functions to get the next value from the OBJECTID sequence for that particular table, and in this case any OBJECTID that you send to the writer will simply be ignored.

However, if you use the Oracle Spatial writer (i.e. a non-ESRI writer) you've effectively short-circuited this mechanism and all bets are off: You will  have to ask SDE to give you the next OBJECTID value for that particular table yourself. The proper way of doing this is to call the SDE function next_row_id(), which will enable SDE to know that this OBJECTID has been used and not try to re-use it later.

The important bit is to know that, for performance reasons, the OBJECTID chosen by SDE isn't based on MAX(OBJECTID)+1, but on the last known value used in the sequence associated with that feature class.

Example that you can use in e.g. an SQLExecutor to get next OBJECTID for a given table:

SELECT sde.version_user_ddl.Next_row_id('MY_SCHEMA_NAME',
    (SELECT registration_id
     FROM   sde.table_registry
     WHERE  table_name = 'MY_TABLE_NAME'
     AND    owner = 'MY_SCHEMA_NAME')) AS OBJECTID
FROM dual

Using this method you ensure that all processes will get unique OBJECTID values, even when running concurrently, and you will avoid the SDE sequence coming out of sync with the actual values used in the feature class table.

David

Reply