onthewight – OUseful.Info, the blog…

A Conversation With Data – Isle of Wight Car Parking Meter Transaction Logs

Killer post title, eh?

Some time ago I put in an FOI request to the Isle of Wight Council for the transaction logs from a couple of ticket machines in the car park at Yarmouth. Since then, the Council made some unpopular decisions about car parking charges, got a recall and then in passing made the local BBC news (along with other councils) in respect of the extent of parking charge overpayments…

Here’s how hyperlocal news outlet OnTheWight reported the unfolding story…

I really missed a trick not getting involved in this process – because there is, or could be, a significant data element to it. And I had a sample of data that I could have doodled with, and then gone for the whole data set.

Anyway, I finally made a start on looking at the data I did have with a view to seeing what stories or insight we might be able to pull from it – the first sketch of my conversation with the data is here: A Conversation With Data – Car Parking Meter Data.

It’s not just the parking meter data that can be brought to bear in this case – there’s another set of relevant data too, and I also had a sample of that: traffic penalty charge notices (i.e. traffic warden ticket issuances…)

With a bit of luck, I’ll have a go at a quick conversation with that data over the next week or so… Then maybe put in a full set of FOI requests for data from all the Council operated ticket machines, and all the penalty notices issued, for a couple of financial years.

Several things I think might be interesting to look at Island-wide:

in much the same was as Tube platforms suffer from loading problems, where folk surge around one entrance or another, do car parks “fill up” in some sort of order, eg within a car park (one meter lags the other in terms of tickets issued) or one car park lags another overall;
do different car parks have a different balance of ticket types issued (are some used for long stay, others for short stay?) and does this change according to what day of the week it is?
how does the issuance of traffic penalty charge notices compare with the sorts of parking meter tickets issued?
from the timestamps of when traffic penalty charge notices tickets are issued, can we work out the rounds of different traffic warden patrols?

The last one might be a little bit cheeky – just like you aren’t supposed to share information about the mobile speed traps, perhaps you also aren’t supposed to share information that there’s a traffic warden doing the rounds…?!

Socially Mapping the Isle of Wight – @onthewight Twitter ESP

Having dusted off and reversioned my Twitter emergent social positioning (ESP) code, and in advance of starting to think about what sorts of analyses I might properly start running, here’s a look back at what I was doing before in terms of charting where particular Twitter accounts sat amongst the other accounts commonly followed by the target account’s followers.

No longer having a whitelisted Twitter API key means the sample sizes I’m running are smaller than they used to be, to maybe that’s a good thing becuase it means I’ll have to start working properly on the methodology…

Anyway, here’s a quick snapshot of where I think hyperlocal news bloggers @onthewight might be situated on Twitter…

The view aims to map out accounts that are followed by 10 or more people from a sample of about 200 or so followers of @onthewight. The network is layed out according to a force directed layout algorithm with a dash of aesthetic tweaking; nodes are coloured based on community grouping as identified using the Gephi modularity statistic. Which has it’s issues, but it’s a start. The nodes are sized in the first case according to PageRank.

The quick take home from this original sketchmap is that there are a bunch of key information providers in the middle group, local accounts on the left, and slebs on the right.

If we look more closely at the key information providers, they seem to make sense…

These folk are likely to be either competitors of @onthewight, or prospects who might be worth approaching for advertising on the basis that @onthewight’s followers also follow the target account. (Of course, you could argue that because they share followers, there’s no point also using @onthewight as a channel. Except that @onthewight also has a popular blog presence, which would be where any ads were placed. (The @onthewight Twitter feed is generally news announcements and live reporting.) A better case could probably be made by looking at the follower profiles of the prospects, along with the ESP maps for the prospects, to see how well the audiences match, what additional reach could be offered, etc etc.

A broad brush view over the island community is a bit more cluttered:

If we tweak the layout a little, rerun PageRank to resize the nodes (note this will no longer take into account contributions from the other communities) and tweak the layout, again using a force directed algorithm, we get a bit less of a mess, though the map is still hard to read. Arts to the top, perhaps, Cowes to the right?

Again, with a bit more data, or perhaps a bit more of a think about what sort of map would be useful (and hence, what sort of data to collect), this sort of map might become useful for B2B marketing marketing purposes on the Island. (I’m not really interested in, erm, the plebs such as myself… i.e. people rather than bizs or slebs; though a pleb interest/demographic/reach analysis would probably be the one that would be most useful to take to prospects?).

If we look at the celebrity common follows, again resized and re-layed out, we see what I guess is a typical spread (it’s some time since I looked at these – not sure what the base line is, though @stephenfry still seems to feature high up in the background radiation count).

For bigger companies with their own marketing $, I guess this sort of map is the sort of place to look for potential celebrity endorsements to reinforce a message (folk following these accounts are already aware of @onthewight because they follow @onthewight) as well as potentially widen reach. But I guess the endorsement as reinforcement is more valuable as a legitimising thing?

Hmm…

Just got to work out what to do next, now, and how to start tightening this up and making it useful rather than just of passing interest…

PS A related chart that could be plotted using Facebook data would be to grab down all the likes of the friends of a person of company on Facebook, though I’m not not sure how that would work if their account is a page as a opposed to a “person”? I’m not so hot on Facebook API/permissions etc, or what sort of information page owners can get about their audience? Also, I’m not sure about the extent to which I can get likes from folk who aren’t my friends or who haven’t granted me app permissions? I used to be able to grab lists of people from groups and trawl through their likes, but I’m not sure default Facebook permissions make that as easy pickings now compared to a year or two ago? (The advantage of Twitter is that the friend/follow data is open on most accounts…)

Pottering Around Council Websites – via Google

Over the last few weeks, I’ve started pondering what sort of data sets might be “almost available” on local council websites, along with the extent to which we might be able to use these datasets to support transparency goals, such as generating signals about the extent of cuts to local council services, or developing data driven local services, such as pub finders;-)

So for example, by chance I came across a page on my local council website detailing property the council is selling off:

Surplus to requirements, eh? I wonder how much property has gone up for sale or lease across other councils over the last year or so, and what sorts of services they used to house along with whether those services have been replaced with alternatives, in any meaningful sense?

As a start for ten, here’s a search to try out on your favourite web search engine:

"property for sale" intitle:council site:gov.uk

This won’t search across all council websites, but it’ll have a stab at ones that are hosted on the .gov.uk domain. For more thoughts on searching council websites by proxy, see Aggregated Local Government Verticals Based on LocalGov Service IDs.

And here’s an example of the sort of local news story that might result… @thisissurrey Surrey County Council makes £68million by selling off land and public buildings

Another area of the IW council website that was new to me was the list of public license registers:

So for example, I can look up establishments with a more than a few gaming machines:

No lat/long data, but there are addresses and postcodes, we means weCanHaz maps easily enough…

public register licenses site:gov.uk intitle:council

Looking for reputable suppliers is something I often turn to the parish magazine for (it’s a proxy for trust…), as well as the local Chamber of Commerce members list. But it seems as if this is also something the trading standards aspect of the council may be able to help out on… in th island’s case, there’s a Buy With Confidence register, for example.

“Trader register” seems to be the phrase to go for?

trader register site:gov.uk intitle:council

For food establishments, whilst the IW council participates in the Food Standards Agency’s ‘Food Hygiene Rating Scheme’, it doesn’t seem to pull any of that data into an access point on the council website? (I think my scraper of the FSA site may have rotted too? Food Standards Agency scraper.)

As well as the statutory disclosure of major spend items, the council also publishes details of local contracts – again, if we’re looking to track evidence about cuts, a log of contracts that don’t get renewed might be interesting over an extended period?

Whilst the council webpages don’t make it easy for you to see all the extant contracts,
another scraper can help….

For the holidaymakers, in part, the council produces a table of Beach Water Quality measures, though not on a map as far as I can tell (which reminds me of an old, old map hack mashup….!)? I suspect some of the beaches may be designated public places (no booze…), but at the last time of looking I couldn’t find any data identifying the extent of such areas on the island, let alone any shapefiles of the same…? I’m not sure if there’s data around showing when and which part of the beaches allow dogs on them, either?

