Computing, GIS and Archaeology in the UK

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:

1. Ensure that the table you are trying to view has a primary key. Error messages if you don’t do this might vary, or be non-existent, but the end result will be that your data won’t display on the map, or won’t be editable.
2. Ensure that there is an entry in the geometry_columns table for the table you are trying to view. The geometry_columns table is specific to postgis and contains metadata about your tables. If the geometry of your table was created using the addgeoemtry function then this will be filled in, but otherwise it won’t. The function probe_geometry_columns() may fix this if run at the psql command line, but sometimes it doesn’t. But, you can fill in records in the geometry_columns table in the normal way. The table contains the following columns:

• F_TABLE_CATALOG (this is left blank for postgresql- it’s an oracle thing apparently)
• F_TABLE_SCHEMA (the schema of your table- if you haven’t set this it’s likely to be the default PUBLIC)
• F_TABLE_NAME (tha name of your table)
• F_GEOMETRY_COLUMN (the name of the geometry column in your table)
• COORD_DIMENSION (the number of spatial dimensions of your geometry- 2 or 3, or 4 if you’re really ambitious)
• SRID (the EPSG code for your projection, eg 4326 for lat/long, 27700 for British National Grid)
• TYPE (the type of spatial object held in your table, eg point, line, polygon etc etc)

The moral of the story- always include a primary key in any tables you expect people to select or edit data in, and always add an entry in geometry_columns if you want to display your spatial data on a map.

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. When I tried to close the document after some time messing around with it, I got a message telling me that I had tried to make a change to the normal template but that I didn’t have permission to do that.

In a bit of a light-bulb moment, I temporarily set Domain Users on the pc to be members of the local Administrator group. That worked- suddenly all the spelling and grammar options were available. Now I just have to dial back that security setting until I can allow people to check spelling and still keep my IT boss happy!

I was quite excited to find that you can use openstreetmap data in your own GIS environment by loading it into PostgreSQL. To me, this makes it much more useful, as I can now begin to use it as an alternative to costly data from the Ordnance Survey. The procedure took some time (mainly due to trial and error), but the following worked for me in Ubuntu:

1. Install osm2pgsql by downloading it from here using subversion
2. The readme.txt file that accompanies it suggests installing a selection of libraries too, which you should be able to install using apt, with the exception of geos
3. Check to see if you have an existing copy of libgeos and download and install from source from here
4. To ensure that libgeos is reachable, find out where it is installed using which geos-config and edit /etc/ld.conf as root to include the path to it (run ldconfig as root afterwards)
5. Install osm2pgsql by running make from the source directory that you downloaded in step 1
6. As your postgresql user, create a new database called “gis”
7. Download the latest planet dump (for UK users, try here for the UK extract)
8. As the postgresql user go to the osm2pgsql source directory from step 1 and run ./osm2pgsql -d gis /path/to/planet/dump.osm.bz2
9. In psql create a spatial index on each of the tables in the gis database using: create index index_name on table.name using gist (geom_column); where you need to find the table names and the name of the geometry column in each case (it’s probably “way”)
10. Some gis will need a primary key and an oid column for each table too.

Note 1: This is a work in progress. I am now trying to figure out how to use this data in both my desktop and web-based GIS. So far I have been able to make a connection to the data using pgarc in Arcmap, but it seems to fail because it wants an ansi encoded database rather than utf8. I can’t make a connection with ziggis at all at the moment. I can make a connection using ogr in mapguide opensource, but it fails when I try and visualise the data, and I couldn’t get the postgis connector to work when I tried it. I have, however, recently found some more information about this so I will keep trying…

Note 2: I couldn’t get the standard osm2pgsql package for ubuntu to work at all, only the source package worked. I think this is to do with the geos library…

How to transform the geometry of a postgis table from one projection to another:

postgisdbf=# UPDATE your_table SET the_geom = TRANSFORM(the_geom, 4326);

Where the_geom is the name of the field holding the geometry, and 4326 is the EPSG code you wish to transform to