Chicago Schools safe passage crimes aggregation

query.sql

select school_nam, schoolid, stuartlynn.cps_safe_passage_routes_buffered_100m.the_geom, date_part('hour', stuartlynn.crimes_2001_to_present_100_meters_from_safe_passage.date),
sum( CASE WHEN primary_type='PROSTITUTION'  THEN 1 ELSE 0 END) as prostitution_count,
sum( CASE WHEN primary_type='OTHER OFFENSE' THEN 1  ELSE 0 END) as other_count,
sum( CASE  WHEN primary_type='THEFT' THEN 1 ELSE 0 END) as theft_count,
sum( CASE  WHEN primary_type='LIQUOR LAW VIOLATION' THEN 1 ELSE 0 END) as liquor_law_violation_count,
sum( CASE  WHEN primary_type='NON-CRIMINAL' THEN 1 ELSE 0 END) as non_criminal_count,
sum( CASE  WHEN primary_type='BURGLARY' THEN 1 ELSE 0 END) as burglary_count,
sum( CASE  WHEN primary_type='PUBLIC PEACE VIOLATION' THEN 1 ELSE 0 END) as public_peace_violation_count,
sum( CASE  WHEN primary_type='RITUALISM' THEN 1 ELSE 0 END) as ritualism_count,
sum( CASE  WHEN primary_type='HOMICIDE' THEN 1 ELSE 0 END) as homicide_count,
sum( CASE  WHEN primary_type='BATTERY' THEN 1 ELSE 0 END) as battery_count,
sum( CASE  WHEN primary_type='ARSON' THEN 1 ELSE 0 END) as arson_count,
sum( CASE  WHEN primary_type='NARCOTICS' THEN 1 ELSE 0 END) as narcotics_count,
sum( CASE  WHEN primary_type='OFFENSE INVOLVING CHILDREN' THEN 1 ELSE 0 END) as offense_involving_children_count,
sum( CASE  WHEN primary_type='GAMBLING' THEN 1 ELSE 0 END) as gambling_count,
sum( CASE  WHEN primary_type='HUMAN TRAFFICKING' THEN 1 ELSE 0 END) as human_trafficking_count,
sum( CASE  WHEN primary_type='ASSAULT' THEN 1 ELSE 0 END) as assult_count,
sum( CASE  WHEN primary_type='MOTOR VEHICLE THEFT' THEN 1 ELSE 0 END ) as motor_vehicle_theft_count,
sum( CASE  WHEN primary_type='INTIMIDATION' THEN 1 ELSE 0 END) as intinidation_count,
sum( CASE  WHEN primary_type='CONCEALED CARRY LICENSE VIOLATION' THEN 1 ELSE 0 END) as concealed_carry_license_violation_count,
sum( CASE  WHEN primary_type='PUBLIC INDECENCY' THEN 1 ELSE 0 END) as public_indecency_count,
sum( CASE  WHEN primary_type='ROBBERY' THEN 1 ELSE 0 END) as robbery_count,
sum( CASE  WHEN primary_type='WEAPONS VIOLATION' THEN 1 ELSE 0 END) as weapons_violation_count,
sum( CASE  WHEN primary_type='KIDNAPPING' THEN 1 ELSE 0 END) as kidnapping_count,
sum( CASE  WHEN primary_type='INTERFERENCE WITH PUBLIC OFFICER' THEN 1 ELSE 0 END) as interference_with_public_officer_count,
sum( CASE  WHEN primary_type='NON-CRIMINAL (SUBJECT SPECIFIED)' THEN 1 ELSE 0 END) as non_criminal_subject_specified_count,
sum( CASE  WHEN primary_type='CRIM SEXUAL ASSAULT' THEN 1 ELSE 0 END) as crim_sexual_assult_count,
sum( CASE  WHEN primary_type='CRIMINAL TRESPASS' THEN 1 ELSE 0 END) as criminal_trespass_count,
sum( CASE  WHEN primary_type='NON - CRIMINAL' THEN 1 ELSE 0 END) as non_criminal2_count,
sum( CASE  WHEN primary_type='DOMESTIC VIOLENCE' THEN 1 ELSE 0 END) as domestic_violence_count,
sum( CASE  WHEN primary_type='CRIMINAL DAMAGE' THEN 1 ELSE 0 END) as criminal_damage_count,
sum( CASE  WHEN primary_type='SEX OFFENSE' THEN 1 ELSE 0 END) as sex_offence_count,
sum( CASE  WHEN primary_type='STALKING' THEN 1 ELSE 0 END) as salking_count,
sum( CASE  WHEN primary_type='OTHER NARCOTIC VIOLATION' THEN 1 ELSE 0 END) as other_narcotic_violation_count,
sum( CASE  WHEN primary_type='DECEPTIVE PRACTICE' THEN 1 ELSE 0 END) as deceptive_practice_count,
sum( CASE  WHEN primary_type='OBSCENITY' THEN 1 ELSE 0 END) as obscennity_count
FROM stuartlynn.cps_safe_passage_routes_buffered_100m, stuartlynn.crimes_2001_to_present_100_meters_from_safe_passage
where ST_WITHIN( stuartlynn.crimes_2001_to_present_100_meters_from_safe_passage.the_geom , ST_BUFFER(stuartlynn.cps_safe_passage_routes_buffered_100m.the_geom::geography, 30)::geometry)
group by schoolid, school_nam, stuartlynn.cps_safe_passage_routes_buffered_100m.the_geom, date_part('hour', stuartlynn.crimes_2001_to_present_100_meters_from_safe_passage.date)