Alternative headline: A practical example of how to use ST_ClusterDBSCAN to find similar real estate properties.
Oftentimes a developer wants to acquire several adjacent lots for a single redevelopment. Each standard sized lot in Chicago is about 3,125 square feet (25 feet wide and 125 feet deep). Because of downzoning in 2004, and since, the zoning rules for many lots allow only about 3-4 dwelling units each. Multiple lots are required to develop buildings with 6-9 dwelling units, which is a sweet spot in Chicago for design and avoiding having to get an upzone.
Chicago Cityscape has long had Property Finder, a tool to locate parcels that meet exacting specifications given existing lot size, current zoning district, distance to transit, and other criteria.
Now, Chicago Cityscape can locate parcels that are adjacent or near each other that all meet the user’s specified criteria (what the website calls “filters”). This is possible because of the PostGIS function ST_ClusterDBSCAN.
ST_ClusterDBSCAN considers all geospatial features in your result set (whatever matches the WHERE clause) and assigns them to a cluster ID according to two inputs: minimum cluster size, and maximum distance each feature can be from any other feature in order to be considered in the same cluster as that other feature.
The function can also assign a feature with a cluster ID of NULL, indicating that the feature did not meet the clustering criteria and is alone.
Show me what that looks like
Chicago Cityscape gives the user three options to cluster: Small, compact clusters with at least 3 properties each; small, compact clusters with at least 5 properties each; large, loose clusters with at least 10 properties each.
Additionally, Chicago Cityscape lets the user choose between showing parcels that weren’t found in a cluster, or hiding parcels that weren’t found in a cluster. The reason to show parcels that weren’t found in a cluster is to visualize where there are and aren’t clusters of parcels in the same map.
A map of Chicago’s Near West Side community area is shown with clusters of vacant lots. The “show all properties” mode is used, which shows clusters with a thick, black outline. Properties that were not in a cluster are still shown but without the thick black outline (enlarge the photo to see the difference).
Sample query
This query looks at all of the vacant lots within 1 mile of the intersection of Washington Boulevard and Karlov Avenue in the West Garfield Park community area of Chicago. The query looks for clusters of at least 3 features (“minpoints”) that are no more than 25 feet apart (“eps”). (The data are projected in Illinois StatePlane East Feet, rather than a projection that’s in meters because it’s easier for me to work with feet.)
I posted another sample query below that’s used to exclude all of the features that were not assigned to a cluster.
SELECT pin14, ST_ClusterDBSCAN(geom, eps := 25, minpoints := 3) over () AS cid, geom
FROM parcels
WHERE property_class = '1-00'
AND ST_DWithin(geom,
ST_Transform(
ST_GeomFromText('POINT(-87.7278 41.8819)', 4326), 3435),
5280)
The screenshot below shows clusters of vacant lots that resulted from the query above. The parcels symbolized in a gray gradient were not assigned to a cluster. Notice how clusters will form across the alleys but not across streets; this is because the streets are wider than 25 feet but most alleys are only 16 feet wide.
The map shows various groups (clusters) of vacant properties in West Garfield Park. Each cluster is symbolized in QGIS using a different color. Properties that are not in a cluster are symbolized by a gray gradient.
Exclusion sample query
This query is the same as above except that a Common Table Expression (CTE) is used (CTEs have the “WITH” keyword at the beginning) to create a subquery. The “WITH” subquery is the one that clusters the parcels and the following query (“SELECT *”) throws out any features returned by the subquery that don’t have a cluster ID (the “cid” field).
with parcels as (
SELECT pin14, ST_ClusterDBSCAN(geom, eps := 25, minpoints := 3) over () AS cid, geom
FROM parcels
WHERE property_class = '1-00'
AND ST_DWithin(geom,
ST_Transform(
ST_GeomFromText('POINT(-87.7278 41.8819)', 4326), 3435),
5280)
) select *
from parcels where cid is not null;
Today I’m adding a bunch of new features to the Chicago Crash Browser, which lives on Chicago Cityscape.
But first…special access is no longer required. Anyone can create a free Cityscape account and access the map. However, only those with special access or a Cityscape Real Estate Pro account will be able to download the data.
Five new features include:
Statistics that update weekly to summarize what happened in the past week: the number of crashes, the number of people killed in crashes, and the number of people with the two worst tiers of injuries. The statistics are viewable to everyone, including those without access to the crash browser.
The statistics will update every Sunday. The numbers may change throughout the week as Chicago police officers upload crash reports.
For data users, the crash record ID is viewable. The crash record ID links details about the same crash across the Chicago data portal’s three tables: Crashes, Vehicles, and People. My Chicago Crash Browser is currently only using the Crashes table. Click on the “More details” arrow in the first table column.
The crash record ID is hidden by default but can be exposed. Use this ID to locate details in the data portal’s Vehicles and People tables.
Filter crashes by location. There are currently two location filters: (1) on a “Pedestrian Street” (a zoning designation to, over time, reduce the prevalence of car-oriented land uses and improve building design to make them more appealing to walk next to); (2) within one block of a CTA or Metra station, important places where people commonly walk to. Select a filter’s radio button and then click “Apply filters”.
Filter crashes by availability of a news article or a note. I intend to attach news articles to every crash where a pedestrian or bicyclist was killed (the majority of these will be to Streetsblog Chicago articles, where I am still “editor at large”. Notes will include explanations about data changes [1] (the “map editor” mentioned in some of the notes is me) and victims’ names.
After choosing a filter’s radio button click “Apply filters” and the map and data table will update.
Filter by hit and run status. If the officer filling out the crash report marked it as a hit and run crash, you can filter by choosing “Yes” in the options list. “No” is another option, as is “not recorded”, which means the officer didn’t select yes or no.
Search by address. Use the search bar inside the map view to center the map and show crashes that occurred within one block (660 feet) of that point. The default is one block and users can increase that amount using the dropdown menu in the filter.
Use the search bar within the map view to show crashes near a specific address in Chicago.
Footnotes
[1] The most common data change as of this writing is when a crash’s “most severe injury” is upgraded from non-fatal to fatal, but the crash report in the city’s data portal does not receive that update. This data pipeline/publishing issue is described in the browser’s “Crash data notes” section.
The “map editor” (me) will change a crash’s “most severe injury” to fatal to ensure it appears when someone filters for fatal crashes. This change to the data will be noted.
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 a grid based on an existing area
1.a. Add a new function to PostGIS
To create a grid, you need a function that draws the tiles based on the polygon. I got this from The Spatial Database Advisor.
-- Create required type
DROP TYPE IF EXISTS T_Grid CASCADE;
CREATE TYPE T_Grid AS (
gcol int4,
grow int4,
geom geometry
);
-- 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_TileSizeX NUMERIC,
p_TileSizeY NUMERIC,
p_point BOOLEAN DEFAULT TRUE)
RETURNS SETOF T_Grid AS
$BODY$
DECLARE
v_mbr geometry;
v_srid int4;
v_halfX NUMERIC := p_TileSizeX / 2.0;
v_halfY NUMERIC := p_TileSizeY / 2.0;
v_loCol int4;
v_hiCol int4;
v_loRow int4;
v_hiRow int4;
v_grid T_Grid;
BEGIN
IF ( p_geometry IS NULL ) THEN
RETURN;
END IF;
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_srid);
ELSE
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),
v_srid);
END IF;
RETURN NEXT v_grid;
END Loop;
END Loop;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE
COST 100
ROWS 1000;
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
into b_chicagoboundary_grid_1320squared
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
INTO news_grid
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:
The ID of the tile, which is a primary key field.
The number of news articles in that tile.
The POLYGON geometry of that tile.
Look at these two maps (the one above, and the one below). The first map shows the whole city. The tiles are colored according to the number of news articles within the area of each tile. The darker the blue, the more news articles within that tile. This map is zoomed in to the Woodlawn area. As you change scale (zoom in or zoom out), the size of the “heat” area (the size of each tile) doesn’t change – they are still 1,320 feet by 1,320 feet. The color doesn’t change either. The typical heat map doesn’t have these advantages.
The State of Illinois map of COVID vaccination sites is pretty bad.
Screenshot of the Illinois Department of Public Health map, taken February 14, 2021.
It’s slow (caused my browser tab to crash after a couple minutes), has misspelled county and city names, missing ZIP code digits, and cannot be searched by address. There are duplicate entries, too.
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.
A map of Illinois showing 30 minute driving areas around each of the 862 COVID vaccination sites.
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.
A drawing of the Meigs Elevated Railway monorail, originally published in Scientific American, July 10, 1886. Via Wikipedia; also printed in Moffat’s book where it is sourced from Railway Age, a trade journal founded in 1856 that still exists today.
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.
Map of the Lake Street Elevated, from Market Street (now Wacker Drive) to Harlem Avenue and South Boulevard.
Heading closer to downtown Chicago
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.
Photograph showing the elevated stub track on Market Street. The view is looking east along Lake Street at Market Street, where the elevated train would turn south. Photo taken by a Chicago Daily News, Inc., photographer in 1908. The caption in the Explore Chicago Collections database says,
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
The Lake Street Elevated’s Market Street stub terminated at Madison Street. The Civic Opera Building is on the left. Image is from the CTA’s collection. Market Street was renamed Wacker Drive when the street was reconstructed as a double decker street starting in 1948.
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.
Austin Town Hall, the former town hall of the Township of Cicero. Photo taken in 2019 by Eric Allix Rogers.
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.
N.B.
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.
The op-ed in the Boston Globe Magazine, 2/23/1992
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.
[snipped]
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.
