Main Street. Will people bike there?
Python, SQL, QGIS, and ValleyBike trip data reveal the average cyclist’s range.
One in a series of projects highlighting my progress as a self-taught programmer.
I began teaching myself Python, SQL, and GIS in spring 2023 — starting from zero. I therefore welcome feedback on these projects and review for errors. And I’d be interested in taking a crack at your own data and geospatial questions too. Please get in touch.
* Update, 22 September 2023: I’ve posted an addendum here.
* Update, 18 September 2023: A reader of this post commented that the ValleyBike system uses electric-assist bicycles (at least during the period studied; the bike share is currently not operating while the City of Northampton seeks a new vendor/operator). I will be reviewing data to see which of my conclusions this fact might affect, and to what extent.
12 September 2023
Background
There’s a fierce debate raging in my western Massachusetts town (pop. 30,000): Is the bike- and pedestrian-friendly redesign plan for Northampton’s Main Street (a) a future-thinking, “once-in-a-generation opportunity” to revitalize downtown and mitigate climate change impacts, or (b) a costly, unnecessary disruption that will devastate local business and choke traffic?
While the points of contention are several—vehicular flow, the fate of parking spaces, foot traffic—my attention has gravitated to arguments over the proposed bike lanes for Main Street. Currently, arterial rail-to-trail paths ably deliver cyclists and pedestrians to the town center periphery. But riders are on their own along Main Street and connecting commercial streets, a stretch ranked among the most dangerous in Massachusetts.
How exactly to design and situate Main Street bike lanes may merit discussion, but many lane skeptics also take issue with incentivizing arrival by bike at all—on the grounds that, basically, people don’t do that. In addition to contending the impossibility of grocery shopping by bike (though, n.b., there are no major supermarkets on Main Street), they seem to view Main Street biking as an activity possible only for a centrally situated minority. A sampling of opinions in our local paper:
“…I’d like to make a list of why a person cannot ride a bicycle to town… They live too far from downtown for a round-trip bike ride… They don’t have the time for a bike trip...” (Source.)
“[City leaders should] not prioritize a nice perk for the select few who are lucky to live close enough to bike at the expense of the vast majority.” (Source.)
“No way someone who lives over 2 miles from the center of town (and there are many of us) is going to bike to a grocery store, for example, and haul those packages back, or eat out at night and bike back over a dark road.” (Source.)
“It seems preposterous to me that the majority of people within four miles of downtown will start accessing [Main Street] by foot or bike.” (Source.)
In short, one of the arguments against Main Street bike lanes is that, in our valley town, Main Street is much too long a bike ride away. But is it? There is public data that can help answer this question: The database of all rides taken through our regional bike share program, ValleyBike.
Project outputs
Is Main Street “preposterously” distant from the average town resident? Are the cyclists we currently see downtown merely “the select few who are lucky to live close enough to bike”? ValleyBike data suggests the answer is “no.” In fact, when we study the ridership, Main Street appears to be both a popular destination and a reasonable ride away from almost everyone in town.
For context, ValleyBike, like New York City’s Citi Bike system, runs a network of docking stations from which users rent and return bikes under per-ride or membership pricing schemes. There are some 70 stations in several clusters across Massachusetts’ Pioneer Valley, including 19 in Northampton—three of those are on Main Street itself, with a fourth nearby serving the same commercial area. I mapped them in QGIS using City of Northampton data:
Then, using Python to combine and clean ValleyBike trip data from 2021 and 2022, and SQL to query the dataset, I examined usage of Northampton’s four central bike share stations:
Overall, across the entire system, ValleyBike users took 204,346 total trips between January 2021 and October 2022.
While the two most popular stations from which to rent a bike (i.e. start a trip) were on the University of Massachusetts - Amherst campus, Northampton’s central docking stations all made the top 25: Northampton Train Station (#3), Pulaski Park/Downtown (#11), Main Street/Court House (#18), and Main Street/Bridge Street (#24). These four stations were, respectively, the 1st, 3rd, 5th, and 7th most popular stations for users to start a trip within Northampton.
The four central stations were just as popular locations at which to return a bike rental (i.e. end a trip). Within Northampton, they ranked 1st (Northampton Train Station), 3rd (Pulaski Park/Downtown), 4th (Main Street/Bridge Street), and 5th (Main Street/Court House).
So, among bike share users at least, these results indicate a high interest in biking to and from the Main Street area specifically.
And how long are those trips to/from Main Street? The answer, per further SQL analysis, is about 20 minutes.
To arrive at this figure, I queried my 204,346-trip dataset for the average duration of trips starting from or ending at each of the four central stations. To account for false-start rentals and other potential user errors, I limited the query to rides longer then 2 minutes and less than or equal to 45 minutes (a possible trip duration for the Northampton-Amherst commute and the cut-off for free rides under several membership plans). Importantly, I also discarded trips that started and ended at the same station—for this analysis, we are interested in trips that are leaving, arriving at, or crossing Main Street, not loop trips. (Despite this paring of the dataset, trip counts in all cases still represented 52 to 65 percent of all trips to/from these popular stations.)
It really looks like riders are fine with 20-minute trips:
| Start station | Number of trips 2 to 45 min | Average trip duration (min) |
|---|---|---|
| Northampton Train Station | 5045 | 21.69 |
| Pulaski Park/Downtown | 3578 | 18.50 |
| Main Street/Court House | 2420 | 19.42 |
| Main Street/Bridge Street | 2310 | 19.66 |
| End station | Number of trips 2 to 45 min | Average trip duration (min) |
|---|---|---|
| Northampton Train Station | 5239 | 21.66 |
| Pulaski Park/Downtown | 3758 | 17.71 |
| Main Street/Bridge Street | 2431 | 18.20 |
| Main Street/Court House | 2283 | 19.67 |
And where does 20 minutes on a bike get you to, exactly? I ran a few isochrone calculations from the Main Street area docking stations to find out:
That is a pretty broad swath of Northampton—and beyond. In fact, the QGIS OpenRouteService isochrone tool estimates the catchment area spans approximately 30,000 people (i.e. equivalent to Northampton’s population). And my own QGIS spatial join calculations find 84 percent of Northampton’s 12,737 structures lie within the 20-minute “radius.” Thus, not only is Main Street a popular bike ride start/endpoint, but ValleyBike data shows us 20-minute one-way trips are perfectly routine, with much of Northampton well within reach.
So, what about skeptics’s consternation about riding two (“no way”) or four (“preposterous”) miles? I dropped two- and four-mile buffer rings around the bus stop in front of Thornes Marketplace on Main Street. As it happens, it appears the average rider does not flinch at these distances, for only at the outer reaches of the four-mile ring do we find structures beyond the 20-minute downtown isochrone:
(The ValleyBike dataset does includes trip distance data, but I used that information as a check rather than primary point of study due to suspect values (e.g. non-loop rides longer than two minutes tallying 0 miles) as well as this post’s interest in the time riders are willing to spend on a bike—for the purpose of our analysis, it is more important to know riders can pedal for 20 minutes, since those 20 minutes could get one halfway to Amherst or dozens of laps around Pulaski Park. Regardless, my SQL queries found average trip distances of 2.5 miles, give or take, depending on the floor I allowed, which overlaps considerably with the 20-minute isochrone.)
Another telling comparison involves cars. The catchment representing a 10-minute drive from a parking lot and garage directly behind Thornes looks quite similar to our 20-minute biking isochrone. The comparison of course grants cyclists a 10-minute head start, but I have to wonder if that gap is all but closed when accounting for a driver’s need to make parking payment and walk to Main Street proper—where our cyclist would have locked up.
In sum, to the extent skeptics might have valid points about cycling-able populations, ValleyBike data should assuage their incredulity over Main Street as a viable biking destination. As we have seen, riders seek to cycle to and fro all the time, and they can do so from far and wide.
Techniques
Much like my NYC Citi Bike projects, I used a host of SQL queries, functions, and clauses to analyze ValleyBike data. To prepare the data, I did a bunch of cleaning with Python and the pandas library, including several .replace, .rename, .drop, and .merge methods when standardizing station information and adding it to trip data.
In QGIS I used the Open Route Service API to generate isochrones and labored through lots of filtering, layer-copying, and symbology and labeling expressions in order to get my maps right—many of the paths and streets in the bike and road layers were comprised of many segments, complicating labels, deletions, highlights, etc. A spatial join (Join attributes by location (summary) tool) enabled me to perform a polygons-within-polygon calculation.
Data sources
I downloaded and processed ValleyBike trip data from a City of Northampton site, which was linked from a separate City of Northampton ValleyBike page. The Amherst-Statistics GitHub page and a ValleyBike annual report got me started with station addresses, and bike path shapefiles came from MassGIS. Other sources included:
