omni-sjoin-enrich

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, 
		sum(count_bike_killed) as bike_killed, 
		sum(count_bike_injured) as bike_injured,
		sum(mc_collision) as mc_collisions,
		sum(count_mc_killed) as mc_killed,
		sum(count_bike_injured) as mc_injured,
		sum(tk_collision) as tk_collisions,
		sum(alcohol_inv) as alcohol_inv,
		b.osm_id 
	from la_collisions a, 
		la_street_buffer b 
	where st_contains(b.omnisci_geo,st_setsrid(st_point(a.point_x, a.point_y), 4326)) = 1
	group by b.osm_id
);

create table la_collision_blkgrps as (
	select 
		a.osm_id,
		a.number_killed, 
		a.number_injured, 
		a.avg_party_count, 
		a.severe_injuries, 
		a.ped_collisions, 
		a.ped_killed, 
		a.ped_injured, 
		a.bike_collisions, 
		a.bike_killed, 
		a.bike_injured,
		a.mc_collisions,
		a.mc_killed,
		a.mc_injured,
		a.tk_collisions,
		a.alcohol_inv,
		b.omnisci_geo
	from la_street_collision_pre a
	join la_street_buffer b
	on a.osm_id = b.osm_id
);