Latest Contributions by Bud

‎05-11-2022

FAQ: What are the recommended naming conventions for geodatabase objects? How To Name Things In ArcGIS Source: Idea - Geodatabase object naming convention recommendations

‎05-11-2022

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

‎05-11-2022

Not sure if you’re looking for SDE object naming conventions or not, but a few come to mind: Use common prefixes so that like-objects will be sorted together in Catalog: ROAD ROAD_EVENTS ROAD_INSPECTIONS Plural vs singular: “ROAD” vs. “ROADS”. Define a case standard: all caps, title case, etc. Define a standard for underscores or no underscores. Suffix database views with “_VW" or "_V” — so that you can tell what objects are tables vs. views in Catalog. Suffix tables/FCs that are generated/loaded by a scheduled job as “_SKD”, or something like that. Use feature datasets to group like-layers, where possible. Unfortunately, we can’t put non-spatial tables in feature datasets. Other standards as needed. Edit: Avoid creating lots of unnecessary owners/schemas/users in enterprise GDBs. When there are lots of owners, it's hard to remember where a FC lives. "What owner was it in again?"

‎05-10-2022

Oracle 18c; 10.7.1 EGDB; SDE.ST_GEOMETRY. I have a polyline FC. I want to create a dynamic layer that will display the startpoints and endpoints of the lines as points. I'm looking for an option that is dynamic, such as: Create something like an XY event layer, except it shows the startpoint and endpoint of lines. Or, an option in the FC symbology to display the start/endpoints as points. Similar to we could do with endpoint arrows in ArcMap. Or, custom Arcade-based symbology. Write an Arcade expression to isolate the start/endpoints of the lines. Display as points. Other? I don't want to: Create a static point FC (via a GP tool, python script, etc). Nor do I want to update such a FC via attribute rules or a db trigger. Create static XY fields in the FC. For example, populate XY fields with an attribute rule or a database trigger. That would require creating 4 permanent fields (start_x, start_y, end_x, end_y). The extra fields would add clutter to the FC and be too much work to implement in other FCs. Update a static point FC on a schedule. I want the data to be dynamic. For example, if I create a new line, I want to see the start/endpoints immediately...as soon as I save the edit. I don't want to wait for a scheduled job to run. Use ST_GEOMETRY functions to get the start/endpoints. That has proven to be far too slow. And the ST_POINT subtype geometry that gets returned by ST_GEOMETRY functions is problematic in ArcGIS Pro/ArcMap. Convert the FC from ST_GEOMETRY to SDO_GEOMETRY in order to get the endpoints in an SQL query. That's not possible due to countless dependencies on the ST_GEOMETRY shape field in the FC. Create a geometric network to show the start/endpoints. I don't a need geometric network. As mentioned, the solution needs to be: Dynamic. Fast (just as fast as XY event layers, which are surprisingly fast) Easy to set up Symbol should be points, not arrows It would be ideal if we could visually differentiate between startpoints vs endpoints (i.e. different colors). But that's not a must-have; it would be acceptable if the start and endpoints for a given line were merged into a multi-part feature. I don't need to be able to select an individual point with the select tool. I just need to see the points in the map. Use case: ArcGIS Pro editing, Portal/Field Maps, etc. Is there a way to create a dynamic endpoints layer in ArcGIS Pro?

‎05-10-2022

For what it's worth, I can in theory create a ST_GEOMETRY function-based spatial index: create index atn_startpoint_shape on infrastr.active_transportation (sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),300125)) indextype is SDE.ST_SPATIAL_INDEX parameters ('ST_GRIDS=320 ST_SRID=300125'); Index ATN_STARTPOINT_SHAPE created. But there doesn't seem to be a way to utilize that index in the map in ArcGIS Pro. For example, I wouldn't insert a row into USER_SDO_GEOM_METADATA for ST_GEOMETRY spatial index, since that's designed for SDO_GEOMETRY columns only. When I try to test a view that would use the index in ArcMap... create or replace view atn_startpoint_vw as ( select objectid, sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),300125) shape from infrastr.active_transportation where sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),300125) is not null ) ...it just crashes. Similar to this issue: ArcGIS Pro crashes when copying Oracle view to FGDB. Equivalent to USER_SDO_GEOM_METADATA for SDE.ST_GEOMETRY? (function-based spatial indexes)

‎05-10-2022

With Oracle's SDO_GEOMETRTY, we can create function-based spatial indexes that vastly improve performance for computed geometry columns. Could Esri add that functionality for SDE.ST_GEOMETRY in Oracle?

‎05-09-2022

I wondered if the SHAPE column was the problem, since ArcGIS Pro and ArcMap seem to be bad at handling the ST_POINT subtype (even when the TREAT() function is used). ArcGIS Pro and ArcMap seem to do a lot better when the datatype is the ST_GEOMETRY supertype. So, instead of using the TREAT() function to convert from the ST_POINT subtype to the ST_GEOMETRY supertype, I used a low-tech method: I converted from ST_POINT to binary to ST_GEOMETRY. Alternatively, converting to text instead of binary would have been an option too. create or replace view startpoint_vw as ( select objectid, sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(shape)),26917) as shape from my_owner.sidewalks --14,524 features where sde.st_numgeometries(shape) = 1 ) But unfortunately, ArcGIS Pro is still crashing. So I'm not sure what the problem is. I have the same problem when building the shape from the X & Y coordinates too: sde.st_geometry ('point (' || sde.st_x(sde.st_startpoint(shape)) || ' ' || sde.st_y(sde.st_startpoint(shape)) || ')', 26917) as shape I also tried limiting the query to a small subset of the rows, in case there was a problematic feature somewhere, but that didn't help either. Pro still crashes. create or replace view startpoint_vw as ( select objectid, sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(shape)),26917) as shape from my_owner.sidewalks --14,524 features where sde.st_numgeometries(shape) = 1 and rownum <= 1000 ) I’m all out of ideas. And quite frustrated.

‎05-09-2022

I have a db view in Oracle 18c (EGDB; ST_GEOMETRY): create or replace view startpoint_vw as ( select objectid, treat(sde.st_startpoint(shape) as sde.st_geometry) as shape --https://gis.stackexchange.com/a/385767/62572 --https://support.esri.com/en/Technical-Article/000011333 from my_owner.sidewalks --14,524 features where sde.st_numgeometries(shape) = 1 --The SQL will fail if there are any multi-part features. Temporary workaround: exclude multi-part features from this view. ) Using Catalog in ArcGIS Pro 2.6.8, I want to right-click --> copy/paste that view to a FGDB (the view gets pasted as a FC). Reason: Quickly take a snapshot of the data for geoprocessing purposes, publishing to AGOL as a FC, etc. Problem: When I paste the view into the FGDB, ArcGIS Pro crashes about 50% of the time. When I re-open Pro after the crash, a FC has been created, but not all of the features were inserted. The number of features that were inserted is usually a multiple of 1000 (i.e. 2000, 4000, 8000, etc.). For the other 50% of the time, the view will paste as a FC without issue (all features successfully created). I have the same problem when I paste as a FC into SDE (the same environment as the source environment). ArcGIS Pro crashes. Has anyone had that problem when pasting views? Any ideas?

‎05-05-2022

ST_GEOMETRY; Oracle: Could ESRI create a function to convert directly from the ST_POINT subtype to the ST_GEOMETRY supertype? That would solve these issues: Bug: Unable to define a query layer in ArcGIS where the data source uses an st_geometry subtype in Oracle Get ArcGIS to recognize ST_POINT Use TREAT() function to convert UDT subtype to supertype I'm aware that there is a quick-and-dirty workaround, where we can convert from subtype to text to supertype (or use well-known binary instead of text). But that workaround is ugly and slow. It would be better to have a more robust & performant solution.

‎05-04-2022

It would be handy if we could store additional information/columns in GDB domains. So that we could use that additional data in SQL queries, reports, labels etc. CODE [domain coded value] NAME [domain description] Additional field: DETAILS

‎05-01-2022

I’m aware that ArcGIS Server can return JSON in a feature service from a feature class. I still want to experiment with JSON and SQL.

‎04-30-2022

I'm wondering if there's anyone out there who has Oracle 21c and SDE.ST_GEOMETRY who could help me with a quick test: If you were to run this query in Oracle 21c, what would it do? select json_object(sde.st_geometry('MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))', 26917)) as sdo_geom from dual Background: I suspect that some new functionality has been added to the JSON_OBJECT() function in Oracle 21c. I have 18c, not 21c, so I'm not able to test it myself. For example, the following SDO_GEOMETRY query works in 21c in an online environment called db<>fiddle. Of course, that environment doesn't have the SDE.ST_GEOMETRY datatype, so I can't do the ST_GEOMETRY test there. select json_object(sdo_geometry('MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))')) as sdo_geom from dual Output: {"SDO_GTYPE":2006,"SDO_SRID":null,"SDO_POINT":{},"SDO_ELEM_INFO":[1,2,1,7,2,1],"SDO_ORDINATES":[0,5,10,10,30,0,50,10,60,10]} Related: Select JSON text of SDO_GEOMETRY using SQL I'm curious what would happen if we did something similar in Oracle 21c, but with SDE.ST_GEOMETRY, instead of SDO_GEOMETRY. Thanks!

‎04-30-2022

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?

‎04-30-2022

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.

‎04-25-2022

(Migrated from a separate idea.) It looks like there are OOTB Esri geoprocessing tools in ArcGIS Pro that use the old/slow cursors. Example: Points To Line GP tool If we look at the code for those tools, we can see that they are outdated. They use the the old/slow cursors instead of the new/fast cursors in the DA module. Could Esri update all GP tools so that they use the new DA cursors/module?

My Ideas

Latest Contributions by Bud

Re: Nomenclature for file and layer naming in Multi-user data systems

Geodatabase object naming convention recommendations

Re: Nomenclature for file and layer naming in Multi-user data systems

Dynamic endpoints layer

Re: ST_Geometry: Function-based spatial indexes

ST_Geometry: Function-based spatial indexes

Re: ArcGIS Pro crashes when copying Oracle view to FGDB

Oracle view crashes ArcGIS Pro and ArcMap

Function to convert from ST_GEOMETRY subtype to supertype

Store additional information in coded value domains

Re: Use JSON_OBJECT() with SDE.ST_GEOMETRY

Use JSON_OBJECT() with SDE.ST_GEOMETRY

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

Re: ST_GEOMETRY FC: Options for using Oracle Spatial functions

Update OOTB GP tools to use new DA cursors

Integrate ArcGIS Data Pipelines in ArcGIS Enterpri...

Send configurable email notifications from ArcGIS ...

Re: How reliable are ArcGIS Online webhooks?

How reliable are ArcGIS Online webhooks?

Sharepoint List Connector

GIS Jobs

Experience Builder Tips and Tricks

Roads and Highways User Group (RHUG)

Local Government Solutions Users

Mapping and Charting Enterprise Databases