Options for computing fields

BB1234

In ArcGIS, what are the options for automatically computing a field in an enterprise geodatabase?

• Example: Populate a field in a road polyline FC (4,000 rows) —with— the zone number from a zone polygon FC (10 rows).
• Purpose: Serve up the data to an Excel pivot table report; connect to the geodatabase via an ODBC connection. Various other GIS uses.
• Environment: Oracle 18c; SDE.ST_GEOMETRY spatial datatype; ArcMap/geodatabase 10.7.1.

Options:

1. Trigger-like mechanism (update FC field or a parallel table in real-time):
   1. Calculation attribute rule (ArcMap incompatibility/db view workaround)
   2. Database trigger
   3. Materialized view - fast refresh option; using materialized view logs
2. Scheduled job (update FC field or a parallel table):
   1. Windows task scheduler on a server: ArcPy script & .SDE connection file
   2. Windows task scheduler on a local PC, which is the mechanism that's used for Scheduling geoprocessing tools: ArcPy script & .SDE connection file
   3. Oracle scheduler to run a stored procedure
   4. Materialized view - scheduled
   5. ArcGIS Notebook Server (extra cost) (not available for 10.7.1 geodatabases)
   6. 3rd party application (example: FME)
   7. Web-based automation services?
      1. Integromat; now called Make
      2. Microsoft Power Automate
      3. Others
3. Server customization:
   1. Custom application/server to make HTTP calls to ArcGIS REST API.
   2. Server object extension (SOEs) or server object interceptors (SOIs)? (extra cost)
4. Query (SDE.ST_GEOMETRY functions are slow)
   1. Database view
   2. Function-based spatial index
   3. Hidden virtual column 
      1. Add a function-based index?
   4. SQL query in Excel or other application.
      1. ODBC connection
      2. Microsoft Query (store SQL query right in Excel file)
      3. Power Query for Excel (connect to database tables and views?)
5. ArcMap or ArcGIS Pro (doesn't help me with my Excel report):
   1. Query layer
6. Logic options:
   1. When a zone gets edited, edit all roads (real-time)
   2. When a road gets edited, update the road's zone field (real-time)
   3. Update all roads on a schedule
   4. Update roads that have been recently edited (editor tracking date) — on a schedule. What if the zone was edited?
7. Manual
   1. Manually run a geoprocessing tool
   2. Manually run a ArcPy script
   3. Manually use the field calculator in ArcMap
   4. Manual data entry in attribute table in ArcMap

Any other options/ideas?

Thanks.

JohannesLindner

Seems like a pretty comprehensive list...

for 5a: You could export the query layer as CSV or XLSX and use that in your report.

 

The method you use will probably depend on

• how often do you consume the field?
• how many applications consume the field?
• how current must the data be?

 

For example, if you need to generate a monthly report in Excel, and that's the only place where you consume the field, it would probably be overkill to use SOE or even scheduled jobs and triggers. Just run a script or tool beforehand.

If you need the report daily, or if you need the field in other applications, it would be a good idea to use jobs or triggers.

If the data has to be current, then you should probably use queries or triggers. Which one could depend on performance: queries would give performance hits for consuming, triggers would give performance hits when editing. If the data doesn't have to be current, a scheduled job could suffice.

 

Server customization seems like overkill in any case. Maybe for really special cases where you can't achieve your goal with SQL, Python or Arcade, or where performance really matters.

 

Manually use the field calculator in ArcMap

For simple stuff like concatenating two fields, sure. For more complex stuff like getting related data or spatial relationships? Either write a small script or run it manually once, then save the Python command as script.

 

Manual data entry in attribute table in ArcMap

For things you don't expect to change and if you edit only few feature, sure. If other applications depend on you not making errors in the process, or if you know the values can change, or if you edit 10s or 100s of features in one go: no. Just finish editing and run CalculateField.