National Flood Risk Assessment - Free

Following on from my last blog about open data from the ordnance survey, I've been thinking about what we can use all this free stuff for. So how about trying to estimate coastal flood risk for all England, Wales and Scotland?

As any fule kno, you need highly accurate elevation data, locations and types of residential and non-residential properties, effective flood propagation models and information on location and type of flood defences, as well as information on flood water levels from rivers and the sea. National agencies like the Environment Agency and SEPA spend a lot of money gathering this kind of data, and use it to estimate risk at the national scale.

Most of this data isn't available free - but how far can we get with the free data that is available? 

Here's a list of free stuff we can use for estimating flood risk in Great Britain (I can't seem to find equivalent open data for Northern Ireland):

Quantum GIS	Excellent open source GIS software.
Spatialite	Open source geodatabase for analysis of big data sets.
Postcode Open	Point data for postcodes - gives a point for each unit postcode, but not boundary polygons.
Landform Panorama	Elevation on a 50m grid. Accuracy is probably around 2-3m.
Estimates of Extreme Sea Conditions - Spatial Analyses for the UK Coast	Estimates of extreme water levels - doesn't actually cover NI despite the title.
The cost of the summer 2007 floods	Estimates of the damages per property from the 2007 floods.
Flooding in England - a national assessment of flood risk	The "official" estimate of national flood risk from the Environment Agency.
Flooding in Wales - a national assessment of flood risk	The "official" estimate of national flood risk from the Environment Agency, also available in Welsh.
National Flood Risk Assessment for Scotland	Some more "official" figures for Scotland from SEPA.

(I've used "official" in quotes because I don't want to place more trust in these figures just because they come from the appropriate government agency.)

And here's the recipe:

1. Sample elevation from the Panorama data in QGIS - there's a nifty plugin to do this called "Point Sampling Tool".
2. Load the postcode data into spatialite. Make life easier by deleting everything over 20m above sea level - we'll assume these will not be at risk from coastal flooding.
3. Load the Estimates of Extreme Sea Conditions data into spatialite - we get a series of points round the GB coastline, each with data on water levels for extreme event probabilities.
4. Use spatialite to join each postcode to the nearest sea conditions estimate point.
5. We now have a set of extreme water levels, and a ground elevation for each postcode. Use these to estimate the probability of flooding, using a log-probability model.
6. Count the number of properties in appropriate probability bands, by assuming there are roughly 16 properties per unit postcode (the UK has 1.7 million unit postcodes, and 27 million addresses).
7. Use the probability to estimate the annual average damages, assuming damages of £25k per property if it floods, taken from "The cost of the summer 2007 floods" report.

Locations of extreme water level estimates around the coast of Great Britain. Water levels have been grouped as high (red), medium (orange) or low (green). You can see higher levels in some places where we'd expect them - East Anglia and the Channel. But they're only medium around the Severn Estuary - which has the 2nd highest tidal range in the world. Mmm...

Cook all this up and you get results like the ones shown in the table below, showing how many properties have a probability of flooding greater than 1 in 200. 

P>0.005 (1 in 200)	Number of properties from this analysis	"Official" Estimate
England	1.4 million	1.2 million
Wales	100 000	140 000
Scotland	90 000	26 000

If we look at the number of properties at risk, we may have a reasonably good answer (or at least one that roughly agrees with the "official" figures), even using data that's not well suited to flood modelling. But using this technique to estimate annual average damages (if we measure the damages from flooding over many years, and calculate the average per year) gives around £30 billion for England, £2 billion each for Wales and Scotland. The "official" estimate is around £1 billion for England and £200 million for Wales (there are no figures for Scotland). So not so good if we start to think about cost of flooding, rather than the number of properties affected.

Postcode locations in colour coded flood risk bands - a lot of central London has an estimated flood probability of >1 in 75. Fortunately we have the Thames Barrier.

