Latest Contributions by Bud

Oracle 18c 10.7.1 EGDB: I want to write a SQL query that has rows with the XY coordinates of each vertex of an SDE.ST_GEOMETRY polygon feature class. I can do it for a line FC using a cross join, a numbers table, and ST_PointN: select a.objectid, b.number_ as vertex_id, a.sde.st_x(sde.st_pointn(a.shape, b.number_)) as x, a.sde.st_y(sde.st_pointn(a.shape, b.number_)) as y from infrastr.active_transportation a cross join infrastr.numbers b where b.number_ <= sde.st_numpoints(a.shape) and a.objectid = 14113   Bud_3-1714664219486.png But if I run that query on a polygon FC instead of a line FC, then I get an error: ORA-20003: ST_Geometry type must be an ST_LINESTRING or ST_MULTIPOINT type. ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2614 If I comment out the lines that use ST_PointN, then the query runs successfully: select a.objectid, b.number_ as vertex_id --a.sde.st_x(sde.st_pointn(a.shape, b.number_)) as x, --a.sde.st_y(sde.st_pointn(a.shape, b.number_)) as y from infrastr.bc_polygons a cross join infrastr.numbers b where b.number_ <= sde.st_numpoints(a.shape)   Bud_4-1714664251200.png So, that tells me that the problem is with ST_PointN. ST_PointN appears to only work for lines: ST_PointN ST_PointN takes an ST_LineString and an integer index and returns a point that is the nth vertex in the ST_LineString's path. Can a function be added to ST_GEOMETRY to get a specific vertex of a polygon? Get vertex of SDE.ST_GEOMETRY polygon using SQL ... View more

ArcGIS Pro 2.9.5: The Go To XY tool doesn't seem to support UTM coordinates that have decimal points. The input text box is red: Bud_0-1714660303895.png The only way to get those coordinates to work is to manually remove the decimal points, which is cumbersome. Could the Go To XY tool be enhanced to support UTM coordinates that have decimal precision? ... View more

ArcGIS Pro 2.9.5: When pasting coordinates from external sources into the Go To XY tool, it's not always clear what format Pro is expecting. For example, when copying UTM XY coordinates from SQL Developer, I didn't know how to format the coordinates; no combination of x,y seemed to work: Bud_2-1714659079207.png I had to poke around in Pro to see how it displays UTM coordinates and then mimic that format. Idea: Could the expected coordinate format be displayed as greyed-out text? The text would disappear when the user starts typing. Bud_1-1714658885575.png It would be similar to what we see in Ontario government web maps in Layers > Search By Location > Go to Latitude/Longitude (DD) > Search by coordinates: Bud_3-1714659297402.png https://www.lioapplications.lrc.gov.on.ca/AgMaps/Index.html?viewer=AgMaps.AgMaps&amp;local  ... View more

ArcGIS Pro 2.9.5: Geoprocessing tools that are docked in the geoprocessing pane can be run using the CTRL+SHIFT+ENTER keyboard shortcut, such as Feature Class to Feature Class. But that shortcut doesn't seem to work for floating gp tools like Export Features (via the Contents pane > layer context menu > Data > Export Features). Bud_0-1714657127163.png Could CTRL+SHIFT+ENTER keyboard shortcut functionality be added for floating GP tools, too? ... View more

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 eGDB: There are situations where the Add Join geoprocessing tool isn't suitable, such as this issue:  BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field values added through a one-to-many join but instead selects all records with duplicate ObjectIDs. A query layer would be a workaround for some use cases (instead of exporting the Add Join data to a FGDB). select cast(rownum as int) as unique_id, --cast as integer to ensure Pro knows the values are integers, not doubles a.*, b.objectid as join_objectid, --"join_' prefix: can't have duplicate field names b.project_id as join_project_id, b.fund_source as join_fund_source, b.year_fund as join_year_fund, b.amount as join_amount from projects a --one left join project_finances b --many on a.project_id = b.project_id Creating a query layer from scratch might be feasible for SQL power users like me, if I'm not in a rush. But for non-SQL people doing fast-paced data analysis, writing SQL queries like that isn't practical. So, I'm wondering if a geoprocessing tool could be added that would create a 1:M join query in a query layer without the user needing to write any code. It would be similar to the Make Aggregation Query Layer geoprocessing tool (screenshot), except it wouldn't aggregate the data. The query would need a calculated unique ID column for when the join is M:M (neither of the two table's ObjectID fields would be unique in that case). I think the unique ID column would need to be database-specific, such as using Oracle's ROWNUM pseudocolumn, or whatever mechanism is used by the database. A tool/query like that would give users an alternative join mechanism when using enterprise geodatabase data, with the known limitation that the data wouldn't be editable, unlike Add Join (the input table is editable). Could a Make Join Query Layer geoprocessing tool be added to ArcGIS Pro? ... View more

There are scenarios where query layers silently exclude rows from the underlying SQL query: IDs aren't unique or have nulls Features with null shapes Other? If Pro is going to automatically exclude rows, it should at least tell us about it. Could a message at the bottom of the attribute table tell us how many rows were excluded? Or if not a count, then warning that some rows were excluded? The message could be similar to the message that's displayed when a join is one-to-many and so there are duplicate rows in the attribute table. ... View more

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 eGDB: My unit has recently completed a months-long project that involved extensive tabular analysis of our EGDB data. The analysis involved complex queries, including non-spatial joins. Ultimately, my coworkers found they couldn't use ArcGIS Pro for the analysis and ended up using Excel as a temporary workaround. Now that the project is complete, they've asked the following question: Going forward, is ArcGIS Pro the right tool for tabular/join-based analysis of our enterprise geodatabase data? Based on the issues listed below, we are coming to the conclusion that the answer is, unfortunately, no. Select By Attributes on joined data: For rows that are 1:M, all rows in the join table get selected, despite the selection SQL expression. BUG-000154798 No virtual attribute functionality Whereas, tools like Excel can have real-time calculated fields (formulas) in true Excel tables (or Power Query) for ad hoc analysis. Trust issues with query layers: Query Layers omit rows from the underlying query without any warning. Here is a specific example, but there are other cases too: Idea - Query Layers — Include features with null shape in attribute table Slow joins to query layers BUG-000160320 Esri Case: 03356933 Definition query on join table will effectively result in an inner join Bug/Article-000007166, BUG-000153808, BUG-000131999, BUG-000129607, BUG-000153950, BUG-000164109 Idea - Add Join — Create independent definition query on join table (pre-filter) Definition query on join returns incorrect rows Esri Case: 03387420 Possibly the same issue: ArcGIS Pro Table Join Shows Wrong Records Fixed in 3.x? Join with definition query: Export has more rows than input (definition query is ignored) BUG-000135505 Esri Case: 03619440 Can't sort on join table field in attribute table BUG-000157874 Esri Case: 03616093 Can’t create 1:M join using unregistered tables, query layers, and database views It’s not always clear why a join is one-to-first vs. one-to-many. Records don't get selected when using Select By Attributes on sorted attribute table Esri Case: 03677944 ArcGIS Pro 2.9.5 - Fixed in 3.x? Export join to FGDB: Join table ID field is null Esri Case: 03616469 BUG-000146098 Adding a definition query excludes a row of data when using Add Join Esri Case: 03704340 BUG-000170869 Inconsistent Join Operation behaviour when joining tables with definition query in ArcGIS Pro BUG-000171969 In ArcGIS Pro, the Select by Attributes tool removes the leading space from a value in clause-mode, but not in SQL-mode. BUG-000173164 Join to unregistered database view is one-to-first instead of one-to-many Esri Case: 0379353 BUG-000173762 One-to-first join doesn't use related record with lowest OBJECTID When a join exists between a table and a feature class in an enterprise geodatabase, 'Select By Attributes' takes a long time to load the results in the attribute table Esri Case: 03893770 BUG-000176895 It's unfortunate that we feel Pro isn't the right tool for tabular analysis of geodatabase data. We've been die-hard ArcGIS Pro users up to this point. But ultimately, users have developed trust issues and have come up against too many walls when doing tabular analysis in Pro. What tools do experienced practitioners use for complex tabular analysis of enterprise geodatabase data? The current use case is: analysis of construction project GIS data, with no room for error. And many other use cases. ... View more

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 eGDB: Staff in my unit use an OS authentication .SDE connection when editing data. When using that connection, the user’s Windows/network username is populated in editor tracking fields: I.e. JSmith. That works as expected. But when doing data maintenance tasks like table design changes, power users like me use a database connection. The .SDE connection uses an Oracle user/owner/schema called ENGINEERING. We need to use that connection to make table design changes, instead of the OS authentication connection, because the OS authentication connection doesn’t have privileges for making table design changes. Often, data maintenance initiatives involve not only table design changes, but also editing tasks like batch populating a field. It isn’t realistic to expect the person to flip back and forth between the DB connection (table design changes) and the OS connection (editing) when performing complex data maintenance tasks. We’ve tried to tell power users to only use the OS connection to make edits, but people inevitably use the DB connection for field calculation edits, too. The problem with using the DB connection for edits is we don’t know what person made the edits, we only know that the ENGINEERING DB connection was used for the edits, because editor tracking inserts ENGINEERING as the user name. That’s not very useful when we’re troubleshooting issues/trying to figure out who made the edit. Idea: When editing using a DB connection, could editor tracking use the Pro licensing username, instead of the user/owner/schema name? JSmith (Pro licensing user, via AGOL or Portal licensing), or JSmith/ENGINEERING But not just ENGINEERING. ... View more