ArcUser Online

• ArcUser Main
• Current Issue
• Previous Issues
• Subscribe
• Advertise
• Submit an Article

Summer 2011 Edition

Analyzing Frequent Responses

Using a US National Grid spatial index

By Mike Price, Entrada/San Juan, Inc.

This article as a PDF.

An index of US National Grid (USNG) coordinates can be summarized to provide public safety mappers with response statistics including number of calls for service, number of units deployed, and total deployment time.

Public safety providers know that calls to certain locations occur at a much higher frequency than others. Medical centers, care homes, schools, public buildings, and hazardous transportation locations are examples of places that receive a disproportionate number of emergency responses each year. Service providers carefully monitor trip counts and time in service for these locations and schedule apparatus and personnel to accommodate the high frequency of these calls. However, to understand these demand locations requires mapping, analyzing, and summarizing them.

Modern computer-aided dispatch (CAD) centers often provide longitude and latitude coordinates for emergency destinations. In the past, these longitude and latitude strings have been concatenated, or added together, to create a single text location index.

USNG provides a unique single-string address for every location on the planet. Using USNG, a single location may be described as a 15-character string with a resolution of one meter. ArcGIS 10 provides tools for calculating USNG addresses for points when other coordinates are available. Because modern CAD centers provide destination coordinates in longitude-latitude, this data can be used to index frequent responses.

In this exercise, we will summarize recent incident data for Redlands, California, and apply a USNG spatial index to count and summarize repeated travel to these high-demand locations. Once these frequent response locations are identified, the emergency facilities that serve these locations can be reviewed, and a preliminary understanding of typical time in service for these locations can be gained.

Getting Started: Setting Up the Redlands Fire Map

Download the sample dataset for this exercise, nationalgrid.zip. Unzip it into a project folder and open Redlands_Fire01.mxd.

This map shows highways and streets in the Redlands area and five fire stations that protect the area. The exercise uses actual incident-level data for 2010 responses in and around Redlands. Notice that this map's coordinate system is universal transverse Mercator (UTM) North American Datum of 1983 (NAD 83) Zone 10, and the unit of measure is meters. The Redlands Fire Department typically maps in California State Plane System, but because the exercise will use USNG (which uses metric units) to index these locations, the map in the sample dataset uses UTM coordinates. Although this switch is not required, it simplifies data validation and map grid display.

Bringing Apparatus and Incident Spreadsheets into ArcGIS 10

Microsoft Excel spreadsheets are often referred to as the "fire chief's database." Public safety data analysts often compile, convert, and transmit data in spreadsheets. In the past, GIS analysts often trained spreadsheets to behave like a database and exported the tabular data in dBASE format, which is compatible with the Esri shapefile format.

Newer versions of Excel (2007 and 2010) do not export dBASE files directly, so another approach was needed. ArcGIS can read Excel spreadsheets directly, but querying, editing, and other analytic functions are restricted or not possible. Using the file geodatabase, Excel worksheets can be imported into a GIS format with full table functionality.

1. Open ArcCatalog and navigate to \Redlands_Fire\XLSFiles\RFD_App_2010_Sample.xls. This sample of Redlands Fire apparatus-level data will help you better appreciate the complex nature of response data obtained from a CAD center. This spreadsheet contains one worksheet that lists several hundred sample response records for individual apparatus. Notice that each incident is assigned an incident number, alarm date, station and shift, incident type and initial dispatch code, unit ID, and numerous date/time stamps. Notice, too, that each apparatus record contains longitude-latitude coordinates and a descriptive address.
2. Scroll across the table for RFD_App_2010_Sample.xls. Because this is apparatus-level data, one incident may have more than one apparatus assigned, which generates multiple records for a single incident number. In the First_On_Scene field, the numeric code 1 indicates that this apparatus was the first to arrive at the incident. Response time for that incident will be measured by this unit's performance.
3. Preview the Database sheet for RFD_Inc_2010.xls. This incident-level data for 2010 will be imported into an ArcGIS file geodatabase. This table is a summary of more than 16,000 original records in the apparatus dataset. The total incident count for 2010 was 8,281. On average, two apparatuses were assigned to a typical incident, although many incidents required only one responding unit.
4. Sort this table on Responder_Count to see the maximum number of apparatuses assigned to each 2010 incident. This table also summarizes the total time accumulated for each incident from the time dispatched (T2) to the time cleared (T5). Look for these fields in the sample data. These times are stored in a legacy format (Lotus 1-2-3 Date/Time). They can now be managed directly in ArcGIS 10—a great enhancement for public safety analysts, for whom time is certainly of the essence.

