The fire department needs an updated wall map of city roadways with a lookup grid and an alphabetically sorted index of roadway names for rapid review. This workflow creates a grid index, and joins its identification information to all roadways within each cell; which are then consolidated into a reference index.
While this illustration uses a gridded map index, this method can be used to summarize features that exist across any kind of aerial boundary unit: for instance zip codes, districts, neighborhoods, etc.
Here is the City of Cocoa's footprint:
Create an Area of Interest Layer
This is for firefighters at Station 2, so their area of interest is mainly focused in the southeast portion of the city and surrounding areas. I created a polygon layer to describe that footprint, using ArcGIS Pro's Direction tool in the Edit toolset to snap digitized vertexes to cardinal directions for 90º corners.
Since the definition query is already in place, and the intended order is already sorted based on the object ID, we can run a custom field calculation to create a new contiguous unique ID to fix these gaps.
Here's what's happening in each of these new fields:
ℹ Precision isn't super important here, so I didn't switch over to a Mercator projection for digitization. I left the coordinate system in NAD83 Florida State Plane East (FIPS 0901) US Feet to avoid errors in the next step which is more important.
Create Fishnet Grid
We'll test ½ square mile grid cells (2640' x 2640') to balance fitting a sufficient amount of streets into a grid cell without being too densely populated and hard to identify a roadway within a cell.
- Open the Create Fishnet tool
- In ArcGIS Pro 3.0, open the dropdown at the top of the Template Extent section and choose Browse to select the Area of Interest layer that we created above as the template for the extent of this grid feature class
- Ensure you're using the appropriate coordinate system with expected unit of measure for your own needs. I'm using FL State Plane East with US Feet as the unit of measure so I can enter a value of feet for an accurate grid cell size
- Enter 2640 in the Cell Size Width & Cell Size Height fields (which is half a mile in US feet)
- Set the Geometry Type to Polygon and run the tool
Cull & Customize the Grid
Many of these cells are over water, and I'd prefer to get rid of those and use that space for the Index. Even if the index doesn't fit there and I end up printing it out separately, I'd still prefer to remove those unnecessary cells in the northeast.
- Open the Attribute Table
- Add a Field to named Status
- Manually select of those unwanted cells (107-110, and down running southeast to cell 33)
- Calculate Values of "Blank" or "Remove" or whatever
- Open the Layer Properties for that Fishnet layer and navigate to the Definition Query section
- Click New definition query
- Where Status | Is Null
Re-Calculate Grid IDs
This will remove those features from the view, but the grid ID numbers will become inconsistent: e.g. After cell 32, numbering will continue on the next line above at 34.
ℹ Optional: To be safe, you can create a new field (named ID_Original) to preserve the original ID. You'll first need to temporarily disable the definition query to record all values; Calculate Field on that new ID_Original and simply use the OID field to populate the ID value for every record. When finished, re-apply the definition query.
- Create another new field named ID_Truncated or something similar
- Ensure there is no selection, and the definition query is set to exclude any unwanted parcels (see the graphic above)
- Open the Calculate Field dialog to calculate values on that new field
- ID_Truncated = autoIncrement()
- Code Block: Enter the following script and click Apply to run
rec=0
def autoIncrement():
global rec
pStart = 1 # adjust start value, if req'd
pInterval = 1 # adjust interval value, if req'd
if (rec == 0):
rec = pStart
else:
rec = rec + pInterval
return rec
- Finally, change the labeling field to the new ID_Truncated
Join Grid IDs to the Roadways
Now run an Intersect process to join the grid cell IDs to each roadway, while splitting roadway segments at the borders of each grid cell- Input Features: the Roadway Centerlines layer, and the Fishnet grid layer
- Attributes: All
- Output Type: Line
The result will be a line dataset set to the extent of the fishnet grid:
Export to Spreadsheet Editor
Move over to Excel/Sheets to finish processing. Run the Table to Excel tool to export the attribute table of the resulting Intersect process. This will make it easier to consolidate the dataset into distinct roadways with multiple IDs concatenated together. Here's an example:
- Open the dataset in Excel and hide all field besides the Address Label (from the Roadway Centerlines dataset) and the ID_Truncated field (from the fishnet grid)
- Add three field headings after these two fields: named Count, End, and Formatted
Clean up the Data
- Select the first row of headings, navigate to the Data tab and click the Filter button to turn on field filtering tools
- Sort the Address Label field, ascending
- Remove any blank or null Address Labels by selecting those records and deleting those rows
- Select cell A1 and press Ctrl+A to select all records
- Navigate to the Data tab and run the Remove Duplicates tool
- Select the Address Label and the ID_Truncated fields and click OK to run. The tool will report how many duplicate records were removed
Create Analysis & Formatting Formulas
Use the following formulas for those three new fields. Count and End are for reference and filtering use later, while the Formatted field is going to help build the index mapping that we're summarizing
- Enter the following formulas for each cell:
- Count: =COUNTIF($A$2:$A$1177,A2)
* Where 1177 is the last row in the dataset - End: =IF(A2=A3, "", "End")
- Formatted, line 2: =B2
- Formatted, line 3: =IF(A3=A2,E2 & ", " & B3, B3)
- Fill the formulas down the the bottom of the dataset, leaving cell B2 as a unique outlier to help start the formatting trick
- The Count field compares downward, and aims to report how many times an Address Label is present across the dataset. A Lane is in the dataset twice, while A Street is in the dataset three times.
- The End field aims to mark the end of a group of addresses. We'll use this flag for filtering reasons next. Notice the value of End on the 2nd of two records for A Lane, and the 3rd of three records for A Street.
- Most importantly, we're beginning by taking the first ID in the dataset and writing that to the Formatted field. On the next row, we're looking upward to see if this next record's address is the same as the previous. If so, that means our address is found on an additional grid cell, so we're concatenating it with the previous cell, separated with a comma and a space. If the previous Address Label is different, then it's a new address and we'll pull a new ID by itself.
Consolidate with Formatting
Now we'll finish up by applying that filter.
- Make those formulas permanent by copying those cells and Paste Special > Values
- Select the filter menu for the End field and remove the Blank category. Click OK to apply the filter
- The unnecessary records are merely hidden here. Hit Ctrl+A to Select All records, and Paste it into a new worksheet to make the dataset permanent. Notice the spreadsheet's row numbers are now sequential
Stylize & Finishing
- Remove the ID_Truncated, Count, and End fields
- Rename the remaining fields to Roadway, and Grid
- Optionally, you may want to use the formula =PROPER(A2) or =UPPER(A2) to change the case of the Address Label field as needed
- Adjust the formatting of the font size and typeface to suit your map's style
At last, copy and paste the range (or segments of the dataset at a time) back into your mapping project for final cartography and layout.
