There has been a lot of fairly excitable posting recently about the continuing rise of OpenStreetMap, and how it’s now being used in place of Google Maps,  in particular since Google started charging for data. People have been talking about how “authoritative” crowd-sourced spatial data can be, and to be honest, I’ve found that the discussions seem to have missed the point a little bit. For me at least. So- here’s a few of my personal thoughts about OpenStreetMap and why it will be a while before I will consider it authoritative at least.

Firstly, a caveat. I love OpenStreetMap data and the whole ecosystem of “stuff” that has evolved around it. I contribute occasionally, though not as often as I’d like to. I’m pleased that it has forced the “traditional” data providers to reconsider what they offer, and perhaps to raise their game. I think it’s one of the main reasons why the idea of “open data” is so well known that even my Gran knows about it.

However, in the process of trying out WalkingPapers (my favourite addition to the OpenStreetMap ecosphere), I printed out the area around my house- in the centre of Lancaster. Not London, I admit, but still in the centre of a city. My road wasn’t on there. Read on, before you give up in disgust, saying “pah, you should just correct any errors you find”. How did I know my road was missing? There wasn’t an unfinished stub, with a note saying “I went home for my tea and I’ll finish this section tomorrow”, it was as if my road did not exist. I only knew my road was missing because I live there, and I have no readily available tools to help me judge the accuracy of an area that I am not familiar with. So, the idea that the end-user should correct any errors they find doesn’t really hold because you’re not always going to know that there’s an error!

I am aware that the traditional data providers do not produce totally error-free data. However, I would expect Ordnance Survey (for example) to have a workflow for their surveyors that doesn’t allow them to simply give up when they get bored or have to go home for their tea, or perhaps not survey a street because they don’t go down it very often.  I can also go to the site and find published information on positional accuracy if I need it. I guess I’m equating “authority” with trust, or reliability, and the issue is less about what’s there, than what’s not there.

When OpenStreetMap is being used as a static base map, by which I mean a backdrop to the information your map is really about, this is less of an issue. But, if like me, you come from a discipline where maps are collections of data to be manipulated and analysed, then you need to be able to trust your data a little more, or at least have a quantitative understanding of what the error is likely to be.

I know that this is not an easy question to answer, and I know people are trying to figure out ways of answering it. I also know it’s not that important for a lot of people, but I would like to see a more nuanced debate- I think that would be better for OpenStreetMap in the end.

Caution- this post won’t make you a pgRouting guru, but it will allow you to get pgrouting up and running on Ubuntu 11.10 and have some data on a map in approx 20 minutes.

1. Follow the instructions here to install PostgreSQL, PostGIS and pgRouting. Note that the instructions are for PostgreSQL 8.3 but they work just as well for PostgreSQL 9.0, just go with whatever version you want and substitute the package name accordingly.
2. If you’re using PostgreSQL 9.0, you might hit an error when loading the driving distance functions, as seen here. There’s a fix, which  may or not have been committed by the time you try this- if not then you need to apply it by replacing two altered files and recompiling/making and installing pgrouting (repeat the “compile pgRouting” step in the link in step 1).
3. Go here and download a selection of osm binary (pbf) files.
4. Follow the instructions here to download and run osm2po. Note that the documentation is currently available only in german. However, the example given in the link will work to get you a working demo, and it’s quite easy to follow the examples. Note also that you need a java runtime environment to use this- so if you don’t have one then download the jre of your choice using your favourite software package manager.

If this whets your appetite and you want to learn more, then there’s an excellent workshop available on the pgRouting site, along with lots of useful howtos and tools.

This, of course, only scratches the surface of pgRouting, but it just goes to show that thanks to all of the great links I’ve listed above, it is possible to get a working demo going extremely quickly.

If you end up doing a lot of work on mapserver and tilecache behind corporate proxy servers, you’re likely to hit a couple of snags, when the proxy thinks you’re trying to do something evil, when really all you want to do is seed a tilecache or look at an external WMS server. Fortunately there are a couple of useful workarounds, which I record here for my own sanity as much as anything else!

Mapserver

