Wednesday, November 9, 2016

Why I like good old fashioned SQL

Every election year I work on the election portal at https://pacific.nwportal.info/. This year, instead of storing the election data that I retrieve from state and county sources in cache as JSON, I stored it in a SQLITE3 database. This added tremendous flexibility in dealing with election data.

One of the new features of the election portal is the Balance of Power chart that shows which party, given current real-time election results, will be in control of the Washington State Senate and the Washington State House. This is where SQL comes in and helps eliminate the need for a bunch of code. For example, this is the SQL I created to determine how many candidates from each party are ahead in the election for the WA State House:
Select Party, count(Party) Winning, MaxVotes from (select max(TotalVotes) MaxVotes, Party, Candidate, Race from (select rc.Name Race, c.Name Candidate, c.Party, sum(rs.votes) TotalVotes from races rc, candidates c, results rs, feeds f where f.ID = rc.FeedID and f.FeedType = "WAXML" and rs.CandidateID = c.ID and c.RaceID = rc.ID and rc.Name like "%State Rep%" group by c.Name) group by Race) group by Party;
This statement returns a record set that looks like this:
Party              Winning       MaxVotes
Democratic           52            21822
Republican           46            22407 
With this, it is easy to determine in code which party will control the house. I only use the "MaxVotes" value to know when the election reporting has started. If the value is 0, I know that the reporting has not begun yet and can set up my programming logic accordingly.

No comments:

Post a Comment