CategoryData

How to make a map of places of worship in Cook County using OpenStreetMap data

The screenshot shows the configuration you need to find and download places of worship in Cook County, Illinois, using the Overpass Turbo website.

If you’re looking to make a map of churches, mosques, synagogues and other places of worship, you’ll need data.

  • The Yellow Pages won’t help because you can’t download that.
  • And Google Maps doesn’t let you have a slice of their database, either.
  • There’s no open data* for this because churches don’t pay taxes, don’t have business licenses, and aren’t required to register in any way

This where OpenStreetMap (OSM) comes in. It’s a virtual planet that anyone can edit and anyone can have for free.

First we need to figure out what tag people use to identify these places. Sometimes on OSM there are multiple tags that identify the same kind of place. You should prefer the one that’s either more accurate (and mentioned as such in the wiki) or widespread.

The OSM taginfo website says that editors have added over 1.2 million places of worship to the planet using amenity=place_of_worship.

Now that we know which tag to look for, we need an app that will help us get those places, but only within our desired boundary. Open up Overpass Turbo, which is a website that helps construct calls to the Overpass API, which is one way to find and download data from OSM.

Tutorial

In the default Overpass Turbo query, there’s probably a tag in brackets that says [amenity=drinking_fountain]. Change that to say [amenity=place_of_worship] (without the quotes). Now change the viewport of the map to show only the area in which you want Overpass Turbo to look for these places of worship. In the query this argument is listed as ({{bbox}}).

The map has a search bar to find boundaries (cities, counties, principalities, neighborhoods, etc.) so type in “Cook County” and press Enter. The Cook County in Illinois, United States of America, will probably appear first. Select that one and the map will zoom to show the whole county in the viewport.

Now that we’ve set the tag to [amenity=place_of_worship] and moved the map to show Cook County we can click “Run”. In a few seconds you’ll see a circle over each place of worship.

It’s now simple to download: Click on the “Export” button and click “KML” to be able to load the data into Google Earth, “GeoJSON” to load it into a GIS app like QGIS, or “save GeoJSON to gist” to create an instant map within GitHub.

*You could probably get creative and ask a municipality for a list of certificates of occupancy or building permits that had marked “religious assembly” as the zoning use for the property.

Two things I don’t like about TIF expenditures in Chicago

Chicago Cityscape's TIF Projects map

I built a map of most Chicago TIF projects that you can filter on the fly. Type in any keyword, alderman’s name, or neighborhood and the map will re-center and zoom to the results.

1. Millions of dollars ($14.4 to be exact) has been or will be given to rich corporations, like Home Depot, to build massive stores with huge roofs and parking lots far away from where people live so everyone has to drive there. It’s highly unlikely they don’t mitigate stormwater runoff (except through temporary storage in a retention pond) or treat any of the water on site, contributing to local flooding and clogged pipes.

According to the project descriptions, property tax payers in these four TIF districts have partially subsidized the construction of over 1,903 car parking spaces and the associated ills of expansive asphalt areas and motorized traffic.

2. A massive subsidy was approved – $96 million – for McCaffery Interests’s Lakeside development on the former U.S. Steel South Works plant to build a mixed-use tower of 250 apartments in an area that has weak transit access and will take decades to fully fill out. We should instead be spending this kind of money building housing in already developed parts of the city (where there’s already amenities, or infrastructure for amenities – the Rezko land comes to mind).

What’s interesting about the Lakeside TIF project approval is that the containing TIF district, “Chicago Lakeside Development Phase 1”, has collected zero property tax revenue because there is no property in it!

Trolley on the future Lake Shore Drive

A tour bus drivers on the Lakeside development. Photo by Ann Fisher.

There are some projects I like, though. TIF has been used frequently to build affordable housing, housing for seniors, and housing for people who need assistance. 78 out of 380 projects mention the word “affordable”.

The City Hyde Park building, designed by Studio Gang Architects, will have 20% of its residential units designated as “affordable”, for families (of varying sizes) earning up to 60 percent of the area median income. The city standard is 10 percent but developers are also able to pay an “in lieu” fee so they don’t have to build the affordable units and instead can offer those units at market rates.

Other projects have a majority of affordable units.

Who are the top property owners in Cook County

235 West Van Buren Street

There are several hundred condo units in the building at 235 W Van Buren Street, and each unit is associated with multiple Property Index Numbers (PIN). Photo by Jeff Zoline.

Several people have used Chicago Cityscape to try and find who owns a property. Since I’ve got property tax data for 2,013,563 individually billed pieces of property in Cook County I can help them research that answer.

The problem, though, is that the data, from the Cook County combined property tax  website, only shows who receives the property tax bills – the recipient – who isn’t always the property’s owner.

The combined website is a great tool. Property value info comes from the Assessor’s office. Sales data comes from the Recorder of Deeds, which is another, separately elected, Cook County government agency. Finally, the Treasurer’s office, a third agency, also with a separately elected leader, sends the bills and collects the tax.

The following is a list of the top 100 (or so) “property tax bill recipients” in Cook County for the tax years 2010 to 2014, ranked by the number of associated Property Index Numbers.

Many PINs have changed recipients after being sold or divided, and the data only lists the recipient at its final tax year. A tax bill for Unit 1401 at 235 W Van Buren St was at one time sent to “235 VAN BUREN, CORP” (along with 934 other bills), but in 2011 the PIN was divided after the condo unit was sold.

Of the 100 names, DataMade’s new “probablepeople” name parsing Python script identified 13 as persons. It mistakenly identified eight names as “Person”, leaving five people in the top 100.

The actual number is closer to 90, arrived at by combining 5 names that seem to be the same (using OpenRefine’s clustering function) and removing 5 “to the current taxpayer” and empty names. You’ll notice “Altus” listed four times (they’re based in Phoenix) and Chicago Title Land Trust, which can help property owners remain private, listed twice (associated with 643 PINs).

[table id=2 /]

Links between Emanuel’s campaign donors and their building projects

The Tribune called out Emanuel’s appearance at a press conference as an endorsement of a locally-designed skyscraper (Studio Gang and bKL Architecture) to be built by Wanda, a Chinese development company – it has yet to receive any approval. Photo: Ted Cox, DNAinfo.

The Chicago Tribune reviewed the campaign contributions of Mayor Rahm Emanuel’s top donors and linked each donor to how it does business with Emanuel or the city. The article overall discussed how easy it is for Rahm to raise more money than what’s probably necessary to be elected a second time.

The Tribune graciously provided this data as a simple table which I’ve republished here in order to add links to building permit information from Chicago Cityscape. The website I’ve developed lists company and person names in an immediately searchable form. Currently there are over 90,000 companies, architects, and property owners that have received a building permit since 2010. Use the Illinois Sunshine database to find out who’s contributing to whom in the Chicago election.

Note: You’ll see “listed under [many] names” for several companies; this indicates that the Chicago building permit database uses different spellings, or the company has changed their name.

[table id=1 /]

Neither the article nor this table are meant to indicate any wrongdoing – campaign donations are public and it’s common to receive them from companies that do business in Chicago. It’s the extent that the donation appears to pay for favors or favoritism over other donors (which may be competing companies), or what’s right, that determines when immorality becomes an issue (a connection that’s hard to demonstrate).

Working with ZIP code data (and alternatives to using sketchy ZIP code data)

1711 North Kimball Avenue, built 1890

This building at 1711 N Kimball no longer receives mail and the local mail carrier would mark it as vacant. After a minimum length of time the address will appear in the United States Postal Service’s vacancy dataset, provided by the federal Department of Housing and Urban Development. Photo: Gabriel X. Michael.

Working with accurate ZIP code data in your geographic publication (website or report) or demographic analysis can be problematic. The most accurate dataset – perhaps the only one that could be called reliably accurate – is one that you purchase from one of the United States Postal Service’s (USPS) authorized resellers. If you want to skip the introduction on what ZIP codes really represent, jump to “ZIP-code related datasets”.

Understanding what ZIP codes are

In other words the post office’s ZIP code data, which they use to deliver mail and not to locate people like your publication or analysis, is not free. It is also, unbeknownst to many, a dataset that lists mail carrier routes. It’s not a boundary or polygon, although many of the authorized resellers transform it into a boundary so buyers can geocode the location of their customers (retail companies might use this for customer tracking and profiling, and petition-creating websites for determining your elected officials).

The Census Bureau has its own issues using ZIP code data. For one, the ZIP code data changes as routes change and as delivery points change. Census boundaries needs to stay somewhat constant to be able to compare geographies over time, and Census tracts stay the same for a period of 10 years (between the decennial surveys).

Understanding that ZIP codes are well known (everybody has one and everybody knows theirs) and that it would be useful to present data on that level, the Bureau created “ZIP Code Tabulation Areas” (ZCTA) for the 2000 Census. They’re a collection of Census tracts that resemble a ZIP code’s area (they also often share the same 5-digit identifiers). The ZCTA and an area representing a ZIP code have a lot of overlap and can share much of the same space. ZCTA data is freely downloadable from the Census Bureau’s TIGER shapefiles website.

There’s a good discussion about what ZIP codes are and aren’t on the GIS StackExchange.

Chicago example of the problem

Here’s a real world example of the kinds of problems that ZIP code data availability and comprehension: Those working on the Chicago Health Atlas have run into this problem where they were using two different datasets: ZCTA from the Census Bureau and ZIP codes as prepared by the City of Chicago and published on their open data portal. Their solution, which is really a stopgap measure and needs further review not just by those involved in the app but by a diverse group of data experts, was to add a disclaimer that they use ZCTAs instead of the USPS’s ZIP code data.

ZIP-code related datasets

Fast forward to why I’m telling you all of this: The U.S. Department of Housing and Urban Development (HUD) has two ZIP-code based datasets that may prove useful to mappers and researchers.

1. ZIP code crosswalk files

This is a collection of eight datasets that link a level of Census geography to ZIP codes (and the reverse). The most useful to me is ZIP to Census tract. This dataset tells you in which ZIP code a Census tract lies (including if it spans multiple ZIP codes). HUD is using data from the USPS to create this.

The dataset is documented well on their website and updated quarterly, going back to 2010. The most recent file comes as a 12 MB Excel spreadsheet.

2. Vacant addresses

The USPS employs thousands of mail carriers to delivery things to the millions of households across the country, and they keep track of when the mail carrier cannot delivery something because no one lives in the apartment or house anymore. The address vacancy data tells you the following characteristics at the Census tract level:

  • total number of addresses the USPS knows about
  • number of addresses on urban routes to which the mail carrier hasn’t been able to delivery for 90 days and longer
  • “no-stat” addresses: undeliverable rural addresses, places under construction, urban addresses unlikely to be active

You must register to download the vacant addresses data and be a governmental entity or non-profit organization*, per the agreement** HUD has with USPS. Learn more and download the vacancy data which they update quarterly.

Tina Fassett Smith is a researcher at DePaul University’s Institute of Housing Studies and reviewed part of this blog post. She stresses to readers to ignore the “no-stat” addresses in the USPS’s vacancy dataset. She said that research by her and her colleagues at the IHS concluded this section of the data is unreliable. Tina also said that the methodology mail carriers use to identify vacant addresses and places under change (construction or demolition) isn’t made public and that mail carriers have an incentive to collect the data instead of being compensated normally. Tina further explained the issues with no-stat.

We have seen instances of a relationship between the number of P.O. boxes (i.e., the presence of a post office) and the number of no-stats in an area. This is one reason we took it off of the IHS Data Portal. We have not found it to be a useful data set for better understanding neighborhoods or housing markets.

The Institute of Housing Studies provides vacancy data on their portal for those who don’t want to bother with the HUD sign-up process to obtain it.

* It appears that HUD doesn’t verify your eligibility.

** This agreement also states that one can only use the vacancy data for the “stated purpose”: “measuring and forecasting neighborhood changes, assessing neighborhood needs, and measuring/assessing the various HUD programs in which Users are involved”.

I’ve got property tax data for Chicago Cityscape

Wrigley Field Ahead of a Seemingless Meaningless Game, September 2011

Wrigley Field is an old baseball stadium in Chicago’s Lakeview neighborhood. Photo by Dan X. O’Neil

1. Licensed Chicago Contractors, my website that tracks what developers and the city are proposing to build or demolish in your neighborhood, is now called Chicago Cityscape.

2. I’m grateful to Ian Dees who helped me get property tax data for 2009-2013 for over 1.4 million PINs (property identification numbers) in Cook County.

I’m going through various parts of the property tax data and figuring out how to integrate it with Chicago Cityscape. The first time Ian got the data I found out I didn’t tell him to get the right PINs. I think I’ve fixed that now.

As part of this process I’m checking properties somewhat randomly, based on the permits I’m browsing. I most recently viewed a Wrigley Field building permit at 1060 W Addison Street – for a Zac Brown concert – so I searched its PIN and how much the property is “worth”. Here goes:

Year Amount Billed Assessed Value
2013 $1,517,665.09 $8,049,996
2012 1,498,971.03 8,049,996
2011 1,493,002.47 8,865,636
2010 1,489,160.89 8,865,636
2009 1,360,673.45 10,613,423

Notice how the assessed value dropped over $2 million from 2009 to 2010. And even though it had three unique assessed values, the annually changing tax rate adjusted the amount billed. You can see this information on the Cook County Property Info portal.

Finding teardowns in Chicago

1923 South Allport Avenue, built 1884

A recent suspected teardown, at 1923 S Allport in Pilsen (25th Ward, 19th place for teardowns from 2006 to now). The demolition permit was issued August 7 and the new construction permit was issued August 5. The new building will have an increase in density, with three dwelling units. Photo by Gabriel Michael.

From Wikipedia, a teardown is a “process in which a real estate company or individual buys an existing home and then demolishes and replaces it with a new one”.

You can find suspected* teardowns in the building permits data on Licensed Chicago Contractors by looking for demolition permits and new construction permits for the same address. I limited my search to situations where the demolition permit was issued within 60 days prior or subsequent to the new construction permit. This shows properties that have a quick turnaround (thus more likely to get built). I didn’t want to include buildings that may have been demolished one year and got a building two years later.

Analysis

This analysis is based on data since January 1, 2006, the start of the first complete year of building permits data in the Chicago open data portal, and ends today. The first demolition permit in this analysis was issued January 10, 2006, and its associated new construction permit was issued five days prior. There may be a case when the demolition permit and new construction permits were issued in different years, but for this analysis I only consider the year in which the demolition permit was issued. (In my review of permits since March I believe that new construction permits are issued most often after the demolition permit.)

Suspected teardowns

The number for teardowns decreased dramatically as the economic crisis approached.

Results

There were 1,717 suspected teardowns in Chicago distributed across 57 community areas (of 77, whose boundaries don’t change) and 45 wards (of 50, whose boundaries changed in 2012).

West Town, Lake View, and North Center share top billing, with the most teardowns each year, but Lake View was #1 for seven of 10 years. Other top five community areas comprise Logan Square (thrice), Lincoln Square (thrice), Bridgeport (twice), McKinley Park (once), and Near West Side (once).

From 2012 to current, the most teardowns occurred in Wards 32 (Waguespack), 47 (Pawar), 1 (Moreno), 44 (Tunney), and 43 (Smith). All of those wards include parts of the top three community areas mentioned above.

The sixth ward with the most teardowns in this period was 2 (Fioretti) but this boundary no longer represents any part of the pre-2012 boundary that covered almost the entire South Loop. That means Ward 2 is now covering the west side. Additionally, the 2nd Ward made sixth place with 28 teardowns and fifth place, the 43rd Ward had 60 teardowns.

The South Loop, represented by the Near South Side community area, has had 0 suspected teardowns from 2012 to now. There was one teardown in the entire time period, where a three-story commercial was demolished at 1720 S Michigan Ave and replaced with a 32-story residential tower.

What else do you want to know about teardowns in Chicago?

* Notes

I use “suspected” because it’s impossible to know from the data if buildings were actually demolished and constructed.

Download the data as CSV for yourself.

Morgan CTA station ranks highly in rail system for building permits

Let Your Conscious Be Your Guide

The gutted cold storage warehouse in the background is within a quarter mile of the Morgan CTA station. Photo by Seth Anderson.

Excluding all of the Chicago Transit Authority stations in the central business district you’ll find that the new Morgan station ranks highly in the number of building permits issued within a quarter mile. It has a top spot when you calculate those permits’ estimated project costs. The CTA recently discussed with DNAInfo the results of a preliminary study it conducted that showed how the Morgan station is at the center of a lot of construction growth in the West Loop/Fulton Market area, and a contributing factor to this growth.

Now that Licensed Chicago Contractors shows you the two nearest CTA and Metra rail stations to each building permit, and I’ve become well-versed in writing PostGIS queries on the fly, I wrote a query that lists the CTA stations with the most building permits within a quarter mile (“nearby”).

First, though, let’s count how many stations don’t have permits nearby. With the query at the bottom you get a list of station names, the number of permits nearby, and a sum of the estimated costs of those permits sorted by the number of permits. Since I used a “LEFT JOIN” I also get a count of all the permits (the table on the LEFT) that don’t have a match with CTA stations (the table on the right).

There are 127 rows returned and a previous count of the table told me there are 145 stations, including ones outside the Chicago city limits. (There are stations in Cicero, Wilmette, Evanston, Rosemont, Oak Park, Forest Park, and Skokie.) The first row represents NULL, or all of the stations that don’t have permits nearby. That leaves me with 126 rows and 19 stations without permits, or 19 stations outside the City of Chicago.

I verified this by eyeballing it. I looked at a map and counted roughly 19 stations that wouldn’t have the 1/4 mile overlap with a Chicago building permit. The two Austin stations, on the Blue Line Forest Park branch and the Green Line Oak Park branch, are near Chicago and also showed up as a discrete station in the query results. Austin on the Blue Line was dead last, actually!

Let’s get back on track and look at Morgan now. I don’t think it’s fair to compare the Morgan station area with an expected, higher-activity area like the Loop and Central Business District so I eyeballed the list and started the #1 ranking with the first station outside the CBD.

  1. Armitage (Brown, Purple Express) is the station outside the CBD with the most building permits nearby.
  2. Damen-Milwaukee (Blue)
  3. North/Clybourn (Red)
  4. Addison (Red)
  5. Morgan (Green, Pink)

There you have it, from 2009 to today, the Morgan station had the fifth highest number of building permits outside of the Chicago Central Business District. It beat Fullerton (Red, Brown, Purple) in Lincoln Park, and Roosevelt (elevated and subway combined) in the South Loop. The station’s construction began in 2010 and the grand opening occurred May 24, 2012. During this period Morgan had the second highest amount of aggregated estimated costs at $199,911,953.00, behind North/Clybourn, at $218,118,037.37.

Take this analysis with several grains of Morton salt, though, because the following caveats are important to consider: building permits are really speculative development; much of these may be for kitchen renovations or porch reconstructions; I didn’t look up when it was “for sure” that the station was being built so I don’t know when developers would have become interested.

Looking at a longer period

I will, however, run a few more queries to find how Morgan’s position changes, starting with expanding the query to “all time” data (really the end of 2006 to today). It turns out that when looking through all available years Morgan’s position remains at #5 but other stations change position.

  1. Fullerton
  2. Armitage
  3. Damen-Milwaukee
  4. Addison
  5. Morgan

During this period, which covers the end of 2006 until today, Morgan had the highest aggregated estimated costs of the above five stations, at $236,707,083.00. It beat Fullerton’s amount of $160,825,680.30.

Looking only at “new construction”

Since these include all permit types, including water heater installations and window replacements, it doesn’t give us a good look at economic expansion in the areas surrounding CTA stations. I’ve filtered the data so only “new construction” building permits come through. I’m still interested in stations outside the CBD. Here’s how Morgan performed when looking at purely the quantity of new construction permits issued from 2009 to today:

  1. Armitage, 46 new construction building permits
  2. Southport, 38
  3. Addison (Red), 34
  4. North/Clybourn,
  5. Wellington,
  6. California-Milwaukee,
  7. Belmont (Red)
  8. Ashland (Green, Pink)
  9. Irving Park (Brown)
  10. Fullerton
  11. Damen (Brown)
  12. Division-Milwaukee
  13. Western-Milwaukee
  14. Ashland (Orange)
  15. Damen-Milwaukee
  16. Western-Congress
  17. Paulina
  18. Addison (Brown)
  19. Diversey
  20. Sedgwick
  21. Loyola
  22. Montrose (Brown)
  23. Sox-35th-Dan Ryan
  24. Morgan, 13 new construction building permits

Let’s remove that date filter and look at the whole building permits period of late 2006 to today.

  1. Southport (Brown Line), 80 new construction permits, all-time
  2. Armitage (Brown, Purple), 72
  3. Western-Congress (Blue), 66
  4. Addison (Red), 64
  5. Belmont (Red, Brown), 63
  6. Western-Milwaukee, 59
    Damen-Milwaukee, 59
  7. North/Clybourn, 55
    Diversey, 55
  8. Division-Milwaukee, 53
  9. Sox-35th-Dan Ryan, 51
  10. Wellington, 50
  11. 35-Bronzeville-IIT, 48
  12. Irving Park (Brown), 44
  13. Morgan, 43 new construction permits

Now switching the order method around and Morgan appears better when you look at aggregated estimated costs, from 2009 to today.

  1. Illinois Medical District, $236,020,000.00
  2. North/Clybourn, $172,373,335.00
  3. Loyola, $161,744,075.00
  4. Polk, $106,000,000.00
  5. Grand-Milwaukee, $77m224,500.00
  6. Wellington, $72m802,300.00
  7. Belmont (Red), $71,300,302.00
  8. Morgan, $68,300,800.00

Last query – remove the data filter and look at aggregated costs for the whole building permits period where Morgan maintains a top 10 position.

  1. North/Clybourn, $277029045.00
  2. Illinois Medical District, 236,020,000.00 (same as 2009 to today period)
  3. Polk, $188,794,975.00
  4. Loyola, $185,444,075.00
  5. Belmont (Red), $1635,00,085.00
  6. Fullerton, $129,444,051.00
  7. Wellington, $111,335,051.00
  8. Granville, $99,356,702.00
  9. Morgan, $83,995,800.00

The data I’d really like to have, though, is sales tax receipts for the same years.

This is not a valid PostgreSQL query. The brackets indicate the options I was using to retrieve the above results. The geometries are in or transformed to EPSG 3435 (Illinois StatePlane East Feet) and 1,320 feet is a quarter mile.

SELECT
 COUNT (P .permit_) AS count,
 MIN (C .longname) as name,
 min(lines) as lines, 
 sum(_estimated_cost) as sum
FROM
 permits P left join
 stations_cta C
ON
 ST_DWithin (
  ST_Transform (P .geometry, 3435),
  C .geom,
  1320
 )
[WHERE] [EXTRACT (YEAR FROM issue_date) >= 2009] [_permit_type = 'PERMIT - NEW CONSTRUCTION']
GROUP BY
 C .gid
ORDER BY
 [count,sum] DESC

Playing around with Chicago data: who’s running red lights?

Range Rover with Illinois license plate "0"

A Range Rover with Illinois license plate “0” is seen moving 40 MPH in a 30 MPH zone through a red light at Ashland and Cortland.

I’m just seeing who’s driving around Chicago one night, using the Tribune-published dataset of over 4.1 million tickets issued from red light cameras.

The City of Chicago has installed at least 340 red light cameras since the mid-2000s to reduce the number of people running red lights and crashing. They’re supposed to be installed at intersections where there’s a higher-than-average rate of right angle (“T-bone”) crashes, which are more injurious than other typical intersection crash types.

Assessing safety wasn’t the Tribune’s story angle, though. It was about showing spikes in the number of tickets issued, which I verified to some extent. The article called the tickets issued during these spikes “undeserved” and “unfair”. The data doesn’t have enough information to say whether or not that is the case; a video or extensive photo review is necessary to rule out rolling right turns while the light was red (a much less dangerous maneuver unless people are trying to cross the street).

The first query I ran assessed the number of people who get more than one ticket from a red light camera. Since I was tired my query was a little sloppy and it missed a lot of more useful order choices and didn’t select the right fields. I fell asleep and started again in the morning. This time, I got it right in just two tries – I needed to try again because I mistakenly put HAVING before the GROUP BY clause.

Here’s the first query, in its final form, to retrieve the number of tickets for each license plate in each state (I assumed there may be identical license plates among states).

select max(ticket_number), max(timestamp), license_plate, state, count(*) AS count FROM rlc_tickets group by license_plate, state HAVING count(*) > 1 order by count DESC NULLS LAST

It resulted in 851,538 rows, with each row representing a unique license plate-state combination and the number of red light violations that combination received. You can reasonably assert that cars don’t change license plates more than a couple times in a single person’s ownership, meaning you can also assert that each row represents one automobile.

851,538 vehicles, which make up 35.1% of all violators, have received 2,601,608, or 62.3%, of the 4,174,770 tickets. (There are 2,424,700 license plate-state combinations, using the query below.)

select count(ticket_number) from rlc_tickets group by license_plate, state

Here’re the top 10 vehicles that have received the most violations:

  1. SCHLARS, IL, 78
  2. 9720428, IL, 59
  3. 8919589, IL, 57
  4. A633520, IL, 52
  5. 3252TX, IL, 45
  6. A209445, IL, 44
  7. N339079, IL, 44
  8. X870991, IL, 41
  9. 239099, IL, 41
  10. 4552985, IL, 40

The next step would be to design a chart to show these vehicles’ activity over the months – did the vehicles’ drivers’ behavior change, decreasing the number of red light violations they received? Did the vehicle owner, perhaps a parent, tell their child to stop running red lights? Or has the vehicle owner appealed erroneously-issued tickets?

When I ran one of the first, mistaken, queries, I got results that put license plate “0” at the top of the list, with only nine tickets (license plates with two or more zeros were listed next).

I googled “license plate 0” and found a 2009 Tribune article which interviewed the Range Rover-driving owner of license plate “0” and the problems he encountered because of it. The City of Chicago parking meter enforcement staff were testing new equipment and used “0” as a test license plate not knowing such that license plate exists. Tom Feddor received real tickets, though.

I then looked up on PhotoNotice the license plate and ticket violation number to find, indeed, the license plate belonged to someone driving a Range Rover at Ashland Avenue and Cortland Street on July 17, 2008. An added bonus was Feddor’s speed in that Range Rover: the camera recorded the car going 40 MPH in a 30 MPH zone.

I was done browsing around for the biggest offenders so next I wondered how many tickets were issued to vehicles licensed in Arizona, where U-Haul registers all of its nationwide vehicles. Arizona plates came in 29th place for the greatest number of tickets.

select count(*) AS count, state from rlc_tickets group by state order by count DESC NULLS LAST

As you may have expected, four surrounding Midwest states, and Ohio, rounded up the top five states after Illinois – but this isn’t notable because most visitors come from there and they each only comprise less than 1.3% of the total tickets. The next state was Florida.

  • 3,986,739, IL
  • 51,104, WI
  • 40,737, MI
  • 27,539, IN
  • 8,550, OH
  • 7,684, MN
  • 7,139, FL

What’s next: I’m working on finding a correlation between the number of reported crashes, and type, at intersections with red light cameras and the number of tickets they issued. I started doing that before running the numbers behind this blog post but it got complicated and it takes a long time to geospatially compare over 500,000 crash reports with over 4.1 million red light tickets.

What else do you want to know?

I will delete all comments that don’t discuss the content of this post, including comments that call red light cameras, or this program, a “money grab”.

Red light camera ticket data insufficient to find cause for spikes

Spike in tickets issued at 119th/Halsted in May and June 2011. Nodes represent tickets grouped by week.

Spike in tickets issued at 119th/Halsted in May and June 2011. Nodes represent tickets grouped by week.

The Chicago Tribune published a dataset of over 4 million tickets issued to motorists for entering an intersection after the light had turned red. They analyzed the dataset and found unexplained spikes, where the number of tickets, being issued by the handfuls each day, suddenly tripled. (Download the dataset.)

I looked at the tickets issued by two of the 340 cameras. I didn’t find any spikes at Belmont/Sheridan (“400 W BELMONT”) but found a noticeable spike in May and June 2011 at the 119th Street and Halsted Street intersection (“11900 S HALSTED”).

I looked at three violations on one of the days that had an atypical number of tickets issued, May 12, 2011. Each motorist was ticketed, it appears, for turning right on red. It’s not possible, though, without watching the video, to see if the motorist rolled through the turn or indeed stopped before turning right.

The Tribune called tickets issued during these “spikes” “undeserved” but that’s hard to say without see the violations on video. The photos don’t provide enough evidence. The Tribune also reported that appeals during these spike periods were more likely to be overturned than in the period outside the spikes. The reporters discussed the possibility of malfunctions and malicious behavior, calling that an “intervention”.

The Chicago Department of Transportation, which oversees the program formerly operated by Redflex and now operated by Xerox, couldn’t refute either allegation, possibly with “service records, maintenance reports, email traffic, memos or anything else”. David Kidwell and Alex Richards report:

City transportation officials said neither the city nor Redflex made any changes to how violations were enforced. They acknowledged oversight failures and said the explosions of tickets should have been detected and resolved as they occurred. But they said that doesn’t mean the drivers weren’t breaking the law, and they defended the red light camera program overall as a safety success story. The program has generated nearly $500 million in revenue since it began in 2003.

The city was unaware of the spikes until given the evidence by the Tribune in January, said David Zavattero, a deputy director for the Chicago Department of Transportation. In the six months since, city officials have not provided any explanations.

“Trust me when I tell you that we want to know what caused these spikes you have identified as much as you do,” Zavattero said. “So far we can find no smoking gun.”

He acknowledged that faulty camera equipment likely played a role.

“I would say that is likely in some of these cases,” Zavattero said. “I cannot tell you that isn’t possible. It is possible. The old equipment was much more prone to break down than the equipment we are currently installing.”

You can download the data but you will likely produce the same results as the Tribune, but maybe a different conclusion. Their analysis has led people at all levels of the civic sphere to call for an investigation, including citizens, some of whom have filed a lawsuit, Alderman Waguespack and 19 other aldermen, and CDOT commissioner – who operates the red light camera program – Rebekah Scheinfeld.

I think they sufficiently identified a suspicious pattern. By the end of the long story, though, the Tribune didn’t prove its hypothesis that the tickets were “undeserved” or “unfair”.

In violation number 7003374335 you can see the driver of a Hyundai Santa Fe turning right at a red light. The Google Street View for this intersection shows that there is no RTOR restriction, meaning the driver is legally allowed to make a right turn here with a red light after coming to a complete stop and yielding to people in the crosswalk. But we can’t see if they stopped first. The next two violations that day at the same intersection I looked showed the same situation. (Find the violation by going to PhotoNotice and inputting 7003374335, NWD648, and CHI.)

I look forward to the investigation. The Tribune made a great start by analyzing the data and spurring the call for an investigation and it seems there’s not enough information in this dataset to explain why there are more tickets being issued.

One dataset that could help provide context – because these spikes, at least the ones that are sustained, don’t seem random – is knowing the number of vehicles passing through that intersection. The speed camera data has this information and allows one to show how different weekend traffic is from weekday traffic.

The "before" image showing the Santa Fe vehicle approaching the stop bar.

The “before” image showing the Santa Fe vehicle approaching the stop bar.

Violation photo 2

After: The Santa Fe vehicle is seen in a right turn.

Note: The Tribune identified 380 cameras but running a DISTINCT() query on the “camera name” field results in 340 values. Some cameras may have identical names because they’re at the same intersection, but you can’t discern that distinction from the dataset.

© 2018 Steven Can Plan

Theme by Anders NorénUp ↑