Computing, GIS and Archaeology in the UK

From the postgis maestro himself comes a handy tip for mass loading shapefiles of identical schemas into postgis:

First, get the table schema into the database, by loading a small file, and then deleting the data. We delete the data so we can loop through all the files later without worrying about duplicating the data from the initial file:

shp2pgsql -s 3005 -i -D lwssvict.shp lwss | psql mydatabase psql -c “delete from lwss” mydatabase

Then, use the shell to loop through all the shape files and append them into the table:

foreach f (*.shp) foreach? shp2pgsql -s 3005 -i -D $f -a lwss | psql mydatabase end

Note the “-a” switch to tell shp2pgsql we are in append mode, rather than the default create mode.

Add a spatial index, and we’re done: psql -c “create index lwss_gix on lwss using gist (the_geom)” mydatabase

Via Drawn, a fantastic collection of paintings with extra visual clues for geek-types. Ace.

It’s possible to get pretty good help on postgresql when using the command line programme psql:

\? shows you all the \ series commands
\d shows you all tables, \c connects to another database, etc…
\h shows you the sql syntax
\h COMMAND shows you the syntax for that command

and, just in- here’s a good postgresql cheatsheet!

There’s the beginnings of an interesting discussion here and here about using archaeological data in Second Life, or using Second Life as a teaching resource. I’m more interested in the first idea, of publishing your excavation as an “experience” rather than as some data and a report. I hadn’t even realised that you could link to external datasources in Second Life, but apparently you can, and people are already using this to release their own GIS projects.

I can see some conceptual issues here, as archaeological sites are part of a landscape rather than a single entity with no relation to it’s surroundings, and I don’t see how you could reproduce that in Second Life without turning it into a sort of Google Earth. However, skirting around that issue, it’s certainly a novel way of displaying data, and of leveraging a “virtual-reality”-like interface without needing to spend lots of time and money in developing it. Bring it on!