Open Source Computing and GIS in the UK

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.

Part of my fabulous new plans for portable gis (which will be revealed soon-ish) involve including a database replication option. This might or might not work on the USB drive, but it needs to be open source, portable, and connect to postgresql at the very least. I have been looking at a couple of options for this, without a vast amount of success, it has to be said.

The packages that I have tried are: Daffodil Replicator, dbreplicator (a fork of daffodil replicator), Symmetric-DS, and db -connector for Funambol. Of these, daffodil and dbreplicator seem like the best bet as they will attempt to do some conflict resolution (eg when the same record is added/altered in both databases). They are also java-based, so should be nicely configurable. Symmetric-DS works well, but doesn’t do conflict resolution, so that kind of rules it out for me. I don’t really know about db-connector for funambol as the documentation that I found was quite out of date and I didn’t get very far with it.

So, daffodil replicator and dbreplicator…

They are quite easy to set up, although the documentation in both cases assumes rather more end-user knowledge than perhaps they should do. Basically you find an appropriate jdbc jar file for your database, grab log4j.jar from somewhere, and tell daffodilreplicator/dbreplicator where to find it, by setting appropriate paths in batch files. Then you start the publication server and set up the details for your “master” database, and then do the same for the subscription server and the “client” database. Both are clever enough to translate between different database types, such as postgresql and mysql, which in some cases would be really handy.

My difficulty with both flavours of the package came when creating the “subscription”, ie telling the client database where to go find the master database. In neither case have I been able to sucessfully set this up, because of various errors. Weirdly, both flavours of the package give me an error in my log file about the first ever subscription that I tried to set up, despite having reinstalled, deleted everything I could find, and started from scratch several times.

The documentation in both cases hasn’t been good enough to resolve my issues, and neither have the forums. Actually, I’m still waiting on dbreplicator, which seems to be a little more active, so I should give them the benefit of the doubt for a couple of days.

I can see a great need for this kind of setup, assuming I can get around these initial teething troubles. So, my question is, has anyone successfully set up any of these packages on windows (sorry, but it needs to be windows at this stage), or does anyone know of any other packages I should try?

If I do manage to get things sorted I’ll post a detailed how-to…

PostgreSQL from 8.2 onwards can be run easily from the command line in windows, without setting up as a service.

Go to your postgresql/bin folder and at a command line type:

pg_ctl start -D location\of\your\data\folder (as specified in initdb) -l logfile

This should output a notice telling you whether the server has started up correctly. It also saves output to a logfile in the bin folder. If the server starts without incident, open another command window at the same location and type:

psql -d yourdatabase

This will allow you to use PostgreSQL as a command line tool.

To stop the server, type (in your second command window):

pg_ctl stop -D location\of\your\data\folder

A post from Daily Cup of Tech a while ago inspired me to add a “tumblog” to my site, for short-format posts on tips and tricks. I will be posting to this regularly to build up a library of code snippets, linux and windows tips and archaeological information.

Read more »