topic Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions in Data Management Questions

ST_GEOMETRY FC: Options for using Oracle Spatial functions

Bud — Wed, 29 Mar 2023 01:05:54 GMT

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.

Unfortunately, it's not possible for me to change the datatype in the table from SDE.ST_GEOMETRY to MDSYS.SDO_GEOMETRY, due to existing dependencies on the ST_GEOMETRY shape column.
Also, it seems like ArcGIS only supports a singe geometry column per table. So it's not like I can just add a SDO_GEOMETRY column to the table, without risking problems in ArcGIS.

Alternatively, what are my options for generating a separate SDO_GEOMETRY column so that I can use Oracle Spatial functions?

For example:

Convert from ST_GEOMETRY to SDO_GEOMETRY in a query on-the-fly.
Downside: the query is complicated/fragile/slow. I don't want to make the conversions every time the queries are used.
~~Add an invisible SDO_GEOMETRY column to the table. Update the column with a db trigger. Add a spatial index.~~
Downside: Invisible SDO_GEOMETRY columns aren't supported in Oracle. Also: Idea: Support invisible SDO_GEOMETRY columns.
Create a parallel table that has a common ID column and a SDO_GEOMETRY column. Create a trigger on the ST_GEOMETRY table that updates the parallel table after INSERT, UPDATE, or DELETE. Or use calculation attribute rules in ArcGIS Pro to do something similar.
Downside: Adds extra tables/complexity that need to be managed. Joining from the ST_GEOMETRY table to the parallel SDO_GEOMETRY table might be slow for large datasets.
Parallel materialized view with an SDO_GEOMETRY column.
Downside: A different department would need to create and manage the the materialized view, since GDB data creators/owners don't have CREATE MATERIALIZED VIEW privileges. (It's always a pain when you can't manage your own stuff.)

Does anyone have any thoughts on those options? Or are there any other options that I've overlooked?

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

Bud — Tue, 31 May 2022 22:16:45 GMT

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=type%20will%20be-,NCLOB,-. 

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:

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

ClaudioRuschel — Sun, 24 Apr 2022 19:42:12 GMT

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.

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

Bud — Sat, 30 Apr 2022 16:50:37 GMT

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.

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

ClaudioRuschel — Sat, 30 Apr 2022 22:00:30 GMT

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.

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

Bud — Fri, 20 May 2022 03:51:30 GMT

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.

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

Bud — Tue, 31 May 2022 15:07:30 GMT

(That title is misleading — the page does have a related discussion...about why I can't just switch to SDO_GEOMETRY.)