quick links or things ive learnt this week

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.

database replication

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.

thursday tip day running postgresql without making it a service

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:

archaeogeek tumblog

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. It’s taking me a while to get it properly integrated into the site, but the feed is working and can be subscribed to separately from the main site rss].

portable gis redux

This is an idea that seems to come up every so often- what GIS programmes can you run from a USB stick. Well it appears that the list has just got longer. I’m probably the last person to realise it’s possible to do this, but I was really pleased to see that both GRASS and PostgreSQL can now be run from a USB stick, along with QGIS, XAMPP (inc Mapserver, OpenLayers, Tilecache), and FWTools.

oxford archaeology wfs server

Well, at last it’s OK for me to tell people that Oxford Archaeology now has a WFS server that is accessible from the outside world. The address is: http://mapdata.thehumanjourney.net/cgi-bin/mapservwfs.cgi It’s a standard MapServer setup, and at the moment contains static data about the sites we have worked on over the last thirty years. This is still a work in progress and there are a whole bunch of things I would like to improve (but at least it’s up and out there):

update on postgis connectors for arcmap

I have made a little more progress with evaluating the various free options for accessing PostgreSQL/Postgis database tables from ArcMap. I have to confess that some of the problem was down to my own lack of experience with Postgis! The issue that I had with PGarc was that it would fail with an error if you had deleted tables from a database. It turns out that this is because deleting tables using the PostgreSQL “DROP TABLE” syntax does not remove it’s reference in the “Geometry_Columns” table.

arcgis connectors for postgis

This post is a discussion of my experiences with a couple of ArcGIS connectors for Postgis. To me, a reliable Postgis connector would be so useful, as we simply don’t have the resources (or the inclination) to purchase the equivalent proprietary products. We are also looking for a solution that will provide a flexible backend for all sorts of interfaces, such as MapGuide Open Source, UDig, and so on, alongside ArcGIS.

minor triumphs major hassles

In my spare time/lunch times I’m in the middle of a major project at the moment, to update our site database. Without going into the gory details of how it ended up in three separate, totally unlinked databases, it is supposed to document the archaeological sites we’ve worked on since the 1970’s, and to help with the administration of project archives, the location of finds within our finds store and so on.