Latest Contributions by Bud

‎12-09-2022

Idea: Need an OOTB geoprocessing tool that can be used to select the greatest 1 per group in a FGDB table. For example, for a given ASSET_ID, select the road inspection that has the latest date. I don’t want to create a new FC or create a database view. I want to make a selection in the attribute table of an existing FGDB table. Related question: GP tool to select greatest 1 per group Edit: The tool should also let you break ties. So, if for a given asset there were multiple rows with the same date, then use a different field to determine what row to use (aka what row to break the tie). Similar to what the SQL expression in this answer does: Using a WHERE clause subquery, select the greatest 1 per group with a tie-breaker roadinsptable.objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC LIMIT 1 ) That SQL expression (or similar) works for mobile GDBs and enterprise GDBs, but not for file GDBs. So we need a geoprocessing tool. There are cases when the tiebreaker would be arbitrary. Any of the ties could be used since we don't always have a different field that can be used to break the tie. But there are other cases where we do have an additional field that we want to use to break the tie (get the max or min of the tiebreaker field per group). So we need the option to use additional fields as tiebreakers. The tool needs to work for standalone tables.

‎12-09-2022

@JohannesLindner That’s useful. Out of curiosity, what was you reasoning for using query layers (enterprise GDBs-only) vs. generic TOC feature layers (datatype-agnostic)?

‎12-09-2022

What SQL standard should we use when writing geodatabase-agnostic SQL expressions? (I.e. SQL:2016, etc.) For example, I want to write an SQL expression that will work in mobile GDBs and all major enterprise GDBs (Oracle, SQL Server, and PostgreSQL) that will select the greatest n per group when used in Select by Attributes. I have what I assume is a geodatabase-agnostic expression that works, to a degree, for getting the greatest n per group: --SQL expression: date_ = (select max(subq.date_) from roadinsp subq where roadinsp.asset_id = subq.asset_id) --https://dbfiddle.uk/HpqMb2ls That works, but it selects multiple/duplicate rows for a given asset in this scenario: the asset has multiple top rows with the same date. I would rather the query only select one row for each asset. For that requirement, I think the SQL will get more complicated, at least if I want to control what duplicate will break the tie — i.e. what specific row will be used. I can think of ways of doing it fairly simply in, say, Oracle. But the SQL would only work in Oracle GDBs, not others. So that makes me wonder, as a starting point, what SQL standard should I use to make geodatabase-agnostic SQL expressions?

‎12-08-2022

Add a line to the SQL Expression docs that clearly states: Correlated subqueries are not supported in file geodatabase SQL expressions Details: A database performs a correlated subquery when a nested subquery references a column from a table in a parent statement one or more levels above the subquery. Since correlated subqueries aren’t supported, SQL expressions on file geodatabases can’t do things like get the greatest 1 per group, and other similar requirements: NOT EXISTS ( SELECT * FROM road_insp r2 WHERE r2.asset_id = road_insp.asset_id AND r2.date_ > road_insp.date_ ) --Demo: https://dbfiddle.uk/gSGjLTCW The above file geodatabase SQL expression would select all rows in the table, which would be incorrect. It’s important to note that the query fails silently, producing incorrect results, rather than throwing an error. Therefore, correlated subqueries should be avoided in file geodatabase SQL expressions to avoid producing misleading results. Related: When EXISTS Doesn't: File Geodatabases and Correlated Subqueries File Geodatabase SQL expression to get greatest n per group Select greatest 1 per group using EXISTS Support correlated subqueries in file geodatabase SQL expressions

‎12-08-2022

BUG-000154284

‎12-08-2022

Is there a way to add checkboxes to the layer symbology in the Table of Contents — to toggle symbology classes on and off? Maybe the underlying mechanism would be an automatically-generated display filter? SQL Expression: AND (<symbology class value field> IN (45,46,47,48)) Is that kind of thing possible? Is the Pro SDK the right mechanism?

‎12-08-2022

On a related note: @JohannesLindner posted a couple of useful ArcPy solutions -- related to finding values with blank cells (or similar): Oracle EGDB: Find junk values in all tables and columns (and log them in a table) [For multiple layers:] How do I check if a field in the attribute table contains a blank cell?

