Skip to main content
  • EN

So, I've read some of the earlier responses to similar questions but I don't know that I gleaned a definitive answer for this.

The setup is in Oracle there are ArcGIS generated views of Versioned spatial data tables Eg. TableName_EVW and that View has SHAPE as an ST_Geometry (instead of Oracle's own SDO_Geometry).

Yes, I know I can read this with the SDE Reader, but I need to get around SDE at the moment as it is misbehaving in the Covid world of VPNs whereas direct connection via native Oracle works MUCH better.

So:

  • All the FME Oracle Readers tried recognise the SHAPE field, but the Reader log tells me that it is skipping over the SHAPE field (presumably because it isn't SDO_Geometry data type)........despite that the log also tells me it recognises the Data Type as ST_Geometry. So the Readers by default return the non-spatial fields, and no geometries because it skips over the SHAPE field.
  • SQLCreator/SQLExecutor methods don't work that well either. Same result even when use an explicit "SELECT SHAPE FROM TableName_EVW". It still won't recognise SHAPE as a geometry despite it being ST_Geometry.

The best method was something @david_r suggested to a post 4 years ago, and that was to Cast the geometry to WKB using sde.st_asbinary within the SELECT statement, and then cast that to FME Feature Geometry within FME using GeometryReplacer

However, the overhead this creates on the Oracle Server to Cast 1,000,000+ spatial records means that it loads the Server with heavy function calculation and vastly reduces the Read time meaning that it isn't as much as improvement over the SDE Reader as hoped.

So........I'm looking for what would be the recommended method in 2020?

As far as I know, ESRI's ST_Geometry storage format is proprietary and not publicly available, which explains why it's necessary to use their libraries, either on the client (e.g. the ArcSDE reader in FME) or in the database (e.g. casting to WKB). Unfortunately I do not know of any other solutions.

As far as I know, ESRI's ST_Geometry storage format is proprietary and not publicly available, which explains why it's necessary to use their libraries, either on the client (e.g. the ArcSDE reader in FME) or in the database (e.g. casting to WKB). Unfortunately I do not know of any other solutions.

Thanks @david_r​ . Having played with this some more, I could similarly only get a results out by sde.ST_AsBinary(SHAPE) or sde.ST_AsText(SHAPE) , with the WKB option being slightly faster. However, as soon as I use one of these functions, the Oracle Reader reads much slower than the SDE Reader so I'm back to using the SDE Reader instead unfortunately whenever the Feature Class has in excess of say, 10,000 Features. For which the SDE Reader is still painfully slow, but the fastest option in my case.

 

Short of creating an extra Oracle server-side script that inserts the SHAPE into a separate SDO_Geometry table so that not creating WKB geometries on-the-fly with a SELECT statement, it appears that is about the limit of options. I'm just really surprised there isn't a better way to transform ST_Geometry client-side by reading the raw binary data and transforming it there: SELECT SHAPE is light-years faster in execution to extract the Oracle data than SELECT sde.ST_AsBinary(SHAPE).

Reply


Community Stats

30,121
Posts
114,324
Replies
37,749
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.