Latest Contributions by Bud

It would be helpful if ArcGIS Pro (and ArcGIS in general) would intentionally ignore non-feature class columns. For example, it would really help if we could add additional geometry columns to a FC. And use those columns in SQL queries.  Let's say we have an Oracle SDE.ST_GEOMETRY polyline FC: Add an additional geometry column that is a different datatype (i.e. SDO_GEOMETRY). Or, add a custom polygon geometry column. Or, add a midpoint, startpoint, or endpoint geometry column. I'm aware that numeric XY columns would be a valid workaround for point geometries. That works in a pinch for points, but not for lines or polygons. Or other non-geometry columns that that aren't supported by ArcGIS. (Populate each via a simple db trigger.) Currently, it's technically possible to add non-FC columns to a table via SQL ALTER statements. But unfortunately, ArcGIS isn't  designed to ignore those columns, so they end up causing problems. A known example is: an additional geometry column will cause errors with versioning in ArcGIS Pro. Whereas, it would be ideal if ArcGIS were designed to intentionally ignore non-FC columns. So that we could use them for building performant SQL queries (on-the-fly geometry columns in queries aren't an option—they're painfully slow). I'm aware there are other options, such as storing non-FC columns in related tables. That works as a last resort, but would take extra effort to manage. Whereas a simple column in a table would be easier in the long run. And wouldn't require constantly joining from the FC to the helper table in queries (can be costly for large tables). ... View more

Is anyone using INVISIBLE Oracle columns to hide a specific column from ArcGIS? 19.2.12.1 Understand Invisible Columns You might use invisible columns if you want to make changes to a table without disrupting applications that use the table. After you add an invisible column to a table, queries and other operations that must access the invisible column must refer to the column explicitly by name. More info: Oracle Base: Invisible Columns For example, I'd hoped to hide an additional geometry column from ArcGIS, since I know ArcGIS only supports a single geometry column per FC. Unfortunately, Oracle doesn't support invisible SDO_GEOMETRY columns, so I hit a wall there.  But that got me thinking, are there any other use cases for invisible columns in feature classes? Maybe there are certain columns or column types that you want to hide from ArcGIS? and/or hide from users? (I know the knee-jerk reaction is to say, "It's not supported. Be afraid, be very afraid." But, the whole point of invisible columns is that applications have no way to see the column. If ArcGIS has no way of knowing about an invisible column, then it ought to be safe. At least, safe enough to consider & test.) Cheers. ... View more

In ArcGIS, what are the options for automatically computing a field in an enterprise geodatabase? Example: Populate a field in a road polyline FC (4,000 rows) —with— the zone number from a zone polygon FC (10 rows). Purpose: Serve up the data to an Excel pivot table report; connect to the geodatabase via an ODBC connection. Various other GIS uses. Environment: Oracle 18c; SDE.ST_GEOMETRY spatial datatype; ArcMap/geodatabase 10.7.1. Options: Trigger-like mechanism (update FC field or a parallel table in real-time): Calculation attribute rule (ArcMap incompatibility/db view workaround) Database trigger Materialized view - fast refresh option; using materialized view logs Scheduled job (update FC field or a parallel table): Windows task scheduler on a server: ArcPy script & .SDE connection file Windows task scheduler on a local PC, which is the mechanism that's used for Scheduling geoprocessing tools: ArcPy script & .SDE connection file Oracle scheduler to run a stored procedure Materialized view - scheduled ArcGIS Notebook Server (extra cost) (not available for 10.7.1 geodatabases) 3rd party application (example: FME) Web-based automation services? Integromat; now called Make Microsoft Power Automate Others Server customization: Custom application/server to make HTTP calls to ArcGIS REST API. Server object extension (SOEs) or server object interceptors (SOIs)? (extra cost) Query (SDE.ST_GEOMETRY functions are slow) Database view Function-based spatial index Hidden virtual column  Add a function-based index? SQL query in Excel or other application. ODBC connection Microsoft Query (store SQL query right in Excel file) Power Query for Excel (connect to database tables and views?) ArcMap or ArcGIS Pro (doesn't help me with my Excel report): Query layer Logic options: When a zone gets edited, edit all roads (real-time) When a road gets edited, update the road's zone field (real-time) Update all roads on a schedule Update roads that have been recently edited (editor tracking date) — on a schedule. What if the zone was edited? Manual Manually run a geoprocessing tool Manually run a ArcPy script Manually use the field calculator in ArcMap Manual data entry in attribute table in ArcMap Any other options/ideas? Thanks. ... View more

