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.

The event visualisation plugin for QGIS is a way of adding tabular geographic data to QGIS in a similar way to the “add XY data from table” option in ArcGIS. I’ve only tried the windows version so far but it is cross-platform.

You download it from here

Extract the zip file and move the files to the following locations:

• Copy plugins/libevis.dll to C:\Program Files\Quantum GIS\plugins
• Copy the imageformats folder to C:\Program Files\Quantum GIS
• Copy the sqldrivers folder to C:\Program Files\Quantum GIS

Ensure the plugin is installed and activated by checking plugins\plugin manager in QGIS, and ticking the checkbox next to EVIS. If it is not present then you haven’t installed it correctly.
In plugins/eVIS choose the data connection option.
Choose access as the database type, and browse to the database in the database name box.
Hit the “connect” button to test the connection.
In the SQL query tab, type the SQL query that defines the data that you want. If you don’t know how to write SQL, make the query in access and choose the SQL View, then copy the query into QGIS. If you want to display the data from one table, then the syntax is “select * from your_table”.
Once you have the right query, hit the “run query” button.
Choose a name for your layer, and choose the fields that represent the x and y coordinates for your data.
Click “OK” to see the data displayed on the map

Never apologise for delayed posts… this is a Sunday Tip Day post, not a Thursday! Anyhow…

I just found a super little cross-platform utility that takes shapefiles and dumps them to a variety of text-based formats. Download it here, and simply unzip it to use it.

There isn’t much documentation, but basically your options are to download to gpx or spreadsheet. The following gives you a simple delimited text file with the coordinates and values from your attribute table:

./shp2text -–spreadsheet /path/to/your/shapefile.shp > /path/to/output.txt

Bob is indeed your mother’s brother.

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!