Category: Tools

Show expansion of transit networks over the decades using Transit Explorer

Yonah Freemark just launched the biggest expansion of transit network mapping on Transit Explorer and I built a new feature for TE that allows users to visualize the size of a network by decade from 1970 to today.

When looking at a region of the world, change the era of transit network that you’re seeing by selecting a decade from the dropdown menu under the “Change the era” heading. In a moment, the map will automatically refresh.

I’ll show you three cities:

  • Salt Lake City
  • Hong Kong
  • São Paulo

Salt Lake City

The Utah Transit Authority opened its first modern light rail line in 1999 from Salt Lake City to the southern suburb of Sandy. It opened the second line, from downtown Salt Lake City to the University of Utah, in 2001, in time for the 2002 Winter Olympics. Some games and ceremonies were held at the university. FrontRunner commuter rail opened in 2008, and the third light rail line, to the airport, opened in 2011 and various line extensions opened in 2013. A BRT route that opened in 2018 in Provo is also mapped but not shown while a BRT route in Salt Lake City is shown under construction in 2024.

View Salt Lake City on Transit Explorer

Hong Kong

In 1970, the only rail transit that Hong Kong had were a tramway on Hong Kong Island and the East Rail Line, which opened in 1910 and was electrified in 1983. Every decade since there was a new line or two on the Hong Kong MTR network, culminating in the ten lines you see in 2024.

View Hong Kong on Transit Explorer

São Paulo

In 1970, São Paulo’s metropolitan network, Metrô, didn’t exist, but it had six regional train lines operated by three railroads – these kinds of trains are shown in brown on Transit Explorer. The metro, shown in blue, began in 1974, and now has six lines (including one monorail line) while the regional train network was modified to five lines. São Paulo saw its first bus rapid transit (BRT) line added in 1988, but added four more BRT lines since then – just five of the over 900 bus routes operated by EMTU.

View São Paulo on Transit Explorer

I played “Cities: Skylines” for the first time

I wouldn’t have guessed that this city simulation video game turned eight years old last month. I’ve known about it for a long time, and I even subscribe to the r/CitiesSkylines subreddit to see screenshots that people post showing their cities.

screenshot of a town created by u/Tramter123 (posted to Reddit) showing a lot of surface car parking lots
The screen grab above isn’t mine. It looks like u/Tramter123 got carried away trying to recreate Any American City and built a lot of surface parking lots. I will need to learn how that affects traffic flows and land values in the game’s mechanics.

What changed is that last week I was having dinner with a friend and he described the videos published by City Planner Plays, a practicing urban planner who resides in Madison, Wisconsin. I watched a video with a title that caught my attention (it was about monorails and trams) and a couple of days later I decided to try it out.

My first gaming session was a little frustrating or stressful…I don’t think having studied how cities happen and how they happened has given me any insight into how to be successful at this game.

I’m still learning how to manipulate the game yet I’m already familiar with the process of this game that it shares with many others: as something grows, you unlock more resources but have to respond to more needs. In Cities: Skylines, the population grows, a cemetery is unlocked, and then someone dies so you have to build a cemetery immediately.

In Rollercoaster Tycoon, the goal was to increase your park’s visitors by building more rides. As attendance grew so did the level of trash and the number of janitors that had to be hired, but sometimes you had to wait to earn more revenue because you couldn’t afford a third janitor yet.

My little town in CS is called Springvalley, which started with being connected to an expressway interchange. The water source is a river. I tried to build a quay and some waterfront property but the city is on a cliff and I wasted a bunch of money trying to level and reshape the earth.

Since Springvalley reached a population of 2,500 people this evening (during my second session of the day) the game unlocked transit. Okay, I think this is why I want to keep playing – I want to see if I can design a transit network bound by whatever constraints the game has implemented.

City-building in real life

I posed a couple of questions to myself after playing. City-building is kinda fun [yeah, duh] but is it possible to make real-life city building fun and more broadly enjoyed?

Relatedly, are there ways I can modify and use Chicago Cityscape to guide people through the local city-building process using the fun and mechanism games like Cities: Skylines (and its predecessor, SimCity)? Read Anthony Moser’s response.


Enjoy this short video of the camera following a bus to the industrial district (and excuse all of the pollution).

Creating a PostgreSQL PostGIS function to get around a DataTables Editor limitation

DataTables is a fantastic software that turns any HTML table into an interactive and editable one.

Chicago Cityscape uses DataTables on pretty much every page. DataTables also provides server-side processing for PHP to grab the right data from a database and show it to the user, 10 records at a time (the number of records can be changed by the user to show more records at a time).

Screenshot showing my new function, using the function, and the results.

Problem

One of the problems I’ve had to get around is that the DataTables Editor script recognizes a SELECT statement with only one function per field. If there’s a second function that’s manipulating a field in the SELECT statement then the ALIAS won’t work and the JavaScript will not be able to read and show the data properly.

I almost always use the two functions ST_AsGeoJSON and ST_Transform on my geographic data to (1) transform it from the SRID of 3435 to 4326 for displaying on web mercator maps, and (2) converting the GIS data into GeoJSON strings for easy transference to the Leaflet JavaScript library.

This is a known issue – see this thread on the DataTables forums – that Allan, the DataTables creator, has acknowledged and provided an alternative solution for.

Solution

It turns out that it’s easy to write a function that combines both functions.

CREATE OR REPLACE FUNCTION ST_TAGJP (field geometry, srid int =4326, simplify int =5)
  RETURNS text
RETURN ST_AsGeoJSON(ST_Transform(field, srid), simplify);

The code above is a function I call ST_TAGJP that combines the two functions I already described, and is flexible by letting the user specify in the arguments the table and field, the SRID to transform (reproject) to, and the simplify variable that can be used in ST_AsGeoJSON that tells it how many decimal points to use in coordinates.

Automatically post your Flickr photos to your Mastodon account

One process I’ve relied on in the past to keep my Twitter postings fresh is automatically cross-posting photos that I upload to Flickr to Twitter. This is done through Zapier and inspired by this answer in their forums.

Zapier currently doesn’t have any Mastodon integrations, but it’s possible to use Mastodon’s API and Zapier’s webhook function to notice your newly-uploaded photos from Zapier’s Flickr integration and “toot” them to your Mastodon account.

Note: I originally set this up as an RSS feed to Mastodon Zap before realizing that Zapier already has a Flickr integration.

First, to prepare for creating a Zap later, you’ll need to create an app in your Mastodon server. You can create an app by going to edit your profile and then opening the “Development” tab.

Select the “New application” button, give it a name, change the privileges, and use “http://zapier.com” as the only Redirect URI. Then check the appropriate boxes so that only a single privilege is checked: “write:statuses”.

Give your application a name, enter https://zapier.com as the Redirect URI, and check only the box next to “write:statuses”.

Want to get more advanced? Read the Mastodon API docs.

Mastodon will create a token for you to insert at the end of this URL that Zapier needs: https://mastodon.social/api/v1/statuses?access_token=INSERT_YOUR_ACCESS_TOKEN

If your Mastodon account is on a different server, then replace “mastodon.social” with the domain name of the server where your Mastodon account is.

Secondly, go to your Zapier account and create a new Zap. Search for the trigger app “Flickr” and then connect Zapier to your Flickr account if not already connected. The trigger should be something like what you see in the screenshot below.

When you make a new zap, use these settings: “Connect this app…Flickr…with this one…Webhooks by Zapier”. Then, “When this happens…New Photo…then do this! POST”. Zapier will automatically give it a name that you can try later and then you can select the “Try it” button to get stated and customize.


You’ll need to customize the action that Zapier takes each time there’s a new photo. Until there’s an integration with Mastodon there’s a little complexity to defining the webhook action that Zapier will do.

The type of webhook you’ll create is a “POST” and you only need to add one field to the payload that’s sent from Zapier to your Mastodon: “status”. The value of “status” can be whatever combination of text and fields that Zapier pulls from your Flickr.

The minimum field to insert is the link to the Flickr photo page. Mastodon will need this to generate a rich media preview to add to the Toot (since it’s not possible to send image attachments).

You can start your Zap now! And follow me on Mastodon.

Want to use RSS instead?

Here are some loose instructions on how to set up the RSS feed that your Flickr account produces.

Insert your Flickr ID (not your username or email, find it here) into this URL which will be the RSS URL for Zapier to check:

https://www.flickr.com/services/feeds/photos_public.gne?id=INSERT_FLICKR_ID&lang=en-us&format=atom

Chicago Crash Browser is back

ChicagoCrashes dot org was, for many years, the only source for people to get information about traffic crashes in Chicago. I started it in 2011.

Chicago Crash Browser v0.2
A screenshot of Chicago Crash Browser v0.2 showing what the website looked like on December 30, 2011.

It was updated annually with data from two years ago, because of how the Illinois Department of Transportation processed the reports from all over the state. I shut it down because it had outdated code, I was maintaining it in my free time, and I didn’t want to update the code or spend all the time every year integrating the new data.

In 2015, the Chicago Police Department started testing an electronic crash reporting system in some districts that meant police officers could write reports and they would immediately show up in a public database (in the city’s data portal). The CPD expanded this to all districts in September 2017. (A big caveat to using the new dataset is that it has citywide data for only four and a half years.)

Since then, whenever someone asked me for crash data (mostly from John to illustrate Streetsblog Chicago articles), I would head to the data portal and grab data from just the block or intersection where someone had recently been injured or killed. I would load the traffic crash data into QGIS and visualize it. I found this also to be painstaking.

Now, with renewed attention on the common and unfixed causes of KSIs (“industry” term for killed or seriously injured) that we’re seeing repeatedly across Chicago – read about the contributing cause of Gerardo Marciales’s death – I decided to relaunch a version of Chicago Crash Browser.

The new version doesn’t have a name, because it’s part of the “Transportation Snapshot” in Chicago Cityscape, the real estate information platform I operate. It’s also behind a paywall, because that’s how Chicago Cityscape is built.

I wanted to make things a lot easier for myself this round and it comes with a lot of benefits:

  • Explore all crash reports in a given area, whether that’s one you draw yourself or predefined in the Cityscape database.
  • Quickly filter by crash type (bicyclist, pedestrian, etc.) and injury severity.
  • Download the data for further analysis.
A screenshot of a map and data table visualizing and describing traffic crash reports in Columbus Park.
What the crash data looks like within Chicago Cityscape.

How to access the Chicago Crash Browser

The crash data requires a Cityscape membership. I created a new tier of membership that cannot be signed up – I must grant it to you. It will give you access only to Transportation Snapshots.

  • Create a free account on Chicago Cityscape. The site uses only social networks for creating accounts.
  • Mention or DM me on Twitter, @stevevance, saying you’d like access to the crash data. Tell me what your email you used to create an account on Chicago Cityscape.
  • I’ll modify your membership to give you access to the “transportation tier” and tell you to sign out and sign back in to activate it.

Once you’re in, this video shows you how to draw a “Personal Place” and explore the traffic crash data there. Text instructions are below.

  1. From the Chicago Cityscape homepage, click on “Maps” in the menu bar and then click “Draw your own map”.
  2. On the “Personal Place” page that appears with a large map, decide which shape you’d like to draw: a circle with a radius that you specify (good for intersections), a square or rectangle (good for street blocks), or an arbitrary polygon (good for winding streets in parks). Click the shape and draw it according to the onscreen instructions. For intersections I recommend making the circle 150 feet for small intersections and 200 feet for long intersections; this is because intersections have an effect on driving beyond the box.
  3. Once you’ve completed drawing the shape, a popup window appears with the button to “view & save this Personal Place”. Click that button and a new browser tab will open with something called a “Place Snapshot”.
  4. In the Place Snapshot enter a name for your Personal Place and click the “Save” button.
  5. Scroll down and, under the “Additional Snapshots” heading, click the link for “Transportation & Jobs Snapshot”; a new browser tab will open.
  6. In Transportation Snapshot, scroll down and look for “Traffic crashes”. You’ve made it to the new Chicago Crash Browser.