There are some huge gaps in this analysis - things we've ignored, rather than just represented badly. We ignore risk from rivers, the effects of flood defences, and we've assumed all the risk comes from residential properties. My gut feeling is that risk from rivers will roughly double the numbers we've come up with. Flood defences will tend to reduce the risk for properties indicated as flooding more often in this analysis - defences will tend to work for a 1 in 10 probability water level, but are less effective for a 1 in 200 probability event. I think this is why the estimate of annual damages is way too big - there are a lot of postcodes near sea level that are in reality protected by defences, but this analysis would have them flooding every year. Would you be still living in one of these houses? And clearly central London doesn't flood very often - we've got the Thames Barrier to stop that happening.

There are big contributions to the risk from business premises. They can be "hot spots" of risk - a bus garage in Carlisle 2005 and a caravan showroom in Tewkesbury in 2007 were significant contributions to damages. The estimates from the 2007 summer floods indicate that the if non-residential damages were accounted for, this would roughly double the risk.

The error in elevation data is also too big for this kind of application. The difference in water levels between 1 in 10 000 and 1 in 1 (i.e. we see a water level greater than this on average once a year) is as low as 1m in some places, so an error of 2-3m equates to a big error in flood probability.  

But despite the holes, I quite like this result - close enough to the "official" estimate to be interesting, but not suspiciously so. I wonder what would happen if we could get access to flood defence and better elevation data? I also want to look at the social aspects of this - are poorer communities more or less at risk? There's a load of UK Census data available that I want to use to try to answer this question, but first I need a holiday...

I never thought I'd see this: Open Map Data from the Ordnance Survey

Ordnance Survey have always been quite protective of their data. Whatever the political and economic arguments for and against this, it's not really affected my work, as I've always had access to OS data through working in academic institutions or as a consultant on government agency projects. But what happens now I'm trying to work independently?

Last year OS released a number of free digital map products, and I've only just caught up with them. I've only had a look at two raster products so far - VectorMap District and OS Street View, which have nominal scales of 1:25k and 1:10k. Other products at smaller scales are also available.

OS have "generalised and simplified" these products to "allow you to easily visualise a variety of information in its geographic context". So they provide a simple background for other spatial data, uncluttered with unnecessary information. This also means that OS can still charge for their fully detailed products. The products are licensed for any use, as long as OS is attributed (licence here).

Data is supplied from the download page via an emailed link, and comes in 10x10km or 5x5km tiles. This can mean a lot of tiles: 400 tiles for one 100x100km national grid reference square of Street View for example. I've used QGIS's excellent VRT functionality to look at many tiles without building an overly cumbersome single raster image - you'll need the gdal plugin to use this.

OS VectorMap District map of Seascale, Cumbria. Contains Ordnance Survey data © Crown copyright and database right 2011.

OS Street View map of Seascale, Cumbria. Contains Ordnance Survey data © Crown copyright and database right 2011.

How does it look? It's OK - fairly uncluttered, reasonably clear. It's not up to the elegance of the full 25k or 50k OS maps, but fine for a backdrop to other data for websites and reports. I think it fulfills the aim of providing adequate context for other spatial data, but you couldn't use these for planning a walk for example - there's no right of way information.

OpenStreetMap using Osmarender. © OpenStreetMap contributors, CC-BY-SA.

I think it looks better than OpenStreetMap or GoogleMaps for web based stuff, and because it's downloadable as tiff it's better for use in "serious" GIS applications.
One problem with the VectorMap data - it's supplied as a tiff (but not GeoTiff - so no spatial reference system information), with a colour table with low saturation colours. I don't like this - the colours look washed out, and while this is OK for some background mapping, I prefer to set the background appearence myself, using transparency.

OS VectorMap District map with colour saturation increased with a factor of 75%. Contains Ordnance Survey data © Crown copyright and database right 2011.

