Examples of field calculations using SQL

444
1
03-27-2023 03:40 PM
Bud
by
Notable Contributor

It's possible to use SQL in field calculations for non-versioned enterprise geodatabases:

Calculate Field (Data Management)

SQL expressions support faster calculations for feature services and enterprise geodatabases. Instead of performing calculations one feature or row at a time, a single request is sent to the server or database, resulting in faster calculations.

Only feature services and enterprise geodatabases support SQL expressions. For other formats, use Python or Arcade expressions.

Out of curiosity, what are some examples of SQL expressions you use as field calculations?

  1. Do you find there are things that are simpler to do in SQL vs Python/Arcade?
  2. Do you use subqueries in your SQL field calculations? Or spatial SQL functions?
  3. Can you simply write something like CASE WHEN 1=1 THEN 1 END as a field calculation? 

I only have ArcGIS Pro 2.6.8 which doesn't seem to support SQL field calculations on enterprise geodatabases. So I can't test and am curious what's possible.

0 Kudos
1 Reply
CoffeeforClosers
New Contributor III

A simple point-in-polygon intersect update statement is faster at the RDBMS level. Note: this assumes and requires that the data have the same SRID.

 

 

UPDATE SourceTable
SET s.ColumnA = t.ColumnB
FROM SourceTable s
JOIN TargetTable t
ON s.Shape.STIntersects(t.Shape)=1