I have SDE.ST_GEOMETRY polyline FCs (tables) in an Oracle 18c geodatabase.
I'm building various SQL queries on the tables. It would help if I could use Oracle Spatial/SDO_GEOMETRY functionality like Spatial's linear referencing functions.
Alternatively, what are my options for generating a separate SDO_GEOMETRY column so that I can use Oracle Spatial functions?
Does anyone have any thoughts on those options? Or are there any other options that I've overlooked?
5. I suppose I could convert #1 to a custom function. And then create a function-based spatial index on the function to precompute the SDO_GEOMETRY.
Downside: Getting Oracle and ArcGIS to actually use the index is tricky. Function-based spatial indexes - Tips. And as far as I can tell, function-based indexes aren't intended to be used this way. A pre-computed column in a table is more suitable.
6. Store the geometry as JSON in an invisible text field in the FC. Use a function-based spatial index to efficiently convert the JSON to SDO_GEOMETRY on the fly.
- Oracle: Output LRS to a text-based format
Downside: I tested it on a FC with 15,000 features and it was slower than I'd hoped.
I stored the JSON representation of the SDO_GEOMETRY in a text field in the FC. And then converted it to SDO_GEOMETRY on-the-fly in a query using sdo_util.from_json(). Unfortunately, it still took 4.5 seconds to run the query on all features. I was hoping it would be closer to 1 second, which is how long it takes to select a true pre-computed shape column from a table.
alter table atn_json_text add json nclob invisible; --ArcGIS uses NCLOB for large text columns, so that's what I did too: https://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-oracle/data-types-oracle.htm#:~:text...
set json = sdo_util.to_json(wkt_lrs(sde.st_astext(shape),sde.st_srid(shape))); --wkt_lrs is a custom function: https://i.stack.imgur.com/dOfPg.png source: https://gis.stackexchange.com/a/428825/62572
select sdo_util.from_json(json) from atn_json_text;
I didn't try creating a function-based index. Reason: As far as I can tell, an FBI would only help me if I'm only selecting the geometry column, not any other columns (which would be rare).
Whereas, if I select other columns too, such as an ID column (a much more common use case), then the FBI won't be used, which isn't what I want.
When you add columns not in the index, the db has to read the table after reading the index. And as in your case the optimizer thinks it will return all the rows, it is easier for the db to just read the table. Source.
But I'm not an expert on FBIs or indexes. So I might have misunderstood something.
7. For what it's worth, I tested a similar solution, but using WKB. Unfortunately, it isn't much faster than the JSON option:
set sdo_blob = sdo_util.to_wkbgeometry(sdo_cs.make_2d(sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING'))));
Execution time: 3.5 to 5.5 seconds (varies)
Even if I did want to use that option, I'd need to wait for a Oracle to fix a few issues:
You didn't mention why the geodatabase needs to keep the ST_GEOMETRY format for that specific FC. The same Enterprise Geodatabase can have both feature classes with the geometry encoded in ESRI format (ST_Geometry) or in the database's proprietary format (in the case of Oracle, SDO_Geometry). The entry in the sde_dbtune table only defines the default format when a new FC is created. OK, such a change means changing the table structure and recreating the spatial index. If this change does not impact other applications or any published geoservices, it seems to be the simplest solution.
Yeah, good point. We have existing dependencies on the ST_GEOMETRY shapes. For example, we have spatial queries between the FCs in question and other FCs…using ST_GEOMETRY spatial operators/functions. I don’t think we could easily do spatial queries between the two different data types. So I’m reluctant to switch some of the FCs from ST_GEOMETRY to SDO_GEOMETRY.
If your spatial queries are performed by ArcGIS tools it would be no problem, because the tool would perform the needed conversions. But If you use SQL with ST_Geometry it seems a real issue to deal. The set of native functions of spatial databases such Oracle Spatial/Graph and PostGIS are much more complete than the ST_Geometry ones. So, if one intends to use SQL, it seems to choose the native spatial database geometry format at the beginning of the project is the best choice.
Yeah. That would be nice.
My organization chose ST_GEOMETRY years ago...and I think it was a perfectly reasonable decision at that time.
- ST_GEOMETRY was recommended by Esri.
- Oracle Spatial wasn’t free then.
- ST_GEOMETRY is easier for non-SQL experts to use than SDO_GEOMETRY (ST_GEOMETRY is simpler and the docs are easier to parse).
Even if that wasn’t the case, it would take some effort to switch all the FCs over to a new spatial type (and would likely have unforeseen complications). So I think I’ll be stuck with ST_GEOMETRY for the foreseeable future. Despite the fact that we will be upgrading our GIS system soon (in theory, that’d be the time to switch).
So all that considered, I think my only option is to create “helper” SDO_GEOMETRY columns for a few polyline FCs…so that I can do my linear referencing SQL work.
But yes, for organizations that are starting from scratch, or companies that are project-based, or for organizations who are switching database vendors …then I would agree that going with SDO_GEOMETRY would be the right choice.
Related: What spatial type does Esri recommend for Oracle EGDBs?
Related post: Idea: Support invisible SDO_GEOMETRY columns
(That title is misleading — the page does have a related discussion...about why I can't just switch to SDO_GEOMETRY.)