In the June 2024 update we introduced a new way to calculate field values in Map Viewer. At that time, Arcade was the only supported language within the new experience. Fast forward to today, we’re excited to announce full SQL support for calculating field values in Map Viewer with the November 2024 update. As part of this work, we’ve also built a new SQL editor for authoring expressions. Keep reading to learn more about the new SQL calculate field experience in Map Viewer.
When should you use SQL?
SQL calculations are more performant compared to calculations that use Arcade, particularly when you’re working with a dataset with many features. As a result, we recommend using SQL to calculate your field values when possible. For example, if your calculation does not require spatial attributes (e.g., feature geometry) or comparison with other features in the dataset.
Additionally, if your layer has editing settings like Sync or Change tracking enabled, SQL is the only supported language to calculate field values.
Calculating fields with SQL
The workflow and steps to calculate fields with SQL is virtually identical to Arcade field calculation. If you haven’t already, check out this blog post first for an overview of the various pieces of functionality available to you throughout the workflow.
After selecting SQL as the calculation language, you will have the ability to set a filter on your calculation. This is an optional step in the workflow, but can be particularly helpful if you want to refine which features will receive the calculated value. You can think of the filter as a WHERE clause for your SQL expression. Only features that meet the condition(s) outlined in the WHERE clause will be impacted by the calculation.
After the optional filter step, you’re ready to start authoring your SQL expression! Just like Arcade, you’ll be able to preview your calculation result prior to running the expression using the preview pop-up and table.
If you’ve used SQL to calculate fields in the Data tab, you may recall that there was a button to check the validity of your expression prior to execution. In the new experience, we don’t have an explicit button to check this. Instead, the validity of your expression is periodically evaluated as you author it in the editor. If your expression is valid, you will be allowed to run the expression on the full dataset using the Run calculation button.
The SQL expression editor
The new SQL expression editor incorporates all the features you know and love from the Arcade editor into a new and exciting SQL calculation experience.
Syntax highlighting
The SQL expression editor leverages the same color palette as the Arcade editor for highlighting syntax elements. Accordingly, all colors used in the editor are compliant with WCAG 2.0 guidelines for maintaining sufficient contrast between foreground and background element colors.
Validation & auto-completion
The editor will help you validate your SQL expression by flagging invalid field names, unsupported characters, or missing function parameters. As you type in the editor suggestions for functions, snippets, and layer fields will appear based on the characters you’ve entered. Additionally, certain keywords will display as options for function parameters where appropriate. For example, when using the EXTRACT() function relevant keywords like YEAR, MONTH, DAY, etc. will appears as options in the suggestions menu for the part_ parameter.
Integrated documentation
Detailed documentation for all supported SQL functions can be accessed using the Functions button in the side panel. The documentation includes descriptions of each function, the expected return type(s), required parameters, and example code snippets. You can also insert a function snippet with parameter placeholders by selecting the Insert button on the bottom of the function’s documentation page.
Layer fields
The Fields button in the side panel will display an overview of your layer’s fields, including their type, alias, and field name. If you have domains or subtypes configured on your fields, you can also view these by drilling into the field using the > button.
Conclusion
The new SQL calculate field experience should behave exactly the same as the previous experience still found in the Data tab. If you have existing workflows or SQL expressions that you use to calculate fields you can bring them into the new experience in Map Viewer seamlessly. In future updates of ArcGIS Online, we plan to continue to improve the field calculate experience for both Arcade and SQL.
Have you used the calculate field experience in Map Viewer? What do you think? Let us know your thoughts and provide any feedback on Esri Community.
Happy Mapping!
Article Discussion: