Thursday, October 30, 2014

Three ballotcounted dispositions mapped per precinct for two elections plus their difference


precinctid | ge2013blank | ge2012blank | bdiff | ge2013count | ge2012count | cdiff | ge2013notcount | ge2012notcount | ncdiff

       801 |          38 |          63 |       -25 |           311 |           504 |        -193 |              339 |              121 |            218
       701 |          31 |          56 |       -25 |           387 |           578 |        -191 |              340 |              124 |            216
       611 |          32 |          54 |       -22 |           343 |           468 |        -125 |              206 |               59 |            147
       610 |          42 |          72 |       -30 |           567 |           734 |        -167 |              267 |               70 |            197
       609 |          44 |          82 |       -38 |           530 |           740 |        -210 |              335 |               87 |            248
       608 |          35 |          66 |       -31 |           413 |           570 |        -157 |              270 |               82 |            188
       607 |          28 |          49 |       -21 |           429 |           555 |        -126 |              218 |               71 |            147
       606 |          46 |          68 |       -22 |           449 |           637 |        -188 |              312 |              102 |            210
       605 |          21 |          36 |       -15 |           289 |           386 |         -97 |              158 |               46 |            112
       604 |          45 |          73 |       -28 |           389 |           578 |        -189 |              298 |               81 |            217
       603 |          50 |          77 |       -27 |           341 |           529 |        -188 |              298 |               83 |            215
...

This postgres SQL (9.5) takes the three ballotcounted dispositions (null, 0, 1) and maps them each per precinct for two elections and shows the difference for each disposition between the two elections. This was over 140 lines of SQL; so there must be a simpler but more optimized method of  obtaining the same result with the OVER or WITH statements. My joins are lacking in sophistication. However, despite the fact that my code is wordy and unreadable; there is no noticeable delay in function or speed. Political piece here.

Wednesday, October 22, 2014

Compare Precinct Voting Lists over time

precinctid cnt_db090514 cnt_db091914 cnt_db100214 cnt_db102014 difference variance
101 939 907 911 911 28 1.0307354555
102 632 622 624 627 5 1.0079744817
103 689 680 681 682 7 1.0102639296
104 428 426 427 427 1 1.0023419204
105 414 404 408 408 6 1.0147058824
106 778 768 772 774 4 1.0051679587
107 946 914 920 922 24 1.0260303688
108 1082 1054 1056 1063 19 1.0178739417
110 687 683 685 688 -1 0.9985465116

...

Monday, October 6, 2014

SQL for flushing Inactive Voters



count | ballotcounted_1 | ballotcounted_2
------+-----------------+-----------------
  634 | 0               | 1
  506 | 0               | 0
  333 | 1               | 1
  211 | 0               |
  153 |                 |
   51 | 1               |
   22 | 1               | 0
    8 |                 | 1
    7 |                 | 0

We can filter 'lost' voters like this:

~14K inactive in Whatcom County
~8K inactive in 42nd LD
~2K marked inactive since Certification of Primary e.g '08/20/2014'

That filter gives us these targets listed by priority

  • 333 of that remaining ~2K who voted in both of the last General Elections.
  • 73 of that remaining ~2K who voted only in the last General Election.
  • 642 of that remaining ~2K who voted only in the General Election before last.
Political piece is here.