Virtual Attributes (ad hoc)

[Bud]

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:

1. 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.
2. 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.
3. 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).
4. 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.

[Bud]

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

[Luke_Pinner]

+1 

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

[Bud]

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

[SSWoodward]

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

[Bud]

@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.

[MarceloMarques]

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

[Bud]

@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.

[wayfaringrob]

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

[Bud]

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

[wayfaringrob]

@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.