Pezholio

Today I was at a ‘quick and dirty’ local spending data workshop at Birmingham City Council’s newly refurbished offices on Lancaster Circus. There’ll be more detailed info to come on the Local Open Data Community (login required), but I just wanted to blog a few of my thoughts post the meeting.

There was a lot of talk about what other councils have done (such as Windsor and Maidenhead, Barnet, Islington etc), and it was agreed that there was a lot of difference in how the data was presented, Paul Davidson also talked about some of the work that the Local eGovernment Standards Body had done regarding getting spending data out there in a linked data format. There did seem to be a bit of resistance to the linked data approach, mainly because agreeing standards seems to be a long, drawn out process, which is counter to the JFDI approach of publishing local data.

However, while I am a fan of the possibilities of linked data, I also recognise that there are difficulties in both publishing the data and also working with it. For example, I think it’s unrealistic to expect every local authority to maintain a triple store to publish their spending data. As we learned from the local elections project, often local authorities don’t even have people who are competent in HTML, let alone RDF, SPARQL etc.

Therefore, I think the way forward is a centralised approach, with authorities publishing CSVs in a standard format on their website and some kind of system picking up these CSVs (say, on a monthly basis) and converting this data to a linked data format (as well as publishing in vanilla XML, JSON and CSV format).

The great thing about the linked data approach is it will mean that each item of spending can have its own URI – e.g.

http://localspending.data.gov.uk/41UD/12345

(The first part of the URI would be the SNAC code for the authority, and then the second part of the URI would be the internal reference number)

As well as having a human-readable summary of the data (together with links to the actual data in RDF, XML, CSV and JSON), there would be a comments box (similar to Adrian Short’s fantastic Armchair Auditor), as well as the ability to ask any questions about an item of expenditure – the answers to these questions would then be automatically published next to the item of spend (hopefully helping to cut down on multiple FOI requests).

While this may be a bit of a pie in the sky idea, I do feel that there does need to be some kind of effort on the part of central government to help move this project along, as we’ve seen already (naming no names!) some authorities have got it drastically wrong, and while there is definitely mileage in the ‘just get it out there’ approach, I think if we’re going to end up with something *really* useful (for both members of the public and local authorities), we need to get the data in one place.

Thoughts?

It’s been a while since my last blog post, and after a request from Twitter, I thought it might be time to dust off Wordpress and do a quick blog post. Since we last spoke, UK Postcodes (the postcodes webservice I blogged about a few blog posts back) has been plodding along nicely and, as a natural tinkerer, I’ve been tinkering ever since.

I’ve sped up request times considerably, and wanted to add the ability to get parliamentary constituencies from a Postcode – originally, I was using the TheyWorkForYou API, which, while useful, didn’t return a National Statistics or Ordnance Survey URI / ID, so I got to thinking about how I could SPARQL this using the National Statistics Endpoint, after some exploration, I noticed that district wards were always inside one parliamentary consituency, so thanks to some linked data knowledge (helpfully pumped into my brain at a two-day training course at Talis a few weeks back), I got to writing a SPARQL query, which you can see below (using the SNAC ID for Coleshill South Ward, where I live):

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX administrative-geography: <http://statistics.data.gov.uk/def/administrative-geography/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX electoral-geography: <http://statistics.data.gov.uk/def/electoral-geography/>

SELECT ?ward ?label ?constituency ?constituencyname
WHERE {
?ward
a <http://data.ordnancesurvey.co.uk/ontology/admingeo/Ward> .
?ward skos:notation "44UBGC"^^administrative-geography:StandardCode .
?ward rdfs:label ?label .
?ward electoral-geography:parliamentaryConstituency ?constituency .
?constituency skos:prefLabel ?constituencyname ;
}
LIMIT 1

This query basically asks for the URI and name of a ward with a specific SNAC ID, as well as the constiuency’s URI and name. I’ve now added this to UK Postcodes.

But I haven’t stopped there, oh no! The next step is to get parishes. Again, ward and parish boundaries don’t overlap, so I’ve used another (slightly more complex) SPARQL query to find out what parish a ward is inside, this time using the Ordnance Survey Endpoint:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX spatialrelations: <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/>
PREFIX admingeo: <http://data.ordnancesurvey.co.uk/ontology/admingeo/>

SELECT ?parish ?parishcode ?ward ?label
WHERE {
?ward a <http://data.ordnancesurvey.co.uk/ontology/admingeo/DistrictWard> .
?ward admingeo:hasCensusCode "44UBGB" .
?parish a <http://data.ordnancesurvey.co.uk/ontology/admingeo/CivilParish> .
?parish spatialrelations:contains ?ward .
?parish admingeo:hasCensusCode ?parishcode .
?parish foaf:name ?label ;
}
LIMIT 1

This fella here is asking for a parish which contains a given ward – at the moment I’m using the OS URI, but I’m hoping to figure out a way of using SNAC IDs before I put it onto the live UK Postcodes system.

Hope this gives people a bit of an insight, and if anyone who is cleverer than me wants to point out any problems, or make any suggestions, feel free!

Some people would say we’re mad, but last Saturday, on the hottest day of the year so far, a group of people eschewed barbecues and beer in the garden for a whole day spent working with mapping data and other bits of open data to help people understand the benefits of open data at Mapitude, an event aimed at developing understanding and practical collaboration between web developers and mappers.

As well as unconference style talks and discussions, there was also a hack day, where a group of us got together to try and solve practical problems using open data in just one day. The brief we decided on was to map a council ward, add some statistical information to it, and then compare it with a neigbouring ward.

As Dan Slee from Walsall Council was in attendance, he was keen that we map St. Matthew’s ward in Walsall, a deprived ward in the centre of Walsall, and compare it with Aldridge Central and South, a neighbouring, but significantly less deprived area.

Once the brief was written up, I got together with Chris Taggart of OpenlyLocal to identify some data sources. The first thing we noticed was that OpenlyLocal didn’t have much information about the wards and councillors in Walsall – however, after a bit of trawling Walsall’s website and adapting an existing screen scraper, OpenlyLocal had Walsall’s data up, this made it easier to get the councillors for each ward.

The next challenge was getting the ward boundaries. Now, a few months ago, this would have been nigh on impossible without either breaking the law, or physically walking the boundaries with a GPS tracker (which, even then, might have been dodgy). However, thanks the the release of Ordnance Survey data, this is now significantly easier.

However, even with Open Data, this was still not an easy task. The boundary data comes in ESRI shapefile format, which, on it’s own isn’t a particularly friendly format for web developers, as it’s designed for desktop GIS software, so the first task was converting the Shapefiles to something we can easily work with online.

Thankfully, I’d been doing some research a few days prior, and chanced upon this bit of work, which includes a program that converts ESRI Shapefiles to MySQL tables, a format that is much kinder to web developers. Once that was done, we then had to convert the boundaries themselves from Ordnance Survey National Grid References to latitude and longitude, which was easily done by this PHP script.

We now had councillors and shapefiles, and already time was against us. The group who put together the brief had decided on population, families claiming child benefit, average income, and number of unpaid carers. This could be retrieved from the National Statistics Data Exchange, but we ran out of time, so ended up simply copying and pasting the data. However, given enough time, we knew we could’ve done it.

You can see the results of our work here, and some of the comparisons are quite stark. We would’ve liked to add crime data from the Police API, but again, pressures of time meant we didn’t get round to it. However, if you look at the crime stats for St Matthew’s and Aldridge Central and South, you can see there’s quite a difference, with crime in St Matthew’s being much much higher. However, this may be slightly skewed by the fact that St Matthew’s takes in much of Walsall town centre where there are a lot of pubs and bars, so fights etc at chucking out time are, sadly, common.

Where next?

So, was it all worth it? Well, I know Chris Taggart will be adding similar functionality to OpenlyLocal in the future, so in that respect it was useful. It also helped me get a handle on how to work with the OS boundary data, and yesterday, I released the Lichfield portion in a much more usable format on the Lichfield District Council open data section.

The most important aspect though, I think, is serving as an example of what can be done with open data. For too long, us open data folks have been banging on about how great open data is and everyone should do it. However, the people who have the power to really open stuff up aren’t always convinced, they might be wary, or not understand the issues. With examples like this, we can start with a problem, and in less than a day, have a solution. It’s these solutions which open people’s eyes to the power of data, and convince them that this is the future.

Well, it’s finally happened, Ordnance Survey have gone from being the bad guy of the internet, a big, bumbling behemoth, squishing innovation wherever it goes, to being the darling of the internet, throwing out free data wherever it goes (well, sort of).

As well as lots of mapping data being released, it also release Codepoint Open, which matches every postcode up a geographical location, it’s what I, along with the oh-so-clever uber trolling project Ernest Marples have been banging on about for ages. Now, finally, developers can get a user’s location details based on one thing that almost everyone in the UK is guaranteed to know, their postcode – this can then be used to get all sorts of useful information, like their nearest school, fire station, police station, cafe, tanning salon (or whatever).

As well as matching postcodes to a physical location, CodePoint Open also matches postcodes to local authority information, something which, in the past, I’ve used the NESS Data Exchange for, but I’ll probably migrate to this, ‘cos it’s easier.

Anyway, although the data has been released and is out there, it’s in a whopping 250mb CSV file, so if anyone wants to use it, they need to download a local copy and import it into a database for their own use. I wanted to do this, but thought, instead of everyone doing this, why don’t I make it easier for everyone (myself included) and wrap it in a web service?

That is wot I dun – enter UK Postcodes. Initially I built this over the Easter break, in a few snatched moments between eating easter eggs and making sure my new puppy didn’t wee anywhere. On the surface it’s very simple, a request like this:

http://www.uk-postcodes.com/postcode/WS136YY.xml

will give you all the information about WS13 6YY in XML format, including latitude/longitude, easting/northing, a Geo Hash URI, as well as the county council (if applicable), district council and ward the postcode is in. You can also have JSON, CSV and RDF just by changing the extension (Thanks to Jeni Tennison for her help with the latter format!).

After posting the results of my fiddling on the UK government Data Developers mailing list – I got a lot of helpful people suggesting other ways I could interact with the data, so I know have methods for finding the nearest postcodes to a point, as well as reverse geocoding (going from a lat/lng point to a postcode) – there’s more information on the API page.

That’s basically it, I’m always happy to hear feedback, so if you’ve got any suggestions or feedback (good or bad!), please feel free to comment below!

