Measure per precinct turnout for active voters

Poltical piece is here.
---unautomated
 drop table bc2009;
 drop table bc2010;
 drop table bc2011;
 drop table bc2012;
 drop table bc2013;

 Select precinctid,Count(ballotcounted_5) as cnt_ge2009
 into bc2009
 From voterdb102014
 Where statuscode = 'A' and
 ballotcounted_5 = '1' and
 precinctid in (Select precincts42 from ld42)
 Group By precinctid,ballotcounted_5
 Order By precinctid;

 Select precinctid,Count(ballotcounted_4) as cnt_ge2010
 into bc2010
 From voterdb102014
 Where statuscode = 'A' and
 ballotcounted_4 = '1' and
 precinctid in (Select precincts42 from ld42)
 Group By precinctid,ballotcounted_4
 Order By precinctid;

 Select precinctid,Count(ballotcounted_3) as cnt_ge2011
 into bc2011
 From voterdb102014
 Where statuscode = 'A' and
 ballotcounted_3 = '1' and
 precinctid in (Select precincts42 from ld42)
 Group By precinctid,ballotcounted_3
 Order By precinctid;

 Select precinctid,Count(ballotcounted_2) as cnt_ge2012
 into bc2012
 From voterdb102014
 Where statuscode = 'A' and
 ballotcounted_2 = '1' and
 precinctid in (Select precincts42 from ld42)
 Group By precinctid,ballotcounted_2
 Order By precinctid;

 Select precinctid,Count(ballotcounted_1) as cnt_ge2013
 into bc2013
 From voterdb102014
 Where statuscode = 'A' and
 ballotcounted_1 = '1' and
 precinctid in (Select precincts42 from ld42)
 Group By precinctid,ballotcounted_1
 Order By precinctid;


Select
 bc2013.precinctid as precinctid,
 bc2013.cnt_ge2013,
 bc2012.cnt_ge2012,
 bc2011.cnt_ge2011,
 bc2010.cnt_ge2010,
 bc2009.cnt_ge2009,
 (bc2013.cnt_ge2013 - bc2009.cnt_ge2009) as difference,
 (bc2009.cnt_ge2009::float / bc2013.cnt_ge2013::float) as variance
From
    bc2013,
    bc2012,
    bc2011,
    bc2010,
    bc2009
Where
 bc2013.precinctid = bc2012.precinctid and
 bc2012.precinctid = bc2011.precinctid and
 bc2011.precinctid = bc2010.precinctid and
 bc2010.precinctid = bc2009.precinctid
;

drop table bcAggre;
Select
 bc2013.precinctid as precinctid,
 bc2013.cnt_ge2013,
 bc2012.cnt_ge2012,
 (bc2013.cnt_ge2013 - bc2012.cnt_ge2012) as ObamaDiff,
 (bc2012.cnt_ge2012::float4 / bc2013.cnt_ge2013::float4) as ObamaVariance,
 bc2011.cnt_ge2011,
 bc2010.cnt_ge2010,
 bc2009.cnt_ge2009
/*(bc2013.cnt_ge2013 - bc2009.cnt_ge2009) as difference2013,
  bc2012.cnt_ge2012 - bc2009.cnt_ge2009) as difference2012
  (bc2009.cnt_ge2009::float / bc2013.cnt_ge2013::float) as variance2013,
  (bc2009.cnt_ge2009::float / bc2012.cnt_ge2012::float) as variance2012 */
INTO bcAggre
 From
    bc2013,
    bc2012,
    bc2011,
    bc2010,
    bc2009
Where
 bc2013.precinctid = bc2012.precinctid and
 bc2012.precinctid = bc2011.precinctid and
 bc2011.precinctid = bc2010.precinctid and
 bc2010.precinctid = bc2009.precinctid
;
Select * from bcAggre Order by ObamaDiff;


# This is R code that uses the DBI compliant RPostgresSQL interface to chart data from SQL queries

library(RPostgreSQL)
jpeg_create <- function() {
   systime <- as.numeric(Sys.time())
   # dev.new()
   jpeg(filename = systime,
          width = 1024, height = 768, units = "px", pointsize = 12,
          quality = 100, bg = "white", res = NA, family = "", restoreConsole = TRUE,
          type = c("windows"))
   Sys.sleep(2)
      }

con <- dbConnect(PostgreSQL(), user= "postgres", password="password", port="5432", host="localhost", dbname="dbanme")
ObamaDiffld42 <- dbGetQuery(con, "Select * from bcAggre Order by ObamaDiff;")
# jpeg_create()
with(ObamaDiffld42,(barplot(cnt_ge2013,names.arg=precinctid,xlab="Ballots Counted = '1' : Active Voter Difference Sorted L -> R for 42nd LD 10/20/2014 Red=2012 GE Blue=2013 GE",xlim=c(0,119),ylim=c(0,1000),col=rgb(0,0,1,.75),cex.names=.675,las=2)))
par(new=T)
with(ObamaDiffld42,(barplot(cnt_ge2012,names.arg=precinctid,xlim=c(0,119),ylim=c(0,1000),col=rgb(1,0,0,.75),cex.names=.675,las=2)))
par(new=F)