ZAMEZ = James Bursa online
Exploring
Construction
Articles

Articles

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.

Street cleaning sign in New York

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 database

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
38760 7 16 52 2 0
38760 2 68 250 12 11.7857

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
7 0 0 0 168 0 0 0 f
2 165 0 3 0 0 0 0 f

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
2 0 0 1 0 0 0
2 0 1 1 0 0 0
2 0 2 1 0 0 0
2 0 3 1 0 0 0
...
2 2 21 1 0 0 0
2 2 22 1 0 0 0
2 2 23 0 0 1 0
2 2 24 0 0 1 0
2 2 25 0 0 1 0
2 2 26 1 0 0 0
2 2 27 1 0 0 0
...

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;
sum
1593276

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";
Borough sum
Bronx 241141
Brooklyn 623667
Manhattan 215005
Queens 424966
Staten 88497

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";
Borough spots weighted
Bronx 241141 184997
Brooklyn 623667 527227
Manhattan 215005 131064
Queens 424966 297766
Staten 88497 19123.1

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;
Borough Street name spots
Brooklyn BEDFORD AVENUE 2654
Manhattan RIVERSIDE DRIVE 2647
Brooklyn EAST 2 STREET 2159
Brooklyn OCEAN AVENUE 2122
Brooklyn PACIFIC STREET 2111
Brooklyn EAST 7 STREET 2003
Queens 34 AVENUE 1970
Brooklyn EAST 18 STREET 1968
Brooklyn EAST 19 STREET 1961
Brooklyn LINDEN BOULEVARD 1910

Most of these roads are very long, as expected.

Digging deeper

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:

Loading zone sign in New York

Links

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!