At FLO Analytics (FLO), we often work with clients that have large volumes of spatial and nonspatial data that are siloed or located in various databases controlled by other departments. Fortunately, Esri ArcGIS Enterprise geodatabase users typically have a database management system that natively supports spatial operations, such as Microsoft’s SQL Server or PostGIS. These databases offer spatial and nonspatial SQL functions that can be used to quickly investigate and check data quality; perform analyses; generate buffers and do selections; and customize data sources for map services, dashboards, reports, or data automation tasks. Many of these functions can be performed using a view, which provides a live query of the data each time it is accessed. This allows source data changes —such as a permit status being updated—to be instantly seen as soon as the map is refreshed. 

Figure 1: Selecting parcels that are within a specific zoning area within a SQL view using geometry functions automatically keeps the selection up to date with changes in either dataset.

FLO has worked with several clients to provide SQL views that combine enterprise geodatabase data with other inspection, permitting, financial, and site sampling databases. The views allow each system to manage its own data of record and reduce or remove the need to process data before displaying it on a map or dashboard. Examples include combining parcel or address geometry with permit information, matching sample site locations to a sample results table, or viewing the latest inspection result for a property. When these data are published as a service through ArcGIS Server they can be easily added to a map or dashboard. This reduces the need to duplicate attributes in the GIS or have people update two systems.

Figure 2: An Esri dashboard that consumes SQL views customized for the audience and tool requirements can simplify data management, in this case a table of results and a location dataset are combined to support map and time interactions.

SQL views can also be used in automation efforts—an approach that FLO used when tasked with creating an efficient, high-level summary dataset representing over one million individual sample results. Views and functions were also a crucial part of automating the contaminant sampling collection and reporting workflow of a major fire recovery effort, allowing us to generate property reports as soon as results were confirmed, which was important for tracking status and insurance claims. Spatial functions further expand what is possible—converting geometry types, generating a buffer, or partitioning data by location. For a cemetery database integration, FLO matched GIS plot polygons with a burial data table managed in a separate system. Each polygon was converted to a point, resulting in a stack of burial records that appear in the center of the plot on the map, without the need to manually create the points themselves.

The use of SQL tools and views can make data investigation and evaluation easier and faster, helping with quality control. SQL can quickly generate a list of unique values, count the occurrences of values, calculate dates and differences, concatenate fields, or even conduct spatial queries such as counting the number of address points within a parcel. FLO can set up these tools to help reduce the effort in creating specialized datasets from existing ones, identify data issues, and allow you to bring in data from other systems of record. If you are interested in learning more, please contact us.

Thanks to Flo-Analytics for this article and for their continued support and sponsorship of WAGISA.