A common way to show the distribution of places (like grocery stores) is to use a heat map. The map will appear “hotter” where there are many grocery stores and “colder” where there are few grocery stores. This kind of map can be useful to show gaps in distribution or a neighborhood that has a lot of grocery stores.
One issue with that kind of heat map is that the coverage areas change their shape and color if you zoom in, since the algorithm that clusters or determines what’s “nearby” or dense has fewer locations to analyze.
I prefer to use grids in the shape of square tiles, since Chicago is grid-oriented city and the vast majority of our streets and our routes move along east-west and north-south lines. The map above shows the location of subjects and topics of news articles in the Chicago Cityscape database.
I use PostGIS to set up most of my spatial data before visualizing it in QGIS.
This tutorial shows the two steps to using PostGIS to (1) create a grid based on an existing area (polygon), (2) assigning each point location to a tile in that grid and counting the number of locations in that tile.
If you don’t have PostGIS installed, you should install it if you work with spatial data a lot. It is much, much faster at performing most of the tasks you use QGIS or ArcGIS to perform. Both QGIS and ArcGIS can read and edit data stored in PostGIS.
Additionally, there is a function within QGIS that can create grids, and another function that can do comparisons by location and count/summarize, so all of this can be done without PostGIS.
For this tutorial, you will need a single polygon with which to create the grid. I used the boundary of the City of Chicago limits.
-- Create required type
DROP TYPE IF EXISTS T_Grid CASCADE;
CREATE TYPE T_Grid AS (
-- Drop function is exists
DROP FUNCTION IF EXISTS ST_RegularGrid(geometry, NUMERIC, NUMERIC, BOOLEAN);
-- Now create the function
CREATE OR REPLACE FUNCTION ST_RegularGrid(p_geometry geometry,
p_point BOOLEAN DEFAULT TRUE)
RETURNS SETOF T_Grid AS
v_halfX NUMERIC := p_TileSizeX / 2.0;
v_halfY NUMERIC := p_TileSizeY / 2.0;
IF ( p_geometry IS NULL ) THEN
v_srid := ST_SRID(p_geometry);
v_mbr := ST_Envelope(p_geometry);
v_loCol := trunc((ST_XMIN(v_mbr) / p_TileSizeX)::NUMERIC );
v_hiCol := CEIL( (ST_XMAX(v_mbr) / p_TileSizeX)::NUMERIC ) - 1;
v_loRow := trunc((ST_YMIN(v_mbr) / p_TileSizeY)::NUMERIC );
v_hiRow := CEIL( (ST_YMAX(v_mbr) / p_TileSizeY)::NUMERIC ) - 1;
FOR v_col IN v_loCol..v_hiCol Loop
FOR v_row IN v_loRow..v_hiRow Loop
v_grid.gcol := v_col;
v_grid.grow := v_row;
IF ( p_point ) THEN
v_grid.geom := ST_SetSRID(
ST_MakePoint((v_col * p_TileSizeX) + v_halfX,
(v_row * p_TileSizeY) + V_HalfY),
v_grid.geom := ST_SetSRID(
ST_MakeEnvelope((v_col * p_TileSizeX),
(v_row * p_TileSizeY),
(v_col * p_TileSizeX) + p_TileSizeX,
(v_row * p_TileSizeY) + p_TileSizeY),
RETURN NEXT v_grid;
LANGUAGE plpgsql IMMUTABLE
The ST_RegularGrid function works in the same projection as your source data.
1.b. Create a layer that has all of the tiles for just the Chicago boundary
--This creates grids of 1,320 feet square (a 2x2 block size in Chicago)
SELECT gcol, grow, geom
FROM ST_RegularGrid((select geom from chicagoboundary where gid = 1), 1320, 1320,FALSE);
In that query, “1320” is a distance in feet for both the X and Y planes, as the “chicagoboundary” geometry is projected in Illinois StatePlane FIPS East (Feet) (EPSG/SRID 3435).
2. Assign each point location to a tile in that grid and count the number of locations in each tile
Now you’ll need a table that has POINT-type geometries in it. For the map in this tutorial, I used a layer of location-based news articles that are used in Chicago Cityscape to highlight local developments.
SELECT grid.id, count(*) as count, grid.geom
FROM news_articles, b_chicagoboundary_grid_1320squared AS grid
WHERE st_intersects(news_articles.geom, grid.geom)
GROUP by grid.id;
This query will result in a table with three columns:
I didn’t make any COVID maps earlier because I didn’t want to spend the time to ensure that I understood the right and wrong ways to map disease, because people make decisions based on maps and I don’t want my maps to end up harming anyone.
Aside from the state website’s usability issues, I’m very disappointed that there is zero data about COVID in the state’s #opendata portal.
These cities and counties have the most COVID vaccination sites, according to the IDPH’s dataset.
For the top 10 or so, it seems to correlate with population. Except Skokie has 7 sites, and Evanston has 4, despite Evanston having 10,000 more residents. Nearly 100% of Illinois is within 60 minutes driving of the current COVID vaccination sites. (More are coming, at least in Chicago.)
Nearly 100% of Illinois is within 60 minutes driving of the current COVID vaccination sites. (More are coming, at least in Chicago.)
And a lot of Illinois is still within 45 minutes driving of the current COVID vaccination sites. Really big gaps in geography appear at the 30 minutes driving threshold.
I’m working with some people to show access via transit. This is super important. I predict that upwards of 75 percent of Chicagoans will be able to access a vaccination site or two within 45 minutes and 100 percent within 60 minutes.
Here’s another shortcoming of the state’s map: Each site’s unique ID is not persistent, making it difficult to compare one day’s list to the following day’s list. I got around that by making a “hash” of each vaccination site and comparing between two versions.
The map has been updated once since I started. The “hash” creates a unique ID based on the attributes of each vaccination site (name, address, city, county, ZIP code). Any time one of those attributes changes, the hash will also change and thus I can more easily find new or modified vaccination sites.
I bought a copy of The “L”: The Development of Chicago’s Rapid Transit System, 1888-1932, written by Bruce Moffat, a historian of electric trains in Chicago. Moffat currently works for the Chicago Transit Authority. (If there wasn’t a pandemic, you’d be able to request a hold on one of the 50 copies at the Chicago Public Library.)
The book is about the elevated trains that were built in Chicago, in competition with the street omnibuses (horse drawn), railways (cable cars and streetcars), and suburban trains (okay, some competition), prior to establishing the Chicago Transit Authority. The CTA is a State of Illinois authority, created by the legislature, that today owns and operates all of the historic and since-built elevated, subway, and at-grade ‘L’ transit as well as buses. It acquired all of the assets of all of the ‘L’, streetcar, and bus companies that were operating when it was established in 1945.
On with the story!
Back in December 1888, the Chicago City Council approved a franchise for the Lake Street Elevated Company to build a Meigs Elevated Railway above Lake Street from Canal Street to 40th Avenue (later named Crawford and now Pulaski Road), then the western border of Chicago. A tract of land west of 40th Avenue (Pulaski Road) was incorporated into the City of Chicago four months later on April 29, 1889.
If you go to the intersection of Canal and Lake Streets today you’ll see the Union Pacific railroad tracks above, heading into and out of Ogilvie Transportation Center, a skyscraper at 444 W Lake Street, a cigar store, and a vintage loft office building.
The Meigs Elevated Railway was a steam-powered elevated monorail – meaning each track had one rail to support a train.
You may not know this: I love monorails. When my family visited Walt Disney World my favorite ride was the inter-park and world famous monorail. I’ve also ridden the monorails in Disneyland (but I don’t remember my time there), Las Vegas, Seattle, Düsseldorf airport, Wuppertal, and three in Tokyo, Japan (Chiba City, Shonan, and Haneda airport; I missed the one in Tama).
I used to be obsessed with monorails. I became a member of The Monorail Society when I was a teenager and my first eBay purchase was a Disney monorail motorized toy in March 2000. I was jealous of my friends in elementary school who had a Lego monorail, and now they regularly sell for $200. I also built a SAFEGE-style monorail out of K’NEX in high school.
It was invented by Josiah V. Meigs in Cambridge, Massachusetts; a 227-foot long demonstration line was built in 1886 on land that is now a Fairfield Inn hotel and before that was the Genoa Packing Co. (demolished in 2013).
The Lake Street Elevated Company organizers (seven incorporators are listed in the book) hired Morris H. Alberger to be the president. According to Moffat’s book, “Alberger had convinced his fellow directors that their railroad should use an experimental and relatively complex elevated railway system developed by Joe V. Meigs”. Alberger was also the president of the Meigs Elevated Railway Company.
Moffat discusses an eighth company organizer: Michael Cassius McDonald, “politically well connected and influential”. He was the “chief sponsor” and “promoter” of the Lake Street elevated proposal which came to be known as “Mike’s Upstairs Railroad”.
The Meigs Electric Railway – the monorail – was never built. Moffat says that the reason the monorail was never built was because it was difficult to promote and raised funds by selling shares.
Almost a year after City Council approved the MER to run over Lake Street, they “deleted the Meigs requirement” in November 1890 so that the Lake Street Elevated Company could build a traditional iron structure. The trains would also be “traditional”. (The first elevated train started running in Manhattan and the Bronx on August 26, 1878 – that was the Third Avenue Elevated – ten years prior to the Meigs monorail being approved in Chicago.)
Even before City Council “deleted” the franchise’s requirement to build a monorail, the Lake Street Elevated Company had already started building the iron structure for a train in December 1889, at Lake and Clinton Streets, where the Clinton Green Line station is now.
That’s the end of the story for the monorail, but I’ll continue talking about the Lake Street ‘L’.
The Lake Street Elevated opens!
Construction had reached “just west of Ashland Avenue” by October 1892, less than three years after the first iron girder was erected at Clinton. A year after that last construction milestone at Ashland, the tracks for service were completed to California Avenue (2800 West).
The Lake Street Elevated Company’s first service was set to begin on October 30, 1893. The opening was delayed, however, until an inauguration on Saturday, November 4, 1893, to mourn the death of Mayor Carter Harrison, who was assassinated during his fifth term. Passenger service began two days later on Monday, November 6, 1893.
In early 1893, the Lake Street Elevated Company wanted to run their trains down Market Street (now Wacker Drive) from Lake Street to Madison Street.
The Market Street “stub” ran past the future site of the Civic Opera Building, opened in November 1929. Operagoers and workers in the office tower of the building would have ridden the ‘L’ here until the Chicago Transit Authority
Extending further into the Garfield Park neighborhood
Tracks were built six blocks west of California Avenue, to Homan Avenue, but the stations were incomplete. Service to the Homan station started November 24, 1893, and four blocks further west to Hamlin Avenue in January 1894.
The Homan Avenue station no longer exists. Today’s Green Line over Lake Street was rebuilt from 1994 to 1996 and the Homan station was abandoned. According to Chicago “L”.org, the CTA decided to move the station two blocks west to Central Park Drive (3600 West). It was “completely deconstructed in spring of 2000 and put into storage”. It was renovated, made accessible, and opened as the Conservatory-Central Park Drive station in June 2001.
Chicago “L”.org notes that this visitors access to the Garfield Park Conservatory, evens out stop spacing, but does not intersect a bus route which Homan Ave does. The CTA closed Hamlin station on March 18, 1956. I don’t know when it was demolished.
Onward, to Austin and Oak Park!
Back to the Lake Street elevated timeline. Serviced operated to Hamlin Avenue in 1894. The next year it was operating to 52nd Avenue (now Laramie Avenue), the western boundary of Chicago. On the other side of that boundary was the Township of Cicero. Austin, a township neighborhood, was annexed by Chicago in 1899. The Village of Oak Park eventually emerged from the township, incorporating in 1902.
Austin was location of Cicero’s town hall. The town hall building, at the Central and Lake station, is now part of the Austin Town Hall Park and Cultural Center, owned and operated by the Chicago Park District.
Moffat’s book describes a lot of political controversy about extending the Lake Street Elevated into Cicero, which seems fitting for the Chicago region. Passenger service to Austin Avenue (now Boulevard) started April 19, 1899.
The next month, on May 14, 1889, trains that ran east-west above Lake Street came down a ramp – to the surface – onto north-south Lombard Avenue a couple of blocks south to Randolph Street. They turned west onto Randolph Street and continued until Wisconsin Avenue/Marion Street. The tracks on Randolph Street were in the middle of the street, and owned by Suburban Railroad, an interurban railway company.
The tracks were previously owned by Chicago, Harlem & Batavia Railway. I’m including that information because I grew up there. However, the railroad never made it that far: “No effort was made to extend the railroad to that distance place, but money was spent to purchase new locomotives and passenger cars and make other improvements.”
Residents here had the option of taking trains into downtown Chicago on the Chicago & Northwestern Railway. Those tracks are now owned by Union Pacific, which also operates the former C&NW lines as Metra’s UP-West Line. The line terminates at Ogilvie Transportation Center, which used to be called Northwestern Station, which was C&NW’s second location for their downtown terminal.
Moffat discussed these passengers’ choices, writing, “Although a ride on the nearby Chicago & Northwestern was faster, the “L’s” more frequent schedule, convenient Loop stops, and lower fare drew many riders away from the steam railroad”. The same is true today; the ‘L’ costs less than Metra but takes longer to reach the West Loop.
The story about the construction and operation of the Lake Street Elevated is almost done. I’m going to end it as soon as the train reaches the current terminus at Harlem Avenue in Oak Park.
Service to Marion Street started in late January 1901, on the street level of South Boulevard, thus ending service on Randolph Street a few blocks south. Trains started servicing the Harlem station on May 20, 1910. Remember that the reason the trains are now on South Boulevard is because Lake Street runs with a slight northwest diagonal, ends at the Chicago & Northwestern Railway embankment, and resumes a few blocks west. In 1961, the line was elevated onto C&NW’s embankment.
Even though the station is currently called “Harlem/Lake”, the station is at Harlem/South Boulevard, and Lake Street is one block north.
Meigs’s railway was mentioned in an op-ed in the Boston Globe Magazine on Sunday, February 23, 1992, as the newspapers’s architecture critic, Robert Campbell, and Peter Vanderwarker, an architectural historian, lamented the towering car infrastructure proposed in the Central Artery/Tunnel Project (also known as “Big Dig”, the most expensive highway construction in the country), as well as the darkening effect of the elevated trains. It’s really quite an essay.
But competition was vicious. Arson and vandalism hampered Meigs, as did his insistence on old-fashioned steam power instead of electricity. Nothing besides the Cambridge test line was ever built. The Meigs monorail made its last run in 1894. Conventional elevated trains, modeled on those of Manhattan and far more massive than Meigs’, soon darkened Boston’s streets.
By the end of this decade, the view will have changed radically. A dramatic Babel of steel and concrete, perhaps resembling a great sports stadium, will rise like a gray mountain in the middle distance at the left of the photo. The introverted automobile will have won its long battle for supremacy over the sociable train.
“MEIGS ELEVATED RAILWAY – Changing TRACKS”, By Robert Campbell and Peter Vanderwarker
Meigs Field, a former airport in downtown Chicago that existed between 1948 and 2003, was named after Merrill C. Meigs, a pilot and former head of the Chicago Aero Commission. He believed that Chicago needed a third airport, within 10 minutes of downtown. The airport was built and named after Meigs in 1949. I haven’t found a relationship between the two Meigs.
Using the footprints of parking lots and garages drawn into OpenStreetMap as a data source, the area of land in Chicagoland occupied by parking lots and garages is 247,539,968 square feet. (The data was exported using HOT Export Tool; you can replicate my export.)
Short answer: To provide more shoppers for the local businesses. Read on for the longer answer.
Over on Chicago Cityscape I added a new feature called “market analysis” which measures the number of people who live within specific walking areas (measured by time) and driving areas (measured by distance).
I am in favor of removing apartment & condo bans in Chicago, especially in areas where they were previously allowed and near train stations.
Jefferson Park is centered around two co-located train stations, serviced by CTA and Metra respectively. There have been multiple proposals for multi-family housing near the stations (collectively called the Jefferson Park Transit Center) and some have been approved.
Always, however, there are residents who resist these proposals and the number of originally proposed apartments or condos gets reduced in the final version (classic NIMBYism).
There’re four reasons – at least – why more housing should be allowed near the Jefferson Park Transit Center:
Locally owned businesses require a significant amount of shoppers who live nearby and walk up traffic
More people should have the opportunity to live near low-cost transportation
It will include more affordable housing, through Chicago’s inclusionary zoning rules (the Affordable Requirements Ordinance, ARO)
There will be less driving, and therefore lower household transportation costs and less neighborhood pollution
To support the first reason, I used the “market analysis” tool to see just how many people live in a walkable area centered around Veterans Square, a mixed-use office and retail development adjacent to the train stations.
Comparatively, 19,707 people live within a 10 minute walk to The Crotch, or the center of Wicker Park, at the intersection of Milwaukee/North/Damen (get the Address Snapshot). The Blue Line station is about 75 feet south of the center point.
I would grant the low Veterans Square number a small discount based on the proximity to the Kennedy Expressway, which severely truncates walking areas up and down the northwest side. Still, even with that discount, ending up with less than half the amount as the one in Wicker Park, is disturbing. Wicker Park is hardly characterized by high-density housing. In fact, all of the new high-rises are just outside the 10 minute walk shed!
In one of Philip K. Dick’s short stories, titled “Precious Artifact”, Dick appears to recognize what tends to happen in American cities.
Earth, “Terra”, has been attacked by “Proxmen” and the “Terrans” have lost. However, one of the Terrans, who has been reconstructing Mars for future Prox inhabitation has come back to Earth. A guide meets him at the spaceport and asks the Terran where he wants to go…
“I’m Mary Ableseth, your Tourplan companion. I’ll show you around the planet during your brief stay here.” She smiled brightly and very professionally. He was taken aback. “I’ll be with you constantly, night and day.”
“Night, too?” he managed to say.
“Yes, Mr. Biskle. That’s my job. We expect you to be disoriented due to your years of labor on Mars…labor we of Terra applaud and honor, as is right.” She fell in beside him, steering him toward a parked ‘copter. “Where would you like to go first? New York City? Broadway? To the night clubs and theaters and restaurants…”
“No, to Central Park. To sit on a bench.”
“But there is no more Central Park, Mr. Biskle. It was turned into a parking lot for government employees while you were on Mars.”
“I see,” Milt Biskle said. “Well, then Portsmouth Square in San Francisco will do.” He opened the door of the ‘copter.
“That, too, has become a parking lot,” Miss Ableseth said, with a sad shake of her long luminous hair. “We’re so darn over-populated. Try again, Mr. Biskle; there are few parks left, one in Kansas, I believe, and two in Utah in the south part near St. George.”
“This is bad news,” Milt said. “May I stop at that amphetamine dispenser and put in my dime? I need a stimulant to cheer me up.”
A vacant lot is for sale near the 606’s Bloomingdale Trail, a popular amenity that’s now known to have an effect in increasing home values. It’s zoned RS-3, which means it bans apartments. If the zoning stays the same, then the vacant lot will only allow a rich family to move in here. If the lot’s zoning is changed to allow apartments or condos, then the vacant lot could welcome families that earn median incomes.
You can build multi-family housing on the lot if you can get a zoning change, but you’ll have to pay the city a fee, convince your future neighbors that they shouldn’t oppose it, convince the alder that he should support it, and you’ll have to hire a lawyer.
Let’s say that zoning changes in Chicago were free and frictionless*. What should be built on this lot?
If the lot would allow multi-family housing, we can build several units for less money per unit than if we built a single-family house. That means that three families (let’s stick with three, which requires a zoning change to RM-4.5) could be housed for less money per family than the cost of one family.
How’s that? The sticker price for this lot is $425,000 right now, and if one family is paying for that plus the cost of building a house, then your minimum investment is pretty massive. (I suspect the lot will sell for something closer to $400,000.)
I looked at new construction costs on Chicago Cityscape, as indicated on building permits issued within 1 mile of the vacant lot, took the average, and added it to the cost of land per unit.
The average new construction single-family house, from the 10 most recent permits, is $304,052.78.
The average new construction multi-family housing, from the 10 most recent permits, is $230,192.13 per unit.
Total cost per unit (land + construction)
Add in the land cost per unit ($425,000 for the single-family house and $141,666.67 per unit for the 3-flat) and you end up with the total costs of:
$729,052.78 for the single-family house
$371,858.80 per unit in the 3-flat
Add in the profit or “cap rate” that a builder wants to make and the price is even higher, but the people who would buy in the multi-family house would be paying much less for their homes.
The city can generate more affordable housing if it “upzones” vacant land and stops banning multi-family housing. (Much of the city’s parcels have been “downzoned” to ban multi-family housing in a process that creates “exclusionary zoning” and allows only – expensive – single-family housing.)
The city and the Chicago Transit Authority will earn more real estate transfer taxes (RPTT) from the sales of the units as condos than from a single-family house.
Three families instead of one would enjoy living to the wonderful amenity that the Bloomingdale Trail and the parks that the 606 offers.
* The City of Chicago charges a zoning change fee of $1,025, and you will most likely have to hire a lawyer, and it will take about 3-6 months, depending on the complexity of the proposal that requires the zoning change. You can use Chicago Cityscape to see actual approval times (excluding the time meeting the alder for the ward of the proposed project).
Maps have been used to devalue neighborhoods and to excuse disinvestment. There should be maps, and narratives, to “greenline” – raise up – Chicago neighborhoods.
The Home Owners’ Loan Corporation “residential lending security” maps marked areas based on prejudicial characteristics and some objective traits of neighborhoods to assess the home mortgage lending risk. (View the Cook County maps.) The red and yellow areas have suffered almost continuously since the 1930s, and it could be based on the marking of these neighborhoods as red or yellow (there is some debate about the maps’ real effects).
The Home Owners’ Loan Corporation and its local consultants (brokers and appraisers, mostly) outlined areas and labeled them according to objective and subjective & prejudicial criteria in the 1930s. Each area is accompanied by a data sheet and narrative description. The image is a screenshot of the maps as hosted and presented on Chicago Cityscape.
The idea of “greenlining”
I might be thinking myopically, but what would happen if we marked *every* neighborhood in green, and talked about their strengths, and any historical and current disinvestment – actions that contribute to people’s distressed conditions today?
One aspect of this is a form of affirmative marketing – advertising yourself, telling your own story, in a more positive way than others have heard about you in the past.
In 1940, one area on the Far West Side of Chicago, in the Austin community area, was described as “Definitely Declining”, a “C” grade, like this:
This area is bounded on the north by Lake St., on the south by Columbus Park, and on the west by the neighboring village of Oak Park. The terrain is flat and the area is about 100% built up. There is heavy traffic along Lake St., Washington Blvd. Madison St., Austin Ave. (the western boundary) and Central Ave. (the eastern boundary).
High schools, grammar schools, and churches are convenient. Residents shop at fine shopping center in Oak Park. There are also numerouss small stores along Lake St., and along Madison St. There are many large apartment buildings along the boulevards above mentioned, and these are largely occupied by Hebrew tenants. As a whole the area would probably be 20-25% Jewish.
Some of this migration is coming from Lawndale and from the southwest side of Chicago. Land values are quite high due to the fact that the area is zoned for apartment buildings. This penalizes single family occupancy because of high taxes based on exclusive land values, which are from $60-80 a front foot, altho one authority estimates them at $100 a front foot. An example of this is shown where HOLC had a house on Mason St. exposed for sale over a (over) period of two years at prices beginning at $6,000 and going down to $4,500. it was finally sold for $3,800. The land alone is taxed based on a valuation exceeding that amount. This area is favored by good transportation and by proximity to a good Catholic Church and parochial school.
There are a few scattered two flats in which units rent for about $55. Columbus Park on the south affords exceptional recreational advantages. The Hawthorne Building & Loan, Bell Savings Building & Loan, and Prairie State Bank have loaned in this area, without the FHA insurance provision. The amounts are stated to be up to 50% and in some cases 60%, of current appraisals.
Age, slow infiltration, and rather indifferent maintenance have been considered in grading this area “C”.
Infiltration is a coded reference to people of color, and Jews.
My questions about how to “greenline” a neighborhood
How would you describe this part of Austin today to stand up for the neighborhood and its residents, the actions taken against them over decades, and work to repair these?
How do you change the mindset of investors (both small and large, local and far) to see the advantages in every neighborhood rather than rely on money metrics?
What other kinds of data can investors use in their pro formas to find the positive outlook?
What would these areas look like today if they received the same level of investment (per square mile, per student, per resident, per road mile) as green and blue areas? How great was the level of disinvestment from 1940-2018?
In the midst of writing this, Paola Aguirre pointed me to another kind of greenlining that’s been proposed in St. Louis. A new anti-segregation report from For the Sake of All recommended a “Greenlining Fund” that would pay to cover the gap between what the bank is appraising a house for and what the sales price is for a house, so that more renters and Black families can buy a house in their neighborhoods.
That “greenlining” is a more direct response to the outcome of redlining: It was harder to get a mortgage in a red area. My idea of greenlining is to come up with ways to say to convince people who have a hard time believing there are qualities worth investing in that there they are people and places worth investing in.
This infographic visually compares the difference between running a PostGIS comparison query like ST_Intersects on a large shape versus a subdivided version of that large shape. Click to embiggen.
Hundreds of GIS intersection comparisons are completed every hour on Chicago Cityscape.*
People are looking at, say, a map of the South Shore community area. That “Place” page then grabs all of the building permits, building violations, business licenses, and other “feature layers” that are stored as points.
A classic “point in polygon” comparison is made using the ST_Intersects(place_geometry, permits_geometry) function.
This has worked very well for several years.
But as Chicago Cityscape handles larger shapes – they come from users drawing their own, large shapes, and from large shapes like the downtown Chicago area – this query doesn’t cut it.
Setting indexes on the geometry is imperative, but it’s not the end of the to optimize performance. That’s because the index of the geometry is a rectangular bounding box (which is also called an “envelope” in GIS) that contains the entire shape of the South Shore community area.
The downtown Chicago area, however, is not even the largest shape I have. That belongs to the new Place, “Neighborhood Opportunity Fund investment zones” (NOF). Combined, they cover 75 square miles of Chicago. Downtown is only 7.7 square miles.
After I added the NOF map and tested its Place page, it “crashed” my server, metaphorically speaking. The query to just count the number of building permits in the area would take about five minutes.
There had to be a better way; in the meantime, however, I divided the NOF map into the West and South sections. This hardly improved the counting time.
Thankfully, today, I saw a tweet from Paul Ramsey linking to his blog that linked to his slides from a recent presentation about the use of PostgreSQL to store and manipulate GIS data.
In it he explained how the ST_Subdividefunction worked. I’m going to demonstrate it using graphics from my own maps.
A normal intersection comparison, using ST_Intersects(place_geometry, permits_geometry) in a query creates a bounding box (envelope) around each geometry and quickly determines whether the two envelopes overlap. If they do, then it checks again to see if the actual geometries overlap. If they do, that data is returned as a response to your query.
When your two datasets are massive, like the NOF zones, which collectively cover 1/3rd of Chicago, and the building permits, which are found across the entire city…well, that led to the five minutes counting time.
Enter ST_Subdivide. To use it properly you would run it against your existing geometry and store the much smaller shapes, derived from the big shape, in a new table. I applied the function to all the 22,203 maps that Chicago Cityscape has and stored their unique IDs and subdivided geometries in a new “lookup” table.
Now, any time I want to compare the building permits against the NOF, the building permits are instead compared to the small shapes that were subdivided.
Chicago Cityscape uses a single table (created as a materialized view) to combine all 22,203 maps. Each map is stored in a source table (for example, there’s a table to hold the 77 community areas) and the materialized view runs once a day to combine all of the maps in the source tables. This ensures our data is managed well: different source tables can hold different information, and the single table holds only the name, type, and geometry of the source tables, for faster comparison. Each entry in the single table also has a “slug”, its unique identifier.
Thus, the materialized view of the subdivided maps is created from the aforementioned single table, using this query:
create materialized view view_places_subdivided as
select gid || '_' || random() as gid, slug, st_subdivide(geom) as geom
The “gid” is designed to create a new unique ID field, as the slug field will be repeated for every subdivided of each map. A unique ID field is necessary if you want to refresh the materialized view concurrently (to allow for other queries to access the materialized view while it’s being refreshed).
* The results are cached for a few hours, because the feature layers change 1-2 times per day and at different times each day, so the limited duration cache accommodates that. Ideally I would code a way to invalidate the cache when the feature layer data is updated.
Update 12/31/19: ST_Subdivide will fail if your geometries have any or certain geometry errors (I don’t know if it’s any kind of error, or certain kinds of errors that make the function fail). Chicago Cityscape has over 37,000 features that ST_Subdivide is attempting to process, and there is a lot of room for error in managing that many features from dozens of sources.
The area in green only allows single-family houses to be built.
Something’s gotta give.
This is all of the land area within two blocks of the Bloomingdale Trail that allows only single-family housing to be built (view full map). This isn’t to say that multi-family housing doesn’t exist here; it definitely does, and there’s probably a handful of two-flats on a majority of the blogs.
All of the five parks of the 606 are within this two block radius, and 49.6 percent of the land allows only single-family housing to be built.
But why build a transportation corridor, a park, a new, expensive, public amenity, and not change the kind of housing – which often determines the kind of family and makeup of a household – that can afford to buy a home near here.
It’s already been shown that detached single-family housing prices have grown intensely the closer you get to the trail. That price growth has meant displacement for some, and “no chance to buy or build a house here” for many others.
There are still plenty of vacant lots within the mapped area; lots that should have a 2-4 unit building built on them, but where only a 1-unit building is allowed.