Information-Pushed Selections for The place to Park in SF

Have you ever ever felt unsure parking in a shady space? Particularly, have you ever ever parked in San Francisco and puzzled, if I measured the common inverse sq. distance to each car incident recorded by the SFPD within the final 12 months, at what percentile would my present location fall?

In that case, we constructed an app for that. On this put up we’ll clarify our methodology and its implementation.

Parking in San Francisco

Car-related break-ins and thefts are notoriously widespread in San Francisco. Simply final week, objects value half 1,000,000 {dollars} had been stolen in a high-profile automobile housebreaking. There’s even a Twitter account monitoring incidents.

The San Francisco Police Division maintains an ongoing dataset of all incidents since January 1, 2018 (there’s one other one for 2003-2018).
The San Francisco Chronicle has created a nice map visualization from this to trace break-ins. We wished to make this information much more actionable, to assist asses the safety of parking in a selected location in real-time.

Therefore, the motivating query: if I’m trying to park in SF, how can I get a way of how protected my present spot is?

Defining a Threat Rating

After all, the danger of a parking spot could be measured in many alternative qualitative and quantitative methods. We selected a quantitative measure, admittedly fairly arbitrary, as the common inverse sq. of the gap between the parking location and each break-in location up to now 12 months.

This simply offers a numerical rating. We then consider this rating throughout a consultant pattern of parking spots throughout SF, and place the present parking spot at a percentile inside that pattern. The upper the rating, the nearer the spot is to historic incidents (inverse of distance), the upper the danger.

We determined to construct a cellular app for exhibiting how safe your parking spot is.

Now, we simply have to make use of the information to compute the danger rating percentile. For this process, we’ll load the SFPD information right into a Rockset assortment and question it upon a person clicking the button.

Loading the Information

To get began rapidly, we’ll merely obtain the information as a CSV and add the file into a brand new assortment.

Later, we are able to arrange a periodic job to ahead the dataset into the gathering through the API, in order that it all the time stays updated.

Filtering the Information

Let’s swap over to the question tab and check out writing a question to filter all the way down to the incidents we care about. There are a couple of situations we need to verify:

• Preliminary report. In keeping with the information documentation, information can’t be edited as soon as they’re filed, so some information are filed as “supplemental” to an present incident. We will filter these out by searching for the phrase “Preliminary” within the report sort description.

• Inside SF. The documentation additionally specifies that some incidents happen exterior SF, and that such incidents may have the worth “Out of SF” within the police district area.

• Final 12 months. The dataset offers a datetime area, which we are able to parse and guarantee is inside the final 12 months.

• Geolocation out there. We discover some rows are lacking the latitude and longitude fields, as a substitute having an empty string. We’ll merely ignore these information by filtering them out.

Placing all these situations collectively, we are able to prune down from 242,012 information on this dataset to only the 28,224 related car incidents, packaged up right into a WITH question.

Calculating a Threat Rating, One Spot

Now that we have now all car incidents within the final 12 months, let’s see if we are able to calculate the safety rating for San Francisco Metropolis Corridor, which has a latitude of 37.7793° N and longitude of 122.4193° W.

Utilizing some good previous math tips (radius instances angle in radians to get arc size, approximating arc size as straight-line distance, and Pythagorean theorem), we are able to compute the gap in miles to every previous incident:

We mixture these distances utilizing our method from above, and voila!

For our app, we are going to exchange the latitude/longitude of Metropolis Corridor with parameters coming from the person’s browser location.

Pattern of Parking Spots in SF

So we are able to calculate a danger rating—1.63 for Metropolis Corridor—however that’s meaningless until we are able to examine it to the opposite parking spots in SF. We have to discover a consultant set of all attainable parking spots in SF and compute the danger rating for every to get a distribution of danger scores.

Seems, the SFMTA has precisely what we want—area surveys are performed to depend the variety of on-street parking spots and their outcomes are revealed as an open dataset. We’ll add this into Rockset as nicely!

Let’s see what this dataset comprises:

For every avenue, let’s pull out the latitude/longitude values (simply the primary level, shut sufficient approximation), depend of spots, and a novel identifier (casting varieties as mandatory):

Calculating Threat Rating, Each Spot in SF

Now, let’s strive calculating a rating for every of those factors, similar to we did above for Metropolis Corridor:

And there we have now it! A parking danger rating for every avenue section in SF. It is a heavy question, so to lighten the load we’ve truly sampled 5% of every streets and incidents.

(Coming quickly to Rockset: geo-indexing—be careful for a weblog put up about that within the coming weeks!)

Let’s stash the outcomes of this question in one other assortment in order that we are able to use it to calculate percentiles. We first create a brand new empty assortment:

Now we run an INSERT INTO sf_risk_scores SELECT ... question, bumping as much as 10% sampling on each incidents and streets:

Rating Threat Rating as Percentile

Now let’s get a percentile for Metropolis Corridor in opposition to the pattern we’ve inserted into sf_risk_scores. We maintain our spot rating calculation as we had at first, however now additionally depend what p.c of our sampled parking spots are safer than the present spot.

Parking-Spot-Threat-Rating-as-a-Service

Now that we have now an arguably helpful question, let’s flip it into an app!

We’ll maintain it easy—we’ll create an AWS Lambda perform that can serve two forms of requests. On GET requests, it is going to serve an area index.html file, which serves because the UI. On POST requests, it is going to parse question params for lat and lon and go them on as parameters within the final question above. The lambda code seems to be like this:

