Vision Zero Project - San Jose, CA¶

SQL - Azure Data Studio¶

PostgresSQL queried datasets to be used in Tableau visualization¶

  • (Panel) Crash details for map
  • (Panel) Corridor Specific safety details
  • (Panel) Top 60 Safety Corridors with rolling total
In [1]:
-- Count total rows in crash locations table

SELECT count(*)
from vision_zero_crash
WHERE "YEAR" >= 2011
(1 row(s) affected)
Total execution time: 00:00:10.288
Out[1]:
count
64188
In [19]:
-- (Panel) Crash details for map

-- reduce violations field to single variable
create or replace function violations_reducer(a text) RETURNS text AS $$
BEGIN
    IF  position(',' in a) > 0 then
        DECLARE
        v text;
        arr text[] = string_to_array(a,',');
        BEGIN
            FOREACH v IN ARRAY arr 
            LOOP
                IF v != 'Unknown' then 
                    return trim(leading ' ' from v);
                END IF;
            END LOOP;
        END;
    ELSE
      return a;
    END IF;
END; 
$$ language plpgsql;


SELECT 
to_date("CRASHDATETIME",'YYYY/MM/DD') as "CRASHDATE",
violations_reducer("VEHICLEVIOLATIONCODEDESCRIPTION") as "VIOLATION",
(CASE
WHEN SPLIT_PART("AGERANGE", ',', 1) != 'NULL' then SPLIT_PART("AGERANGE", ',', 1) 
else '30-49' end) as "AGE",
(CASE  
WHEN "LIGHTING" = 'Dusk - Dawn' then 'Dusk/Dawn'
WHEN "LIGHTING" LIKE 'Dark%' then 'Dark'
else 'Daylight' end) as "LIGHTING",
(CASE  
WHEN "VEHICLEPARTYCATEGORY" LIKE '%Pedestrian%' then 'Pedestrian'
WHEN "VEHICLEPARTYCATEGORY" LIKE '%Bicycle%' then 'Bicycle'
else 'Vehicle' end) as "INVOLVING",
"WEATHER",
"DAYOFWEEKNAME",  
"NARRATIVE", 
"VEHICLEINVOLVEDWITH", 
"COLLISIONTYPE",
"MINORINJURIES", 
"MODERATEINJURIES", 
"SEVEREINJURIES",
"FATALINJURIES", 
"ROADWAYCONDITION", 
"ROADWAYSURFACE",
"INTASTREETNAME", 
"INTBSTREETNAME", 
"INTERSECTIONNUMBERINT", 
"PROXIMITYTOINTERSECTION",
"LATITUDE", "LONGITUDE"
FROM vision_zero_crash 
WHERE "YEAR" >= 2011
ORDER by "CRASHDATETIME"::date
Commands completed successfully
(64188 row(s) affected)
Displaying Top 10 rows.
Total execution time: 00:00:11.086
Out[19]:
CRASHDATEVIOLATIONAGELIGHTINGINVOLVINGWEATHERDAYOFWEEKNAMENARRATIVEVEHICLEINVOLVEDWITHCOLLISIONTYPEMINORINJURIESMODERATEINJURIESSEVEREINJURIESFATALINJURIESROADWAYCONDITIONROADWAYSURFACEINTASTREETNAMEINTBSTREETNAMEINTERSECTIONNUMBERINTPROXIMITYTOINTERSECTIONLATITUDELONGITUDE
2011-01-01Unsafe U-Turn16-29DarkVehicleClearSaturday29 year-old female driver vs unspecified driverOther VehicleRear End0000No Unusual ConditionsDryALMA AVPALM ST4168Non-Related37.3115987-121.8808112
2011-01-01Speeding30-49DarkVehicleRainSaturdayUnspecified driver vs parked carParked VehicleOther0000No Unusual ConditionsWetMANZANITA DRSARATOGA AV87460Non-Related37.31164303-121.97861969
2011-01-01Speeding16-29DaylightVehicleCloudySaturday21 year-old female driver vs objectFixed ObjectOther0100No Unusual ConditionsWetBLOSSOM HILL RD/DWCOTTLE RD42668Non-Related37.25377914-121.8031774
2011-01-01Speeding30-49DaylightVehicleRainSaturdayUnspecified driver vs objectFixed ObjectOther0000No Unusual ConditionsWetDELMAS AV/SHEPHERD AVWILLOW ST50214Non-Related37.31217342-121.8938377
2011-01-01Speeding30-49Dusk/DawnVehicleCloudySaturday40 year-old male drunk truck driver vs 29 year-old male driverOther VehicleBroadside0000No Unusual ConditionsWetBAYSHORE FRJULIAN ST14960Non-Related37.35317711-121.8672439
2011-01-01Unsafe Turn Movement16-29DaylightVehicleRainSaturday28 year-old male drunk driver vs objectFixed ObjectOther0100No Unusual ConditionsWetCAPITOL EXQUIMBY RD32922Non-Related37.3238552-121.8084406
2011-01-01Unsafe Turn Movement30-49DarkVehicleCloudySaturdayUnspecified driver vs 2 parked carsParked VehicleOther0000No Unusual ConditionsDryESTEBAN WYSOLANO DR57530Non-Related37.23211429-121.7981208
2011-01-01Unknown65 plusDaylightVehicleClearSaturday84 year-old male driver vs unspecified driverOther VehicleRear End0000No Unusual ConditionsDryKIELY BLSARATOGA AV79188Non-Related37.32015115-121.9724316
2011-01-01Unknown16-29DarkPedestrianRainSaturday44 year-old male driver vs 21 year-old male pedestrian with serious injuryPedestrianOther0010No Unusual ConditionsWetELEVENTH STSAN CARLOS ST55813Non-Related37.33649044-121.8764777
2011-01-01Speeding30-49Dusk/DawnVehicleRainSaturdayUnspecified tipsy driver vs 2 parked carsParked VehicleOther0000No Unusual ConditionsWetMENKER AVPARKMOOR AV90474Non-Related37.31720424-121.9216311
In [4]:
-- (Panel) Corridor Specific safety details

ALTER TABLE safety_corridors 
ADD COLUMN IF NOT EXISTS "STREETNAME" VARCHAR;

UPDATE safety_corridors
SET "STREETNAME" = UPPER("SAFETYPRIO");

UPDATE safety_corridors 
SET "STREETNAME" = REPLACE("STREETNAME", 'AVE', 'AV');

UPDATE safety_corridors 
SET "STREETNAME" = REPLACE("STREETNAME", 'EXPRESSWAY', 'EX');

UPDATE safety_corridors 
SET "STREETNAME" = REPLACE("STREETNAME", 'ROAD', 'RD');

UPDATE safety_corridors 
SET "STREETNAME" = REPLACE("STREETNAME", 'STREET', 'ST');
    
UPDATE safety_corridors 
SET "STREETNAME" = REPLACE("STREETNAME", 'LANE', 'LN');

-- SELECT *
-- FROM safety_corridors
-- ORDER BY 1;
NOTICE: column "STREETNAME" of relation "safety_corridors" already exists, skipping
Commands completed successfully
Commands completed successfully
Commands completed successfully
Commands completed successfully
Commands completed successfully
Commands completed successfully
Commands completed successfully
Total execution time: 00:00:00.482
In [3]:
-- (Panel) Top 60 Safety Corridors \w moving average and rolling total

BEGIN;

CREATE TEMPORARY TABLE Astreet ON COMMIT DROP
as SELECT 
SUM("MINORINJURIES") as minorA, 
SUM("MODERATEINJURIES") as moderateA, 
SUM("SEVEREINJURIES") as severeA,
SUM("FATALINJURIES") as fatalA, 
"INTASTREETNAME",
"YEAR"
FROM vision_zero_crash 
WHERE "YEAR" >= 2011
GROUP BY "INTASTREETNAME", "YEAR";


CREATE TEMPORARY TABLE Bstreet ON COMMIT DROP
as SELECT 
SUM("MINORINJURIES") as minorB, 
SUM("MODERATEINJURIES") as moderateB, 
SUM("SEVEREINJURIES") as severeB,
SUM("FATALINJURIES") as fatalB, 
"INTBSTREETNAME",
"YEAR"
FROM vision_zero_crash 
WHERE "YEAR" >= 2011
GROUP BY "INTBSTREETNAME", "YEAR";


CREATE TEMPORARY TABLE crashes ON COMMIT DROP
as SELECT 
CASE WHEN bstreet."INTBSTREETNAME" IS NULL THEN astreet."INTASTREETNAME" ELSE bstreet."INTBSTREETNAME" END AS "STREETNAME",
(coalesce(astreet."YEAR", bstreet."YEAR")) as "YEAR",
(coalesce(astreet.minorA, 0) + coalesce(bstreet.minorB, 0)) as "MINORINJURIES",
(coalesce(astreet.moderateA, 0) + coalesce(bstreet.moderateB, 0)) as "MODERATEINJURIES",
(coalesce(astreet.severeA, 0) + coalesce(bstreet.severeB, 0)) as "SEVEREINJURIES",
(coalesce(astreet.fatalA, 0) + coalesce(bstreet.fatalB, 0)) as "FATALINJURIES",
(coalesce(astreet.minorA, 0) + coalesce(bstreet.minorB, 0) + coalesce(astreet.moderateA, 0) + coalesce(bstreet.moderateB, 0)) as "MMI",
(coalesce(astreet.fatalA, 0) + coalesce(bstreet.fatalB, 0) + coalesce(astreet.severeA, 0) + coalesce(bstreet.severeB, 0)) as "KSI"
FROM Astreet astreet
full outer join Bstreet bstreet
ON bstreet."INTBSTREETNAME" = astreet."INTASTREETNAME"
and bstreet."YEAR" = astreet."YEAR"
ORDER BY "STREETNAME", "YEAR";


CREATE TEMPORARY TABLE topstreets ON COMMIT DROP
as SELECT 
(CASE  
WHEN "STREETNAME" in (
'MONTEREY RD', 
'ALUM ROCK AV',
'CAPITOL EX',
'BLOSSOM HILL RD',
'JACKSON AV',
'MCKEE RD',
'SENTER RD',
'TULLY RD',
'WHITE RD',
'ALMADEN EX',
'MCLAUGHLIN AV',
'BRANHAM LN',
'KING RD',
'STORY RD',
'SANTA CLARA ST',
'SARATOGA AV',
'FRUITDALE AV',
'HILLSDALE AV',
'FIRST ST') then 'True' 
else 'False' end) as "PRIORITY",
SUM("KSI") as "KSI", "STREETNAME"
FROM crashes
GROUP BY "STREETNAME"
ORDER BY "KSI" DESC
LIMIT 60;

CREATE TEMPORARY TABLE safetycorridors ON COMMIT DROP
as SELECT *
from topstreets
where "KSI" > 30 or "PRIORITY" = 'True';


SELECT *,
SUM("MINORINJURIES") OVER(PARTITION BY "STREETNAME" ORDER BY "STREETNAME", "YEAR") AS "ROLL_TOT_MIN",
SUM("MODERATEINJURIES") OVER(PARTITION BY "STREETNAME" ORDER BY "STREETNAME", "YEAR") AS "ROLL_TOT_MOD",
SUM("SEVEREINJURIES") OVER(PARTITION BY "STREETNAME" ORDER BY "STREETNAME", "YEAR") AS "ROLL_TOT_SEV",
SUM("FATALINJURIES") OVER(PARTITION BY "STREETNAME" ORDER BY "STREETNAME", "YEAR") AS "ROLL_TOT_FATAL",
SUM("MMI") OVER(PARTITION BY "STREETNAME" ORDER BY "STREETNAME", "YEAR") AS "ROLL_TOT_MMI",
SUM("KSI") OVER(PARTITION BY "STREETNAME" ORDER BY "STREETNAME", "YEAR") AS "ROLL_TOT_KSI"
FROM crashes
WHERE "STREETNAME" IN (select "STREETNAME" from safetycorridors)
ORDER BY "STREETNAME", "YEAR";

COMMIT;
Commands completed successfully
Commands completed successfully
Commands completed successfully
Commands completed successfully
Commands completed successfully
Commands completed successfully
(360 row(s) affected)
Commands completed successfully
Total execution time: 00:00:02.372
Out[3]:
STREETNAMEYEARMINORINJURIESMODERATEINJURIESSEVEREINJURIESFATALINJURIESMMIKSIROLL_TOT_MINROLL_TOT_MODROLL_TOT_SEVROLL_TOT_FATALROLL_TOT_MMIROLL_TOT_KSI
ALMADEN EX2011761600920761600920
ALMADEN EX201259197278913535721709
ALMADEN EX2013397324651744210421614
ALMADEN EX20143022545292046415826823
ALMADEN EX201522122434622676171230229
ALMADEN EX201651104061427786211236333
ALMADEN EX20173291141230995221340435
ALMADEN EX201841840494350103261345339
ALMADEN EX2019262552517376128311550446
ALMADEN EX2020311443457407142351854953
ALMADEN EX2021371240494444154391859857
ALMADEN EX2022231054339467164442263166
ALUM ROCK AV2011491660656491660656