Improve Support for Grouping, Predicate, and Aggregate SQL Functions (COUNT, GROUP BY, SUM) in ArcMap, ArcGIS Pro

Idea created by alstickney on Jul 13, 2018
    Reviewed
    Score30
    • Hornbydd
    • fiona.renton
    • alstickney

    I recently discovered that you cannot use standard aggregate or grouping SQL keywords (i.e. COUNT, GROUP BY, SUM) in data query functions within both ArcMap (as of 10.6) and ArcGIS Pro (I am running 2.1), at least without using a subquery - which is not always a straightforward use case. 

     

    ArcMap: Building a query expression—Help | ArcGIS Desktop  

    ArcGIS Pro: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop 

     

    This is not as big a deal for functions like Select by Attribute, but is a serious limitation on tools like Make Query Layer (or Table) in terms of their usefulness for data analysis and extraction workflows. 

     

    For example, I recently had a scenario where I had a polygon feature class of tax parcels and three related tables containing tax assessment information for parcels in Missoula County: Property, Commercial, and Residential. Each parcel has a geocode (unique ID). The Property table contains general tax assessment information for each parcel and tax year. The Property ID uniquely identifies a tax parcel in a given tax year (one-to-one relationship with parcels). The Residential and Commercial tables contain assessment information on residential or commercial improvements (structures) for each parcel and tax year. There may be zero or multiple records for each tax parcel/property ID (one-to-many relationship). 

     

    Here is an example of what these tables look like, with PropertyID 327491 highlighted. 

    Example of three related tables of property assessment information to join on Property ID and tax year

    If I want to count how many residential, commercial, or total structures are associated with each parcel for the whole county, currently I have a process in Modelbuilder using a combination of ArcToolbox tools (i.e. Summary Statistics and Join Field) to:

    1) count (SUM) the number of PropertyID instances in the Residential and Commercial Tables by tax year.

    2) Join it to the Property table. 

    3) Count (SUM) the number of propertyIDs, commercial structures, and residential structures by geocode and tax year (GROUP BY). 

    4) Join the aggregate structure and property ID counts to the tax parcel feature class for visualization and analysis. 

    model for summarizing property assessment information (i.e. structure count) and joining to tax parcel data

    While this model is helpful, it has to process over 15,000 records in each table and takes about 6-8 hours to run, so making adjustments to the model or re-running it is time-intensive. 

     

    When I discovered the Make Query Table and Make Query Layer tools, I thought, what a great replacement! I can write an SQL query that will prepare that table for me and then I can either create a join or write a Python script to extract data from it. An example SQL Query might look like this:

    SELECT Property.GeocodeSearch,Property.PropertyID,Property.TaxYear, COUNT(Res.PropertyID) AS ResCount FROM Res, COUNT(Com.PropertyID) AS ComCount FROM Com
    LEFT JOIN Property ON Property.PropertyID = Res.PropertyID
    LEFT JOIN Property ON Property.PropertyID = Com.PropertyID
    GROUP BY Property.Geocode;
    WHERE Property.TaxYear = 2017;



    Unfortunately, this is not possible with SQL on the client side (in ArcGIS Desktop - ArcMap or ArcGIS Pro) unless I converted the above SQL statement into an appropriate series of subqueries, which is beyond my skills. For those GIS users who don't have database privileges to create a view on the database side, this can result in a loss of query/extraction function and having to develop slow or clunky workarounds. 

     

    Please consider expanding support for predicate, aggregate, and grouping functions in ArcMap and ArcGIS Pro to enable these types of data workflows.