There's a fairly simple fix for this, by building your own colour table, which can the be loaded into QGIS. I've made a couple of spreadsheets to help me do this in Gnumeric and Excel formats (downloadable here and here). You'll need to save the colour table from QGIS, paste into columns A-F in the first sheet, and select a "Saturation Factor". "1" produces fully saturated colours, "0" has no effect. 0.75 works pretty well. The adjusted colour table can then be saved as a CSV from the second sheet, and loaded into QGIS.
Useable colour tables can be downloaded here: 25%, 50%, 65%, 75%.
I'll be having a look at the vector data too - and will post again with the results.

Mission Accomplished

You've all been dying to know, haven't you? Mmmmmm? So what does TOBU stand for?

I named my blog after Tales of Brave Ulysses, a track from Cream's 1967 psychedelic blues classic Disraeli Gears. Because I knew I might have a lot of time on my hands out here in Indonesia, I decided I would try to read Ulysses, James Joyce's classic, and incomprehensible, novel of Dublin life.

It was either this or the latest Jackie Collins.

Ulysses describes a day (16th June, 1904) in the life of Dublin characters, through conversation, internal monologue and the infamous stream of consciousness passage at the end of the book. The non-hero is Leopold Bloom, a half Jewish ad-canvasser, and the book casts him as Ulysses, wandering through the capital like his namesake through the mediterranean. Joyce deliberately blurs the boundaries between thought and speech (there are no quotation marks), so the result is deliciously ambiguous - did he say that, or just think it?

Joyce avoids the traditional authors' tricks of how to reveal the characters' thoughts (first person narrative, artificially expressive dialogue), instead using myth and allusion to acknowledge the artifice of the author. Yet the result is unmistakeably real. No other book I've read has so realistically portrayed what it is to live and think as a human. Random childhood memories jostle with practical thoughts, higher emotions and bodily sensations; like suddenly remembering your first teddybear when you were thinking about politics, and trying to fart while popping to the shops for some milk. Life IS like this book.

It's also funny. Here's some laugh-out-loud moments from the modernist classic:

Cheese digests all but itself. Mighty cheese.
Who can argue with that?

O, fie! Out on't! Pfuiteufel! You naughtn't to look, missus, so you naughtn't when a lady's ashowing of her elemental.
Frankie Howerd evidently got his act from Ulysses.

Lord love a duck, he said, look at what I'm standing drinks to! Cold water and gingerpop! Two fellows that would suck whisky off a sore leg.
I love that expression.

And with that he took the bloody old towser by the scruff of the neck and, by Jesus, he near throttled him.
This is quoted in Lucky Jim, recalled by Dixon as being "a line from a book he'd once read". I'm rather impressed Jim Dixon has read Ulysses, even though with typical diffidence he won't admit to it.

Do fish ever get seasick?
We've all wondered about that.

I shall be celebrating with a gorgonzola sandwich and a glass of Burgundy when I get back to civilisation.

Star Highway

I've become a tourist. After finishing a week's training in Maumere, I've been riding the trans Flores highway, all the way to Labuanbajo.

I didn't have much luck finding a bike to hire at first, they were all little scooters (so not really up to some of the big hills along the way), and hiring a big, proper bike turned out to be quite expensive. But with typical Indonesian generosity, someone at the government office where I was training offered to lend me his bike for a couple of weeks. So I was the proud borrower of a 200cc Honda Tiger - which is as big as bikes get over here.

The trans Flores highway does this for 100s of km.

So I headed off on the first leg from Maumere to Riung, to meet other volunteers there. 300km and 10 hours later I arrived - an average speed of 30km/h is typical here. The route was spectacular - arching over Flores' spine of volcanoes down to Ende, a dusty town down by the sea, back up to Air Gila, down to Mbay, then along the broken asphalt to Riung.

Don't look down - you wouldn't want to run out of road here.

The nature of the countryside is what makes the trans Flores highway. Flores is mostly mountain, so the road winds up and down through sequences of hairpin bends, with sometimes sheer drops on one (or both) sides. Great motorcycling, although there are the usual hazards of roadworks, gravel, goats, and some big holes. Really big holes - where there is more hole than road. But there's a lot of work being done to improve the road, widening some corners and fixing the surface. So in places there are stretches of new asphalt snaking up the hillside, something that anyone who loves riding bikes likes to see. 

