Leon's Weblog

April 9, 2013

Storing parking restriction information in a SQL Server

Filed under: Software Dev — Leon @ 11:32 am

Recently, I was working on a project that involved storing parking restriction information in a database. Here’s how I did it.

Goal

  • Efficiently store parking restriction details (e.g. No Parking Tues & Fri from 9:30-11:00) associated with work locations.
  • Ability to search the data by any combination of parking days and times.



Design
I though I would start by getting some inspiration from the NYC DOT parking restriction database which is available free for download. Unfortunately, this design was disappointing. Essentially what is provided is over 700K entries of parking sign numbers, the actual text description of the parking restriction, and the location (“on”, “from”, and “to” streets). While this lets you search for the regulation using the location information, getting a list of locations based on the time is not possible. Also, the storage requirement for this data is over 100MB just for the text while the actual information should take up an order of magnitude less space.

Instead of storing strings of street sign text, I decided to use a bit mask. Seven bits are needed for parking days so the basic integer field on the SQL Server will work just fine. The “from” and “to” times can be stored in separate date/time fields. This can obviously be fine tuned to smaller data types based on storage requirements (e.g. the SQL Server tinyint field contains 8 bits while the integer field contains 16)

Implementation
Since the bit mask will be used quite often it will be convenient to store the list of codes defining each bit position in a table as follows:

Code Description Note
1 Sun Least Significant Bit
2 Mon
4 Tue
8 Wed
16 Thu
32 Fri
64 Sat Most Significant Bit

It is important to note here that the codes are just multiples of 2. We could store the bit position instead but then we would have to calculate 2 to the power of that number quite often. Also, deciding which is the most and least significant bit is not important but it is critical to be consistent with the choice. In this case the days Sun, Mon, Tues etc… are bits 1, 2, and 3 when reading the bit string right to left.

Data Entry
There are many ways to populate this table of parking restrictions. I wont get into much details here but will note some caveats. The equivalent of “Any Day” parking is having all the bits set to one (i.e. integer value 127). Likewise, “Any Day Except Sun” has the least significant bit set to 0 for an integer value of 126.

Search
Building a web interface to search the data by parking restriction is trivial. We can use the table above to generate a list of check boxes whose values are the codes from the table and the text are the descriptions. The values of the selected check-boxes will get added up to create another bit string. I chose to make the time selection control using an drop-down list of times in half-hour increments. It also helps to have a little validation to ensure the user selected “from” time is earlier than the “to” time. From here, the actual search on the SQL Server depends on your requirements; however, it is very likely that a few bitwise operators will be needed. In my case, I wanted to allow the user to find jobs which matched any of selected parking restriction days so I used the bitwise AND operator to find records where [ParkingBitMaskField] & SearchBitMask > 0.

Since the user will not want to see the parking bit mask as a search result, a bit more code is needed to create a human readable output string. Here is one way to do it (in VB.NET).

Public Function ConvertParkingString(ByVal ParkingDays As Int16, ByVal ParkingTimeFrom As TimeSpan, _
                                     ByVal ParkingTimeTo As TimeSpan) As String
    Dim SB As New StringBuilder()

    If ParkingDays = 0 Then
        SB.Append("N/A")
    ElseIf ParkingDays = 127 Then 'any day
        SB.Append("Any Day")
    ElseIf ParkingDays = 126 Then 'except sunday
        SB.Append("Except Sun")
    Else
        Dim ParkingDaysLookupArray As String() = {"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"}
        Dim ParkingDaysArray As Char() = Convert.ToString(ParkingDays, 2).PadLeft(7, "0"c).ToCharArray()
        For i As Int16 = 0 To 6
            If ParkingDaysArray(6 - i) = "1" Then SB.Append(ParkingDaysLookupArray(i) & " ")
        Next
    End If

    If ParkingTimeFrom.Ticks > 0 Or ParkingTimeTo.Ticks > 0 Then
        SB.Append(" ")
        SB.Append(ParkingTimeFrom.ToString("hh\:mm"))
        SB.Append(" - ")
        SB.Append(ParkingTimeTo.ToString("hh\:mm"))
    End If

    Return SB.ToString
End Function

The code first handles a few special cases (e.g. any day parking) and then builds a sting of days that were selected in the bit string. The ParkingDays integer is converted to character array and forced to be 7 positions long. Then the array is parsed in reverse to check for which bits are selected. This is a good start to make a readable implementation of this function but there is room for optimization. For example, instead of converting the bit mask to a character array we can use a bit of math and accomplish the same task by diving the ParkingDays variable by 2 in a loop and checking the remainder while ParkingDays > 0.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment