Counting New York’s parking spots
Published 2014-07-07 · By James Bursa
New York City publishes a data feed containing a list of all parking signs in the city. While I lived there, I owned a car for some time (and spent lots of time searching for parking), so I downloaded the data to see if I could analyze it.
The sign data
The data comes in two files. The first file is called locations, and gives each side of city block a number. For example block S-026538:
M,S-026538,WEST 135 STREET,CONVENT AVENUE,SAINT NICHOLAS TERRACE,S
is the south side of West 135th St between Convent Avenue and St Nicholas Terrace (the M at the start means Manhattan).
The second file, signs, lists each sign. For example, the block above has these 7 entries describing 3 signs and the start and end of the block:
M,S-026538,1,0000 , ,Curb Line M,S-026538,2,0016 , ,Property Line M,S-026538,3,0068 ,W ,NO PARKING ANYTIME (SINGLE ARROW)(SUPERSEDES R7-40 DON'TUSE R7-40)(SUPERSEDED BY SP-854CA) M,S-026538,4,0105 , ,NO PARKING (SANITATION BROOM SYMBOL) 11:30AM TO 1PM TUES & FRI <----> (SUPERSEDED BY PS-80B) M,S-026538,5,0256 , ,NO PARKING (SANITATION BROOM SYMBOL) 11:30AM TO 1PM TUES & FRI <----> (SUPERSEDED BY PS-80B) M,S-026538,6,0318 , ,Property Line M,S-026538,7,0331 , ,Curb Line
When is parking free?
As you can see, this data is difficult to analyze directly. It’s mostly text, and includes various noise and even comments. I wrote a Perl script to parse the sign descriptions and classify them and determine when parking is and isn’t allowed at each location.
The script looks at the list of signs and splits the block into regions where a certain restriction applies. New York doesn’t paint individual parking spots on the street – cars can park anywhere allowed, and they usually park as close together as the driver can manage. The data includes the position of each sign in feet from the start of the block, so the number of spots in each region can be estimated by dividing by average car length (e.g. 20ft).
As for times, the rules always repeat weekly. I divided the week into 2 × 24 × 7 half-hour periods, and each ½ hour is stored as free, metered, or no parking.
The script creates a PostgreSQL database that’s more convenient for further analysis. I used a dimensional model for the database. Here’s what the database looks like for the same block as above. If you’re not interested in the technical details, I recommend that you skip to the next section!
The fact table, parking_fact, ends up with two rows for this particular block:
parking=> SELECT * FROM parking_fact WHERE block_id = 38760 ORDER BY "Start position";
|block_id||regulation_id||Start position||Length||Parking spots||Weighted parking spots|
The first section is 52 ft long and thus contains 2 parking spots. The second section is 250 ft long and an estimated 12 cars could fit there.
The block_id column is a key into block_dimension, which describes where the block is:
parking=> SELECT * FROM block_dimension WHERE block_id = 38760;
|block_id||Borough||Street name||From street||To street||Side|
|38760||Manhattan||WEST 135 STREET||CONVENT AVENUE||SAINT NICHOLAS TERRACE||S|
The regulation_id links to regulation_dimension.
parking=> SELECT * FROM regulation_dimension WHERE regulation_id IN (7, 2);
|regulation_id||Hours free parking||Hours metered parking||Hours street cleaning||Hours no parking||Hours no standing||Hours no stopping||Hours bus stop||Angle parking||Special interest|
It shows that for the first section of parking, all 168 hours in a week are no parking. For the second section, 165 hours per week are free, while parking is prohibited for street cleaning for 3 hours.
Further detail is available in regulation_time_dimension:
parking=> SELECT * FROM regulation_time_dimension WHERE regulation_id = 2 ORDER BY "Day of week", "Half hour";
|regulation_id||Day of week||Half hour||Free parking||Metered parking||Street cleaning||No parking|
I’ve only shown a sample of the 336 rows (7 × 24 × 2), but notice that the Free parking column is 0 and Street cleaning is 1 for Half hours 23, 24, and 25 on Day of week 2, which means 11:30—13:00 on Tuesday.
Counting the spots
So how many parking spots are there? It’s now easy to answer that with some SQL:
parking=> SELECT SUM("Parking spots") FROM parking_fact;
Or break it down by borough:
parking=> SELECT "Borough", SUM("Parking spots") FROM parking_fact INNER JOIN block_dimension USING (block_id) INNER JOIN regulation_dimension USING (regulation_id) GROUP BY "Borough" ORDER BY "Borough";
Of course not all of those spots are available at all times. In fact, many are in no parking anytime zones. I added a Weighted parking spots measure that accounts for this by multiplying the number of spots by the fraction of time they are available:
parking=> SELECT "Borough", SUM("Parking spots") AS spots, SUM("Weighted parking spots") AS weighted FROM parking_fact INNER JOIN block_dimension USING (block_id) INNER JOIN regulation_dimension USING (regulation_id) GROUP BY "Borough" ORDER BY "Borough";
Here’s a more complex query that shows where you might have the best chance of finding free parking:
parking=> SELECT "Borough", "Street name", sum("Parking spots") spots FROM parking_fact INNER JOIN block_dimension USING (block_id) INNER JOIN regulation_dimension USING (regulation_id) WHERE "Hours free parking" > 150 GROUP BY "Borough", "Street name" ORDER BY spots DESC LIMIT 10;
|Brooklyn||EAST 2 STREET||2159|
|Brooklyn||EAST 7 STREET||2003|
|Brooklyn||EAST 18 STREET||1968|
|Brooklyn||EAST 19 STREET||1961|
Most of these roads are very long, as expected.
How does the number of available spots change over time? Using R, I plotted some graphs showing this. This graph shows how the number of spots varies over a week for the whole of New York City.
You can see how spots become metered during the day except on Sunday (pink). You can also see the pattern of street cleaning, with its double peaks on Monday, Tuesday, Thursday, and Friday (green).
It’s easier to see some of the patterns in this combined graph. This shows just Manhattan, the most central part of New York, instead of the whole city above.
You can see that the amount of free parking almost halves from its overnight stock to the peak of restrictions on weekday mornings.
The number of spots affected is actually higher than this graph shows, because as some spots become available after cleaning, others immediately become restricted. This graph shows what happens to each spot over the 48 hours of Monday and Tuesday. (24×7 no parking spots are excluded.)
You can see that most spots have restrictions at least once during that period.
Conclusions and ideas
I don’t know how much the city is already using this data - I didn’t find anything online. It could be really useful for planning changes to improve the difficult parking situation in New York. Here are a few ideas:
Most of the city’s stock of parking spots gives no income to the city. About 1 million spots are free parking at all times except during street cleaning. Is there some way for the city to get income from them without hurting residents? For example, would resident parking permits help?
About 70,000 of Manhattan’s 215,000 spots are classified as no parking. What’s the breakdown of reasons for the restriction, and given the great demand for parking there, could any be made available to the public or to uses like ZipCar?
100,000s of spots get cleaned each day. How many cars have to move, and how does the benefit of cleaning compare to the time spent and fuel used? In some neighborhoods, drivers sit in their cars during the cleaning restriction, often with the engine running for air conditioning or heat. Could it be done less frequently or without moving cars?
I made the source of the script and some queries and graphs available on GitHub, and the latest data files are on the NYC site.
Please send me a message if you do anything interesting using this code or make improvements!