afcotroneo

from pymapd import connect
import osmnx as ox
import geopandas as gpd

afcotroneo

con = connect(user="username", 
              password="password", 
              host="my.host.com", 
              dbname="dbname", 
              protocol="http")
              
print('Connection Successful!')

afcotroneo

# Area of Interest Graph
G = ox.graph_from_place('Los Angeles, California', network_type='drive')

afcotroneo

# Map Preview Graph
ox.plot_graph(G)

afcotroneo

# Load Edges into GeoDataFrame
nodes, edges = ox.graph_to_gdfs(G)

# Drop Unwanted Columns
edges.drop(['oneway', 'lanes', 'maxspeed'], inplace=True, axis=1)

# Preview Data
edges

afcotroneo

# Check Coordinate Reference System
edges.crs

afcotroneo

CREATE TABLE la_streets (
  osm_id TEXT ENCODING DICT(32), 
  name TEXT ENCODING DICT(32),
  highway TEXT ENCODING DICT(32),
  length float,
  geometry GEOMETRY(LINESTRING, 4326) ENCODING COMPRESSED(32));

afcotroneo

# Load Streets into Existing Table
con.load_table_columnar('la_streets', edges)

afcotroneo

create table la_street_buffer as (
  select
    osm_id,
    st_buffer(geometry, 10) as omnisci_geo
  from
    la_streets
);

afcotroneo

create table la_street_collision_pre as (
	select 
		sum(number_killed) as number_killed, 
		sum(number_injured) as number_injured, 
		avg(party_count) as avg_party_count, 
		sum(severe_injury_count) as severe_injuries, 
		sum(pedestrian_collision) as ped_collisions, 
		sum(count_ped_killed) as ped_killed, 
		sum(count_ped_injured) as ped_injured, 
		sum(bike_collision) as bike_collisions,