ST_GEOMETRY FC: Options for using Oracle Spatial functions
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
- SDO_UTIL.TO_JSON
- SDO_UTIL.TO_JSON_VARCHAR
- SDO_UTIL.TO_JSON_JSON
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...
update atn_json_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
commit;
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:
update atn_blob
set sdo_blob = sdo_util.to_wkbgeometry(sdo_cs.make_2d(sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING'))));
commit;
select
sdo_util.from_wkbgeometry(sdo_blob) wkb_to_sdo
from
atn_blob
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:
