In [3]:

    

import os
import requests

def get_table_url(table_name, base_url=os.environ['NEWSROOMDB_URL']):
    return '{}table/json/{}'.format(os.environ['NEWSROOMDB_URL'], table_name)

def get_table_data(table_name):
    url = get_table_url(table_name)
    
    try:
        r = requests.get(url)
        return r.json()
    except:
        print("Request failed. Probably because the response is huge.  We should fix this.")
        return get_table_data(table_name)

shooting_victims = get_table_data('shootings')
print("Loaded {} shooting victims".format(len(shooting_victims)))
homicides = get_table_data('homicides')
print("Loaded {} homicides".format(len(homicides)))


    

    




Request failed. Probably because the response is huge.  We should fix this.
Request failed. Probably because the response is huge.  We should fix this.
Request failed. Probably because the response is huge.  We should fix this.
Request failed. Probably because the response is huge.  We should fix this.
Request failed. Probably because the response is huge.  We should fix this.
Loaded 12955 shooting victims
Loaded 1749 homicides

In [2]:

    

from shapely.geometry import shape

# Ohio Street on the north, Kinzie Street on the south, Leclaire Avenue on the west, and Lamon Avenue on the east
# I generated this GeoJSON using http://geojson.io/
boundary_json = {
    "type": "FeatureCollection",
    "features": [
        {
            "type": "Feature",
            "properties": {},
            "geometry": {
                "type": "Polygon",
                "coordinates": [
                    [
                        [
                            -87.75301694869995,
                            41.89130427991353
                        ],
                        [
                            -87.7481460571289,
                            41.891376160028166
                        ],
                        [
                            -87.74800658226013,
                            41.88790186196206
                        ],
                        [
                            -87.7528989315033,
                            41.887837965055716
                        ],
                        [
                            -87.75301694869995,
                            41.89130427991353
                        ]
                    ]
                ]
            }
        }
    ]
}

boundary = shape(boundary_json['features'][0]['geometry'])


    

In [7]:

    

from shapely.geometry import Point

def record_in_bounds(record, bounds, coordinate_field="Geocode Override"):
    coordinates = record['Geocode Override'][1:-1].split(',')
    if len(coordinates) != 2:
        return False
    point = Point(float(coordinates[1]), float(coordinates[0]))
    if bounds.contains(point):
        return True
    
    return False
    
def records_in_bounds(records, bounds):
    in_bounds = []
    for record in records:
        if record_in_bounds(record, bounds):
            in_bounds.append(record)
            
    return in_bounds

shooting_victims_within_bounds = records_in_bounds(shooting_victims, boundary)
homicides_within_bounds = records_in_bounds(homicides, boundary)


    

In [16]:

    

import pandas as pd

# Load records into Pandas data frames in case we need to do further analysis
shooting_victims_df = pd.DataFrame.from_records(shooting_victims_within_bounds)
homicides_df = pd.DataFrame.from_records(homicides_within_bounds)


    

In [18]:

    

# Output data to CSV to share with reporters
shooting_victims_df.to_csv("hubbard_playlot_park__shooting_victims.csv")
homicides_df.to_csv("hubbard_playlot_park__homicides.csv")


    

In [ ]: