agude / SWITRS-to-SQLite

Python script for converting California's Statewide Integrated Traffic Records System (SWITRS) reports to SQLite.
https://alexgude.com/blog/switrs-to-sqlite/
Other
11 stars 3 forks source link

What are `primary_ramp` and `secondary_ramp`? #11

Open agude opened 3 years ago

agude commented 3 years ago

The columns primary_ramp and secondary_ramp don't match everything I would expect from the codebook: https://tims.berkeley.edu/help/SWITRS.php

SELECT primary_ramp, secondary_ramp, count(1) as total
FROM collisions
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500;

Yields:

primary_ramp secondary_ramp total
0 5352014
1 TO 60595
2 FR 25290
3 SF 8424
4 NF 8168
5 NO 5380
6 EF 5345
7 SO 5231
8 WF 4971
9 EO 3656
10 WO 3255
11 TO 2014
12 SF 1357
13 NF 1237
14 TR 994
15 EF 864
16 NO 774
17 WF 761
18 FR 732
19 SO 643
20 WO 472
21 EO 471
22 CN 300
23 TR 240
24 CO 193
25 CN 100
26 TO TO 86
27 CO 73
28 TO TR 71
29 TO SF 43
30 TO NF 41
31 TO NO 34
32 TO WF 29
33 TO CN 24
34 TO SO 23
35 TO FR 22
36 TO EF 20
37 TO EO 20
38 FR TO 19
39 FR SO 17
40 FR EO 16
41 TO WO 16
42 FR FR 15
43 FR NO 15
44 FR NF 10
45 FR SF 9
46 FR WO 9
47 FR WF 8
48 WO FR 8
49 NO FR 7
50 NO NO 6
51 NO TO 5
52 SF TO 5
53 TO CO 5
54 WO WO 5
55 NF TO 4
56 SO FR 4
57 EF TO 3
58 EO FR 3
59 FR TR 3
60 NF NF 3
61 NF SF 3
62 NO SO 3
63 SF SF 3
64 SF SO 3
65 TR TR 3
66 CN TO 2
67 EO EF 2
68 EO EO 2
69 FR CN 2
70 FR EF 2
71 SF FR 2
72 SF NF 2
73 SO SO 2
74 SO TO 2
75 TR TO 2
76 0 1
77 CN WF 1
78 CO SO 1
79 EF FR 1
80 EF WF 1
81 EO TO 1
82 EO WO 1
83 NF NO 1
84 NO EO 1
85 NO SF 1
86 SF NO 1
87 SF WF 1
88 SO EF 1
89 SO NF 1
90 SO NO 1
91 SO SF 1
92 TR CN 1
93 TR EO 1
94 TR FR 1
95 TR NF 1
96 TR NO 1
97 TR SF 1
98 TR SO 1
99 WF SO 1
100 WO EF 1
101 WO EO 1
102 WO SF 1
103 WO SO 1
104 WO TO 1
agude commented 3 years ago

I think its:

{N,S,E,W}X is direction, and X{O,F} is ON,OFF-ramp.

I think TO is "To..." and FR is "From...". I don't know what CO and CN are, possible "Collector" and "Connector"? No idea what TR is...

Here is just primary_ramp:

primary_ramp total
0 5399803
1 TO 61029
2 FR 25415
3 SF 1374
4 NF 1248
5 TR 1006
6 EF 869
7 NO 797
8 WF 762
9 SO 655
10 WO 490
11 EO 480
12 CO 194
13 CN 103
14 0 1
agude commented 3 years ago

This (https://iswitrs.chp.ca.gov/Reports/jsp/samples/RawData_template.pdf) says the values are:

NO: NB On Ramp
NF: NB Off Ramp
SO: SB On Ramp
SF: SB Off Ramp
EO: EB On Ramp
EF: EB Off Ramp
WO: WB On Ramp
WF: WB Off Ramp
To
From
Transition
Collector
Connector
blank 

So I'd guess TR is "Transition".