Importing an Excel Worksheet into a File Geodatabase

After reviewing the source data table, import it into an existing file geodatabase.

1. In ArcCatalog, right-click RFD_Inc_2010's Database sheet and select Export, then To Geodatabase (single). Set Output Location to \Redlands_Fire\GDBFiles\CASP835F\Incidents.gdb and name it RFD_Inc_2010. Because this spreadsheet will be exported to a geodatabase, long field names will be preserved. Click OK to export the table.
2. Open the exported table in Incidents.gdb and verify that all 8,281 records were transferred. Sort the exported table on Responder_Count to see incidents with 12 responders.
3. Look at the data in the T5_T2_Sum1 column. Sort in descending order in this field. Notice that total deployment time for all apparatuses for the first incident was more than 340 minutes, so the average time per apparatus was just under 30 minutes. This incident is coded as a vehicle accident with injuries, and it appears that many medical units were needed. The 100 series incidents are mostly structure fires (code 111), which often require many apparatuses. Close ArcCatalog and return to ArcMap.

Calculating USNG Addresses for 2010 Incidents

1. In ArcMap, switch from Layout View to Data View. Click Add Data and navigate to \Redlands_Fire\GDBFiles\CASP835F\Incidents.gdb and add the RFD_Inc_2010 table. After adding it to the map, open the table and verify that it contains 8,281 records. The next step is to assign USNG coordinates to all records as a new point feature class. Inc_Number will be the unique string used to support the tabular join performed later in the exercise that will allow USNG coordinates to be joined to the RFD_Inc_2010 table.
2. Open ArcToolbox and choose Data Management Tools > Projections and Transformations toolset > Convert Coordinate Notation. This tool converts a table containing point coordinate fields to a point feature class. The coordinate field for the input table can be one of many notations (Global Area Reference System [GARS], Military Grid Reference System [MGRS], and others). The output point feature class contains a point coordinate field in the coordinate notation you choose—USNG in this case. Complete the dialog box as indicated in Table 1, making sure to set Output Coordinate Format to USNG.


Join the RFD_Inc_2010_USNG table to the RFD_Inc_2010 table.

3. Click OK to build this new point feature class. When the points are added to the map, open and inspect the table. It's pretty simple, but it is also very powerful. By joining on Inc_Number, all incidents in the table can be populated with a USNG coordinate.

Table Updates and Tabular Joins

1. Add a field to RFD_Inc_2010 to store the USNG coordinate. Open the attribute table, click Table Options in the upper left corner, and select Add Field. Name the field USNG, select Text as the field type, allow NULL values, and set the width to 20 characters. As presently deployed, USNG coordinates contain up to 15 active characters. With additional space for readability, it will become slightly longer.
2. Join the RFD_Inc_2010 table to the RFD_Inc_2010_USNG table using Inc_Number as the join field. Click OK to complete the join. Inspect the work and save the project.
3. Now populate the new USNG field with coordinates obtained from the joined table. Select USNG in RFD_Inc_2010 (the host table) and open Field Calculator. Make sure VBScript is selected and use the following string to calculate this field:
   
   [RDF_Inc_2010_USNG.USNG]
4. Click OK and inspect the values in the USNG field. You may need to drag out the cell for the USNG field. If it has been populated with the correct values, remove the tabular join. Close ArcToolbox.

Dialog box field	Value to input
Input Table	RDF_Inc_2010
Output Feature Class	\Redlands_Fire\GDBFiles\CASP835F\Incidents.gdb\ RFD_Inc_2010_USNG
X Field (Longitude)	LonDec84
Y Field (Latitude)	LatDec84
Input Coordinate Format	DD
Output Coordinate Format	USNG
ID (optional)	Inc_Number
Spatial Reference (optional)	GCS_WGS_1984

Table 1: Convert Coordinate Notation dialog box

Summarizing and Looking at Statistics

Because every incident contains a specific USNG address, the new USNG field can be used to summarize the total number of responses to each address. By tracking and summarizing the number of apparatuses assigned and the total deployment time for each incident, the equipment and time allocated to each location can be reviewed.

