Latest Contributions by Bud

I have an SDE.ST_GEOMETRY value (Oracle 18c): select sde.st_geometry('LINESTRING EMPTY', 26917) as shape from dual I want to select one of the ST_GEOMETRY object's attributes: Entity Numpts Minx, miny, maxx, maxy Area Len SRID Points How can I do that with Oracle SQL?  ... View more

Regarding Esri technical article #000011694: How To: Create Oracle Spatial function-based indexing That article has out-of-date & incomplete information: 1. The article suggests that we should use the index type: MDSYS.SPATIAL_INDEX. That's the old way of doing it. Oracle now recommends we use the new version of that index type: MDSYS.SPATIAL_INDEX_V2. 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. 2. Also, the Esri article says: When creating a query layer that references an Oracle view containing an Oracle Spatial attribute, the attribute must have a spatial index. Without the spatial index, spatial queries cannot be executed. That's no longer true.  Spatial Index No Longer Required Creating and using a spatial index is no longer required for the use of any Oracle Spatial and Graph features. However, spatial indexes are highly recommended, and not using them can negatively affect performance in some cases. Ideally, we would use spatial indexes whenever we can. But that's not always possible — and for small datasets, it's not always necessary. Edit — 2022-06-16: In hindsight, I'm not sure if point #3 is correct. Needs verification? 3. There is information missing from the article about performance and USER_SDO_GEOM_METADATA. In addition to creating an entry in USER_SDO_GEOM_METADATA for the SDO_GEOMETRY column/function, we also need to create an entry for any spatial views that use the column, so that those spatial views will be performant in the map in ArcGIS: Answer from Albert Godfrind (Oracle Spatial Team): If you want to also see the content of the view on a map (using some GIS tool), you will probably also need to define metadata for the view. This is NOT needed for spatial queries, but is a common requirement for GIS tools. insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid) values ( 'MY_SPATIAL_VV', 'SHAPE', sdo_dim_array ( sdo_dim_element('long', -180.0, 180.0, 0.5), sdo_dim_element('lat', -90.0, 90.0, 0.5) ), 4326 ); commit; Could Esri please update this technical article so that it has up-to-date information? Thanks. ... View more

I have scenarios where I need a real-time copy of a FC in the same GDB. For example, I have an Oracle ST_GEOMETRY FC. I have a requirement where I need to store a real-time copy of that FC where the SHAPE is SDO_GEOMETRY, not ST_GEOMETRY. It would help if there were an OOTB mechanism that could sync the master ST_GEOMETRY FC to the copy SDO_GEOMETRY FC in real-time. I'm aware that there are ways to do that with custom code, such as calculation attribute rules. Attribute rules would work, but I'm reluctant to add custom code to dozens of FCs — since my system already has numerous complex attribute rules and other customizations. I would prefer to keep things simple and use OOTB/no-code functionality instead. I also want to avoid customizing the db. That would involve painstaking project requests with the DBA team — not a realistic option. There are other difficulties too, such as versioning and using SQL to convert between spatial datatypes. Could Esri consider giving us a no-code option for syncing a table in real-time within the same GDB? Thanks. ... View more

When creating an enterprise geodatabase index in ArcGIS Pro: If I enter an index that is longer than 16 characters, ArcGIS Pro automatically truncates the index (to 16 chars): The index name I wanted: a2345678901234567 The index name I got:        a234567890123456 Most enterprise databases now support object names that are 128 characters long (example: Oracle was 30, now 128). The 16-character limit in ArcGIS Pro is too short. When we have hundreds of tables with multiple indexes, we need long index names so that we can clearly describe and differentiate between like-indexes. Could Esri please consider supporting longer index names when creating indexes via ArcGIS Pro? ... View more

Could Esri provide recommendations in the documentation regarding GDB object naming conventions? Examples: Prefix so that like-objects will be sorted together in Catalog: ROAD ROAD_EVENTS ROAD_INSPECTIONS Plural vs singular: “ROAD” vs. “ROADS”. Text case: all caps, title case, etc. Underscores or no underscores. Suffix database views with “_VW”. Suffix tables/FCs that are generated/loaded by a scheduled job as “_SKD”, or something like that. Use feature datasets where applicable. Indexes: use a “_IDX” suffix or "IDX_" prefix. Other? Related: Nomenclature for file and layer naming in Multi-user data systems ... View more