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.

I learnt something new this week (week 2 in my new job)- it’s probably not new to everyone else, but just in case someone is interested I thought I would document it… Scenario: You have some Ordnance Survey Mastermap data in a PostgreSQL database imported using OGR2OGR. You wish to display it using Mapserver. The key fields you are interested in for styling your data are “descriptivegroup”, “descriptiveterm” and “make”. These dictate the actual detail about the styling, such as the type of building or type of land.

This might seem slightly off-topic, but hey, it’s about Computing, GIS and Archaeology as far as I’m concerned… namely the likely demise of Yahoo’s Del.icio.us (yes, I know the current status is “for sale” but three days ago they were going to close it so there’s a measure of uncertainty there, I think you’ll agree). I’ve been using this since 2005 as a place to chuck all my useful tips and tricks on how to do “stuff”, or interesting things I’d like to read later.

Aware that there haven’t been Thursday Tip days for a couple of weeks, or indeed anything else in the way of blog posts (follow-up post coming along soon)- here’s a real quicky: If you want to be able to connect to your postgresql data using an external programme (such as Open Office Base, QGIS, gvSIG or Mapserver) AND be able to properly view/select and edit the data, you will need to do the following:

The event visualisation plugin for QGIS is a way of adding tabular geographic data to QGIS in a similar way to the “add XY data from table” option in ArcGIS. I’ve only tried the windows version so far but it is cross-platform. You download it from here Extract the zip file and move the files to the following locations: Copy plugins/libevis.dll to C:\Program Files\Quantum GIS\plugins Copy the imageformats folder to C:\Program Files\Quantum GIS

Never apologise for delayed posts… this is a Sunday Tip Day post, not a Thursday! Anyhow… I just found a super little cross-platform utility that takes shapefiles and dumps them to a variety of text-based formats. Download it here, and simply unzip it to use it. There isn’t much documentation, but basically your options are to download to gpx or spreadsheet. The following gives you a simple delimited text file with the coordinates and values from your attribute table:

This is an “interesting” one- particularly if you manage a lot of windows pcs in a domain, so you have domain users and local users on your pc… I started getting complaints from people that the spell-checker in word didn’t work. What they meant was that the spelling and grammar options simply weren’t available to them. I checked that the language was set, and found that it wasn’t, and not only that, but it didn’t seem to persist if I did set it, even if I set it as the default.

Apologies for the delay- it’s been a roller-coaster week at Archaeogeek Towers due to family health issues. Hopefully it’s getting sorted now though. In the process of preparing Portable GIS, I needed to change a string in multiple files of multiple formats within multiple folders. I looked at various windows- based options, most of which had a charge associated with them, but one of my colleagues suggested a linux approach.

How to mount a USB stick in Ubuntu server using the command prompt only: Do a sudo tail -f /var/log/syslog and plug the usb drive in. Look for lines like these: Feb 18 12:58:32 shuchi kernel: [17192272.616000] sda: assuming drive cache: write through Feb 18 12:58:32 shuchi kernel: [17192272.616000] sda: sda1 Make a directory in /media named usbdrive. Mount it like this: sudo mount /dev/sda1 /media/usbdrive To unmount before ejecting: sudo umount /media/usbdrive

I’ve been a big fan of google desktop for a while now, not so much for the search options but because I like the gadgets. There’s nothing like the Remember The Milk gadget for making you feel guilty about tasks you’ve put off, and I’m currently addicted to the Twitter gadget. However, for some time, Google desktop refused to let me click any web links, access the options, view my google calendar, open links in google chats etc.