Ideas by Kristen
CDOT Logo.png

Chicago Street Lights Engineering

Company: Milhouse Engineering and Construction

Client: City of Chicago

Goal: Create a system to replace the 365,365 streetlights in Chicago

Role: Assistant Project Manager

Tools: Google Earth, Google Maps, Visual Basic, Excel, ArcGIS, Google Sheets, Google Maps

Background

141 TIF Districts in Chicago

If you own property in Chicago, you pay taxes. Your taxes are summed with everyone else’s taxes in the surrounding area which is called a Tax Increment Funding district (“TIF”). The sum of tax dollars for that area iis called a Tax Increment Funding Fund (“TIF Fund.” It is a bit redundant, but I didn’t name it).

There are 141 TIFs in Chicago, each with its own TIF Fund to spend on community projects including park renovation, sidewalk replacement, and street lighting repairs. Our job was to help our client determine which poles they needed to replace in each TIF so that they could use their TIF funds most effectively.

The City gave Milhouse a database filled with details about all 365,365 street light poles in the city, including height, location, damage, amount of rust, color, etc. I used this database to create a Visual Basic program that would automatically determine which poles needed to be replaced with TIF funds, shortening the manual process from 24 hours to approximately a minute.


Personal Goals

  • To figure out how to make the user interface simple for both engineers and interns

  • To anticipate potential errors, including:

    • User not understanding that the computer is not frozen, but that the program is running

    • User typing in information incorrectly into program

    • A TIF containing no poles in need of replacement

Project Goals

  1. Determine which streetlight defects are more important than others within a TIF

  2. Translate defects of the poles in the TIF into meaningful numbers

  3. Rank the poles inside the TIF in order of priority

  4. Create consolidate all relevant information for poles in the specified TIF


join me, won’t you?

To illuminate my process, I pull excerpts from the code so that you can see a bit of work that went into the project. As a note, I had never used Visual Basic prior to this project and was learning as I wrote the program. I was aided by prior knowledge of Python and MATLAB, so my primary hiccup was learning VBA syntax.

 
 

Prioritizing Defects

Let’s play a game. Guess which pole poses a more immediate safety risk to passersby:

Pole 1 is leaning over 45 degrees from the ground

Pole 2 has no issues

It’s clear that Pole 1 poses a more immediate threat, as it may fall onto people walking nearby, so prioritizing Pole 1 over Pole 2 is easy. Imagine a more difficult situation:

Pole 1 is leaning over 45 degrees from the ground

The horizontal arm that holds the light on Pole 2 (aka the "mast arm") is dangling from the pole

Determining which poses a bigger risk is a bit more difficult, as both poles could pose a safety risk to nearby citizens. If you had to evaluate a pole that had more than one issue, and had to compare it others, it would become rather subjective and cumbersome. To reduce human judgement in the overall process, my project manager and I worked together to determine “tiers” of danger for each possible defect (Fig 1). I used these tiers in the program that I created to list the poles in each TIF in order of priority. (Note: the numbers in the “value for each” column are arbitrary.)

Fig 1: Tiers of Danger

Fig 1: Tiers of Danger


Making The User Form

 

The users of this program would range in experience from college interns to Professional Engineers nearing retirement. Given the age range, the code needed to be as user friendly as possible. I determined that the easiest way for people to interact with the program is through a dropdown menu, in which they only had one option to choose.

The backbone of the user form is as follows:

  1. Determine the names and numbers of all of the TIFs

    • (e.g. TIF 002: 41st St and King Rd)

  2. Sort the list into numerical order via a selection sort for ease of use

  3. Put the list into a combo box (aka “dropdown menu”) for the user to choose

  4. Create functions that are triggered by other events

    • clicking the “Sort” button without a TIF listed

    • clicking the “Sort” button with a TIF listed

    • closing out of the window

The Code

 

Rating Poles

Using the values we decided on in the Prioritizing Defects section, we found which poles have the most dangerous defects and should be replaced first. Imagine a pole that has the following:

  • Wiring with a Rack and Spool, thus is routine (Tier 4: 25 Points)

  • No Wiring Issue (Tier 5: 10 points)

  • No Mast Arm issue (Tier 5: 10 points)

  • Major Pole and Foundation Leaning (Tier 1: 2000 points)

  • Major Damage (Tier 1: 2000 points)

  • Major Rust on the Pole Base (Tier 1: 2000 points)

  • Minor Anchor Bolt Damage (Tier 3: 99 points)

    The sum of points in this pole is 6144. We then compare this pole to all of the other poles in the TIF and make a chart of the poles in order to give to the client.

The client then works with the Milhouse Project Manager to determine which poles need to be replaced and by what method they should do the replacement.