"designated public places" site:gov.uk intitle:council

In terms of advertising local events, the council maintain a major events calendar, although I couldn’t spot an iCal feed so I can’t easily subscribe to it in my own calendar…

If you need to find somewhere to park, the council does publish lists of car parks – sort of:

Ooh – my mistake – they do a Google Map too [on which I also spy a KML link]…

As well as accommodation for holiday folk, the island has its fair share of care homes. Quality inspections, it seems, aren’t a council thing – data for that is handled by the Care Quality Commission.

The Isle of Wight Council doesn’t publish FOI disclosure logs as a matter of course, though some other councils do, along with responses:

(foi OR freedom information) +"disclosure log" site:gov.uk intitle:council

And why are FOI disclosure logs interesting? Well for one thing, they allow us to take the FOI Route to Real (Fake) Open Data.

Okay – that’s enough for now, methinks…

Getting Started With Local Council Spending Data

With more and more councils doing as they were told and opening up their spending data in the name of transparency, it’s maybe worth a quick review of how the data is currently being made available.

To start with, I’m going to consider the Isle of Wight Council’s data, which was opened up earlier this week. The first data release can be found (though not easily?!) as a pair of Excel spreadsheets, both of which are just over 1 MB large, at http://www.iwight.com/council/transparency/ (This URL reminds me that it might be time to review my post on “Top Level” URL Conventions in Local Council Open Data Websites!)

The data has also been released via Spikes Cavell at Spotlight on Spend: Isle of Wight.

The Spotlight on Spend site offers a hierarchical table based view of the data; value add comes from the ability to compare spend with national averages and that of other councils. Links are also provided to monthly datasets available as a CSV download.

Uploading these datasets to Google Fusion tables shows the following columns are included in the CSV files available from Spotlight on Spend (click through the image to see the data):

Note that the Expense Area column appears to be empty, and “clumped” transaction dates use? Also note that each row, column and cell is commentable upon…

The Excel spreadsheets on the Isle of Wight Council website are a little more complete – here’s the data in Google Fusion tables again (click through the image to see the data):

(It would maybe worth comparing these columns with those identified as Mandatory or Desirable in the Local Spending Data Guidance? A comparison with the format the esd use for their Linked Data cross-council local spending data demo might also be interesting?)

Note that because the Excel files on the Isle of Wight Council were larger than the 1MB size limit on XLS spreadsheet uploads to Google Fusion Tables, I had to open the spreadsheets in Excel and then export them as CSV documents. (Google Fusion Tables accepts CSV uploads for files up to 100MB.) So if you’re writing an open data sabotage manual, this maybe something worth bearing in mind (i.e. publish data in very large Excel spreadsheets)!

It’s also worth noting that if different councils use similar column headings and CSV file formats, and include a column stating the name of the council, it should be trivial to upload all their data to a common Google Fusion Table allowing comparisons to be made across councils, contractors with similar names to be identified across councils, and so on… (i.e. Google Fusion tables would probably let you do as much as Spotlight on Spend, though in a rather clunkier interface… but then again, I think there is a fusion table API…?;-)

Although the data hasn’t appeared there yet, I’m sure it won’t be long before it’s made available on OpenlyLocal:

However, the Isle of Wight’s hyperlocal news site, Ventnorblog teamed up with a local developer to revise Adrian Short’s Armchair Auditor code and released the OnTheWIght Armchair Auditor site:

So that’s a round up of where the data is, and how it’s presented. If I get a chance, the next step is to:
– compare the offerings with each other in more detail, e.g. the columns each view provides;
– compare the offerings with the guidance on release of council spending data;
– see what interesting Google Fusion table views we can come up with as “top level” reports on the Isle of Wight data;
– explore the extent to which Google Fusion Tables can be used to aggregate and compare data from across different councils.

PPS for a list of local councils and the data they have released, see Guardian datastore: Local council spending over £500, OpenlyLocal Council Spending Dashboard