import json
from botocore.vendored import requests
import os

ROCKSET_APIKEY = os.environ.get('ROCKSET_APIKEY')
QUERY_TEXT = """
WITH vehicle_incidents AS (
    SELECT
        *
    FROM
        sf_incidents TABLESAMPLE BERNOULLI(10)
    WHERE
        "Incident Subcategory" IN (
            'Motor Car Theft',
            'Motor Car Theft (Tried)',
            'Larceny - Auto Elements',
            'Theft From Car',
            'Larceny - From Car'
        )
        AND "Report Sort Description" LIKE '%Preliminary%'
        AND "Police District" <> 'Out of SF'
        AND PARSE_DATETIME('%Y/%m/%d %r', "Incident Datetime") > CURRENT_DATE() - INTERVAL 12 MONTH
        AND LENGTH("Latitude") > 0
        AND LENGTH("Longitude") > 0
),
spot_score AS (
    SELECT
        AVG(
            1 / (
                POW(
                    (vehicle_incidents."Latitude"::float - :lat) * (3.1415 / 180) * 3959,
                    2
                ) + POW(
                    (vehicle_incidents."Longitude"::float - :lon) * (3.1415 / 180) * 3959,
                    2
                )
            )
        ) as "Threat Rating"
    FROM
        vehicle_incidents
),
total_count AS (
    SELECT
        SUM("Depend") "Depend"
    FROM
        sf_risk_scores
),
safer_count AS (
    SELECT
        SUM(sf_risk_scores."Depend") "Depend"
    FROM
        sf_risk_scores,
        spot_score
    WHERE
        sf_risk_scores."Threat Rating" < spot_score."Threat Rating"
)
SELECT
    100.0 * safer_count."Depend" / total_count."Depend" "Percentile",
    spot_score."Threat Rating"
FROM
    safer_count, total_count, spot_score
"""

def lambda_handler(occasion, context):
    if occasion['httpMethod'] == 'GET':
        f = open('index.html', 'r')
        return {
            'statusCode': 200,
            'physique': f.learn(),
            'headers': {
                'Content material-Sort': 'textual content/html',
            }
        }
    elif occasion['httpMethod'] == 'POST':
        res = requests.put up(
            'https://api.rs2.usw2.rockset.com/v1/orgs/self/queries',
            headers={
                'Content material-Sort': 'software/json',
                'Authorization': 'ApiKey %s' % ROCKSET_APIKEY
            },
            information=json.dumps({
                'sql': {
                    'question': QUERY_TEXT,
                    'parameters': [
                        {
                            'name': 'lat',
                            'type': 'float',
                            'value': event['queryStringParameters']['lat']
                        },
                        {
                            'identify': 'lon',
                            'sort': 'float',
                            'worth': occasion['queryStringParameters']['lon']
                        }
                    ]
                }
            })).json()
        return {
            'statusCode': 200,
            'physique': json.dumps(res),
            'headers': {
                'Content material-Sort': 'software/json',
            }
        }
    else:
        return {
            'statusCode': 405,
            'physique': 'technique not allowed'
        }

For the client-side, we write a script to fetch the browser’s location after which name the backend:

perform getLocation() {
  doc.getElementById("location-button").model.show = "none";
  showMessage("fetching");
  if (navigator.geolocation) {
    navigator.geolocation.getCurrentPosition(handleLocation, perform (error) {
      showMessage("denied")
    });
  } else {
    showMessage("unsupported")
  }
}

perform handleLocation(place) {
  showMessage("querying");
  var lat = place.coords.latitude;
  var lon = place.coords.longitude;
  fetch(
    'https://aj8wl2pz30.execute-api.us-west-2.amazonaws.com/default/sf-parking?lat=" + lat + "&lon=' + lon,
    { technique: 'POST' }
  ).then(perform (response) {
    return response.json();
  }).then(perform (consequence) {
    setResult(consequence['results'][0]);
    showMessage("consequence");
    doc.getElementById("tile").model.justifyContent = "begin";
  });
}

perform setResult(consequence) {
  doc.getElementById('rating').textContent = parseFloat(consequence['Risk Score']).toFixed(3);
  doc.getElementById('percentile').textContent = parseFloat(consequence['Percentile']).toFixed(3);
  if (consequence['Percentile'] == 0) {
    doc.getElementById('zero').model.show = "block";
  }
}

perform showMessage(messageId) {
  var messages = doc.getElementsByClassName("message");
  for (var i = 0; i < messages.size; i++) {
    messages[i].model.show = "none";
  }
  doc.getElementById(messageId).model.show = "block";
}

To complete it off, we add API Gateway as a set off for our lambda and drop a Rockset API key into the atmosphere, which may all be accomplished within the AWS Console.

Conclusion

To summarize what we did right here:

• We took two pretty easy datasets—one for incidents reported by SPFD and one for parking spots reported by SFMTA—and loaded the information into Rockset.
• A number of iterations of SQL later, we had an API we may name to fetch a danger rating for a given geolocation.
• We wrote some easy code into an AWS Lambda to serve this as a cellular net app.

The one software program wanted was an internet browser (obtain the information, question in Rockset Console, and deploy in AWS Console), and all instructed this took lower than a day to construct, from thought to manufacturing. The supply code for the lambda is offered right here.