The East Coast Greenway is always two things: A current route shown on public maps and supported with events and outings and other interpretation, and an envisioned route, which is an ever-changing mix of potential rights of way, routes under negotiation, active construction projects, and completed work. Before we built this system, Greenway staff manually updated an extensive spreadsheet with the status of each segment of their trail, but the segments were identified only by name, with mileages attached but without a rigorous spatial data connection. As a result, tabular reporting was often out of sync with their map data, and it was a tedious process of using consumer routing services like Google Maps to try to calculate distances to include in their tracking table.
Because East Coast Greenway staff were already managing the public route in CARTO.com, it made sense for them to do the same with their envisioned route. This is a rare web project where we wrote virtually no custom code, outside of some database queries. We took advantage of CARTO's excellent data APIs combined with a set of Google Sheets to power a live-updated spatial and tabular dashboard that's always up to date with the Greenway's current GIS information.
The result is an extensive and powerful Google Sheets dashboard with national and state-by-state views, updated every few minutes from the source data on CARTO.com. We also have a simple web map made using CARTO's built-in map tools, and an automated system to make monthly snapshots of the envisioned route data, which feed into a variant dashboard where program staff can look at past snapshots to record change over time and share results with stakeholders, funders, and the larger Greenway community.
In 2020, we added new demographics overlays, using the Census API to assemble a range of demographic data for all the states, from Florida to Maine, crossed by the Greenway, and then adding that data both to the QGIS editing system and the Google Sheets dashboards used by Greenway staff. We pulled demographics at the state, county, tract, and block group levels, and then automated a method to created buffered distance queries to assess who lives in tracts that are within anywhere from a half-mile to 25 miles of the planned Greenway route. This work allows trail planners to consider income, race/ethnicity, education, and other factors when assessing different potential trail routes.