(Oh, and a massive thanks goes out to Adrian Short (actually it was Adrian who created the torrent of the data – proving that file sharing isn’t always used for nefarious purposes :cough:#debill:cough:) Matthew Somerville, who translated all those Eastings and Northings in the original dataset to Latitude and Longitude – you can download his version from the MySociety mirror.)

Finding out who your councillor is isn’t always easy is it? Even if you know which council provides your services, wards often have odd sounding names, and council websites don’t always have an easy postcode search. With this in mind, and now the Office of National Statistics have released a brand new API and because I’ve got a day off looking after my new puppy, I decided to have a play and see if I could make it easier.

First I trawled through the documentation to find the best API methods for my needs, it turns out that this URL did what I needed:

http://neighbourhood.statistics.gov.uk/NDE2/Disco/SearchSByAByPostcode?LevelTypeId=14&Postcode={POSTCODE}

This basically asks the NeSS Data Exchange to return the details of a ward (which in Office of National Statistics language is level type ID 14) which a particular postcode is in. Helpfully, I don’t get the SNAC ID straight away (you’ll see why I need this later), so I have to get the ONS internal ID, and make another API call:

http://neighbourhood.statistics.gov.uk/NDE2/Disco/GetAreaDetail?AreaId={AREA ID}

This gives a whole bunch of extra info about the area, including the SNAC ID (which is called the ExtCode). Once I have that, I can then move across to OpenlyLocal, where I can get the councillor’s information:

http://openlylocal.com/wards/snac_id/SNAC ID.xml

Once I’ve got this info, I can then display it all, like so! Obviously if the council isn’t on OpenlyLocal, then we can’t show their details, but we can say who their council is and what ward they’re in, and then, with a bit more jiggery pokery on the OpenlyLocal side, we can direct them to their local authority’s website, like so!

Give it a try yourself, and let me know any feedback in the ol’ comments below. Obviously, this only shows your district council if you’re in a two tier council, but I’m working on getting county council info later

Oooh, it’s worth pointing out that you need a PSI click-use licence to publish the Data Exchange data on your own website, but it’s a doddle to apply for and goes live instantly!

Update: I’ve published the code on Github in case you want to see how I did it!

Well, it’s been an exciting few months for the open data movement, with the future of the Ordnance Survey and geographical data going out to consultation and data.gov.uk being launched, but imagine my horror when I finally received a response to the petition I started to make postcode data free to non-profits. The full text is below:

Under the Postal Services Act 2000, Parliament set up Royal Mail as a public limited company with the Government as its only shareholder.   Under this framework, the Government established an arm’s length relationship with the company so that the company had more commercial freedom and could run its operations without interference from the shareholder. 

The Postcode Address File (PAF) dataset was designed and engineered by Royal Mail and is owned and managed by the company as a commercial asset of the business (containing around 29 million addresses in the UK).  Royal Mail developed the PAF with the primary purpose to aid the efficient delivery of mail, though over the years the PAF has come to be used for a number of purposes other than the postal purpose for which it is designed and was established.  Indeed, many organisations, including new postal operators, banks, insurance companies and others offering to deliver goods to your door, use the information held on the database.  The PAF is also used in other business processes, including mailing list “cleaning”, anti-fraud activities and various customer services.

Royal Mail invests significantly in collating and maintaining the Postcode Address File (PAF) and this cost is recovered through an independently regulated licensing arrangement.  It would of course be very time-consuming and costly for anyone to try to replicate the list, so Royal Mail licenses PAF data, for a fee, allowing others to use it.  Under Section 116 of the Postal Services Act 2000,   Royal Mail must maintain the PAF and make it available to any person who wishes to use it on “such terms as are reasonable”. 

This requirement is replicated as a condition of Royal Mail’s licence issued by the postal regulator, Postcomm.  Provision exists for Royal Mail to recover a reasonable charge for the supply of PAF and it must not impose any term or condition other than reasonable restrictions to safeguard its intellectual property rights (IPR), and to ensure that the PAF and its updates are used to support effective addressing.

As access to the PAF is governed under a condition of licence, Postcomm monitors its practice.  Royal Mail’s licence obliges the company to make access to the PAF available on reasonable terms. Postcomm allows the company to make a reasonable specified profit margin and monitors its accounts.  

Postcomm has previously undertaken a public consultation reviewing how the PAF was managed.  The consultation started in 2006 and finished in 2007.  Postcomm took all the diverse uses of the PAF into account before reaching its decision in 2007, announcing more safeguards for the management of the address information held in the PAF with the aim of making sure that the PAF is maintained properly and made available on fair and reasonable terms.  The findings of the consultation can be found on Postcomm’s website (www.psc.gov.uk). 

If any PAF user or stakeholder feels that Royal Mail is not complying with the terms of section 116 of the PSA 2000 or Condition 22 of its licence, they can either raise concerns direct with the company or with Postcomm.  Postcomm would consider the merits of any such concerns in the light of its statutory duties.

This is, word for word, the exact same response I got from Lord Young via my MP, Mike O’Brien after I wrote to him in October (which is also exactly the same response as the Free PAF petition got earlier last year). No mention of data.gov.uk, no mention of the Ordnance Survey consultation, just a flat out ‘no’, even though steps have already been put into place to free postcode data!

I can only assume that this was written (read: copy and pasted) by someone who has little or no understanding of the issues, has seen ‘postcodes’ and ‘royal mail’ and just whacked a standard response on. It doesn’t really do much for your faith in democracy and the system when you know more than the Prime Minister’s office seems to know.

As some of you have have seen, Sue Cameron of the Financial times has written a confused and desperate sounding article about the government’s recent announcement to open up the postcode dataset for public reuse.

As the FT doesn’t have any commenting functionality (my biggest bugbear with newspaper websites), I’ve drafted a letter to the editor. I’ve not sent it yet, so would appreciate your comments! The full text is below:

Sir,

In Sue Cameron’s article ‘Mandy and Gordon – the unravelling’, Ms. Cameron claims Gordon Brown’s opening of the PAF dataset will be a ‘free gift from the taxpayer to major money makers such as Google’.

Yes, large corporations will benefit slightly (very slightly – they won’t have to pay £1,200 per year to use the PostZon dataset), but the real winners will be small, community built websites, such as Planning Alerts, Job Centre Pro Plus, The Straight Choice and countless other sites, which, until recently were powered by a free service – ernestmarples.com, which tied location data to postcodes.

These sites offered services like alerting people to planning applications and jobs in their area for free, providing useful, innovative services for free and taking the load off the stretched public sector.

However, in October, Ernestmarples.com was forced to close by heavy-handed legal action from the Royal Mail, and without a free location-based dataset, these sites could not function and were closed down.

Linking locations to postcodes is an extremely valuable service which should not be in the control of one private organisation, the data was sourced at the public’s expense and should be free for the public to reuse as they see fit. Who knows – it might end up saving the government a few quid too.

Yours Faithfully

Stuart Harrison

iPhones are ace aren’t they? Despite howls of derision from some camps, in the space of a few years, it’s completely turned the mobile world on its head.

I’ve wanted to build an iPhone app for Ratemyplace – the food safety ratings website I run at the council on behalf of 8 other councils in Staffordshire for a while, but unfortunately just didn’t have time to learn Objective-C, the language that iPhone apps are built in – at the same time, I’m also aware that not everyone has an iPhone, so have been scouting around for ways to build apps that can be ported to other platforms quickly and easily.

About 6 months ago, I looked at Phonegap, which promised to allow me to build apps quickly using only HTML and Javascript. I beavered away for a few weeks, and finally had an app I was (sort of) pleased with. It used native features such as geolocation, and I used the UiUi Kit CSS library to make the app look as much like a native app as possible. I was all ready to submit it to the app store. Then the 3.0 firmware upgrade came.

My iPhone app was busted – native geolocation no longer worked, and a little hack I’d worked out to get a toolbar at the bottom and a navbar at the top was also kaputt. I tried to fix it for a bit, but other priorities took over, and the project went on hold.

Then, a few weeks ago, I came across Appcelerator Titanium, a platform that does pretty much everything Phonegap does, but (in my opinion) much better. While Phonegap uses a portion of the iPhone’s API called the webview to display webpages as though they were native apps, Titanium goes one better, it compiles your code into a mixture of web views and native Objective-C code. Like Phonegap, it also works with Android, and a promise of more platforms to come.

This means that you can not only take advantage of many more native iPhone features, your app is much less likely to be rejected (as a lot of phonegap apps were) because of the use of what Apple calls ‘private APIs’.

I dusted down my old bits of code, bookmarked the API documentation, and set to work. In the end, as well as doing my other duties, the app took me 5 days (and one beery evening) to complete. You can see a few screenshots below:

I submitted it to Apple yesterday, and I’m not expecting to hear anything for a good few weeks, if not months (Apple’s approvals processes to get an app into the app store are notoriously strict) but I’m reasonably hopeful. I’m now looking at porting my code to Android phones, with a view to looking at Blackberry and Nokia devices too.

Looking forward a few years, with the growing adoption by mobile phone browsers of HTML5 and CSS3, I can see that apps are going to become more browser based, and (hopefully), this will lead to standards being adopted across the industry, but, until then, it seems that platforms like Titanium are going to be the best way to get your apps to as many people as quickly as possible.

I love Fixmystreet – it’s the nearest thing we’ve got to a national problem reporting hub, while other council website bury their reporting facilities under pages and pages of navigation, Fixmystreet is simple, quick and direct.

What I also love is its openness, as well as being able to pull out reports for problems in your area, you can also inject problem reports directly into the system, meaning anyone can build their own front end for the system.

I’ve been thinking about Fixmystreet a lot recently, after building my own frontend for it via the Ubermap, I’ve also been thinking about repurposing our existing streetscene reporting forms, and getting them to use Fixmystreet.

It was while fiddling about under the hood, that I thought ‘wouldn’t it be cool if people could report problems via Twitter?’

Twitter is a fast way of communicating, and crucially, it’s mobile. There’s a plethora of mobile apps and frontends for Twitter, and even if you’re only on an aged Nokia 3310, you can send tweets by text message. This means that when you spot a problem, you can report it straight away. You could even add a picture.

I got cracking on it, and, within the space of 24 hours I had a prototype – users sign up with their Twitter username and email address on the Fixmytweet website, and whenever they spot a problem, they can send a tweet in this format:

@fixmytweet {postcode} {description of problem} {twitpic link (optional)}

(If you’ve got a smartphone with a Twitter app that supports the geolocation API you don’t even need the postcode!)

The system then parses the tweet and sends the necessaries to Fixmystreet. Fixmystreet then sends an email to the user, with a link for them to add more detail and approve the report. It then gets sent directly to the council! (and, if the council is smart, they can even integrate it with their CRM system)

I’m currently testing this out on a test version of Fixmystreet, and it seems to be working well so far. If you’re interested in helping test me test it, please sign up on the Fixmytweet website and send a test tweet.

I’ll keep everyone posted on developments on Twitter and this here blog!

It’s been a while since I first published my Beginners’ Guide to Twitter in Local Government, and since then (although, obviously not as a direct result of my blog post!) there’s been a plethora of local authorities using Twitter.

However, with a few notable exceptions, most councils seem to prefer the ‘fire and forget’ method of sticking an RSS feed into Twitterfeed and leaving it to run. Hell, even I was guilty of it up until recently – even though I was making an effort to monitor and engage, I was still letting the news articles automatically publish via Twitterfeed.

There’s been a bit of a discussion recently via Twitter as to the rights and wrongs of this, and I’m definitely in the ‘wrong’ camp now – A few months ago I turned off the RSS feed for @lichfield_dc press releases and now do them manually. Why? Well, I shall tell you…

You don’t talk to a robot

Twitter is, by and large, a social medium – if all you’re doing is chucking out press releases, people will assume you’re not interested in engaging or are just going to generate boring content. This means you aren’t going to get followed by as many people who might otherwise be interested in you.

Auto tweets look ugly

Take this press release for example. If I were to use an auto tweeter, here’s what it would look like:

News: Everyone is invited to get into the festive spirit at this year’s Christmas Fayre taking place in the city… http://is.gd/5eZUT

Pretty boring huh? Plus the content gets cut short and can often not make sense. However, if I conjour up a manual tweet, I can tailor it much more to the Twitter medium and come up with something a lot more friendly:

This means you can get your message across much more easily and in a style that fits in with Twitter’s informal approach.

Stopping information overload

Sometimes you’ll have a press release which, while it might be relevant for the website, might not be suitbale for Twitter. If you’re blindly tweeting everything you put out, then people might be much more likely to be turned off by your content and reach for that big button marked ‘unfollow’.

This is all well and good, but…

…I can hear the objections already – ‘I don’t have time to write manual tweets’ – but how difficult it it to write a 140 character tweet? If you’re going to take online engagement seriously, it’s definitely worth just taking 2 minutes to show your followers you care.

If you really must use automated tweets, then make sure you mark your account up as such – call it something like ‘councilx_news’ and state very clearly that all you’re doing is publishing the latest news – then if further down the line you decide to engage a bit more, you can start another account for more human interaction.

I’m a massive hypocrite

However, after saying all this, I do agree that sometimes, automated tweets have their place. We do still tweet food safety inspections, and @ldcplanning has been happily tweeting planning applications for a while now. However, it’s not really practical to tweet large volume, samey tweets manually, and you don’t get the same advantages with tweeting press releases.

In fact, I recently did a straw poll amongst our followers about whether to get rid of the food safety tweets, especially as much of the content is replicated by @ratemyplace, but the majority seemed to like them. So maybe automated tweets isn’t as open and shut a case as I’d like it to be?