Mapserver has some great documentation on how to use proxy authentication when requesting data from an external web server, but unfortunately if you’re still on Mapserver 5.6.x, and on windows, you are likely to find that some of the directives don’t work. When you test your mapserver layer in a browser, you get a blank screen, and the following error message in your logs:

HTTP request error. CURLOPT_PROXYAUTH not supported. Requires Curl 7.10.7 and up

Needless to say, the later versions of mapserver (5.6.6) are using Curl versions greater than 7.10.7 so we’ll ignore that part of the message and concentrate on the CURLOPT_PROXYAUTH part. A quick google will inform you that this must be explicitly compiled into mapserver, and as far as I can tell, none of pre-compiled windows binaries for 5.6.x have been compiled with this option.

Enter cntlm, which is designed to stand between your application and your corporate proxy server, providing proxy authentication on the fly. To use it with mapserver, install the latest binaries and follow the configuration hints on the home page. The basic process is to run cntlm from a command line to establish the type of authentication that your proxy server uses, then fill in the supplied cntlm.ini file with the appropriate values for your configuration. If you don’t know these, you can easily find them by opening firefox or similar and  looking at preferences->advanced->network->settings.  You’ll need the URL of the proxy, the port, and the type.

Then, assuming that cntlm is installed on the same server as mapserver, your map file needs only the following layer metadata parameters (in addition to anything else you have defined):

wms_proxy_host: localhost

wms_proxy_port: specified in cntlm.ini (default is 3128)

wms_proxy_type: http or socks5 etc

With luck, once you have cntlm configured correctly, your external WMS layer should display just fine!

Tilecache

If when seeding your tilecache you receive “bad gateway” errors, or inexplicably corrupted tiles, it’s possible that the tilecache_seed.py script is also being blocked by the proxy server on your network. Somewhat counter-intuitively (at least it seems that way at first), this can be easily circumvented by switching off the proxy settings in Internet Explorer on the machine you’re running the script on (and switching back on once the script completes successfully). Note that it has to be Internet Explorer- you’ll need another approach if that’s not installed.

The reason for this is that tilecache_seed.py uses the python module urllib2, which uses ProxyHandler to handle proxies (who would have guessed). This will attempt to autodetect proxy settings, and one of the options it checks is in Internet Explorer (I guess this equates to a registry setting somewhere but we don’t go digging in the registry without our vaccinations up to date and all our affairs in order, do we?). A longer term solution might be to extend the tilecache_seed.py script to explicitly set up a ProxyHandler with no proxies defined, as in this example.

September is clearly conference month in the geo world. FOSS4G in Denver, which I didn’t go to, was closely followed by the AGI GeoCommunity in Nottingham, which I did. I participated FOSS4G vicariously, following twitter and starring lots of posts in my google feed, and it’s only now that I’m having chance to catch up on them all and assimilate them. Seems like there was a lot of soul-searching going on, about the future of the organisation, the conference, and the geospatial industry in general. Seems also like there was a really good vibe to the event, perhaps related to a really good choice of venue, which kept people together for socialising, networking and whatever. Perhaps these two factors are related? Get a bunch of conference-goers together over some geo-beers, and no doubt a lot of discussion will take place.

One of the key questions that seems to have come up as a result of the success of this year’s event has been whether or not there should be an annual North American event alongside the global event. Personally, not being in North America, I can only see this from the international perspective, and to me it looks bad. Diluting the FOSS4G brand has to be a bad thing. How many of the big players will choose to come to the international event when they “know” everyone “important” is going to be at the North American event? However, from the perspective of raising cash for the foundation, perhaps it’s a good idea because it’s probably more commercially viable. I don’t know, but I kind of wish the idea hadn’t been brought up…

With my new directors hat on, I’m sure there’s going to be a lot more discussion about this, alongside the organisational changes necessitated by dissolving the Executive Director’s post. I’m not going to go into that at all, but I would just like to say that I think Tyler did a fantastic job for OSGeo, particularly on the international scene- here in the UK he “was” OSGeo and things won’t be the same without him.

Over to the AGI GeoCommunity event. Having been on the working group helping to organise the event this year, again I had a different perspective on things to normal. Being on the working group is quite easy in the run up to the event as the AGI team do most (nearly all) of the hard work. However, you’re expected to help with exciting things like bag packing, chairing streams, and generally being around and visible throughout the two days. No sleeping during not so interesting talks! Attendance was good in the end, after a rush of late registrations, the new venue (the East Midland Conference Centre at the University of Nottingham) was a big success, and in the end even the last minute substitution of two plenary speakers (for perfectly good reasons) didn’t upset things!

I chaired the session on Open Source and Open Data on day one, which included some really worthwhile papers. Standing out (a couple of weeks after the event) are Antony Tuffour’s paper on Open Source Software Stack and Standards- Integration and GI for Everyone- about introducing a FOSS web mapping system at the London Borough of Hackney, and Mark Iliffe’s talk on When Gov 2.0 doesn’t exist- Mapping Services in the Developing World. I’ve seen Mark’s talk a couple of times now and it’s always inspirational. All the papers are available on the AGI website, so go take a look.  The famous soapbox event in the evening was won by my new(ish) boss. His talk was in reaction to the great FUD debate from a few weeks ago, excellently shown up for the absurdity that it is at the time by Paul Ramsey, and again here. Catch the YouTube video while you can!

Other things of note include the relaunch of the Technical Special Interest Group again. It’s easy to be cynical about this, as even I have now been around the AGI long enough to have seen the previous attempt lose momentum and stall, but enthusiasm is everything, and there is a lot of interest in the group as a relatively neutral umbrella under which both proprietary and open source vendors can talk techy without the sales pitch. Look out for events towards the end of the year.

There was plenty of soul-searching at the AGI GeoComm as well as at FOSS4G. While the two organisations are quite different, the same basic questions come up around providing best value, what the organisation is actually for, and how best to retain sponsorship and income in difficult financial times. Let’s just hope everyone pulls through in the end…

I imagine I’m not alone in having parents and grandparents who don’t really understand what I do for a living. “I work in computing and do stuff with maps” is the easy approach (in fact it’s easier now that I don’t have to tag on the bit about being an archaeologist but not actually digging, and no it’s not like Time Team or Indiana Jones). Sometimes people ask why we don’t just “do everything with google maps”, which is the cue for a sit down and a longer chat about how (deep breath) you can’t do *everything* with google. So far, so good…

This all changed a couple of weeks ago, when, to my surprise I got elected to the Board of Directors of OSGeo. Cue shock, and great rejoicing (and in my head at least, tearful Oscars-style acceptance speeches). Mr Archaeogeek thinks this is cool enough to tell parents, grandparents, family friends etc. I do too, don’t get me wrong, but…

How to explain what it means to people who don’t use computers all that much, let alone know about gis, or open source or OSGeo?

There have been a few good articles on how to explain why you work in open source (the “how do you make money?” argument), but I feel like I need to go further back and explain about software licensing. I don’t think that most people really understand the difference between the way software is sold/licensed, and most other products, so I’m working on a car analogy that explains why open source software needs to exist. It goes something like:

“Can you imagine, that if you brought a car, and something went wrong with it, you couldn’t lift up the bonnet and take a look? Or if you did need to take it to a garage you had to use the one the car salesman told you to use? Or after 2 years they told you that they wouldn’t support your old car any longer, and you had to buy a new one? Or you couldn’t insure more than one person to drive it, but had to buy a new car for each person? Well that’s sort of what closed-source software is like”

(Note this is an analogy-in-progress)

Once that’s out of the way, then I can get on to gis, and hence to OSGeo, and all is fine!

Over the last couple of months I have been investigating options for setting up a standby server for PostgreSQL, you know, the sort of magical thing that stops your day/night/week being totally wrecked when, to quote Joel Spolsky  you “go crying to the system administrator and asking piercingly sad questions about why the backup system is “temporarily” out of commission and has been for the last eight months”. As a relative beginner to all of this, if you look in the PostgreSQL documentation, you will get totally overwhelmed and confused (well I did, anyway) as the information you need is spread across several chapters and is dense, even by the PostgreSQL documentation’s standards. So, when I found some documentation that was actually quite clear and concise, and got me through the process without too much angst, I thought I’d record my notes here. Big disclaimer- these are by no means comprehensive or complete. No flaming!

