Latest Contributions by Bud

Edit: Mods, I suppose this post could be moved to the Data Management community (or whatever's appropriate). What resources do you recommend for learning about SRIDs? SRIDs have always been a gap for me. For example, when working with a FC using SQL, it's not clear to me if/when I would need to change the SRID for the output geometries. If I extract points from a line FC via an SQL query: When constructing the resulting points using SQL, should I use the original SRID from the lines, or do I need a new SRID since the geometry type has changed? (from lines to points) select objectid, --Side note: If anyone's wondering, this is why I convert to/from WKB: https://support.esri.com/en/Technical-Article/000011333 sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))),sde.st_srid(shape)) points_shape -- 🡅 --Question: When constructing the resulting points, should I use the original SRID from the lines, -- or do I need a new SRID since the geometry type has changed? (from lines to points) from lines --Note: The original SRID of the lines is 300051.   Or, if I remove the M-dimension from lines via an SQL query: Do I need to use a different SRID when constructing the non-M-enabled geometries via SQL?   Thanks. ... View more

The legacy 9.3 SDE docs are very useful. Example: XML columns in a geodatabase stored in Oracle When I look in the latest version of Enterprise/ArcMap/Pro documentation, I can't find an up-to-date version. Where can I find a modern version those SDE docs? Thanks.   ... View more

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