‎12-08-2022

What's the database type and version? [Edit: That's relevant for possible SQL solutions. I posted this thinking I was the first person to reply. Meanwhile, Johannes had already posted a solution in the background without me realizing it.] For Oracle, here's a related PL/SQL post: Oracle EGDB: Find junk values in all tables and columns (and log them in a table)

‎12-08-2022

It's possible to do this with PL/SQL. --DROP TABLE incorrect_value_results; --CREATE TABLE incorrect_value_results (id NUMBER, table_name VARCHAR2(30), column_name VARCHAR2(30), val_count NUMBER, value varchar2(30)); TRUNCATE TABLE incorrect_value_results; DECLARE l_count NUMBER; l_inv_char_str VARCHAR2(2000); TYPE invalid_char_val_rec IS RECORD( cnt NUMBER, inv_char VARCHAR2(20) ); TYPE invalid_char_val_tab IS TABLE OF invalid_char_val_rec INDEX BY PLS_INTEGER; l_inv_char_vals invalid_char_val_tab; l_index NUMBER; BEGIN l_index := 0; -- Loop through each table in the schema FOR i IN (SELECT table_name FROM user_tables) LOOP -- Loop through each relevant column for this table -- Exclude column EVT_FROM FOR j IN (SELECT column_name, data_type FROM user_tab_cols WHERE table_name = i.table_name AND column_name NOT IN ('EVT_FROM','ANGLE','UND','OFFSET') AND table_name NOT LIKE 'A_%' AND table_name NOT LIKE 'SDE%' AND table_name NOT LIKE '%ANNO%' AND table_name NOT IN ('INCORRECT_VALUE_RESULTS','NUMBERS') AND data_type IN ('VARCHAR2', 'CHAR', 'NCHAR', 'NVARCHAR2', 'NUMBER')) LOOP IF j.data_type IN ('VARCHAR2', 'CHAR', 'NCHAR', 'NVARCHAR2') THEN EXECUTE IMMEDIATE 'SELECT COUNT(1), '||j.column_name|| ' FROM '||i.table_name|| ' WHERE UPPER('||j.column_name||') IN('' '', '' '', ''0'', ''-'', ''NULL'', ''<NULL>'' ) GROUP BY '||j.column_name BULK COLLECT INTO l_inv_char_vals; ELSIF j.data_type = 'NUMBER' THEN EXECUTE IMMEDIATE 'SELECT COUNT(1), '||j.column_name|| ' FROM '||i.table_name|| ' WHERE UPPER('||j.column_name||') <= 0 GROUP BY '||j.column_name BULK COLLECT INTO l_inv_char_vals; END IF; -- If there are results then log them l_index := l_index + 1; FORALL k IN 1..l_inv_char_vals.COUNT INSERT INTO incorrect_value_results (id, table_name, column_name, val_count, value) VALUES (l_index, i.table_name, j.column_name, l_inv_char_vals(k).cnt, l_inv_char_vals(k).inv_char); END LOOP; END LOOP; END; / COMMIT; Pros: The PL/SQL script's performance is very good. It scans millions of records in less than 10 seconds. And it might be possible to optimize it even further. Cons: The code is somewhat lengthy. It might be simpler to do it with ArcPy. I don't need the solution to work for all geodatabase types; I just need to use it on my Oracle enterprise geodatabase. But an ArcPy solution would be more beneficial to a wider audience.

‎12-08-2022

Oracle 18c/10.7.1 geodatabase: I want to find junk values in all tables and columns (in a specific schema/owner/user): Loop through all tables Loop through each number and text column If a column has junk values, then add a record to a log table that summarizes the column's data integrity issue (i.e., insert a rolled-up record into the log table with a COUNT column, not a row for each problem value): 0 (number) " " (text; single space) " " (text; double space) "0" (text) "-" (text) "NULL" (text; not a true null) "<NULL>" (text; not a true null) Thanks.

‎12-07-2022

