Steven’s note: I originally drafted this post in December 2016, for Transitland (my contract employer at the time). Here’s the original (archived) post on Transitland’s website.
Photo of a Russian-built tram in Saint Petersburg by Hans-Rudolf Stoll.
We [TransitLand] recently added the feed for surface transit in Saint Petersburg, Russia. ORGP is the transport organizer’s name in the Latin alphabet. ORGP website is a central source of information for the various municipally- and privately-operated ferry, bus, and tram routes.
St. Petersburg (SPb) also has trolley bus routes, which drive using electricity they collect from an overhead wire. These buses can only deviate from their route where an intersection between wires is available.
There are 1,066 routes in the SPb feed.
5 ferry routes
48 tram routes
51 trolley bus routes
Remaining are bus routes using conventional buses.
I couldn’t find ridership information for these routes, but the statistics on the Wikipedia article say that a much higher proportion of SPb residents ride the subway each weekday than people in Chicago ride the ‘L’ in my hometown. The ‘L’ runs mostly on elevated track, but has some subway and at-grade sections.
Speaking of the subway in SPb, we don’t have their GTFS feed yet and we could use your help. If you know the URL to the SPb’s subway feed, submit it on this form.
The Transitland connection
One of the features in Mapzen’s new Mobility Explorer, launched in late November, is to drill down into the transit routes, stops, and operators in a city – all of that data is based on what’s available in Transitland. It visualizes this data quickly and easily; think of it as an expansion of what Transitland’s Playground does.
Once you’re in Mobility Explorer, search for “Saint Petersburg, Russia” and click on the result. On the left sidebar, click on “show routes” and you’ll see that all of the routes in SPb will appear in the same color.
Click “style by: mode” to distinguish the subway, ferry, bus, and trolley bus routes in different colors.
A great feature of Mobility Explorer is that it tells you the Transitland API call to get the data in the current map view. You’ll find this link under the main “Show [xyz]” buttons in the left sidebar.
It’s very common see regular length buses with three doors in Europe, while buses in North America often have two doors. Articulated buses often have 3-4 doors, 1-2 more than their counterparts in the United States.
How many houses, buildings, units, and lots are vacant in Chicago depends on how you measure them. I’m aware of at least five ways to measure housing vacancy, using publicly available data, each with varying degrees of accuracy and coverage.
Each measurement method has its own TAKEAWAY so you can quickly scan to see how many units are potentially vacant. The bottom line, I believe, is that the vacancy rate in Chicago is quite low and vacant units is not a viable and scalable source for the additional housing that Chicago needs. However, I am compelled to add that it’s unclear how many additional homes Chicago needs because there are no analyses that uncover the shortfall or recommend a goal production number.
1. Vacant units, per Census bureau
The US Census Bureau says there are 1,258,704 dwelling units in Chicago, and that 10.2% of them are vacant (128,796 units). The Census bureau has two types of vacant, which generally break down into “listed for rent or for sale and temporarily unoccupied” and “all other reasons”. This data comes from American Community Survey (ACS) 5-year survey.
The Census says 37.3% of Chicago’s vacant units are “temporarily unoccupied” (38,450 units) and 62.7% are vacant for “other reasons” (64,589 units). This data comes from ACS 1-year data, which has larger margins of error than the 5-year survey data. The “other reasons” are what I am calling “truly vacant”.
The most common survey response within “other reasons” is that the unit is currently being renovated or repaired (20.2%). Additional top “other reasons” are:
Currently being renovated/repaired: 20.2%
Personal/family reasons: 15.6%
Needs repairs: 14.6%
Preparing to rent/sell: 13.1%
Abandoned/possibly condemned: 10.1%
Up For Growth, a national policy organization, has assessed that Chicagoland has an underproduction of 129,218 homes (using 2019 data), and about 3.3 percent of the existing housing stock in the region. Assuming that 80 percent of this underproduction is “assigned” to the City of Chicago, then this underproduction represents about 1.6 times the number of vacant units (vacant for any reason in the Census Bureau’s survey) that likely exist in Chicago.
TAKEAWAY from #1: Fewer than all 64,589 units are “truly” vacant, however. About 21,508 of those units are purportedly being renovated or repaired and will be rented or sold soon.
A large number of the remaining 43,081 “truly” vacant units are unlikely to join the rental or sale market soon. Assuming that all of these 43,081 vacant units are rented or sold then Chicago, using that 80 percent assignment of the 129,218 underproduction, would still have an underproduction of 60,294 homes.
2. Vacant units, per Chicago’s Vacant Building Registry
From a February 2023 snapshot of the Chicago Vacant Building Registry, which requires landlords to register buildings as vacant once they are vacant for more than 30 days, there were 6,521 dwelling units that were reported by owners as vacant. Comparing this to the above Census bureau figure that nearly 65,000 units are truly vacant, this would mean that 90% of vacant units are not registered in the VBR.
The reasons for vacancy were not included in the VBR data I received from the city. Also, these units are likely already included in the Census figure above and not in addition to.
TAKEAWAY from #2: There are at least 6,500 vacant dwelling units in Chicago.
3. Buildings reported as vacant to 311
Chicagoans can report to 311 that they suspect a building to be vacant. There is no link to building violation citations or feedback on these reports as to whether the suspicion was founded by a city worker.
Notice how the number of reports dropped by about half from 2022 to 2023. I don’t know if there are fewer suspected buildings to be reported, fewer people are reporting buildings, or there are barriers to reporting and collecting the reports.
TAKEAWAY from #3: This data is probably not reliable to understand the number of vacant buildings in Chicago.
4. Buildings cited as needing to be registered in the Vacant Building Registry
In 2023, Chicago Department of Buildings inspectors cited 24 buildings with a violation for not being registered in the Vacant Building Registry.
TAKEAWAY from #4: If about 90% of vacant units are not registered then there are drastically few citations being issued to force registration.
5. Vacant lots, per Cook County Assessor Office
The CCAO classifies nearly every property in Cook County. The classification 1-00 represents vacant lots. At present there are 32,207 vacant lots in Chicago. 22,645 of these (70.3%) are in “R” zoning districts and allow only residential uses. Another 4,566 lots (14.2%) are in “B” and “C” neighborhood mixed-use zoning districts.
TAKEAWAY from #5: Tens of thousands of new construction homes could be built on vacant lots.
Addendums
In its Chicago monthly market update (from January 2024) for the multifamily housing sector, Colliers brokerage reported that the Chicago MSA “has a vacancy rate of 5.3%, which is below the national rate of 7.6%”.
Brookings Institution posted an article (in October 2024) describing other sources for housing shortage estimates, from The National Association of Home Builders (NAHB), Freddie Mac, Moody’s Analytics (Mark Zandi), and National Association of Realtors (NAR). Brookings found that, nationally, the United States is short 1.5 to 5.5 million homes, according to the four sources.
A 2018 article from Freddie Mac determined that “the current pace of building is not enough to meet demand” and that 370,000 fewer units were built in 2017.
Chicago should have a rental registry, a database of dwelling units that are rented to tenants, for at least two reasons:
The city can know things about the rental units, including how much they cost, where they are, and if any are vacant and could be occupied if only people knew they were available and how to get in touch with the owner.
The city can know who the owners are and contact them to issue citations or advise them, or fill out for them, emergency rental assistance during pandemics and other times of necessity.
Building and administering a rental registry from scratch would be very expensive – probably tens of millions to start and more than one million annually.
I propose a kludgethat uses existing databases and modifies existing standard operating procedures amongst a small group of Cook County and Chicago agencies. A kludge is a workaround. It has other meanings and an uncertain etymology.
An ideal rental registry helps solve at least four problems:
A 9-unit apartment building in Little Italy is undergoing renovation.
The kludge has four parts
1. Incorporate data about the number of units declared on Real Estate Transfer Tax forms (which in Cook and many other counties are transmitted to the Illinois Department of Revenue digitally).
There is already a city office that reviews or audits these forms looking for instances where the buyer or seller incorrectly claimed certain exemptions from RETT, because of how the city can lose revenue. That office can also enforce that the number of units was correctly entered on the form.
2. For banks that hold city deposits, amend legislation to require that their newly issued or refinanced mortgages specify the number of units in the required submitted documentation. The ordinance that regulates banks that hold city deposits was amended a few years ago to require that they report how many loans they issue in Chicago for both commercial and residential properties.
Databases 1 and 2 are checks for each other.
3. “Hire” the Cook County Assessor’s Office to create and operate the database for the unit count data from 1 and 2 (likely as an augmentation of their existing database).
The database would also store any data the CCAO collects through the commercial valuation data they obtain from third party sources as well as from the owners who volunteer it (Assessor Kaegi is already collecting and publicly publishing this information).
At this point, with features 1, 2, and 3, we are assembling a pretty broad but incomplete record of where rental units are. It will be come more complete over time as properties transfer (sell) and the details of the transfer (sale), and the properties themselves, are recorded.
It doesn’t have a clue as to the rental prices.
4. The Cook County Assessor’s Office creates new property classifications. Property classifications allow for the comparison of like buildings for the purpose of establishing assessed values for all properties that are not tax exempt.
One of the most common classifications in Chicago is “2-11”, for apartment buildings with two to six units. This means that, generally, the value of the ubiquitous two-flats and three-flats get compared to other each other and sometimes to four-flats, etc.
I suggest that there should be a few new property classifications, but I have only one idea so far: classify limited equity and Chicago Housing Trust properties differently.
Bickerdike is one organization that built a lot of limited equity row houses and detached houses in the 1990s and 2000s but I am not aware of a publicly accessible database identifying them.
These houses represent permanently affordable housing and we should have a better system to track them!
This screenshot of part of a spreadsheet is the apartments data that the Cook County Assessor’s Office collected for the 2021 tax year.
How broad is the kludge?
Using the Real Estate Transfer Tax data from 2022 Q1 to Q3, there were 3,550 buildings in Chicago having 22,217 units transferred. (I don’t know how many were arms length transactions, meaning they were sold to new owners.)
In the CCAO’s apartments data collected for the Rogers Park Township, there is semi-detailed information about 715 buildings that have seven or more apartments comprising 18,541 units. Details include the unit size breakdown by bedroom count.
Chicago has 556,099 rented dwelling units in buildings with two or more units (according to the ACS 2021 1-year estimate). In my limited analysis I’ve already found data about 7.4 percent of them, and that’s only for part of the city [1].
Notes, limitations, and updates
[1] There may also be duplicates between the buildings in the RETT database and the CCAO apartments dataset.
These databases would not have information about detached (“single family”), single-unit semi-detached (rowhouses and townhouses), and condos used as rentals. This severely limits the coverage of information. As it stands, Chicago Cityscape has data coverage of unit count information for about 37 percent of multi-family (apartment) buildings.
5th Ward Alderperson Desmond Yancy proposed an ordinance that would establish a rental registry (O2023-0004085). The rationale for such is shown in the screenshot below. (Go directly to the ordinance’s PDF.)
Screenshot of the proposed rental registry benefits.
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.
This is one thing I wish I knew while learning PostgreSQL: Don’t ever use the VARCHAR column type. I have never encountered a situation where that would have been superior to using TEXT.
The problem with VARCHAR column type is that it can be a fixed-width column that doesn’t allow text longer than a certain number of characters. This maximum character length can be introduced by the person who creates the table and adds the column.
The maximum character length can also be created by a GIS program (specifically, QGIS and the ogr2ogr command line tool) as well as some online tools that can convert spreadsheets to SQL and even write the table definition and “CREATE TABLE” statement. In both cases, the program tried to guess the column type and seemed to default to VARCHAR(y character length) instead of TEXT.
The maximum character length is a problem because trying to insert more text than allowed into that field will encounter an error and the INSERT or UPDATE statement will fail. I think it’s also a bad idea to create these columns because it makes it expensive to change your mind later about what length of text will go into that field.
Other things I wish I knew earlier
Using transactions to test your query and its affect on your data so that you can easily undo any changes.
How and when to use indexes beyond the “btree” index type (for example, “gin” for array and JSONB columns).
Add “time_created_at” and “time_updated_at” columns to basically every dataset. This makes it easy to sort things later; sometimes when creating a new dataset it’s not always obvious how you might want to display or manipulate the data until days, weeks, or months later. Sorting data by “when was the information changed last” is a common use case for me.
