Introduction

A PostGIS data base provides a single platform for sharing spatial data on a network and for conducting sophisticated spatial operations on large sets of data using spatial SQL statements. The database can be connected to R, Geoserver and other programs in order to design sophisticated spatial anlayses and web mapping applications. The database also provides a simple mechanism to load spatial data into a desktop GIS for analysis using the desktop GIS capabilities.

These are some brief instructions showing how to pull down subsets of the data as files for processing locally in a desktop GIS. This may be preferable to providing site data in the form of a set of individual files.

Connecting to QGIS

Either Arc or QGIS could potentially be used in this way. However at the moment there is no driver installed to connect directly to PostGIS on the version running in the PC labs. The files saved by QGIS can however be opened in Arc after being downloaded and processed in Arc if required.

Open QGIS and then right click on the postgis (elephant) icon in the browser panel. This prompts you to set up a new connection.

Connecting to the data base

The connection details to fill in are the following

Name: Fill in any name you want to call the connection

Service: Leave this blank

Host: 172.16.49.31

This is the network address for an internal connection. This will only work on campus, although off campus access could be arranged for some trusted users.

Port: 25432

This is the port that postgis runs on.

Database: bu

This is the name of a database which holds some useful data

Username; student

Password; student

This provides access which is limited to reading the data, but not altering it. Some data is hidden from student users.

Test the connection

If all is correct you should see this message when testing the connection.

Save the details and then connect to the data base.

Tables shown in browser panel

You should now see a list of the tables in the database in the browser panel. These table are all available layers and they can be added to the map canvas simply by clicking on them. However, be careful when doing this, as clicking on a large layer will result in loading all the layer at once into the canvas.

This can be slow in the case of very large layers for the whole UK, and, in the cae of extremely large global layers you could possibly freeze the GIS for some considerable time, although eventually all the data will be loaded. Think of this as the equivalent of downloading an entire large file from a website and consider how long this might take, so be careful with it. Small layers, of the size normally handled in a desktop GIS, will load as quickly as from disc. All the layers in the BU database should load within a few minutes, but larger layers will be slower.

Clicking on a layer will load it into the canvas (with some randomly selected colours for the lines, points or polygons). Raster layers are not shown in this browser.

I chose the Natural England layer for special protection areas which loaded in about 10 seconds.

Zoom in

Having a fairly light layer like this loaded is a good idea as it sets up the QGIS canvas to the right general area. You can now zoom into a well known site like Arne, and use the Open Layers plugin to load a Google or OSM basemap from the web.

Opening the data base manager

Now go to the DB manager option on the top menu and open up the connection in the manager. This provides a much ore complete interface to the data base than the browser panel with options to run SQL queries, which is a more spphisticated way of loading data.

Looking at the attibute tables

You can now browse through the tables and look at the data contained in the attribute columns of each.

Preview

The preview tab provides a view of the data prior to loading it into the map canvas. Do be aware that previewing very large data layers may be slow.

Previewing raster layers

Raster layers are marked with an icon that consists of coloured pixels. There are only a few raster layers in this particular data base at the moment. You should never preview the whole raster layer if an overview of the same raster exists. Overviews are marked with o_. Find those for previewing.

For example this is an overview of the national SRTM layer.

Lidar data overview

Clicking on 0_100_dsm_arne_hh will show an overview of the high resolution (2m) dsm_arne_hh 2m lidar raster that has been loaded into the data base. There are some missing areas, which were also missing in the orignal data that was downloaded from the site. Rater tables can hold disjunct, non continuous, data (in this case only the areas around Arne and Hengistbury). This is useful to avoid wastefully loading large amounts of high resolution data into the database that will not be used. However if the canvas isfocussed on a site without any data you won’t get results of course.

Downloading the raster data for Arne

Clicking on the raster layers without the 0_ prefix will load them straight into the canvas. Only the data that corresonds to the area showing on the canvas will be loaded at this point However if you then zoom out from the initial region (or click when zoomed out) more data will begin to be loaded, which will slow up the GIS. As the raster in the database has not been prepared for rapid viewing through pyramiding and is being loaded from the server this must be avoided. The fact that PostGIS can handle very large layers does not imply that they can all be downloaded and visualised quickly without additional steps being taken to prepare them for faster visualisation such as forming WMS layers in a Geoserver.

Saving the data locally

In most cases the raster data processing that students will conduct only needs a subset of the larger data set held in the data base for the site of interest. So, once the area data has been loaded into the canvas the next step would be to save this subset locally for processing in the usual manner. The server would then be disconnected to prevent any slow downs when the the area is zoomed out. Right click on the layer and use save as to save the subset to a file on disk after setting the area to the map view extent.

Adding more data for the area

Any of the other layers that hold data for the area zoomed into on the map canvas can be added and saved locally as a subset in the same way through setting the extent saved to the map canvas. There are many other more spophisticated methods for subsetting layers that can be achieved through running spatial queries, but the simple approach outlined here provides a quick way to get some smaller local files for processing a specific area from the larger data sets held on the server.