How I made the maps:

The SQL code below rolls up, pivots and, calculates medians of the voter age ranges. It’s a query against four views of the voter information, which is itself a view that decodes the various voter codes in the database. This generated the table that QGIS joined to the shape files. I used QGIS to export GeoJSON to MapBox for the interactive web map.

My first set of web maps used qgis2web and Leaflet, but that was only for one congressional district. This map was far too large for a serverless solution – the JSON file ended up being nearly 70 megabytes. Converting to TopoJSON would help, but then I’d need to write some conversion for Leaflet or incorporate D3. Ain’t nobody got time for that. 🙂

MapBox is powerful, and for hobby use like this, it’s free. Their API is compact and fun. I’ll use them again for my next project.

Main Query

SELECT 
  dbo.vw_CountVoterByPrecinct.CountyPrecinct, 
  dbo.vw_CountVoterByPrecinct.VoterIDCount, 
  dbo.vw_CountAgeRangeByPrecinct.AgeRange, 
  dbo.vw_CountAgeRangeByPrecinct.AgeRangeCount, 
  dbo.vw_CountPartyByPrecinct.PartyCode, 
  dbo.vw_CountPartyByPrecinct.PartyCodeCount, 
  dbo.vw_CountAgeRangeByPartyByPrecinct.AgeRangePartyCodeCount
FROM 
  dbo.vw_CountAgeRangeByPrecinct 
FULL OUTER JOIN
  dbo.vw_CountVoterByPrecinct 
FULL OUTER JOIN
  dbo.vw_CountAgeRangeByPartyByPrecinct ON 
    dbo.vw_CountVoterByPrecinct.CountyPrecinct = dbo.vw_CountAgeRangeByPartyByPrecinct.CountyPrecinct 
FULL OUTER JOIN
  dbo.vw_CountPartyByPrecinct ON 
    dbo.vw_CountAgeRangeByPartyByPrecinct.PartyCode = dbo.vw_CountPartyByPrecinct.PartyCode AND 
    dbo.vw_CountVoterByPrecinct.CountyPrecinct = dbo.vw_CountPartyByPrecinct.CountyPrecinct ON 
      dbo.vw_CountAgeRangeByPrecinct.AgeRange = dbo.vw_CountAgeRangeByPartyByPrecinct.AgeRange AND 
      dbo.vw_CountAgeRangeByPrecinct.CountyPrecinct = dbo.vw_CountVoterByPrecinct.CountyPrecinct

dbo.vw_CountVoterByPrecinct

SELECT
  TRIM(CountyCode) + TRIM(Precinct) AS CountyPrecinct, 
  COUNT(VoterID) AS VoterIDCount
FROM 
  dbo.vw_DecodedVoterRegistrations
GROUP BY 
  CountyCode, Precinct

dbo.vw_CountAgeRangeByPrecinct

SELECT
  TRIM(CountyCode) + TRIM(Precinct) AS CountyPrecinct, 
  AgeRange, 
  COUNT(AgeRange) AS AgeRangeCount
FROM
  (SELECT 
    VoterID, 
    CountyCode, 
    Precinct, 
    (CASE 
      WHEN Age BETWEEN 18 AND 24 THEN '18-24' 
      WHEN Age BETWEEN 25 AND 34 THEN '25-34' 
      WHEN Age BETWEEN 35 AND 44 THEN '35-44' 
      WHEN Age BETWEEN 45 AND 54 THEN '45-54' 
      WHEN Age BETWEEN 55 AND 64 THEN '55-64' 
      WHEN Age BETWEEN 65 AND 74 THEN '65-74' 
      WHEN Age BETWEEN 75 AND 84 THEN '75-84' 
      ELSE '85+' 
    END) AS AgeRange
    FROM
      dbo.vw_DecodedVoterRegistrations) AS AgeRanges
GROUP BY 
  CountyCode, 
  Precinct, 
  AgeRange

dbo.vw_CountPartyByPrecinct

SELECT        
  TRIM(CountyCode) + TRIM(Precinct) AS CountyPrecinct, 
  PartyCode, 
  COUNT(PartyCode) AS PartyCodeCount
FROM            
  dbo.vw_DecodedVoterRegistrations
GROUP BY 
  CountyCode, 
  Precinct, 
  PartyCode

dbo.vw_CountAgeRangeByPartyByPrecinct

SELECT        
  TRIM(CountyCode) + TRIM(Precinct) AS CountyPrecinct, 
  AgeRange, 
  PartyCode, 
  COUNT(PartyCode) AS AgeRangePartyCodeCount
FROM
  (SELECT 
    VoterID, 
    CountyCode, 
    Precinct, 
    PartyCode, 
    (CASE 
      WHEN Age BETWEEN 18 AND 24 THEN '18-24' 
      WHEN Age BETWEEN 25 AND 34 THEN '25-34' 
      WHEN Age BETWEEN 35 AND 44 THEN '35-44' 
      WHEN Age BETWEEN 45 AND 54 THEN '45-54' 
      WHEN Age BETWEEN 55 AND 64 THEN '55-64' 
      WHEN Age BETWEEN 65 AND 74 THEN '65-74' 
      WHEN Age BETWEEN 75 AND 84 THEN '75-84' 
      ELSE '85+' 
    END) AS AgeRange
    FROM            
      dbo.vw_DecodedVoterRegistrations) AS PartyAgeRanges
GROUP BY 
  CountyCode, 
  Precinct, 
  PartyCode, 
  AgeRange

dbo.vw_DecodedVoterRegistrations

SELECT        
  dbo.tblVoterRegistrations.CountyCode, 
  dbo.tblVoterRegistrations.VoterID, 
  dbo.tblVoterRegistrations.NameLast, 
  dbo.tblVoterRegistrations.NameSuffix, 
  dbo.tblVoterRegistrations.NameFirst, 
  dbo.tblVoterRegistrations.NameMiddle, 
  dbo.tblVoterRegistrations.Exemption, 
  dbo.tblVoterRegistrations.Address1, 
  dbo.tblVoterRegistrations.Address2, 
  dbo.tblVoterRegistrations.City, 
  dbo.tblVoterRegistrations.State, 
  dbo.tblVoterRegistrations.Zip, 
  dbo.tblVoterRegistrations.MailingAddress1, 
  dbo.tblVoterRegistrations.MailingAddress2, 
  dbo.tblVoterRegistrations.MailingAddress3, 
  dbo.tblVoterRegistrations.MailingCity, 
  dbo.tblVoterRegistrations.MailingState, 
  dbo.tblVoterRegistrations.MailingZip, 
  dbo.tblVoterRegistrations.MailingCountry, 
  dbo.tblVoterRegistrations.GenderCode, 
  dbo.tblVoterRegistrations.RaceCode, 
  dbo.tblVoterRegistrations.BirthDate, 
  dbo.tblVoterRegistrations.RegistrationDate, 
  dbo.tblVoterRegistrations.PartyCode, 
  dbo.tblVoterRegistrations.Precinct, 
  dbo.tblVoterRegistrations.PrecinctGroup, 
  dbo.tblVoterRegistrations.PrecinctSplit, 
  dbo.tblVoterRegistrations.PrecinctSuffix, 
  dbo.tblVoterRegistrations.VoterStatus, 
  dbo.tblVoterRegistrations.CongressionalDistrict, 
  dbo.tblVoterRegistrations.HouseDistrict, 
  dbo.tblVoterRegistrations.SenateDistrict, 
  dbo.tblVoterRegistrations.CountyCommissionDistrict, 
  dbo.tblVoterRegistrations.SchoolBoardDistrict, 
  dbo.tblVoterRegistrations.AreaCode, 
  dbo.tblVoterRegistrations.PhoneNumber, 
  dbo.tblVoterRegistrations.PhoneExtension, 
  dbo.tblVoterRegistrations.EmailAddress, 
  dbo.tblRaceCodes.RaceDescription, 
  dbo.tblPartyCodes.PartyDescription, 
  dbo.tblCountyCodes.CountyName, 
  dbo.tblGenderCodes.GenderDescription, 
  FLOOR(DATEDIFF(DAY, dbo.tblVoterRegistrations.BirthDate, GETDATE()) / 365.25) AS Age
FROM            
  dbo.tblVoterRegistrations 
LEFT OUTER JOIN
  dbo.tblRaceCodes ON dbo.tblVoterRegistrations.RaceCode = dbo.tblRaceCodes.RaceCode 
LEFT OUTER JOIN
  dbo.tblPartyCodes ON dbo.tblVoterRegistrations.PartyCode = dbo.tblPartyCodes.PartyCode 
LEFT OUTER JOIN
  dbo.tblCountyCodes ON dbo.tblVoterRegistrations.CountyCode = dbo.tblCountyCodes.CountyCode 
LEFT OUTER JOIN
  dbo.tblGenderCodes ON dbo.tblVoterRegistrations.GenderCode = dbo.tblGenderCodes.GenderCode
WHERE (dbo.tblVoterRegistrations.VoterStatus = 'ACT')