Look what's waiting round the corner. I actually thought the road just stopped when I saw this.

After a couple of days at Riung, enjoying the beaches, islands and beer, it was time to head to Bajawa, a cute little town nestling between volcanoes covered in lush forest. Then on to Ruteng, and finally Labuanbajo. I was heading to this tourist spot to try to see Komodo Dragons on the nearby island of Rinca - but being out of tourist season, couldn't find a boat going out there. But I did see a real, live volcano - something that the villagers there seemed very proud of.

A real live volcano!

I earned my keep by cooking in Bajawa - volunteers Sarah provided the wine, and Rachel gutted the fish. Teamwork.

So this has been a fun road trip - something positive to end my time in Indonesia. I've covered 1250km, I don't know how many hairpin bends, and goodness knows how many Bintang. It's been so good to catch up with all the other volunteers on Flores, who've welcomed me with a place to stay and a cold beer at the end of my day's ride. I'll miss them.

Labuanbajo - end of the road at the western tip of Flores

A Tale of Two Islands

It's different in Flores. That's what lots of other volunteers who've worked in both South Sulawesi and Flores have said, and after a few days here it does seem like a different world. 

I'm in Maumere for a week training local government staff in spatial planning. Staff do actually seem to want to work, and every day when I arrive at the office, everyone there is either working or talking about work. There's still time for the usual chatting/smoking/drinking coffee/eating cake, but that seems to come after at least a couple of hours work has been done.

I do feel like a week's training in the office here has had more impact than my 10 months in Sulawesi - people know what they want to do, and just need some guidance in how spatial data can help them do it. In fact they're so on the ball I suspect they'd probably do fine on their own. 

Whiteboard, projector, pink nylon curtains, picture of the President, self

People here are up to the usual Indonesian standards of friendliness. In one incident I was dragged of the street by a group of teenagers, force fed fried banana (served with chilli sauce, of course) and introduced to the family and dogs. This is a Christian majority area, so dogs are actually kept as pets. And only occasionally as food. Beer is also widely available. Apart from the eating dogs thing, it's all good.  

It's blue - come back in a few years and it might be red

A colleague at work has also offered to lend me his motorbike to tour round the island, which is the sort of thing that just wouldn't happen at home, and today we had a trip to see the incredible coloured crater lakes of Kelimutu.

I had to pay this monkey several thousand Rupiah to get this photo

It's been hard work, fitting what was originally a 5 week training course into 7 days, along with some new stuff. But I've been enjoying it, and I'm looking forward to seeing more of Flores for two weeks "jalan jalan" around the island. If I'd been posted here from the start I might have stayed.

The End of the Road

I've quit. Thrown in the towel. Am packing up and going home.

I can't see how all this planning I'm meant to be helping with is going to benefit the community when only 13% of Jeneponto's budget is available for projects, with the rest being swallowed by civil servant wages. When I see waste on that level, I feel like I'm being taken for a bit of a mug.

What's worse, I think that international NGOs, with the best intentions, are contributing to the problem. There's a dependency culture, with the shortcomings of the government being papered over by NGOs. Where's the incentive to improve government systems, when UK/Canada/Japan/Australia etc will step in when the government wants to implement a project? All the resources required to help Jeneponto are present in Indonesia - so why are we sending volunteers here?

Bantaeng, just up the road, has a forestry office of 40 people. And there is still some forest in Bantaeng. So what are the 100 plus people in forestry in Jeneponto doing exactly? Every time I go into the office and see only 2 people working, and the rest watching TV, chatting or sleeping, I think of why I came half way across the world to help Jeneponto.

I feel sorry for a lot of colleagues, who do want to work and help the community, but are let down by a corrupt and ill-managed goverment system, and are not empowered to change things.

There's personal feelings too - I've never felt at home in Jeneponto, and I think the culture here is just too different from mine. I think I may be particularly British, and Jeneponto might be particularly Indonesian. 