I've got too many views in my GIS database. I'm starting to wonder if the database isn't the right place to store queries. Any tips? Only store queries as database views if the view is spatial? ...since dragging views into the map is convenient; .lyr files in a network folder are less convenient. Try not to have "views on top of views"? I.e., don't split views into manageable parts. Put all logic into a single big view to avoid clutter. Store all other queries in the applications that use them -- i.e. reporting software such as Excel or Power BI? Store the master query as a .sql file in a network folder? Refactor queries so that they're simpler. And possibly eliminate some unnecessary queries. Get outside input from codereview.stackexchange.com or some other kind of code review. Other? How are things done in the non-GIS world? I'm starting to get the impression that people don't create many database views. Instead, logic is usually stored at the application level. Is that your experience?  (ArcMap/Enterprise 10.7.1, ArcGIS Pro 2.6.8, Oracle 18c, SDE.ST_GEOMETRY)   ... View more

I have a multi-part road polyline FC. For the longest part of a given polyline, I want to use the line/part's midpoint to get the WARD_ID of the closest polygon (single-part polygon FC). The WARD polygons aren't topologically clean. There are slight overlaps and gaps. So that's why I want to get the closest polygon -- within a 30m tolerance. Is there a way to do that operation with geoprocessing tools?  License level = Advanced. Thanks. ... View more

Idea:  Release ST_GEOMETRY functions code (on GitHub, or something like that) Reasons: Esri seems to have stopped improving ST_GEOMETRY functions. Meanwhile, we still have issues and improvements that need addressing. If Esri isn’t going to address those needs, then it would make sense to let us do it ourselves. Currently, the code is locked down in the EXTPROC.DLL in Oracle. So we can’t see how the functions work or extend them. Why not let us see and use the code? I don’t think Esri would lose any money by doing this. It’s not as if customers buy ArcGIS Enterprise so that they can get SDE.ST_GEOMETRY (there are much bigger reasons than that). That might have partially been the case years ago, but these days, organizations tend to go with native spatial datatypes, since the functionality is broader, the DB vendors continue to improve the products, and performance is usually better. So Esri wouldn’t lose anything by opening up ST_GEOMETRY. One of the unspoken advantages of the ArcGIS platform is that users are empowered to build and manage their own stuff in the database. For example, as a data creator in Oracle, I can create tables, sequences, triggers, and views (and depending on the organization, functions too). Why not take it a step further and empower us to solve more advanced spatial database problems — by providing access to the ST_GEOMETRY code? Worth considering? ... View more

Does anyone have any SQL optimization tips they can share? Either for spatial queries or non-spatial queries? I work with SDE.ST_GEOMETRY and SDO_GEOMETRY in Oracle 18c. But info about optimization in other DBs/datatypes is fine too. For example, I learned recently that the cost of ST_GEOMETRY functions doesn’t get included in the explain plan cost. I had a query that used a bunch of nested ST_GEOMETRY functions (in the SELECT clause) that was really slow, yet the explain plan cost was surprisingly low. It took me a while to realize that Oracle doesn’t usually include the cost of user-defined functions in the explain plan. Especially not for SDE.ST_GEOMETRY functions, since they’re stored in the EXTPROC.DLL file, not in the Oracle kernel. So Oracle has no way to know how expensive they are. https://stackoverflow.com/questions/66671812/are-functions-considered-in-execution-plan Any others come to mind? Cheers. ... View more

Idea: Rebrand and expand Notebook Server as ArcGIS Automation Server (or something similar/appropriate). Reason: I've never had any luck explaining notebooks to senior management or core IT people. They don't seem to get it — and don't understand why we need it. Meanwhile, automation is a main theme in IT strategic plans. So buying a product that has automation right in the name would be a much easier sell...and directly align with corporate initiatives.  The product could also be expanded to include more no-code options. For example, without writing code, let users set up scheduled emailed reports to notify them if x records exist in a FC. ... View more