Useful links

• PostgreSQL official documentation
• Easier to follow but less comprehensive guide
• Also useful

Basic Idea
We are setting up a warm standby postgresql server with streaming replication. In the event of a failure of the primary server, the standby server will take over but will not be queryable until that point. Streaming replication reduces the window of data loss between a primary server failing and the standby server being in a position to take over.

Important Notes
Additional network and operating system specific software will also be required to handle the change in IP address and the trigger to move the standby server into read/write mode (see later). The two servers must be as identical as possible. In particular the same version of postgresql must be installed and the servers must have the same architecture (32 or 64bit).

Primary Server Preparation: postgresql.conf

Set up continuous archiving on the primary server by setting the following parameters in postgresql.conf (note that the archive_command line has been split over two lines for readability- in real life that would be one line):

 archive_command = on
 archive_command = 'cp %p

   /location/where/write-ahead-logfiles/should/go/%f'
 wal_level = archive
 max_wal_senders = 5
 wal_keep_segments = 32
 listen_addresses='*'

The location of the write-ahead-logfiles (WAL) should be accessible to both the primary and the standby server, and preferably remote from the primary server (for obvious reasons). The %p and %f symbols are specific to the archive_command and are substituted for the path and the file name respectively when the command is executed.

Primary Server Preparation: pg_hba.conf

Add a connection from the standby server to the main server as follows:

host replication [user] [addressrange] md5

The database name “replication” should not be changed- it’s a pseudo-database connection specifically to allow replication to take place and does not reflect an actual database.

Standby server configuration

Ensure that the location of the WAL files are accessible from the standby server and that a connection can be made to the database on the primary server.

Create a file called “recovery.conf” in the data directory for the standby server. This should have the following parameters as a minimum (the primary_conninfo command has been split over two lines here for readability):

 standby_mode='on'
 primary_conninfo = 'host=primaryhostIP user=databaseuser

   password=databasepassword port=port'
 trigger_file='/tmp/psql.trigger'
 restore_command = 'cp /path/to/WALfiles/%f %p'

Substitute the correct connection details in primary_conninfo, and the path to the remote WAL logs as appropriate. Also substitute operating-system specific file copy commands in the restore_command entry. The trigger file does not have to exist at this point. Its purpose is to tell the standby server when to move into read/write mode (see later). You may also wish to replicate the settings from the primary server’s pg_hba.conf to ensure that all connections will be allowed in the event that the standby server is used.

Take a base backup of the primary server

A base backup is not the same as a database dump. It is a backup of all the files in the postgresql data directory. The location of this can vary. In linux, you can find it by running the following command at a command prompt:

 ps auxw | grep postgres | grep -- -D

In any operating system, if you can connect to the database server (eg with pgadmin3) then enter the following SQL command:

 SHOW data_directory;

You can take a backup of the data directory when the server is running, or when it is stopped. It is simplest to take it when the database server is stopped (and it need not be stopped for very long).

Backing up data directory when postgresql is stopped

• Stop the database in the appropriate way (eg /etc/init.d/postgresql stop on linux or stop the service in windows).
• Backup the entirety of the data directory as found above, using whatever method you like, such as tar.
• Copy the backup file to somewhere accessible to the standby server
• Restart the database

Backing up the data directory without stopping postgresql

• Connect to the database server as the postgres user and issue the following command (where ‘label’ is anything you want to label the backup in the log files with):

SELECT pg_start_backup('label',true);

• Disconnect from the server and back up the contents of the data directory as above
• Connect to the database server as the postgres user and issue the following command:

SELECT pg_stop_backup();

• Copy the backup file to somewhere accessible to the standby server

Restoring the backup to the standby server

• Stop the postgresql service on the standby server by the appropriate means.
• Replace the contents of the database directory with that from the backup, being sure not to overwrite recovery.conf
• Start the postgresql service.

