{"id":1391512,"date":"2021-11-07T22:09:59","date_gmt":"2021-11-08T06:09:59","guid":{"rendered":"https:\/\/www.esri.com\/arcgis-blog\/?post_type=blog&p=1391512"},"modified":"2021-11-07T22:27:26","modified_gmt":"2021-11-08T06:27:26","slug":"make-aggregation-query-layer-a-new-geoprocessing-tool-to-compute-and-view-dynamic-summary-statistics-from-related-records","status":"publish","type":"blog","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/arcgis-pro\/announcements\/make-aggregation-query-layer-a-new-geoprocessing-tool-to-compute-and-view-dynamic-summary-statistics-from-related-records","title":{"rendered":"Make Aggregation Query Layer – a new geoprocessing tool to compute and view dynamic summary statistics from related records"},"author":5971,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","format":"standard","meta":{"_acf_changed":false,"_searchwp_excluded":""},"categories":[37101,23851],"tags":[42181,24321,222602,30061,759602],"industry":[],"product":[36561],"class_list":["post-1391512","blog","type-blog","status-publish","format-standard","hentry","category-announcements","category-data-management","tag-arcgis-pro","tag-geoprocessing","tag-spatial-aggregation","tag-summary-statistics","tag-time-series-analysis","product-arcgis-pro"],"acf":{"authors":[{"ID":5971,"user_firstname":"Tanu","user_lastname":"Hoque","nickname":"Tanu Hoque","user_nicename":"mahoque","display_name":"Tanu Hoque","user_email":"ahoque@esri.com","user_url":"","user_registered":"2018-03-02 00:17:50","user_description":"Tanu is a product engineer on Esri Mapping Team focusing on map service, print service, and ArcGIS Pro. He also works on spatio-temporal analysis, spatial aggregation and real-time data. Tanu\u2019s background includes a masters in Urban Planning from University of Akron, Ohio, USA and a bachelor degree from Khulna University, Bangladesh, and worked as GIS Coordinator in City of West Springfield, and GIS Specialist in a hydrology modeling center in Bangladesh before joining Esri.","user_avatar":""}],"short_description":"A geoprocessing tool to compute summary statistics dynamically from a related table, and visualize results by joining them to a spatial table.","flexible_content":[{"acf_fc_layout":"content","content":"

I’m very excited to introduce you to this geoprocessing tool that was released with ArcGIS Pro 2.8 earlier this year. <\/del>This tool allows you to perform some aggregated analysis from related records<\/a> that was never even possible until ArcGIS Pro 1.4. But creating such layers was not a very user friendly task until 2.8 release. On top it required some expertise in SQL. Now with the advent of this tool anyone without any SQL knowledge can perform such analysis.<\/p>\n

Before we start, please note that you must have your data in an enterprise database such as PostgreSQL, SQL Server, Oracle etc.<\/p>\n

Let’s say you work at a federal agency. You have monthly unemployment data, recorded at county level, stored in a table. Let’s further assume this is a historic data set that spans over multiple years – meaning you have maximum 12 records for each county for each year. Now you need to make maps or perform some analysis using data aggregated at state level for the current month. As you have already figured out that there is a one-to-many relationship i.e. a state has >1 counties for multiple months.<\/p>\n

Here are the steps (or something similar<\/em>) that you’d most likely take:<\/p>\n

    \n
  1. Select unemployment records for the current month using Select By Attributes<\/a> geoprocessing tool<\/li>\n
  2. Run Summary Statistics<\/a> geoprocessing tool to compute total or\/and average unemployment for each state using the state name\/fips field as the Case<\/strong> field<\/li>\n
  3. Join<\/a> the output result to your state boundary layer<\/li>\n
  4. Apply a new or predefined symbology to the layer with an attribute field that stores an aggregated results<\/li>\n<\/ol>\n

    Now, if you need to do the same for a different month or a different quarter or a fiscal year, you’d need to repeat the same steps. In this particular case, it might be okay to keep repeating, but in other cases such as property sales etc. where data comes in more frequently, keep repeating these steps might not be a good idea.<\/p>\n

    Instead wouldn’t it be more efficient if you could define such layer just once, and click a button to switch from one month to another month or quarter to produce map or data for that particular time window?<\/p>\n

    That is exactly what you can do with Make Aggregation Query Layer<\/a> geoprocessing tool. You will find this tool inside Layers and Table Views <\/strong>toolset in the Data Management Tools<\/strong> toolbox in ArcGIS Pro.<\/p>\n"},{"acf_fc_layout":"image","image":{"ID":1391622,"id":1391622,"title":"MakeAggregationQueryLayer_Annotated_small","filename":"MakeAggregationQueryLayer_Annotated_small.png","filesize":89199,"url":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small.png","link":"https:\/\/www.esri.com\/arcgis-blog\/products\/arcgis-pro\/announcements\/make-aggregation-query-layer-a-new-geoprocessing-tool-to-compute-and-view-dynamic-summary-statistics-from-related-records\/makeaggregationquerylayer_annotated_small","alt":"","author":"5971","description":"","caption":"","name":"makeaggregationquerylayer_annotated_small","status":"inherit","uploaded_to":1391512,"date":"2021-11-08 04:50:59","modified":"2021-11-08 04:50:59","menu_order":0,"mime_type":"image\/png","type":"image","subtype":"png","icon":"https:\/\/www.esri.com\/arcgis-blog\/wp-includes\/images\/media\/default.png","width":241,"height":547,"sizes":{"thumbnail":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small-213x200.png","thumbnail-width":213,"thumbnail-height":200,"medium":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small.png","medium-width":115,"medium-height":261,"medium_large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small.png","medium_large-width":241,"medium_large-height":547,"large":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small.png","large-width":241,"large-height":547,"1536x1536":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small.png","1536x1536-width":241,"1536x1536-height":547,"2048x2048":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small.png","2048x2048-width":241,"2048x2048-height":547,"card_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small-205x465.png","card_image-width":205,"card_image-height":465,"wide_image":"https:\/\/www.esri.com\/arcgis-blog\/app\/uploads\/2021\/11\/MakeAggregationQueryLayer_Annotated_small.png","wide_image-width":241,"wide_image-height":547}},"image_position":"right","orientation":"vertical","hyperlink":""},{"acf_fc_layout":"content","content":"

      \n
    1. Open the tool<\/li>\n
    2. Fill in the top part with\n