Page 20 of 171

Two-flat journal 6: How to make an all-electric house

May 2, 2021 / / 0 Comments

After the bill drama with Peoples Gas last year, in which I was billed a $50 “base” fee per unit per month for the privilege of having a gas line to my house, I decided to make the gut rehab of my two flat all electric. I think that making an all electric house is easy, but it takes a lot of research to know what that means and how to select materials and appliances.

Read all of the “Two-flat owner journal” entries

Why go electric?

I have several reasons for keeping natural gas out of my house:

  • Natural gas has point source emissions causing indoor air pollution that need to be vented and evacuated properly (gas stovetops emit methane into your kitchen, so turn on your hood that hopefully vents to the outside)
  • The price of natural gas is not falling as fast as electricity is falling
  • Electricity is more and more likely to come from renewable sources, especially in Illinois because of our state policies that require ComEd and Ameren to buy more and more power from renewable sources
  • A future photovoltaic solar panel array could be integrated and some of the electricity in the house would come from its own generator
  • Without gas pipes in the house, there is less infrastructure to build and maintain
  • Climate change is continuing to make life harder for the world’s inhabitants and electrical heating & cooling causes less fossil fuel emissions (“An electrified home uses 8,333 more kWh annually than a gas (or propane) home but displaces 96 million BTU of fossil fuel use.”Elevate+Rocky Mountain Institute study)

There are still some downsides to having an all electric house, namely when the power goes out on the block then nothing in the house will turn on. There are solutions, for this, though, including integrating a battery pack or using a fossil fuel-powered generator with its own tank.

The Passivhaus-certified single-family house in Hyde Park that I toured in 2018 has a small natural gas-powered generator in the rear yard, fueled by a typical Peoples Gas supply line.

What does it mean to have an electric house?

I think there are three categories of choices that one makes, about visible appliances, invisible appliances, and heating & cooling.

A visible appliance is one you use directly and frequently, like an oven and a clothes dryer. These are essentially the only two appliances that have gas and electric versions and electric versions are just as commonly available as gas version.

An invisible appliance is one that’s in a closet or in the basement, like a water heater. Most people I know have a gas-powered water heater (usually a tank, not tankless), and there are two kinds of electric water heaters (which I detailed in Two-flat journal 3). Again, these are very common and electric water heaters (standard with resistance heating, tankless, and hybrid heat pump) can be picked up anywhere a gas-powered water heat can be purchased.

Think of a heat pump as a “reversible air conditioner”: an air conditioner removes heat in the air of a space and puts it outside, while a heat pump “removes” the heat in the air of the outside and puts it inside.

Heating & cooling is the complicated category of the three. A typical new construction house or condo has gas forced air for heating, a condenser for air conditioning, and the air is pushed through the house via ducts. In the electric universe, however, mini splits/air source heat pumps have been around for 40 years and are extremely efficient at both heating and cooling. In cold climate region 5, where Chicago is, air source heat pump manufacturers have additional products to deal with the extreme cold temperatures.

Going electric in the heating and cooling category is the only one that necessitates deeper research on windows, wall assembly, and insulation to go beyond the basic energy efficiency code (Illinois Energy Conservation Code 2018). While the air source heat pump is efficient (some have a coefficient of performance, COP, of 3-4, meaning it transfers three to four times as much heat energy to the house as the energy they consume) it will work very hard to keep a house warm during negative temperature days (F°) and thus it’s important to have a well-sealed house so the conditioned air you’re paying for doesn’t escape.

If you want your house to be all electric, a lot of these choices can be made over time. For example, you can stop the air pollution by buying a range with an induction cooktop, which is extremely efficient, safe for households with children, and very easy to clean. I like to cook soup in my Dutch oven (which is compatible with induction cooktop) and I would rather not have to have the burner on for an hour, accompanied by a noisy vent fan.

Financial benefits of an all electric house

In addition to certain federal tax credits for replacing certain appliances, which you can claim when you file your tax return, there are often local incentives. ComEd has a new Electric Homes program that offers $4,000 cash (to the builder or general contractor) for an electric house (new construction or renovation) that meets their requirements. (2021 is the second year in operation, and there’s no guarantee it will operate next year.)

From ComEd’s marketing:

Building your clients’ dream homes – why not make them energy efficient?

ComEd provides a $2,000 incentive per home for electric homes new construction! All-electric home construction can sound daunting at first, but with the knowledge and help of ComEd, you too can benefit from tight envelope, all-electric HVAC, heat pump water heating, lighting and appliances. Construction of single-family homes, duplexes, townhomes, and 2-4 flats are eligible.

Make the jump to high-quality, next-generation, electric homes. Reduce energy bills while providing superior comfort. Prepare for a clean, resilient energy future.

Learn about additional clean energy strategies like solar power, electric vehicle charging, smart homes and induction cooking.

Please reach out to Sophia Seol SSeol@slipstreaminc.org if you have any questions.

I’m planning to buy these products

I’ve done a lot of research on appliances in all three categories because my architect needs to know exactly which appliances I’m selecting so the kitchen, utility closet, basement, and other spaces can be designed to fit them. Thankfully, a lot of appliances have similar dimensions so it’s easy to match the available space with the catalog of laundry machines, refrigerators, etc.

My architect and I reviewed a printed set of plans last week, mainly so he could alternate between drawing on AutoCAD and marking up issues.

There are two main attributes to watch for when selecting electric appliances: the yellow Energy Guide label that estimates the annual cost of operating the appliance, and whether it has Energy Star certification. One of the ComEd Electric Homes requirement is that every appliance that can be Energy Star certified is – I’ve found that there are no certified ranges.

My favorite place to shop for appliances online is Abt. When I polled my Twitter followers last summer for appliance store recommendations, Abt was overwhelmingly the most recommended store.

Note: I have no idea which ones will be in stock when it comes time for me to buy any of them, but these are the ones I would pick right now.

All appliances and equipment are Energy Star-certified except where noted.

Visible appliances

  • Dishwasher. I’ve got three options on my list (each costs less than $500):
  • Refrigerator (a different one for each dwelling unit)
  • Oven/stovetop/range
    • Frigidaire FCRE3052AB ($629) – Not Energy Star certified, not induction
    • Frigidaire GCRI3058SS ($1,169) – Not Energy Star certified, yes induction

Invisible appliances

  • Water heater. Something to know about tankless is that it’s not necessary to have a single tankless source for the whole house, as it’s possible to have multiple tankless water heaters at each water source, sized to the need – the kitchen sink can have one, and the shower and the lavatory can share another. The hybrid heat pump water heater currently has a 10% federal tax credit, up to $300.
    • Rheem brand’s “Performance Platinum 40 Gal. 10-Year Hybrid High Efficiency Smart Tank Electric Water Heater” (Home Depot)
    • Each dwelling unit will have the same water heater but may not have the same kitchen and laundry appliances.
  • Clothes washer. Stacked, to save space, from (each is about $800):
  • Clothes dryer. Stacked, to save space, from (each is about $800):

Heating and cooling (HVAC)

Mini split systems have two parts: An outdoor condensing unit and one or more indoor fan units. They’re connected by a refrigerant line, an outgoing condensation line, and electricity from the outdoor unit to the indoor units that is routed through a small diameter (3″) hole in the exterior wall.

Three outdoor heat pumps on the roof
Three larger-capacity heat pumps on the roof of a new construction and all-electric three-flat in Pilsen. Photo: Steven Vance.

There are several options for indoor units: Wall-mounted, ceiling mounted cassette, ceiling mounted box, and ducted (the outdoor unit will provide the hot or cold refrigerant and the ducted unit will blow air through a duct network). My architect and I have selected two ceiling-mounted cassettes per dwelling unit; these fit within the 16″ between joists and avoids the protrusion of wall-mounted units.

  • Outdoor unit, Mitsubishi MXZ-3C30NAHZ2 (Ecomfort, $3,365 x2)
  • Ceiling cassette indoor unit, Mitsubishi MLZ-KP09NA (Ecomfort, $895 x4)

I will need a venting system to exchange fresh air into each dwelling unit, without relying on the typical situation where the leakiness of houses brings in fresh air. An energy efficient way to do this is to use an “energy recovery ventilator” (ERV) that transfers the heat or chill of outgoing conditioned air to incoming fresh air to reduce the amount of energy that the heat pump would expend to warm or chill the incoming air.

An alternative to ducted ERVs is to use a wall-mounted model, such as the Zehnder ComfoAir 70 ventilation unit, Holtop ERVQ-B150-1A1F, or Blauberg VENTO Expert A50-1 W (that’s on Blauberg’s German-market website, and that company has a subsidiary called VENTS that has an American-market website). There’s also Panasonic’s WhisperComfort ($420) that can be mounted on the wall or ceiling. However, I need to verify that either has air moving capacity required by the indoor air quality standard ASHRAE 62.2, specified in the ComEd Electric Homes program. It might be possible to use two of these, including one in the bathroom.

What’s missing

Oh, one more thing, the electrical panels in the basement will need to be replaced (which is part of the replacement of the entire electrical system), and ComEd will likely need to string higher-amperage lines from the alley overhead power lines to the house. This area requires more research and possible a conversation with an electrical contractor or ComEd. I’m currently assuming that I can specify that the electrical contractor will handle this with ComEd.

Two-flat journal 5: Costs

April 25, 2021 / / 0 Comments

Updated May 3, 2021, to add more insight from Robinson Meyer (The Atlantic) as to why lumber prices are so high.

My architect and I are still working on plans, slowly but surely. Read my previous entry, Two-flat journal #4, to understand why that seems to be taking awhile.

There is something else on my mind as we work toward the goal of a gut-rehabbed two-flat: How much this whole project is going to cost.

I’ve talked to several contractors, engaged a structural engineer to specify and design the new steel beam in the basement, and obtained quotes for all new windows from four manufacturers.

One contractor happily gave me an estimate, based on incomplete plans, that was about $220,000. That price could go down with more specific plans and instructions, as the estimate had variability based on unknowns, and it doesn’t include the cost of purchasing the windows. More likely, I think the price will go up due to material costs.

How much windows might cost

All window quotes I’ve obtained include installation by the manufacturer’s selected installers, which has a benefit from some companies, mainly that the maker will guarantee the installation for a period of time.

The first floor living room window is currently double hung with a mullion and a tripartite transom window above it (with stained glass, that’s been covered on the inside). This window will become a “Chicago window” with a centered picture window flanked by casement windows.

I would share the quotes with you but I don’t think they would be very helpful at this point because I haven’t evaluated each of the quotes on the quality of the window. For example, one of the window quotes was three times higher than the next highest quote, but the maker guarantees installation for 10 years and is a higher-quality window. But what is the factor of difference in quality, is it three times? And how valuable is a 10-year installation warranty? It’s unlikely I would need to avail that benefit and the three times difference in price means I could replace all of the windows *again* two times for the same price! (Assuming prices didn’t increase between now and that future moment.)

The 15 new windows, according to the four quotes, will cost anywhere from $12,000 to $46,000. I should mention that the highest quote doesn’t include any discounts or special offers, as those will be offered once I re-engage the estimator and ask for one.

Look at windows 1.1 and 1.3, which look like bay windows. These are both Chicago windows, which have a center picture (fixed) window flanked by two operable windows (casement windows, in this case).

There are a couple of opportunities to reduce window costs. I could convert more of the casement windows to be double hung windows (which I don’t want to do as I prefer single hung windows), or I could change the window opening size. A couple of the window openings are taller than most of the window makers have in their standard window design, so an upper transom (fixed) window would be required. However, changing the window opening size may end up shifting costs to a different plan of adding bricks and adjusting walls.

Another way to reduce the window cost would be to use models that are less energy efficient, but I also don’t want to do that. I’ve insisted that every window be Energy Star certified – this is about the only certification standard that I understand, and it’s common across most window makers in the Chicago area. (There are also Passive House and Passivhaus certified windows, and companies that import higher-quality and more efficient windows from European manufacturers, but I haven’t bothered with any of those because I assume the prices will be even higher.)

Lumber and other construction materials

That lumber prices have more than doubled over prices a year ago is well known if you read real estate industry news media, or if you’ve shopped for wood at Menards to build a couple of benches for some nicer outdoor space or installed a new porch.

My gut rehab will require a lot of plywood (to replace the subfloor), “soft lumber” replacement studs, and some replacement joists.

The St. Louis Federal Reserve maintains “FRED”, an amazing website with interactive charts to explore economics statistics, including lumber. The pricing information comes from the Bureau of Labor Statistics and their Producer Price Indexes.

The chart for the plywood price index below shows very stable pricing in fall and winter 2019-2020, and then in May 2020 prices start climbing and the index increased by 100 points to March 2021.

U.S. Bureau of Labor Statistics, Producer Price Index by Commodity: Lumber and Wood Products: Plywood [WPU083], retrieved from FRED, Federal Reserve Bank of St. Louis; https://fred.stlouisfed.org/series/WPU083, April 25, 2021.