If you watch the log files on the standby server you should see that it will reach a stage where it is successfully connected to the primary server. Errors about the cp command not being able to find archive files are usually non-fatal, as are entries about zero-length logfiles. However, if your log files show other errors, then it is likely that the base backup has not been correctly restored. Try redoing this with the postgresql service stopped to avoid issues.

Note

We have set up a warm standby server in this scenario. This means that you cannot connect to the standby database with psql or pgadmin3 to check that it is working! The log files will simply show a cycle of synchronising with the WAL files from the remote location, and then attempting replication via TCP with the primary server.

How to bring up the standby server

In the event of a failure in the primary server, something (e.g. a heartbeat process) needs to create the trigger_file in the location specified in the standby server recovery.conf file. This can be an empty file, so on linux a simple “touch” command will be enough. Once the standbyserver recognises this file, it will switch to read/write mode. This is clear from the log files, and database connections will be allowed.

The recovery.conf file will be automatically renamed recovery.done once successful recovery has taken place.

In the event that the primary server is reinstated, it will be necessary to synchronise the two databases- the base backup procedure run on the standby server should be sufficient.

Woefully out of date now, here’s a quick run down on the OSGIS 2011 conference, 3rd in that series, held at the University of Nottingham Centre for Geospatial Sciences in Nottingham over the 21st and 22nd of June.

The 21st was a day of workshops, under the banner of Interoperability and the OGC. My new colleague, Matt, and I did a workshop on using Ordnance Survey Open Data and Mastermap with Mapserver and PostgreSQL, using the OSGeo Live DVD. You can see a slightly edited version of the workshop below, or on slideshare. I have to admit that most of the kudos must go to Matt for creating some super scripts to make the initial data processing *much* easier, and to some of my other colleagues for their efforts in styling the data once it’s in Mapserver. The scripts and a small subset of the open data are available here- you’ll have to supply your own Mastermap!

Day Two was all about the talks- and I was impressed by the standard. The focus of OSGIS has always leant slightly towards the academic, so the stand-out talks for me were the ones that demonstrated that you can do real spatial analysis with open source GIS. There were also some very good papers on mapping in the developing world. Two of my ex-colleagues from Oxford Archaeology also did a joint paper showing how the use of open source software has progressed there- that was really good to see- it was nice to know that the baton had been passed on when I left! I gave people an introduction to the OSGeo:UK local chapter, which is also available on Slideshare here, and we had the chapter AGM. It’s extremely gratifying to see the numbers of people willing to hear about, and get involved with, the local chapter. I was going back through the reports I’d given in previous years, and the numbers of people signed up to our mailing list steadily creep up, year on year- we’re now well over the 100 people mark! (BTW, if you’re interested, our website is here).

All in all it was a really good couple of days. Next year the conference will be running from the 4th to the 5th of September, so for anyone that can’t make FOSS4G and wants to give OSGIS a try, now’s your chance!

I’ve been attending the AGI GeoCommunity Conference here in the UK for a few years now- and this year the AGI kindly asked me if I would sit on the working group for organising GeoCommunity 2011. Being completely new to conference organisation, and wanting to get some experience for the glorious day when OSGeo:UK holds FOSS4G in the UK, I jumped at the chance. This year’s event takes place from September 20-22nd, in Nottingham (a departure from previous years, where it has been in Stratford-upon-Avon),  but the working group has met a couple of times already to get things organised. To be honest, the AGI team themselves do most of the hard work, along with the Conference Chair, but the working group decides on things like keynote and plenary speakers, assesses the papers, and decides on really important things like the theme for the party. At the event itself, I understand we have the exciting business of stuffing all the conference bags with flyers, as well as being visible through the event to help people out, moderate sessions, keep speakers to time etc.

Last week we all met in Nottingham to work through the paper selection.  This year, around 80 abstracts were received, for approximately 50 slots. The AGI uses a blind marking process for selecting papers, so we all received the abstracts with the names and any organisational details removed and had to rank them in order. This is remarkably hard to do! It’s quite easy to identify the best and the worst papers, but deciding on the relative merits of (say) papers 53-67 is very difficult. It’s also hard to be objective about this kind of thing- everyone has their own particular likes and dislikes, and their own area of expertise. However, with a working group that represents a diverse range of interests, we did end up with a reasonable consensus at the end of this process. After the blind marking, considerably more paper shuffling took place to get a balanced set of conference streams.  Grouping papers into coherent sessions and balancing out speakers was probably the hardest part of the whole process (yes, by now we knew the authors names!). The whole process was a lot of fun, including the occasional acts of sabotage as papers were (literally) stolen from one stream to go into another.

