Query San Diego Traffic Collisions

This guide will show several queries that analyze traffic collisions in San Diego.

The Dataset

The dataset that we will be working with provides us with data from traffic collisions that occur San Diego County. This dataset is updated every 24 hours. See below an example row.

{
    "address_name_intersecting": " ",
    "address_number_primary": "500",
    "address_pd_intersecting": " ",
    "address_pd_primary": " ",
    "address_road_primary": "ROSECRANS",
    "address_sfx_intersecting": " ",
    "address_sfx_primary": "STREET",
    "charge_desc": "VIOLATION OF BASIC SPEED LAW SPEED UNSAFE FOR CONDITIONS",
    "date_time": "2018-01-01 01:00:00",
    "hit_run_lvl": "MISDEMEANOR",
    "injured": "0",
    "killed": "0",
    "police_beat": "616",
    "report_id": "180310",
    "violation_section": "22350",
    "violation_type": "VC"
  }

Getting Sample Rows

This query the first 5 rows from the table.

select * from  vay.`san-diego/traffic-collisions/pd-collisions-datasd-v1.csv` limit 5
            

[
  {
    "address_name_intersecting": " ",
    "address_number_primary": "6500",
    "address_pd_intersecting": " ",
    "address_pd_primary": " ",
    "address_road_primary": "EL CAMINO DE TEATRO",
    "address_sfx_intersecting": " ",
    "address_sfx_primary": " ",
    "charge_desc": "VIOLATION OF BASIC SPEED LAW SPEED UNSAFE FOR CONDITIONS",
    "date_time": "2018-01-01 00:30:00",
    "hit_run_lvl": "",
    "injured": "0",
    "killed": "0",
    "police_beat": "124",
    "report_id": "180282",
    "violation_section": "22350",
    "violation_type": "VC"
  },
  {
    "address_name_intersecting": " ",
    "address_number_primary": "500",
    "address_pd_intersecting": " ",
    "address_pd_primary": " ",
    "address_road_primary": "ROSECRANS",
    "address_sfx_intersecting": " ",
    "address_sfx_primary": "STREET",
    "charge_desc": "VIOLATION OF BASIC SPEED LAW SPEED UNSAFE FOR CONDITIONS",
    "date_time": "2018-01-01 01:00:00",
    "hit_run_lvl": "MISDEMEANOR",
    "injured": "0",
    "killed": "0",
    "police_beat": "616",
    "report_id": "180310",
    "violation_section": "22350",
    "violation_type": "VC"
  },
  {
    "address_name_intersecting": "18TH",
    "address_number_primary": "0",
    "address_pd_intersecting": " ",
    "address_pd_primary": " ",
    "address_road_primary": "PALM",
    "address_sfx_intersecting": "STREET",
    "address_sfx_primary": "AVENUE",
    "charge_desc": "PEDESTRIANS OUTSIDE CROSSWALKS",
    "date_time": "2018-01-01 01:26:00",
    "hit_run_lvl": "",
    "injured": "0",
    "killed": "1",
    "police_beat": "721",
    "report_id": "180573",
    "violation_section": "21954A",
    "violation_type": "VC"
  },
  {
    "address_name_intersecting": " ",
    "address_number_primary": "3100",
    "address_pd_intersecting": " ",
    "address_pd_primary": " ",
    "address_road_primary": "LOGAN",
    "address_sfx_intersecting": " ",
    "address_sfx_primary": "AVENUE",
    "charge_desc": "STOP REQUIREMENTS STATE STOP LINE",
    "date_time": "2018-01-01 02:05:00",
    "hit_run_lvl": "MISDEMEANOR",
    "injured": "0",
    "killed": "0",
    "police_beat": "512",
    "report_id": "180317",
    "violation_section": "22450(A)",
    "violation_type": "VC"
  },
  {
    "address_name_intersecting": " ",
    "address_number_primary": "6200",
    "address_pd_intersecting": " ",
    "address_pd_primary": " ",
    "address_road_primary": "DIVISION",
    "address_sfx_intersecting": " ",
    "address_sfx_primary": "STREET",
    "charge_desc": "TURNING MOVEMENTS AND REQUIRED SIGNALS",
    "date_time": "2018-01-01 02:26:00",
    "hit_run_lvl": "MISDEMEANOR",
    "injured": "0",
    "killed": "0",
    "police_beat": "439",
    "report_id": "180319",
    "violation_section": "22107",
    "violation_type": "VC"
  }
]
            

Total collisions

This query gets the total number of collisions that happened this year.

select
  count(
    case year(date_time) when year(now()) then 1 else null end
  ) as total
from
  vay.`san-diego/traffic-collisions/pd-collisions-datasd-v1.csv`


[
  {
    "total": "621"
  }
]
            

Kill ratio

This query shows us the ratio of number of car collisions that had a member killed, compared ones that had a member injured, per year.

select
  year(date_time) as `year`,
  (
    count(
      case injured when 0 then null else 1 end
    )
    /
    count(
      case killed when 0 then null else 1 end
    )
  ) as kill_ratio
from
  vay.`san-diego/traffic-collisions/pd-collisions-datasd-v1.csv`
group by
  `year`
            

[
  {
    "kill_ratio": "72",
    "year": "2018"
  },
  {
    "kill_ratio": "92",
    "year": "2019"
  },
  {
    "kill_ratio": "285",
    "year": "2020"
  }
]
            

Chance of injury and death

This query shows us the chance of injury and death per traffic collision. As you can see, the fatality rate has been declining over time.

select
  year(date_time) as `year`,
  (
    to_number(count(
      case killed when 0 then null else 1 end
    ), "###.###")
    /
    count(*)
  ) as kill_ratio,
   (
    to_number(count(
      case injured when 0 then null else 1 end
    ), "###.###")
    /
    count(*)
  ) as injured_ratio
from
  vay.`san-diego/traffic-collisions/pd-collisions-datasd-v1.csv`
group by
  `year`
            

[
  {
    "injured_ratio": "0.4481122551988694",
    "kill_ratio": "0.006157884110640016",
    "year": "2018"
  },
  {
    "injured_ratio": "0.4416989183288677",
    "kill_ratio": "0.004763322417386127",
    "year": "2019"
  },
  {
    "injured_ratio": "0.45893719806763283",
    "kill_ratio": "0.001610305958132045",
    "year": "2020"
  }
]
            
vay logo

© Copyright 2019 Vay Technologies LLC.
All rights reserved.