SO!VOTE - Using web services to visualize and deliver poll data

Idea

The Canton Solothurn makes its poll data available on the internet (http://www.wahlen-abstimmungen.so.ch). You can even grab some results from 1997. Though there is plenty of information available, the presentation of the data is rather mediocre:

You can get the results of the communities only district by district. Since we have ten of them you will click several times to get the information.
The federal and cantonal results from one poll sunday are located on two different sites.
The only outputformat is CSV (Comma-separated values): CSV example
To get all the information (yes-/no votes and the election participation) from one district you have to download two files.
The only graphical preparation is a bar chart.

So why not grab the data from their website, combine it with some geographical data from SO!GIS and use WMS/WFS to visualize and deliver the combined stuff.

Implementation

What do we need to get some nice colored maps and a single file with all the interesting information in it?

Since we want to make heavy use of web services we need a WMS/WFS server: GeoServer has a nice plugin for creating charts on maps and extension for an excel output format. Then we need some place where we can store the data. Because we use WFS(-T) to communicate we can use any datastore that is supported by the WFS server. In this case we choose Postgresql/Postgis. That's almost all we need. If we want to make our life a little easier we can use GeoTools to communicate with the WFS server. The process looks like this:

Get the CSV files from its origin.
Check if the particular community (more precisely: the combination of the community name, the voting proposal and the date) is already available in our datastore.
Get the geographical data and attach it to the poll data.
Save the combined data in the datastore.

Let's have a more detailed look at it:

Get the CSV files

This is easy: just read the data from the ten districts of one poll sunday and store it in an array. The array is filled with comma separated strings containing data from the different voting proposals:

Aetingen;Vorlage 1:;Finanz. Luftverkehr;80;38;;67.80;32.20;
Aetingen;Vorlage 2:;VI Kriegsmaterial;32;89;;26.45;73.55;
Aetingen;Vorlage 3:;Minarett-Initiative;64;57;;52.89;47.11;
Balm b. Messen;Vorlage 1:;Finanz. Luftverkehr;37;17;;68.52;31.48;
Balm b. Messen;Vorlage 2:;VI Kriegsmaterial;17;37;;31.48;68.52;
Balm b. Messen;Vorlage 3:;Minarett-Initiative;36;17;;67.92;32.08;

Check if the community exists in our datastore

We only want to insert data that does not yet exist in our datastore (think of a cron job that will update every 10 minutes incoming results during poll sunday). This can be done with a simple WFS request. If a feature for the specific poll sunday, community and voting proposal is returned we can check the next community. So append some filters to the WFS request, something like this:

http://.....&FILTER=cql_filter=gem_name=Aetingen+AND+vorlage=Vorlage 1:+AND+datum=20091129

If no features will be returned we can continue with our process:

Attach some geographical data

If the combination does not exist in our datastore we request the community boundary from a WFS. Then we create a GeoTools SimpleFeature with all the poll data and the community boundary and insert it into our datastore with a transactional WFS request. That's it.