BLS has monthly detailed reports so you can find data about more than the products FRED has charts for. Let’s dig in to the March 2021 report (indexes mean that the pricing represents percentage changes based on 100% being the price when the index was established):

  • “Softwood dressed 2-inch lumber, 2 inches in nominal thickness only, not edge worked” (a.k.a. 2×4 studs) (index established June 2012):
    • March 2020: 205.1
    • March 2021: 324.1 (this means that the price has increased by 119% year over year, a more than doubling of price)
  • “Softwood plywood products: rough, sanded, and specialties” (index established December 2011):
    • March 2020: 139.0
    • March 2021: 242.9 (again, this means that the price has increased by 103.9%, doubling the price)

In addition to general demand being much higher, there are other reasons why lumber costs so much more right now, according to Robinson Meyer writing in The Atlantic last week.

Since 2018, a one-two punch of environmental harms worsened by climate change has devastated the lumber industry in Canada, the largest lumber exporter to the United States. A catastrophic and multi-decade outbreak of bark-eating beetles, followed by a series of historic wildfire seasons, have led to lasting economic damage in British Columbia, a crucial lumber-providing province. Americans have, in effect, made a mad dash for lumber at the exact moment Canada is least able to supply it.

[snipped]

“There are people who say, ‘Climate change isn’t affecting me,’” Janice Cooke, a forest-industry veteran and biology professor at the University of Alberta, told me. “But they’re going to go to the hardware store and say, ‘Holy cow, the price of lumber has gone up.’”

[snipped]

It has lost 2.5 billion board feet of annual production capacity since 2019, enough to shift prices in a North American market of 70 billion annual board feet, Jalbert said.

Read Robinson’s full article to see how the bark-eating beetles overwhelmed the forests of British Columbia and the northern forest belt in Canada and why their rampage is fueled by climate change.

The same contractor, when they checked in with me recently, said that the prices of other construction materials had gone up, too.

In the same Producer Price Index report, it looks like wood doors and door frames went up 29.2% from March 2020 to March 2021; metal windows are up 7.1%, double hung wood windows are up 6.8% and wood casement windows are up 5.1%; wood moldings are up 16.9%.

I didn’t see any notable price increases in plumbing materials or kitchen cabinets – all were close to inflation. The PPI doesn’t have vinyl window products, or I don’t know under which category it falls.

Two-flat journal 4: How’s the renovation going?

March 22, 2021 / / 1 Comment

I get this question several times a week, and I want to give everyone who asks a clear and accurate response without ignoring them (which I sometimes do). This blog post is how I can do that efficiently, and be more organized and mindful about the progress.

tl;dr:

  • The renovation part of the renovation project hasn’t started.
  • The preparation part of the renovation project has been going on since August 2020.
  • I do not know when we’ll be done, or when we’ll have a permit, or when construction will start.

“Why is it taking so long?” is the question that people don’t ask aloud, but I guess that my friends are like me and curious.

It’s taking a long time because my architect and I don’t spend a whole lot of time working on the project. It’s all relative, of course. We spend about 10 hours a week on this (at most), and that includes a lot of learning and research.

^ Not my house 🙂 These three mini-split outdoor heat pump condenser units are for someone else’s three-flat that’s described below. Each has a heating capacity of 36,000 BTUs, and can perform at 80 percent capacity when the outdoor air temperature is -13°F. Read more about this tour on my @ChiBuildings Twitter thread.

We both have full-time jobs, have other things to work on, and our experience in this arena is limited. My architect, who prefers to stay out of the blogging spotlight, is extremely experienced in multi-family new construction residential architecture, though. We also rely heavily on the input of friends and neighbors who live in similar houses and have experience dealing with the construction idiosyncrasies of old buildings in Chicago. I also rely on the input of other architects who are experts in designing renovated and new construction one, two, and three-unit houses (thank you K.D. and P.M.).

This is a learning process for both of us. There are so many decisions to make, independently and together. We both want to be deeply involved in understanding what it takes to buy and gut rehab a house that I will live in. If we do it well this time, it would make it easier for either one of us to do it again for a different house.

What we’ve done in the past two weeks

  • I invited a fourth general contractor into the house to take a look. I am not getting estimates yet from these four GCs because the plans / permit drawings aren’t far enough along. Each GC, however, has reviewed the current plans prior to the visit so it’s easier to point and explain what’s going to change.
    • One of the big things that will change is replacing the wooden center beam in the basement to a steel I-beam, and changing out some of the joists. This will require temporarily shoring (supporting) the joists. Each GC has had a slightly different opinion on the procedure. But we get to hear each procedure and come up with the one we think is best.
  • I received and organized three quotes for all-new windows, as well as researched and modified the window schedule. “Organizing” the quotes means adding a new column to my window schedule spreadsheet with each manufacturer’s pricing so I can compare them. Part of window research is figuring out the type of window I want for each room and opening and assessing the quality of the manufacturer. For example:
    • I am learning that casement windows are more energy efficient because they create a better seal when closed and locked than hung windows.
    • I prefer single-hung windows to double-hung windows, but some manufacturers don’t make single-hung windows.
    • Every manufacturer has different maximum window heights. One of the openings is 80 inches tall and, for one manufacturer, the maximum height for a casement window is 72 inches and the maximum height for their hung windows is 75 inches. Thus, the estimator added transom windows to all openings that exceeded their maximums. That’s fine, but then it adds a new decision point: What proportion should be operable windows and what proportion should be transom windows?
While discussing window openings and installation details, my architect realized we didn’t collect enough data when we measured it last fall. We went to my house last week to get a closer look, take some new measurements, and inspect the conditions of the sills. The last GC had pointed out that some of the sills should be replaced.

  • My architect continued talking to the structural engineer I hired to make sure the engineer’s drawing has the right details and lists the specifications we asked him to write out on the drawing.
  • Settled on an HVAC system. For a couple months I’ve been researching mini-splits off and on. I got an estimate for equipment + installation from one company, which was much higher than I expected. I combined their projections for required heating capacity with projections from others, as well as CoolCalc.com, to settle on a lower capacity.
    • I started looking at more retailers who will sell mini-split systems directly to the consumer, or the GC, to get grounded in pricing and availability. Then, when I was done specifying the schedule, my architect took a look at the designs and the cut sheets, and figured out where the indoor units should go. He recommended choosing the cassette style that gets mounted within the ceiling between two joists. It costs more, but it looks a lot better since there’s not a big box hanging on the wall in the living room and kitchen.
    • Last Friday my architect and I visited a “near Passive House” standard three-flat being built in Pilsen. The developer, who will live in one of the units, also used mini-splits, from Mitsubishi. It was cool to see them installed (on the roof) and how they were connected to an air handler that serves as the blower to push the air through a traditional duct and out via an “energy recovery ventilator” (in the winter, the warmth of the outgoing air is transferred to the fresh incoming air so the machinery has to expend less energy warming up cold air).

While I was writing this, another of the four GCs that toured the house checked in. I told them that after this week we’ll have made significant progress since my architect has the week off of work.

What we’re working on next

  • Last week, my architect started drawing a window detail in the plans because there are good ways of installing windows and bad ways. I don’t fully understand them, so I’m not going to write about them here, but he did explain them to me and pointed out some good examples in his house and some bad examples in someone else’s house.
  • Finish the “panelboard schedule”. This diagram shows each circuit that will be in a breaker box, and labels them according to the circuits in the electrical drawings. Not every circuit breaker will be identical, as some circuits – like the ones for the water heater and mini-splits – need 30 or 40 amp breakers.
  • I am going to research the construction type’s fire protection requirements by reading the Chicago Building Code and noting the required fire ratings for the basement ceiling, the ceiling between floors, the interior front door’s rating, the roof rating, the rating for the light well wall, and the rating requirement for load bearing walls.

What I’m worried about

All of the decisions that have to be made. See the three points about windows above. I’ve got three quotes for the windows, all based on about the same schedule. But after I receive the fourth quote that I’m expecting this week, my desire for certain windows may change. I may want more casement windows, or different proportions of transom windows and casement-picture-casement window layouts.

The cost of everything. I have significant savings but I will still need to borrow some money. I need to start researching the universe of options and the availability of options. On top of this, lumber pricing has doubled since the start of the COVID-19 pandemic.

I shoveled the walks every time it snowed. This is significant because I don’t live here so I took the 82 Kimball/Homan bus from where I live to where I don’t live. I enjoyed it because it got me out of the house when it was cold, and it gave me some moments of exercise.
Map of the density of news articles in the Chicago Cityscape database

How to visualize the density of point data in a grid

February 16, 2021 / / 0 Comments

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.

  1. 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:

  1. The ID of the tile, which is a primary key field.
  2. The number of news articles in that tile.
  3. 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.
Screenshot of COVID vaccination sites map on Chicago Cityscape

A new map for finding COVID vaccination sites in Illinois

February 16, 2021 / / 0 Comments

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 made a new version of the state’s COVID vaccination sites map.

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. 

The new map of COVID vaccination sites on Chicago Cityscape.

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.