Could Esri create some detailed docs on empty strings, nulls and NaN in ArcGIS? For ArcGIS supported datatypes, which datatypes allow empty strings as values? Which datatypes have no concept of an empty string, only null? Example: Oracle. For procedural programming languages that are used in ArcGIS, how do they handle nulls? Python: “None” Arcade/JS null quirks Others Define the concept of empty strings. Is “ “ (text) different from “” (empty string)? Empty string best practices in ArcGIS. When to query for both empty strings and nulls in ArcGIS. Sometimes people set a default value to a space “ “ or zero, to work around a field that doesn’t allow nulls. Is that a recommended practice? Describe the different ways to set a value to null in the Attribute Table: Delete a value with the keyboard delete key or by cutting the value (ctrl+x). In an Oracle GDB, the value will be automatically set to null upon saving. Tip: Don’t select the record and hit the delete key – that will delete the entire record (not what we want). Or, use the field calculator: set the value to null, but not “<null>”. Or, if the field has a domain, select <Null> from the picklist. Incorrect: Don’t try to set the value to the literal text “<null>” or "null". That’s not the same thing as it being truly null. Null isn’t a value. It’s the absence of a value. What is NaN? When is it used in ArcGIS? Example: M values in linear referencing. I think there are cases where NaN is actually negative infinity (float), in Oracle at least. Nil For example, Create NIL (zero vertex) geometry I’m aware that there are some bits and pieces of docs pertaining to nulls. But I don’t think those pages cover all the points mentioned above.

‎12-07-2022

Is the thought: while ctrl+c , down arrow, and ctrl+v works, it would be helpful to use the industry standard of ctrl+d, for consistency between applications. And also, ctrl+d is more efficient (one keyboard action vs three)?

‎12-07-2022

Check out the answer I have in this post: FGDB: Propagate duplicate features via 1:M join to related table The docs seem to suggest that’s already possible, under specific circumstances. Add Join (Data Management) : Related: Add Join will not create a 1:M join Solution: Make sure both FCs/tables have ObjectIDs and are in the same geodatabase.

‎12-07-2022

@JoshuaBixby @Do you find there’s functionality that’s lacking in mobile geodatabases that fgdbs supported?

‎12-07-2022

Regarding the docs: SQL for reporting and analysis on file geodatabases What parts of ArcGIS does the syntax in that page pertain to? For example, where in ArcGIS can we use INNER JOIN in FGDB SQL? I can see that the word "reporting" is in the title. But I can't help but wonder if that SQL syntax would apply to all FGDB SQL in ArcGIS, including geoprocessing SQL expressions, and not just reporting. The page makes several references to ArcObjects. So, my questions are: Can we use that SQL in SQL expressions in GP tools like Select By Attributes? Example: File Geodatabase SQL expression to get greatest n per group Or is that SQL limited to the FGDB .NET SDK? (I'm not sure about that terminology; this isn't my area of expertise) Should the title of that page be changed to SQL for reporting and analysis on file geodatabases (ArcObjects)? Or do those docs apply to some other part of ArcGIS? Thanks.

My Ideas

Latest Contributions by Bud

Geoprocessing tool to select greatest 1 per group with tiebreaker

Re: Customize Pro UI: Checkboxes in TOC to toggle symbol classes

What SQL standard to use when writing geodatabase-agnostic SQL expressions?

Clarify the docs: Correlated subqueries not supported in FGDB SQL expressions

Re: View SQL definition of FGDB database views

Customize Pro UI: Checkboxes in TOC to toggle symbol classes

Re: Documentation/tips about empty strings, nulls, and NaN in ArcGIS

Re: How do I check if a field in the attribute table contains a blank cell?

Re: Oracle EGDB: Find junk values in all tables and columns (and log them in a table)

Oracle EGDB: Find junk values in all tables and columns (and log them in a table)

Documentation about empty strings, nulls, NaN, and Nil in ArcGIS

Re: Table editing - Ctrl+D to copy from cell above

Re: Option to Create One-to-Many Join

Re: FGDB SQL: Support self-correlated subqueries (inner join on a virtual table)

What part of ArcGIS do these docs pertain to? "SQL for reporting and analysis on file geodatabases"

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

Roads and Highways User Group (RHUG)

Local Government Solutions Users

Mapping and Charting Enterprise Databases

ArcGIS Arcade