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.