Coloring & Mapping Ballot Boxes in Ankara

[EDIT]: To overcome the overlapping boxes problem discussed below, I merged the boxes in the same building and created another map. Result after merge is available here and here (click on a marker to get details at that location):

 (go to the bottom of the page to see the per ballot box results, i.e. before merge)

I just created a map of Ankara with ballot boxes (sandik) colored by the winner party for that ballot box. Here is what I did:

  1. Dataset comes from CHP (main opposition party) database as YSK (official election institute) has not revealed the vote counts.

     

  2. Go to your Google Drive. Click create. Connect more apps. Choose Fusion Table.
    Create Fusion Table
  3. Filter the previously downloaded xlsx formatted spreadsheet to Ankara ballot boxes only using excel and create a new workbook by copy/pasting to a new spreadsheet and save it as csv.
  4. Combine ALAN, ILCE and IL fields of the table and create a new column called ADRES using MS Excel’s concatenate function as explained here.
  5. Created a column KAZANAN to label the party with the highest vote count: =MATCH(MAX(C2:AB2),C2:AB2,0)
  6. Created another column KRENK for icon/marker type. Here is a list of available markers in Fusion Table Maps. Excel Expression: =IF(B2=1,”small_yellow”,IF(B2=2,”small_red”,IF(B2=3,”small_blue”,”small_green”)))
  7. Upload your csv file to Fusion Tables. When you click create Fusion Table in the above caps, you are forwarded to import page:
    Import spreadsheet Fusion Tables
  8. Google Fusion table automatically geocodes for you if you set the field type for ADRES as location and go to the menu Files>Geocode… NOTE: This process takes very long time. And there supposed to be a daily limit for auto-geocoding for free accounts but it looks like that this somehow was not applied to me.Auto geocode the given column
  9. It sometimes fails to detect write address. You can manually alter the geocode information by switching to table view and clicking edit the row.Edit Row
  10. Edit geocode pops up a map where you can enter an alternative address and hit search button for suggestions or directly enter @lon,lat information into the search box.Edit geocode
  11. To assign different colors/markers to ballot boxes go to Tools > Change map and click Change Feature Styles. Then choose Use icon specified in a column.Marker selection

Here is what to improve:

  1. Ballot boxes in the same address are superimposed.
  2. Some boxes are mislocated by Google Geocoder.

Here is the output:

Ankara ballot boxes colored

 

Zoom out once to get the view of Ankara by ballot box (before merging & includes every town):

One thought on “Coloring & Mapping Ballot Boxes in Ankara

Leave a Reply

Your email address will not be published. Required fields are marked *