idea Virtual Attributes (ad hoc) in ArcGIS Pro Ideas

Virtual Attributes (ad hoc)

Bud — Wed, 31 Jan 2024 07:36:19 GMT

We need virtual, ad hoc SQL columns in the attribute table.

Think of a field calculation, but it’s a dynamic, temporary, read-only mechanism used for one-off tabular data analysis. Takes advantage of the power of SQL; should be as easy to set up as definition queries.

The field would be set up in the feature layer properties, not as a persistent field or as a persistent object in the database. Allows editing FC fields in the same attribute table. Avoids the need to create a clunky query layer or view and then join it back to the FC. No exclusive locks or privileges required other than SELECT.

Examples:

case when class='A' then 1 else 0 end as class_flag
- Sort the class_flag field as descending (and sort other fields too). Is a workaround for things like Promote Selected Records.
Quickly get the total cost from a related table (1:M) using a subquery:
(select sum(cost) from budget b where p.id=b.id and year=2025)
- One-time criteria that doesn't justify a permanent field, database view, query layer, etc.
- I want to see the total right in the FC attribute table. I don't want to use a relate to point to a related table.
case when last_edited_date is not null then 1 else 0 end as null_flag
- Sort null_flag and last_edited_date descending. Used to specify how nulls are sorted in the attribute table (nulls first or last).
Lots of other scenarios.
- From LukePinner in a comment — Using read-only corporate data: concatenate two or more fields, split a field, change the case of some text values, etc. For example, relate via a composite ID field.

Re: Virtual Attributes (ad hoc)

Bud — Fri, 08 Dec 2023 08:04:00 GMT

Existing mechanisms aren’t suitable:

Field Calculator
- Don't want to create a new field. Or can't create a new field due to locks, privileges, etc.
- Not dynamic. If edits are made then the field becomes out-of-date.
- Slow performance on large datasets.
- Calculation and Code Block parameters are clunky to use. I'd rather just write a succinct CASE expression like case when a<>b then 1 end .
- SQL field calculations: not enough datatypes supported. Versioned data not supported.
Calculation Attribute Rule
- Too involved; not suitable for ad hoc analysis.
- Don't want to create a new field. Or can't create a new field (or attribute rule) due to locks, privileges, etc.
- Slow performance on large datasets when calculating the entire field.
Query Layer
- Only supported for enterprise geodatabases.
- Can't edit data (FC fields).
- Slow performance when a large FC is joined to a query layer. Especially when sorting columns.
- Attribute table of FC becomes buggy when joined to a query layer.
Database View
- Don't always want to set up a new object in the database. Or don't have privileges.
- Too involved for ad hoc analysis.
- Can't edit data (FC fields).
- Slow performance when a large FC is joined to a database view (aka a self-join). Especially when sorting columns.
- Can't modify SQL definition via ArcGIS without recreating and re-registering the view. FGDB views can’t be modified by SQL client.
- Attribute table of FC becomes buggy when joined to a database view.
Report
- Want to use attribute table functionality, not a report. Not suitable for ad hoc analysis.
- Can't edit data.

Fast performance could be achieved by:
Building the calculated column right into the main SELECT query that is sent to the database, not as a self-join which is less efficient.

Example:

select
    objectid,                                      --FC field
    asset_id,                                      --FC field
    class,                                         --FC field
    case when class='A' then 1 end as class_flag   --Calculated column
from
    roads

Re: Virtual Attributes (ad hoc)

Luke_Pinner — Fri, 07 Apr 2023 08:25:50 GMT

I use virtual attribute fields in QGIS and they're very useful.

Re: Virtual Attributes (ad hoc)

Bud — Fri, 07 Apr 2023 08:34:54 GMT

@Luke_Pinner Interesting. I'll have to check out QGIS.

Re: Virtual Attribute Fields (ad hoc) - Status changed to: Needs Clarification

SSWoodward — Fri, 07 Apr 2023 16:27:35 GMT

Thanks for the Idea @Bud,

This seems very similar to the idea linked below. Let me know if you think this its a match and I'll merge them.

Link - Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases

Re: Virtual Attribute Fields (ad hoc)

Bud — Fri, 07 Apr 2023 21:41:11 GMT

@SSWoodward As far as I know, that other idea pertains to columns that are defined in the database table DDL:

create table t1 (
  id              number,
  product         varchar2(50),
  price           number(10,2),
  price_with_tax  number(10,2) generated always as (round(price*1.2,2)) virtual
);

https://oracle-base.com/articles/11g/virtual-columns-11gr1

Those columns are called Computed Columns (SQL Server) and Virtual Columns (Oracle and PostgreSQL).

Whereas the Virtual Attributes (ad hoc) idea above is for an ArcGIS Pro mechanism in the attribute table that only exists temporarily in a feature layer in the map document.

Re: Virtual Attributes (ad hoc)

MarceloMarques — Sat, 08 Apr 2023 19:08:53 GMT

You can use native database feautures that can be leveraged to help with this, the first thing that comes to mind is Database Materialized Views for large datasets and it is also possible to modify an existing view directly in the database using SQL statements, hence you do not need to use ArcGIS to drop / recreate the view. I hope this helps.

Examples:

An Essential Guide to Oracle Virtual Column By Practical Examples (oracletutorial.com)

ORACLE-BASE - Materialized Views in Oracle

Re: Virtual Attributes (ad hoc)

Bud — Tue, 11 Apr 2023 04:45:20 GMT

