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