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:

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.