I've been trying to think of things I'll miss. Some colleagues, the guys at local NGO Mitra Turatea, the other VSO volunteers, riding around the coast and mountains on my motorbike. I don't think I'll be too sad to leave Jeneponto itself, as I've no great affection for the place, but I've had some good times in Bali, Bira and Malino.

The saddest thing is that, at the moment, I don't think I believe in development in Indonesia any more. I don't regret doing this - I'd regret not having done it a lot more - but spending another 12 months here would drive me mad.

Rice fields, sunset, Malino

Local news for local people

I came across this article in the Makassar local paper, Fajar, last weekend, and have only just got round to translating it. Some interesting stuff on what's happening in Jeneponto politics right now - and explains a lot about why stuff doesn't work here.

From Fajar, 19 June 2011

Jeneponto Delays Reception of Civil Servants

Amidst the bustle at BKDD [Agency for Civil Service Affairs and Provincial Training], in which various Kabupaten [districts] prepare to select civil servants for the 2011 intake and organise proposals for central government, Jeneponto apparently alone is quiet. This area has, it seems, not yet proposed the civil service positions required for 2011.

After is was confirmed to the interested parties, Jeneponto apparently blocked selection of 2011 civil service positions. In fact, the postponement of recruitment just mentioned will last for the next 2 years. The most important reason is budgetary. The local government is still investigating the financial capacity of the Kabupaten which is not yet stable, after the total budget deviated from the agreed amount.

In addition to budgetary factors, the total number of civil servants in this area has also been assessed as exceeding the limit set, namely 6592 staff. "If local government employs more civil servants, where are the wages to come from?", said chairman of the local assembly Alamzah Mahadi Kulle. But there are still 3511 people yet to become civil servants since their names were added to the database from 2005 onwards. The 2011 budget for Jeneponto is only IDR355 billion [approximately GBP27m], with the result that it is certainly difficult for the local government budget to cover wages for civil servants that now number 6592. "Almost 87% of Jeneponto's budget is spent on paying civil servants", he added.

Head of the BKDD in Jeneponto, Hasnan Arsyad, says the policy of delaying reception of civil servants in 2011 is based on the outcome of a meeting with the local assembly in May. "Delay of civil service recruitment has already been announced in the official report of the meeting between the local assembly and the Jeneponto government", said Hasnan.

While this is happening, BKDD Bantaeng [neighbouring district] have agreed a proposal for 400 new civil servants, although there were votes in the legislative body to delay recruitment because reception of the 2010 intake is not yet complete.

"We have proposed 400, because in 2010 we had 200 people retire, with the same number in 2009", said head of the Agency, Jumaing Rahing.

Secretary of the Bantaeng assembly, Nirdin Halim, has assessed the increase in civil servants for 2011 to be enough. It is certain the government should focus more on increasing budget for community interests. "If civil servant numbers increase, the local budget must surely be exhausted in wages", said Nurdin.

For the intake of civil servants this year, he has asked for honorer [a type of contract worker without the same status as a full civil servant] only. "The reception of the provious intake of honorer has been completed. The new honorer are already working and busy."

Some key facts/observation from my placement put the above into some context:

• Jeneponto district has a population (2009) of 334 175 people. There is 1 civil servant per 50 head of population. The rate in the UK is 1 public sector worker per 10 population.
• Many civil servants have other jobs.
• Many in my office don't work afternoons. Or mornings. There are many civil servants with no officially allocated tasks.
• Money for projects is scarce, so civil servants spend a lot of time writing proposals to central government and international NGOs.
• Government buildings and facilities are run down, ill equipped and ill suited to their requirements.
• If there is money for equipment, there is little for training, maintenance or running costs. For example, there is an environmental monitoring lab full of new equipment which has never been used since purchase in 2008.

So it seems that the number of public sector workers is 5 times smaller than numbers in Europe - but it's still too big for the money available in Jeneponto. The high wages bill is preventing any of these civil servants operating effectively - my guess is that you'd need to halve the head count to maximise productivity. But that's unthinkable here.