@MarceloMarques Thanks, however I don’t really consider materialized views to be suitable for ad hoc calculations for fast-paced data analysis work. Materialized views need to be set up by the DBA, since CREATE MATERIALIZED VIEW privileges are not included in the Oracle Data Creator user type. So in my case, getting the DBA to set up a MV requires an official project request. So we’re talking years, whereas I’m looking for a temporary solution that can be set up in seconds, similar to a definition query.

Regarding virtual columns in Oracle tables, my understanding is virtual columns aren’t supported by ArcGIS. And virtual columns aren’t meant for one-off data analysis calculations. Instead, they’re a persistent column in the underlying database table.

Regarding modifying a database view using a SQL client (not possible for file geodatabase views):
Yes, that can be helpful in some cases. I do it regularly. But it can cause problems if the view is registered with the geodatabase. And database views have other limitations, as mentioned above.

Re: Virtual Attributes (ad hoc)

wayfaringrob — Mon, 10 Apr 2023 17:54:58 GMT

This would be great. @SSWoodward That other idea only seems to apply to one type of data source.

Re: Virtual Attributes (ad hoc)

Bud — Mon, 10 Apr 2023 21:09:52 GMT

It's too bad this idea is currently in a status that disallows upvotes. It seemed to get a bit of traction initially before it was changed to Needs Clarification.

Maybe people can comment instead to show their support (like rburke did). That way notifications about the post would be a reminder to come back and upvote when the status changes.

Idea - Kudo an idea that is flagged as Needs Clarification

Re: Virtual Attributes (ad hoc)

wayfaringrob — Mon, 10 Apr 2023 21:11:43 GMT

@Bud agree, the lockout on upvotes when the status is anything other than 'open' serves to cut traction when it's most likely to get it.

Re: Virtual Attributes (ad hoc) - Status changed to: Open

SSWoodward — Mon, 10 Apr 2023 23:36:35 GMT

I've moved it back to open while this Idea gets looked into further. Thanks for clarifying.

Re: Virtual Attributes (ad hoc)

Luke_Pinner — Tue, 11 Apr 2023 09:39:40 GMT

Yes, this is a very different idea to "Link - Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases" or native database functionality. It's more akin to storing label/popup definitions in a layer (lyrx/aprx map layer) in that the virtual fields would be properties of the layer, not the data source and could be generated on the fly using Arcade or python expressions.

A simple use case that I constantly run up against - data source: read-only corporate data. I need to do some calcs on an attribute (concat two or more fields, split a field, change case of some text values, etc.) e.g for using in symbology. I know I can manually change symbology entries or group them, but that's manual and tedious and I know I can export the table or feature class to a copy that I can edit, but that's an issue for data management. And query layers are fiddly and don't work on non-GDB datasources.

Re: Virtual Attributes (ad hoc)

Bud — Wed, 12 Apr 2023 16:32:15 GMT

@Luke_Pinner

Well said!
This really does feel like a gap in core ArcGIS technology. I think Esri needs to keep in mind that the data analysis industry is a very fast-paced business. We can’t afford the time or messiness that comes with the classic workarounds you described. We need a clean, efficient, read-only way to do dynamic analysis on attributes. (While still having the option to edit FC fields in the same attribute table, if needed.)
But I don’t think that functionality exists yet.

Re: Virtual Attributes (ad hoc)

wayfaringrob — Tue, 11 Apr 2023 16:03:09 GMT

Exactly @Luke_Pinner and @Bud!

Re: Virtual Attributes (ad hoc)

MarceloMarques — Wed, 14 Jun 2023 17:31:56 GMT

You can create virtual column in the RDBMS as an alternative solution.

ORACLE-BASE - Virtual Columns in Oracle Database 11g Release 1

SQL Language Reference (oracle.com) - Note on Oracle Virtual Columns

Specify Computed Columns in a Table - SQL Server | Microsoft Learn

PostgreSQL: Documentation: 15: 5.3. Generated Columns

I hope this helps.

Re: Virtual Attributes (ad hoc)

Bud — Wed, 14 Jun 2023 20:28:40 GMT

I think SQL would be orders of magnitude faster than Arcade. I suspect that's why arcade expression fields weren't implemented for attribute tables. The performance would have been very poor for anything but the smallest of tables.

Re: Virtual Attributes (ad hoc)

Bud — Wed, 05 Jul 2023 15:25:03 GMT

A related performance tip about using CASE in Oracle SQL -- from Esri Case #03356933:

We've been looking specifically into using the CASE statement in Oracle views and people state that they encounter performance issues. I found some users suggesting to use DECODE instead of CASE. The view I created is below. The performance greatly improved on sorting. Maybe this can be an alternative for the user. The performance went from 50 seconds to sort the flag column to 13 seconds.
create or replace view SDE.TEST_TABLE_DECODE_VW as
select objectid, PRIMARYOID,
DECODE(type,'NT',1)flag
from SDE.test_table2
Here is the forum thread that suggested using DECODE:
https://blog.toadworld.com/2018/01/26/oracle-coding-best-practices-making-a-case-for-the-case-statement

@MarceloMarques might find that interesting.

Re: Virtual Attributes (ad hoc)

MarceloMarques — Wed, 05 Jul 2023 15:40:22 GMT

@Bud - Indeed. Some or the newer Oracle SQL Functions might not have the same performance as the Old functions, I was aware of the CASE and DECODE case because I have seen this in the past and I am certain there are a lot of other interesting cases, writing good SQL statements is always a challenge. Thanks for sharing with everyone. 🙂

Re: Virtual Attributes (ad hoc)

Bud — Fri, 01 Dec 2023 20:22:02 GMT