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.