The trials and tribulations of attempting to drag Tower Hamlets into the open data age
Update: If the table below isn’t loading, it’s a problem with LBTH’s database being down
This week I was copied in on an email from Vickie of WiW to LBTH about getting an RSS feed of planning applications. The feed unfortunately appears to be unforthcoming. However, seeing how I don’t get much opportunity to use spreadsheets in my job, I thought I’d have a play around with trying to extract some data through brute force.
MS Excel has the ability to extract tables from HTML (and a variety of other formats) and back in the day when I used to deal with money laundering reports, I used this function to extract data from a SQL database to monitor training compliance. Anyway, enough about me living the dream.
I then did some research and found that googlespreadsheets have the function ‘importhtml’ which has a variety of options, but allows data on a table to be extracted. The only searchES with a static URL are searches on postcodes (I use http://planreg.towerhamlets.gov.uk/WAM/findCaseFile.do?postcode=e to extract all records with a postcode).
I then built a query that pulls from the google spreadsheet into the table below. You will see I’ve also created a permanent page on this blog to make it easy to find the latest updated table, which can be found here. I think it should update itself from LBTH’s database automatically, but I’ll need to to keep reviewing it.
One problem is that if an application doesn’t have a postcode it won’t be reported on this search. The railings for Capital Wharf don’t have a postcode (they originally did, but after an error with the address this was removed), so would not show up here. In fact the only way to find such planning applications (if you don’t know they exist) is to either search on a street-by-street basis, or to search the weekly lists.
The weekly lists (here) cover registrations over the last 28 days, which means you a) have to remember to search once every 4 weeks, and b) requires you to undertake at least 4 searches to cover that period.
A complication is that planning applications take a variable amount of time between registration and being considered by the council. For example at the time of writing there is an application PA/12/02235 which has not yet been decided by the planning sub committee, but was first registered in July 2012. It is now the 3rd week of January, so we are somewhat outside of the 28 day period covered by these searches.
A complete planning register appears to be available on a map (here) but is only found on the planning website as 15th in a list of 16 links, after having read past ‘view planning applications’ and ‘planning consultations’, so you might not ever find yourself reading the map. The map is useful, but doesn’t provide a hyperlink to the relevant planning docs, so isn’t that useful, and further it doesn’t allow applications to be filtered by registration date, so if you were to view it regularly, identifying which are the newest applications and which are older is not simple (especially an issue if a site has multiple applications, for example 21 Wapping Lane).
Clearly, this database is not for the casual nosey parker! What it fundamentally means is that as a local resident I, nor anyone else is able to actually find out a complete list of planning applications. So in the meantime, keep an eye out for planning notices attached to lamp posts and check on my table here in the knowledge that it’s incomplete.
UPDATE: The table below identifies current applications based on whether the decision date is blank, however, more review of the database indicates that actually, there are records which are coded as ‘decided’ which have no date attributed, so the search may return some decided cases erroneously. There are also six records which have both a blank date and a blank status (ie neither current nor decided).