make_geojson.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "from shapely.geometry import Point\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT * from \"12cb3883-56f5-47de-afa5-3b1cf61b257b\" WHERE \"YEAR\" = '2019'\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get(\n",
    "    'https://data.boston.gov/api/3/action/datastore_search_sql?sql={}'.format(sql))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "j = r.json()['result']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(j['records'],\n",
    "                 columns=[x['id'] for x in j['fields']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(32000, 19)"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [],
   "source": [
    "# group by geometry\n",
    "df_counts = df.groupby(['Lat', 'Long']).size()\n",
    "df_counts = df_counts.reset_index()\n",
    "df_counts['geometry'] = df_counts.apply(lambda x: Point(float(x['Long']), float(x['Lat'])), axis=1)\n",
    "df_counts = df_counts[[0, 'geometry']]\n",
    "df_counts.columns = ['total_crashes', 'geometry']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "geo_df = gpd.GeoDataFrame(df_counts)\n",
    "geo_df.to_file('data/boston/crime_rollup.geojson', driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Location\n",
       "(42.27934055, -71.09130505)    1\n",
       "(42.28362830, -71.09349553)    4\n",
       "(42.30095488, -71.06537601)    1\n",
       "(42.30220359, -71.06786694)    1\n",
       "(42.30775053, -71.06971431)    1\n",
       "(42.31052866, -71.08671835)    3\n",
       "(42.32896475, -71.07250818)    5\n",
       "(42.33729692, -71.07919582)    9\n",
       "(42.37080805, -71.04292706)    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:crash-model]",
   "language": "python",
   "name": "conda-env-crash-model-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}