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

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 an article on the All Points Blog that goes into more detail about the potential support for PostgreSQL/PosGIS in ESRI products. The caveats to this are listed below, and I have to ask- what’s the point guys?

It will only be in Enterprise ArcSDE- so you’ll need ArcGIS Server Enterprise Basic as a bare minimum, and of the windows products it will only work on Server 2000/2003. So- if you have loads of money to spend on your GIS you’ll be able to use PostGIS- but not if you don’t have thousands to spend.

It will contain it’s only spatial implementation alongside the PostGIS “spatial type”. So that will keep things clear and easy to follow then.

We had this debate ages ago, but it seems as if ESRI really haven’t listened. I guess it doesn’t matter that much to low-end users as there are alternatives but it seems like they are missing the boat. However apparently they are really interested in getting feedback at their non-existent forum so that’s OK…

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.

Before I go any further, I should state that my aims for creating a portable GIS are not so much having a production GIS setup on a stick, because obviously performance and storage are an issue, but it might be useful for demonstration purposes, and I do feel as if the take up of these opensource tools might be more if they were easier to install and came in one handy package. So what I really want to achieve is a single portable package, probably a zip-file, that can be downloaded and unzipped onto a Window pc (they can learn about the joys of linux once we’ve won them over to the basic concept of opensource). No separate downloads, long installation and configuration processes, just a zip file. I do also like having portable packages on my main computer, as if it breaks it’s a lot easier to reconstruct the setup!

The light-bulb moment for me was when Leif told me that he had seen GRASS working on a USB stick using Msys at the recent CAA conference in Berlin. This intrigued me, so I looked into it and it works quite nicely- as long as you use the same drive letter to mount your USB stick to all the time. I still have to figure out how to figure out the drive letter and change the paths, probably in a batch file. Put simply, you download Mingw and Msys onto the stick, follow the instructions for downloading GRASS, edit the install_grass.bat file to change the drive letters, and off it goes.

PostgreSQL was something I had looked into a while ago, been told it wasn’t possible, and given up on. However, the recent 8.2 release appears to have dropped the requirement for a dedicated non-administrative user to run it. If you download the windows binaries, without the installer, you can set it up whilst logged on as the standard user. When you next run PostgreSQL from a different machine, you just have to use the same username as on your installation pc, regardless of whether that user exists on the pc that you are currently using. Once you have PostgreSQL installed, you can install Postgis into the same directory.

Currently there are a couple of gotchas. Most of these programmes require you to set environment variables, which might not be allowed on the particular pc that you are using, and require some command line experience. I still haven’t quite figured out how to get GRASS to work if I change the drive letter. Also, PostgreSQL does indeed run very slowly on a USB stick. And, I haven’t fully explored this to make sure there isn’t a hidden trap somewhere, or security holes that might be exploited. Having said that, if you want a package that can be unzipped onto a pc with a minimum of fuss then I think it’s almost there.

What’s next? Documentation, as always, and the permission of the various programmers that it’s OK to do this. Oh, and comments please, on what else we might include, or words of wisdom if you’ve already tried it.