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.
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.
Sample Data - Wards Table
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.
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.
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.
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.
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).
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.
Sample PHP generated Twitter messages
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.
Main RSS Feed containing a full breakdown of all the election results
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.
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 ).
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.
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.
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!
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.
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