Latest Contributions by Bud

@MichaelVolz I don't have enough experience with the new index type to say one way or the other. I submitted the idea only because that's what Oracle recommends. My uneducated guess is that there wouldn't be a performance gain in most scenarios. If there was, I think Oracle would have mentioned it in the docs. But they actual hint at the opposite, at least when it comes to creating the index. I'm not sure about performance when using the index. 5.1.1 Using System-Managed Spatial Indexes Effective with Release 12.2, spatial indexes can be system-managed by specifying INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 at index creation. You are strongly encouraged to use this index type for all new spatial indexes you create, regardless of whether the spatial table or the spatial index is partitioned, and you may also want to use it if you decide to re-create legacy spatial indexes. The main benefit is simplified spatial index management. This is most beneficial in cases of partitioning, because this new index type eliminates the need for most, if not all, index partitioning management operations. Full support is provided for almost all Oracle Database base table partitioning models, including: Single-level partitioning: range, hash, list Composite partitioning: range-range, range-hash, range-list, list-range, list-hash, list-list, hash-hash, hash-list, hash-range Partitioning extensions: interval (but not interval-based composite partitions), reference, virtual column-based partitioning The old INDEXTYPE=MDSYS.SPATIAL_INDEX (without the “_V2”) is still available for use, It may provide slightly better index creation performance, especially with small data sets and no partitioning involved. You might also want to use the old type if you need to drop a legacy spatial index and then want to re-create it in exactly the same form as it was before. However, in all or almost all cases you will want to specify INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 when creating any spatial index. ... View more

For what it's worth, SDO_Geometry’s SPATIAL_VECTOR_ACCELERATION is disabled in my database (18c), since it's only enabled by default in 21c+. Oracle Spatial Vector Acceleration: Flick the Switch I tried checking to see if it's enabled (from a non-DBA connection) in SQL Developer, but got errors: Check if SPATIAL_VECTOR_ACCELERATION is enabled (as a non-dba) I don't know much about SPATIAL_VECTOR_ACCELERATION. But it sounds like it will make SDO_Geometry even faster in some cases. ... View more

Related idea (doesn't answer your question): ArcGIS should use the new SDO_GEOMETRY spatial index type: SPATIAL_INDEX_V2 ... View more

Yes, get_coordinates() is undocumented. I've talked to folks on the Oracle Spatial team about that. They plan to document it in the next release. If for some reason we didn't want to use get_coordinates(), maybe since it's undocumented, then there are other ways to get the startpoint X&Y from SDO_GEOMETRY. The performance of the alternative techniques is roughly the same as get_coordinates(), although slightly more complicated: select v.x, v.y from sidewalks cross join sdo_util.getvertices(shape) v where v.id = 1  Or with function sdo_startpoint_x(shape sdo_geometry) return number is begin return shape.sdo_ordinates(1); end; function sdo_startpoint_y(shape sdo_geometry) return number is begin return shape.sdo_ordinates(2); end; select sdo_startpoint_x(shape) as startpoint_x, sdo_startpoint_y(shape) as startpoint_y from sidewalks Or select     json_value((shape).Get_GeoJson(),'$.coordinates[0][0]' returning number) as x,     json_value((shape).Get_GeoJson(),'$.coordinates[0][1]' returning number) as y from     sidewalks There is also: sdo_lrs.geom_segment_start_pt But that just returns a LRS geometry. We'd still need to get the XY from the SDO_ORDINATES_ARRAY, which can be done via cross join sdo_util.getvertices(shape). But that's more clunky than I'd like. Related: Idea: Add a SDO_ORDINATES member function (for extracting collection elements by index)   The nice thing about ST_GEOMETRY is that it's easy to use, well documented, and there aren't several weird ways to do a thing...there's usually just one way to do it. But with SDO_GEOMETRY, as I demonstrated above, it's messier.   Is it possible to lookup SDE.ST_GEOMETRY by index? I don't think so. That's part of the problem with ST_GEOMETRY. The ordinates are hidden in a blob. Whereas SDO_GEOMETRY ordinates are in plain sight in an array in an object attribute (easily accessible by index number). Note: With ST_GEOMETRY, we can get a specific point/vertex via the point index number: SDE.ST_PointN. But that's just as slow as SDE.ST_StartPoint. And I assume you weren't really referring to that.   It seems hard to make any real claims about performance without testing more here. The time difference just looks too large for these to be equivalent queries. Yes, the "startpoint function test" was just one test for a specific use case. I haven't done any official benchmarking with dozens of documented tests or anything like that. But the test I did was legitimate & correct. And it's a good example of how slow ST_GEOMETRY functions can be when used on a medium-sized dataset. I stand by my statement that ST_GEOMETRY functions are generally slower. I've heard the same from others, including Esri staff. ... View more

I stand by my statement that ST_GEOMETRY functions are generally slow compared to SDO_GEOMETRY functions. I've done lots of testing over the past couple of years. And the results are always the same.  But I should clarify that my testing has been with constructor, accessor, and geometry functions. Not relational functions. I've tested dozens of constructor, accessor, and geometry functions, and I consistently find that they're slower than I need them to be. And to be fair, ST_GEOMETRY has been perfectly fast when simply selecting the geometry column from a FC in a map. And the spatial indexing seems to work fine too. But that's not what I was referring to in this post...it's the functions that are slower than SDO_GEOMETRY. I'd be very interested in seeing reproducible examples of ST_GEOMETRY constructor, accessor, or geometry functions being faster than SDO_GEOMETRY. I've not experienced that yet.  Here's what I did to make sure the data was apples-to-apples: Created two new line FCs via Catalog with no extra columns, just the OBJECTID and SHAPE. One FC with the ST_GEOMETRY datatype. And one with the SDO_GEOMETRY datatype. Loaded features from an existing FC into the new FCs via the Catalog load tool. Tested equivalent functions for each datatype in queries in SQL Developer. Hit CTRL+END to select all rows in the query, not just the first 50 rows that are selected by default. To me, that seems like a good way to do objective testing for functions. Some additional thoughts on testing: Ideally, the test would be done with auto-generated fake data that can be publicly shared via db<>fiddle or  Oracle Live SQL (or whatever works). Then testers could use that public/fake data and load it into our test FCs in our local environments. For example, here's a query that can be used to generate a grid of SDO_GEOMETRY line features:  https://community.esri.com/t5/arcgis-enterprise-questions/generate-grid-line-features-using-sql/m-p/1182869/highlight/true#M33343 The grid can be as big as we want, generating more features as needed. That query can be used to create a Query Layer or spatial view in ArcGIS Pro. Then that query/view could be loaded into our test FCs via Catalog...creating features to do the test on. The same kind of thing can be done for polygons using this query:  https://community.esri.com/t5/arcgis-pro-questions/convert-grid-lines-to-polygons/m-p/1183816/highlight/true#M56289 And I imagine the first query could be modified to make points instead of lines. As mentioned, I'd be interested to see cases where ST_GEOMETRY functions (constructor, accessor, or geometry) are faster than SDO_GEOMETRY functions. They could be out there...and I just haven't seen them yet. ... View more

I don't have a way to easily do screen recording. But I imagine it would be easy for you to reproduce on your end. ... View more

Thanks @DuncanHornby and @KoryKramer. For our notes: If I understand correctly, it's possible to do it indirectly if we have a validation attribute rule on the table. But if we don't have a validation attribute rule, then there isn't a GP tool that can directly flag rows with invalid domain values. ... View more

On a side note: (I do a similar thing to what @jcarlson mentions above, but I use SQL queries in Oracle, instead of Arcade.) One thing I find frustrating is that domains don't have a date column/property to track the last time the domain was modified. Scenario — sync to external system: I have a sidewalk FC that I sync as assets to a work order management system. The sync logic is based on a SYNC_NEEDED column in a spatial view on the FC. That SYNC_NEEDED column is based on the LAST_EDITED_DATE. But ideally, It would also account for changes to domains as well. For example, the sidewalk FC has a MATERIAL column that uses a MATERIAL_SIDEWALK domain. If someone were to change a value in the domain from, say, CONCRETE to CONCRETE - CAST-IN-PLACE, then ideally, the affected features in the FC should be synced, so that the new material information would show up in the external system. But since there's no way to query for domains that have changed, that doesn't seem to be possible.  The only other option for capturing the domain changes would be to re-sync the entire FC on a schedule, which is problematic since the external system (IBM Maximo) is extremely slow at syncing records. The only realistic option is to sync changes-only, not sync all rows. Related: Idea: Add a timestamp column to GDB_ITEMS table (Oracle) Auto-Updating features [flagging edited features for syncing to external system: Maximo] ... View more