Publishing Election Results

Work Completed By: Terry Rich-Whitehead

DESCRIPTION

With the upcoming election of county councillors we need to update the way in which we publish and update the up to the minute information on election results and how they affect the political makeup of the authority.

We need a unified method by which to ensure that once we have confirmed a particular result it is syndicated to all of the appropriate communication channels.

In addition to such a service we will need a way to publish the results and provide summary information on the state of the council during the ongoing election process.

DETAILS/LOCATION OF PRACTICAL DEMONSTRABLE OUTPUT

Details of where code, web pages etc can be found.

At present all demonstration pages are only available on a local Virtual Machine. For further details see Appendix A.

DESCRIPTION OF THE WORK CARRIED OUT

Describe how you approached this piece of work, any technologies, tools or techniques that you found useful or tried and discarded. Include any examples that you used for inspiration and any contacts you have made in carrying out the work.

Overview

This project was divided out into the following subject areas.

Creating RSS Feeds

Sourcing information on Electoral Areas

Reviewing the Google graphing API

Exploring the Twitter API

Some of these areas were more successful than others. Creating RSS feeds was found to be very straightforward however; amassing data to simulate election results was difficult due to the inability to access data that could be easily manipulated. The Google graphing API worked satisfactory but it has some scope for improvement.

The project produced some proof of concept demonstrations in the form of the following :-

Database for managing results

RSS Feed for Electoral Areas

RSS Feed for Declared Results

Detailed results page showing example graphics

Sample of JavaScript generated graphics

Due to the inability to access data on the electoral areas that could be used in a dynamic way it was found necessary to develop a sample database. This database was to contain data information on electoral wards,
candidates, number of electors, seats, votes cast. Etc.

diagram01.gif

Database Schema

The database is at the time of writing only populated with a small amount of data which is sufficient to provide sample demonstrations of the Feeds and web pages. Accompanying the database there is a series of web based screens that allow the addition, viewing and editing of data.

Below is a screen shot of the sample data held in the database.

Capture-1.gif

Sample Data - Wards Table

Capture-2.gif

Sample Data - Results Table

The database also contained a couple of views that provided a simple means to extract data prior to been displayed on a web page or placed in a RSS feed.

Capture-10s.jpg

Database - Administration Screen

RSS Feed for Electoral Areas

The RSS Feed for electoral areas was produced via PHP. The PHP code executes a query on a view held in the database. This feed will display information on all electoral areas (provided there entered in to the database). When results have not been declared the feed will show them as not been declared with no date and time. Selecting the item title will link the user to a web page showing a breakdown on the result accompanied by a graphical representation. The database view used for this feed also calculates the percentage of votes cast for each candidate.

Capture-3.gif

Sample Item from RSS Feed

RSS Feed for Declared Results

This feed is very similar to the previous feed except that it is based on a different database view. The view used for this feed only lists electoral areas that have been declared and calculates which candidate(s) have won the seat. The results are reverse ordered on the date and time declared which results on the latest results been placed at the top of the feed. Again like the previous feed the item title links to a more detailed results page.

Capture-4.gif

Sample Item from Declared Results RSS Feed

Detailed results page showing example graphics

Detailed results for each electoral area are be generated via dynamic web page written in PHP which quires the database used for the RSS feeds. Data is then displayed in table and also used to invoke images generated by the Google graphics API.

Capture-6.gif

Sample Results Page

The Google graphics API initially looks very comprehensive. However, some of the graph types that could have been used to represent election results proved disappointing. For instance it was imposable to produce a bar graph where to columns could be clearly labelled. Even the Pie graph as used in the demonstration comes with it problems.

The major problem with the Pie graph concerns the labelling of the segments. The core of the problem is that the label text is not wrapped which in the case of long labels results in the text been cropped because it extends beyond the defined image size. Long labels can be accommodated by increasing the image width which relies on a certain amount of guesswork.

Searching the net for any other graphic APIs that could be suitable proved fruitless. However, a promising set of JavaScript functions was discovered that proved a good solution for producing simple bar graphs. (HTML Graphics by Gerd Tenler http://www.gerd-tentler.de/tools).

Capture-7.gif

Sample JavaScript generated graphics

Exploring the Twitter API

The combined problems in understanding the Twitter API, accessing it via PHP and posting data through the WCC proxy/firewall did cause a few problems particularly as the Boarder manager returns a success code (200) when in fact the connection had been blocked. However, with the aid of a packet logging tool the problem was identified and resolved. With the proxy issue resolved it was fairly straightforward to make PHP calls to Twitter. Most effort was spent developing code to post messages.

Capture-15.gif

Sample PHP generated Twitter messages

pda-1.jpg

Twitter Access via a PDA running Windows Mobile 6

PROJECT OUTCOME

Describe the degree to which the work was successful in addressing the project description. Include reasons why or why not.

Most of the projects requirements were met including the use of the Twitter API for the dissemination of data. The prime objective was to produce an RSS feed to provide up to date information on the elections. Two dynamic feeds were produced one detailing all the electoral areas despite been declared or not and a feed showing results as they are declared. Each of the feeds provides a link to a relative details results page.

Capture-8.gif

Main RSS Feed containing a full breakdown of all the election results

Capture-9.gif

RSS Feed containing declared results in reverse date order

The Google graphs API did proved useful in creating pie charts despite it’s idiosyncrasies with labelling in general.

Capture-12.gif

Google Pie Chart

Most of the other charts available via the API were not suitable for displaying election data. The was an interesting Google-o-meter ( aka John Snow’s swing-o-meter ).

Capture-13.gif

Swing-o-meter

Gerd Tenler JavaScript functions provided a simple but fully customisable way of producing Bar graphs. Parameters can be generated server side and instantiated on the web page. The graphical element of the bar chart can use an image as a background fill. So with a little graphics work more aesthetically pleasing charts can be produced. The example below shows a simple cylindrical effect.

Capture-11.gif

Graph produced using Gerd Tenler’s JavaScript Functions

Finally software was developed to allow messages detailing election results to be forwarded to Twitter. Once the electoral seat is declared a PHP generated message is generated and passed to Twitter. There is a limitation in that the API only allows 100 messages per hour to be posted.

SHORT TERM BENEFITS

What immediate impact could the output of this R&D work have on the organisation – could it provide benefits without compromising our strategic approach?

This project demonstrated how quickly a system could be put in place. It took under four days to produce the barebones of a system that has the capability to disseminate electoral information. With just perhaps one week’s additional effort a robust and aesthetically pleasing solution could be completed.

STRATEGIC IMPLICATIONS

How the work carried out fits with our strategic direction or how it should contribute to our strategic thinking.

Developing a system such as this highlights the need to make data sources more easily accessible. One of the man problems with this solution was accessing meaningful data. The only data I could access was held on the main WCC web site in forms that could not be easily accessed or incomplete. Hence the need to create local database which was not the most ideal solution. All the information that had to be recreated must have resided somewhere with in the council. Ideally there should be a service that makes this data available. For instance a service that provided details of all the electoral wards, a service that held electoral candidate information and a service that could provide historical electoral data. If this information/data was more freely available then this short development could have been produced in half the time.

OTHER NOTES ETC

Using XAMPPLITE

Some of the Apps Strategy development work has required the use of XAMPPLITE to provide a full web development environment. XAMPPLITE is a suite of software that includes Apache, MySQL, PHP and phpMyAdmin the core ingredients for web server development.

The glory of XAMPPLITE is that it does not need to be installed, it can be run from portable devices such as USB memory sticks.

For demonstration purposes a copy can be found at the following location :-

H:\ICTSharedInfo\Strategy\Apps Strategy Team\tric\xampplite

To start XAMPPLITE run the application xampp-control.exe

To avoid conflicts it is advisable to copy the xampplite to a local drive as if more than one person has started MySQL it my give unexpected results.

Once started a control window will be displayed. On start up no services are started these should be started from the control panel by use of the appropriate start button.

Capture-14.gif

XAMPP control Panel

WARNING Do not select the Svc check box as this will set what has been selected as a service on the local machine!

Capture-16.gif

XAMMP showing Apache and MySQL running.

Once Apache is running then any browser can be used connect to it by use the localhost address e.g. http://localhost. One connected the following page will be displayed.

Capture-17.gif

XAMMP showing Apache and MySQL running.

The XAMPP link takes the user to the main XAMPP admin/dome page.
Link(s) below take the use to Apps Strategy projects.

Further Information
Appendix A

The XAMPPLITE package can be found at :-

http://www.apachefriends.org/en/xampp-windows.html

The installation consists of the following software :-

  • Apache 2.2.9
  • MySQL 5.0.67 (Community Server)
  • PHP 5.2.6
  • XAMPPlite Control Version 2.5 f
  • XAMPPlite Security 1.0
  • SQLite 2.8.15
  • OpenSSL 0.9.8i
  • phpMyAdmin 2.11.9.2
  • Webalizer 2.01-10
  • Zend Optimizer 3.3.0
  • eAccelerator 0.9.5.3 for PHP 5.2.6
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License