1. In the RFD_Inc_2010 table, right-click the new USNG field heading and check Summarize. In the summarize dialog box, expand Responder Count and check Sum.
2. Expand T5_T4_Sum1 and check Sum. Remember, this represents total time between dispatch and unit cleared for all apparatuses. Expand LonDec84 and LatDec84; set both to Average. Expand the Address1 text field and check First. Interestingly, many coordinate addresses have identical text addresses, but some have more. If you are curious, select Last as well and check the results. Store the summary table in \Redlands_Fire\GDBFiles\CASP835F\Incidents.gdb and name it RFD_Inc_2010_Sum1. Click OK to summarize.
3. Add and open the table; inspect your results. You should have 3,235 records. Save the map.

If you sort Count_USNG in descending order, you will see that 2010 included 129 trips to 1618 Laurel Avenue, involving 275 apparatuses and nearly 5,400 minutes (90 hours) of deployment time. There are four other addresses that generated more than 100 responses in a year. As you might imagine, these facilities are extended care/convalescent medical care facilities. In most jurisdictions, this type of facility generates the most calls. Let's put them on the map.

Plotting, Saving, and Symbolizing Frequent Responses

1. In the table of contents (TOC), right-click RFD_Inc_2010_Sum1 and select Display XY Data. In the dialog box, set Ave_LonDec84 as the X Field and Ave_LatDec84 as the Y Field. Click the Edit button and change the coordinate system to Geographic > World > WGS_1984. Click OK to add these points as an event theme.
2. Zoom in and query several points. In the Data Frame properties, set Display Units to US National Grid and check out point coordinates by comparing the USNG field for a point to its map coordinates. They should be identical. If not, something is wrong.
3. To test this, use the Info tool to examine the intersection of Church and Citrus. You can see how street names and addresses alone don't provide a good spatial index. When finished, zoom back out to Bookmark Redlands Fire 1:60,000. Save the project.
4. To make these points permanent, the XY theme should be exported to a geodatabase feature class. In the TOC, right-click RDF_Inc_2010_Sum1 Events and choose Data > Export Data. Export all features in the data frame's coordinate system and store them in \Redlands_Fire\GDBFiles\CASP835F\Incidents.gdb. Name the feature class RFD_Inc_2010_Sum2.


After using a definition query to suppress the locations with only one call, thematically map the remaining calls by frequency using the Frequent Responses layer.

5. Add these points in the map and inspect the table. Check that all 3,235 records are properly posted, then remove the event theme and save the map again. Practice sorting and selecting records to identify locations that cause frequent and time-consuming responses.
6. Wouldn't it be nice to load a predefined legend that shows the frequent responses by size and color? If you carefully studied the sample dataset, you might have noticed a layer file named Frequent Responses.lyr. This file will apply visual symbology to the sites of frequent responses. Before loading the legend, use a definition query to exclude points that have only one response. In the TOC, right-click RFD_Inc_2010_Sum2, select Properties, and open the Definition Query tab. Create and apply a definition query stating "Cnt_USNG > 1". It should reduce the point count to 1,073 points.
7. Finally, apply a legend. Reopen Properties for RFD_Inc_2010_Sum2, select the Symbology tab, and click the Import bar. Navigate to \Redlands_Fire\GDBFiles\CASP835F\ and select Frequent Responses.lyr. Apply the Value Field to Count_USNG and click OK. Before leaving Layer Properties, return to the General tab and rename this layer Frequent Responses. Click OK to apply these changes and inspect your work.
8. Finally, switch to Layout View and add Frequent Responses to your legend. Inspect your work and save one last time.

For some additional challenges, go back to the source incident data and map the responses by number of apparatuses per call or by total and average time on calls. You might even try mapping calls by National Fire Incident Reporting System (NFIRS) type. (Hint: 100 = fire; 200 = rupture or explosion; 300 = rescue or EMS; 400 = hazmat. Values above 500 are service and other calls.)

Summary

So what do you think? Is this a new use for USNG? Do you understand national grid data just a little better? For years, we have built complicated, unfriendly spatial indexes so we could efficiently summarize spatial data. Now, by simply applying a full 15-character USNG address to an incident set, we can quickly summarize and evaluate the data. Think of other ways to use this method (such as summarizing Doppler rainfall data over time). Index fire hydrants using USNG, and no two hydrants will ever have the same unique ID (unless, of course, they are on top of each other).

Additional USNG Resources

See "Introducing the United States National Grid" and "Rescue behind the Rocks." Both articles ran in the June–September 2005 issue of ArcUser.

Acknowledgments

Thank the fine staff of Redlands Fire Department and City of Redlands GIS for providing this very interesting and complex dataset. The actual data you just modeled supports ongoing public safety planning and deployment analysis in Esri's hometown.