For this walkthrough we're going to develop a dashboard based on health code violation data for Allegheny County (PA). These data are hosted by the Western Pennsylvania Regional Data Center.
A. Violation Data Download
- Browse to the violation dataset here.
Follow the Food Facility/Restaurant Inspection Violations link.
Take a moment to study the data preview. We'll be making use of the following columns:column description encounter unique ID assigned to each inspection; as inspections often result in multiple violations, values in this column may be repeated id unique ID assigned to the facility that was inspected facility_name self explanatory bus_st_date business starting date description one of 50+ business categories, such as 'Restaurant with Liquor' description_new one of 30+ violation types, such as 'Date Marking of Food' or 'Pest Management' inspect_dt the date of the inspection municipal one of the county's 129 boroughs or townships, or one of Pittsburgh's 35 wards rating V = violation, N = non-critical violation url web address for complete inspection report in PDF form - Download the dataset in CSV format. It will contain 400,000+ records and may take a few minutes to download. Note that the location of the facility is not included. For this, we'll download a different dataset.
- Return to the Food Facility Inspections page and follow the Geocoded Food Facilities link.
This dataset includes an id column corresponding to the one in the violations dataset along with x and y columns (longitude and latitude). In addition to these, we'll also be making use of the bus_cl_date (closing date) field. - Download this dataset in CSV format as well. It will contain 32,000+ records.
- Rename the first file 'violations.csv' and the second 'facilities.csv'.
B. Violation Data Preparation
- Open ArcGIS Pro, start without a template, and establish a connection to the folder where you saved the data above.
- Create a new file geodatabase in the same folder.
- Import the two CSV files into the geodatabase as tables.
- Run the Join Field tool to join the tables on the id field, making violations the Input Table and facilities the Join Table. (Be sure to use id and not _id!). For Transfer Fields, choose bus_cl_date, x, and y.
- Open the violations table and confirm that the transfer fields were added successfully.
- Remove fields that are not of interest: _id, start_time, end_time, low, medium, and high. While these can be removed one at a time from the attribute table GUI, you may find it more efficient to go to Data Design > Fields, select the fields while holding the Ctrl key, and then click Delete.
- To add some clarity, change the name of the description field to facility_type and the description_new field to violation_type. Adjust the aliases as well.
- Click Save on the ribbon to save these schema changes.
- Right-click on the violations table in the Contents pane and choose Create Points From Table > XY Table to Point.
- Set the Output Feature Class to violations_fc, storing it in your file geodatabase created above. Click OK to create the feature class.
You'll see a warning about Null geometries. There are some 5800+ violations that had no X/Y coordinates in the facilities table, roughly 1.4% of the full dataset. In a real-world project, you might want to work to reduce this number, perhaps geocoding the locations using their addresses. We'll just move on.
You should see the new violations_fc layer added to the map.
C. Municipal Boundary Compilation
400K+ features is a large dataset. One way we'll make it more manageable is to allow the user to select a municipality of interest from a list. We could produce this list from a standalone table, created by summarizing on the municipal field. However, we'll also want the map to zoom to the selected municipality, which can only be configured if the list is based on a feature layer (i.e., we need the polygon geometries too). The municipal field contains values for 129 boroughs and townships (Aleppo through Wilmerding), but also 35 wards in the City of Pittsburgh (Pittsburgh-101 through Pittsburgh-135). The boundaries can be found in the same portal where we downloaded the Health Department data, though the wards are in one dataset and the municipalities in another. So in this section we will download and merge the two datasets.
- Browse to the Municipal Boundaries dataset here and download it in Shapefile format.
- Import the shapefile into your file geodatabase, renaming the new feature class townships_and_boroughs.
- Repeat the above steps for Pittsburgh's wards (dataset here). The name of this feature class should default to Wards, which is fine.
- Add the two feature classes to your map in ArcGIS Pro.
We'll need to make some edits to each of these layers. Let's start with the townships_and_boroughs.
- Delete the City of Pittsburgh feature, making sure to Save your edit.
Now let's consider the formatting of the names. In the violations data, the municipal field values use title casing and generally omit the 'Borough' and 'Township' identifiers (e.g., 'Cheswick Borough' is recorded as simply 'Cheswick'). In the townships_and_boroughs layer, the NAME field values are all caps (e.g., 'CHESWICK') and the LABEL field includes the 'Borough' and 'Township' identifiers (e.g., 'Cheswick Borough'). Neither is a perfect match. Let's create a new field based off of the NAME values. - Add a new field called municipal (to match the violation data), setting its Type to Text and its Length to 50 characters.
- Use the Calculate Field tool to populate the field according to this expression:
!NAME!.title()
Manually edit values in the new column so that they match their spellings in the violation data. (You may want to sort on the field.) Be sure to Edit > Save when done!
Old value New value Baldwin Baldwin Boro Baldwin Baldwin Twp Ben Avon Heights Ben Avon Hgts East Mckeesport East McKeesport Mccandless McCandless Mcdonald McDonald Mckees Rocks McKees Rocks Mckeesport McKeesport Mount Lebanon Mt Lebanon Mount Oliver Mt Oliver Springdale Springdale Boro Springdale Springdale Twp Upper St. Clair Upper St Clair Now let's make the necessary edits to the Wards data.
- Add a similarly defined municipal field to the Wards feature class.
- Use the Calculate Field tool to populate this field with the following expression:
"Pittsburgh-" + !municode!
- Delete all fields from the Wards feature class with the exception of OBJECTID, Shape, GlobalID, Shape_Length, Shape_Area, and the just-added municipal. Save that edit.
Now we're ready to combine the two layers.
- Open the Merge tool, selecting Wards and townships_and_boroughs as the Input Datasets. Be sure that Wards is the first of the two.
Give the new feature class a name of municipalities, storing it in your project geodatabase.
For the Field Matching Mode parameter, select Use the schema of the first dataset only.
Click Run to perform the merge.
- Open the new feature class's attribute table. It should contain 164 features and have values in the municipal field for all features.
- Save your Pro project now with a name of your choosing.
Ultimately we're going to want to do some more data wrangling in Pro, but we can shift our focus to starting the dashboard now. Let's publish our violations and municipalities data to ArcGIS Online, making it available to the Dashboards platform.
D. Publish the data to ArcGIS Online
At this point we could symbolize our data in Pro and share the map as a web map for use in AGO. Or we could share the layers individually and do the symbolization and web map creation in the AGO map viewer. Let's do the latter.
- Right-click on your violations_fc layer and choose Share > Share As Web Layer.
- Assign a name of Violations_<your initials or PSU id> to the new layer. (Layer names must be unique across AGO organizations...)
- Create a new folder for this project's data, with a name like alleghenyco.
- Click Analyze to determine whether the data is ready to be published.
You should receive an error that Unique numeric IDs are not assigned.
- Right-click on that error message and select Auto-Assign IDs Sequentially.
You should also see a warning about some field types not being supported by all clients. We can safely ignore that.
- Click the Publish button. The publishing process may take a couple minutes. You may see a warning message similar to the one reported when you ran Analyze. Click OK to dismiss the warning. Eventually, you should see a message that the layer published successfully.
- Repeat the above steps for your municipalities layer, naming it Municipalities_<your initials or PSU id>.
E. Create a web map containing the violation data
- Sign in to the PSU AGO organization.
- Go to the Content tab and look in your alleghenyco folder. You should see the two feature layers published above, along with a service definition item for each.
- Click the ... next to the Violations layer and select Open in Map Viewer.
- Feel free to change the default symbol for the violations to some other shape/color, if you like. We're going to focus our attention now on the layer's popup.
- Click the Pop-ups button on the right-hand options pane. You should see a popup appear, giving you a preview of what end users will see.
We can make this information much more user friendly. We could keep the default tabular display of fields/values. If going this route (we're not going to), I would access the Fields list and remove fields not of interest to end users -- OBJECTID, encounter, id, x, and y. I would put the fields in a more logical order (by clicking the dots to the left of the field and dragging up/down). Lastly, I would click the Fields button (just below Pop-ups) and change the Display_Name for each field to implement title casing and to replace underscores with spaces.
Let's instead take full control over the display of the information by adding a Text element.
- Click the ... to the right of the Fields list and select Delete.
- Click the Add Content button and then Text.
An editor will appear with a number of tools for configuring the popup content. Note that attribute values for the clicked feature can be added by enclosing the attribute name in curly braces; e.g., {facility_name}. These can be added by typing manually or by accessing the { } dropdown list on the toolbar.
Configure the content as shown below. A few points to note:
- I used the table control to add a 2x3 table for the first three attributes. Tables appear centered in the editor, but will be left aligned in the popup itself when you close the editor.
- You can reduce the spacing produced when hitting Enter by holding down Shift at the same time.
- To produce the link, type the link text first, select that text, then click the link button and set the link URL to {url}. (Recall there's a field in the attribute table by that name.)
- Feel free to experiment and organize your popup differently. For example, you could choose to include multiple attributes in the popup title (maybe add the violation type to the facility name). Or you could include the business starting and closing dates. Or use an Arcade expression to calculate how long ago the inspection occurred, and include that next to the inspection date.
- If you know HTML, note that it's possible to directly edit the HTML source code produced by the editor.
Facility type: {facility_type} Inspected: {inspect_dt} Violation: {violation_type} Address:
{num} {street}
{city}, {state} {zip}
Full inspection report (opens in new tab)
With the popups configured, you then have a decision to make as to how to save those settings. If you click the ... next to the layer name in the Layers panel and click Save, you'll be saving the settings as part of the layer. The settings will "follow the layer around" in all web maps you add it to. If you don't save the settings as just mentioned, instead only saving the web map, the custom popups (along with other settings such as symbolization) will only appear in that web map. If the layer is added to other web maps, it will have the same default popup you saw earlier.
- Add the municipalities layer to the map.
- Apply a dark gray, 2-pixels wide, dashed outline symbol with a transparent fill.
- Also in the Symbology section of the Properties panel, toggle off the Show in map legend switch. No need to clutter the legend.
- The user should have no need to see popup info for the municipalities, so making sure that layer is selected in the Layers panel, go to Popups and toggle off the Enable popups switch.
- Remove your initials from the ends of the layer names (leaving just Violations and Municipalities). You're simply changing the layer's name within the web map, not the name of the underlying item in your AGO content.
- Save the web map with a name like Violations_Map_<your initials or PSU ID).
OK, let's get started on the dashboard!