In a completely non-scientific assessment of the abstracts- “openness” was reasonably popular, although perhaps more from an open/crowd sourced data perspective rather than open source software. In the final programme, however, open source software gets a mention in a number of papers spread across pretty much all of the streams. With hindsight I’m happy that this is the right approach as it avoids ghetto-ising open source solutions rather than presenting them as viable solutions to every day problems. The whole open/crowd-sourced data debate does get its own stream though, as it’s such a popular topic at the moment.

All in all, I have to say I’m in awe of the AGI staff who make all of this look so easy. I’m also really looking forward to the event, as the programme looks really good, and the new venue should be fantastic. If you’re interested, early bird bookings are available till the end of July. For those that know about the now infamous AGI soap-box georant- it’s new location will be superb…

A quick note on some really useful things I’ve picked up this week. They might only be new to me, but I thought I’d jot them down for the sake of future google searches…

Crosstab Queries: These are those clever queries that take a set of records, aggregate them up, and transpose the rows into columns. In Microsoft Access there is a wizard for doing this, but in PostgreSQL you have to do it the hard way.  Fortunately, it turns out it’s not *that* hard! The official documentation is here, and there’s a very good tutorial here, showing you how to circumvent some of the restrictions on the final table, such as adding a total column and row, or adding additional columns.  All I’d like to add to the tutorial is that you must be careful about how you create your basic aggregate query (returning the records that you wish to transpose), because it’s easy to return a crosstab that doesn’t group your results as you’d like them to. Check it carefully!

QGIS Table Manager Plugin: I don’t know how I managed to miss this one, but having found it, I’ve used it intensively this week. Something that’s not entirely clear when using QGIS is that many of the attribute table management options, such as adding, renaming, or deleting columns are only available when using PostGIS tables. This is frustrating, because the options are present when working with other file types, but are not active. Enter the table manager plugin by Borys Jurgiel, which you can install from the third-party repositories. It’s still quite basic, in that it only works on shapefiles (I think)- but it allows you to re-order and rename columns as well as adding and deleting them. For those people that fall back to the perilous route of editing the dbf in a spreadsheet package, it’s a life-saver (What, you’ve accidentally sorted your dbf in a different order to your shp? Oh dear, that’s your attributes attached to the wrong features then).

Open Office Presentation Minimizer: Not quite GIS, though tested on a GIS-related presentation that I’m writing! This takes all the pain out of optimising images in your presentations to keep the file-size down whilst also maintaining image quality. You install it by downloading the oxt and adding it using the Open Office Extensions Manager,  unless you are using Ubuntu where this will appear to install correctly but remain inactive regardless of how many times you open and close Open Office Impress. For Ubuntu, find the package in the standard repositories and install using apt-get or your installer of choice. Do note the american spelling of “minimiz(s)er”, for those of us in the UK. I assume this will also work in LibreOffice as well as Open Office.

A short case study into flexibility, collaboration, and why open source software is so damned cool:

At my new place of employment, we’re doing a lot of work with Ordnance Survey Mastermap data, so one of my colleagues built a quick python wrapper around the ogr2ogr script to easily pop the data into postgresql, or shape file, or whatever support format you like. This is now available on Github (caveat- it doesn’t do change-only updates yet- we’ll keep you posted on that). After a chance comment on the OSGeo:UK mailing list, our friends at Faunalia, who do great things with Quantum GIS, ported the code into a QGIS plugin.

So- in the space of one week, QGIS gained itself a Mastermap loader, through a simple bit of collaboration. Nice work guys! (Nothing to do with me, I was on holiday). Can you get that sort of flexibility, rapidity and collaboration with (insert your favourite proprietary package here)? I doubt it…