First Scottish QGIS user group

Together with the good folks at thinkWhere I have been organising the first Scottish QGIs user group meeting.  It is happening on 19th March 2014 in Stirling at the Stirling Management Centre.  Doors open at 9:30 with a 10:00 start.  Registration is through Evenbrite ( and there are 50 places available working on a first come, first served basis.

Details on how to get to the Centre are available here (

The agenda will be published a bit closer to the time once speakers have been finalised.  If you would like to present let me know as it would be good to have a mix of input to the day.  There are both 20 minute and “lightning talk” 5-10 minute slots available.

A big thanks to thinkWhere for hosting this first QGIS event in Scotland and QGISUK for enthusiasm and passion.

I am in the process of rendering a series of map tiles based on the OS OpenData products using the script (and an updated version that uses all cores on the machine to speed things up).  The different raster products are rendered at different scales and then displayed using LeafletJS and OpenLayers applications as simple demonstrations.

The following command generates the tiles for the zoom levels I need:

python -z '7-9' -e -p raster -r average osvmd.vrt osvmdtiles/

Continue reading

OpenLayers GeoServer WMS EPSG:27700

A sample page demonstrating an OpenLayers application loading a GeoServer WMS with data in British National Grid (EPSG:27700).

        <title>OpenLayers GeoServer WMS</title>
        <script type="text/javascript" src="openlayers/OpenLayers.js"></script>
        var map;
        var bounds = new OpenLayers.Bounds (300000, 700000, 400000, 800000);
        var attr = "Contains Ordnance Survey data. (c) Crown copyright and database right 2013.";
        var os_options = {
                format: "image/jpeg",
                layers: "opendata:OSVMD"

         Continue reading OpenLayers GeoServer WMS EPSG:27700

GeoServer WFS-T EPSG:27700

A sample page demonstrating the GeoServer WFS-T capabilities using data in British National Grid (EPSG:27700) in an OpenLayers application.  Customised from the template in the GeoServer installation.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "">
<html xmlns="">
    <link rel="stylesheet" href="/openlayers/theme/default/style.css" type="text/css" />
    <style type="text/css">
        body {
            margin: 1em;
        #map {
            width: 600px;
            height: 600px;
            border: 1px solid black;
    <script src="openlayers/OpenLayers.js"></script>
     Continue reading GeoServer WFS-T EPSG:27700

Image Optimisation – Comparison Table

Product File Size  No. Files  Uncompressed  Optimised    Optimised+Overviews  Total Storage
OS VML Col 0.3-10MB  562  184MB      2-30MB  3.12GB
OS VML BW 1-3.5MB  562  184MB 0.3-5MB    0.3-5MB  1.05GB
OS VMD Col 0.2 – 2.2MB 80 67.2MB 0.8 – 2.2MB   1-4.5MB  231MB

There are number of things to look at in terms of optimising images for web mapping:

  1. File size and total storage required
  2. Draw performance
  3. Image quality Continue reading Image Optimisation – Comparison Table

Optimising Raster For Web Mapping (on Linux)

OS raster colourThe previous post showed how to process panchromatic and colour TIFF images on Windows to optimise them for web mapping.  Here I’ll show you how to do the same thing using bash scripts on Linux.  This assumes you have GDAL installed (gdal-bin and python-gdal on Ubuntu/Debian/Mint).  Continue reading Optimising Raster For Web Mapping (on Linux)

Optimising Raster For Web Mapping

OS rasterI have been working through an exercise to find alternatives to storing raster data in an expensive proprietary database format. We have a lot of raster data in various formats (ECW, jpg, LZW and Packbits compressed TIFF, uncompressed TIFF, GeoTiff) and of different types (historic black and white mapping, full colour aerial photography, panchromatic (greyscale) aerial photography, full colour base mapping tiles at different resolutions). Continue reading Optimising Raster For Web Mapping

pgRouting on Windows 7

pgrouting-networkDrive time isochrones with pgRouting and PostGIS on Windows 7 Pro.  I am using Postgresql 9.0, 9.1 and 9.2, PostGIS 2.0.3 and pgRouting 1.0.7-dev.  Anita Graser has some excellent pgRouting resources on her blog  and I used them in conjunction with Jo Cook’s pgRouting with ITN guide to load and build my network dataset.

One of the problems I had while following this post was that I had more than the three airports she used in her example and to manually update the table 116 times for each play park in the county was not an option.

So, how to write a looping function in Postgresql to loop through all the play parks and calculate all the drive times for each facility in one go?

Postgresql docs are a good place to go and this is what worked for me in the end.  Once the table is complete finish up using Anita’s how to.

-- 1. create calculate playpark costs function
-- NOTE: function returns an integer representing the next play park nearest node id
-- NOTE: loops through every nearest_node, calculating drive times and inserting records into the table
FOR nn IN SELECT nearest_node FROM osplayparks_pts LOOP
 INSERT INTO catchment_pp (
 (SELECT sum(cost) FROM (
 SELECT * FROM shortest_path('
 SELECT objectid AS id,
 source::int4 AS source,
 target::int4 AS target,
 traveltime::float8 AS cost
 FROM angus_routes',
 nn, -- this updates each time with a new value for nearest_node
 false)) AS foo ) AS cost
 FROM vertices_tmp);
$$ LANGUAGE 'plpgsql';

-- 2. run calculate playpark cost function
SELECT * FROM insertPlayParkCosts();

Installing pgRouting on Windows 7


This quick guide outlines the steps I took to get Postgresql, PostGIS and pgRouting installed on Windows 7 Pro.

1. Install Postgresql

Download the installer from, double click to start the installer and accept most of the defaults except for the port to run the server on.  I installed 9.0.13, 9.1.9 and 9.2.4 running each server on a different port.  So 9.0.13 is running on 5432, 9.1.9 on 5433 and 9.2.4 on 5434.

The installers install the correct version of pgAdmin III for each version of the database and it is recommended to use the right pgAdmin III with the right Postgresql.  This ensures database integrity and interactive functionality suitable for each version.  For Postgresql 9.2 use pgAdmin 1.16.1, for Postgresql 9.1 use 1.14.3 and for Postgresql 9.0 use 1.12.3.

2. Install PostGIS

Download the installers from OSGEo.

For Postgresql 9.0 install and select the correct instance of Postgresql on port 5432

For Postgresql 9.1 install and select the correct instance of Postgresql on port 5433. I could have installed a later version of PostGIS on 9.1 but I needed 1.5.5 to work with AstunTechnology’s PortableGIS Loader! (See archaeogeek’s blog)

For Postgresql 9.2 install and select the correct instance of Postgresql on port 5434

3. Install pgRouting

For the development versions of pgRouting visit Boston GIS and get 1.0.7dev for Windows 32-bit and PostgreSQL 9.2 (I am running 32-bit Windows).  pgRouting contains the following features:

  • Dijkstra algorithm – Shortest path algorithm, which named in honor of Prof. Dr. Edsger Wybe Dijkstra who has invented it
  • A-star (A*) algorithm – Shortest path algorithm using heuristical function
  • Driving distance – area person can cover in certain time from start point using road network
  • TSP Travelling Salesman Problem solution
  • Shooting star (Shooting*) algorithm – Shortest path algorithm for real road networks with turn restrictions, traffic lights and one way streets.

To install and enable pgRouting (read the README) copy the contents of /lib (.dlls required for problem solving) and /share/extension (.sql files to enable pgrouting in the database) to the C:\Program Files\PostgreSQL\9.2\lib\ and C:\Program Files\PostgreSQL\9.2\share\extension folders respectively.

Then, using the psql prompt or pgAdmin III, do:


Now you should have a pgRouting-enabled PostGIS database.

There are older version of pgRouting available to download from Github.  You’ll need to extract the zip archive and run the various SQL files to install the required routing functionality as well as copying the .DLL files to the correct folder as above.  Boston GIS have also release dev versions of pgRouting 2.0.0 which I have not tried.

Exporting Data from Oracle to PostGIS

Traditionally exporting from Oracle to PostGIS involved an intermediate step Oracle to Shapefile and then Shapefile to PostGIS. But this means that field names longer than 10 characters in Oracle get truncated in the conversion to Shapefile. And using the QGIS or PgAdminIII shp2pgsql gui has no easy way to set the schema or SRID other than manual intervention. I guess it could be run from the command line though…

Using Safe’s FME you can go directly from Oracle to PostGIS. But there is no way to define the destination schema in the PostGIS database. The PostGIS writer expects there to be a schema with the same name as the Oracle schema in order to complete the extraction and translation. See!topic/fmetalk/zQsUUJNR5h8

So, open-source here we come. ogr2ogr can read and write to a number of different formats including Oracle (this uses the proprietary OCI.dll). On Windows install OSGeo4W with GDAL/OGR. Run the setup.exe and “Advanced Install” to install the gdal-oracle10g package. See

The command to issue at the command line is (all on one line):

ogr2ogr -a_srs <srs> -overwrite -f "PostgreSQL" -nln <postgis schema>.<postgis table> PG:"host=<postgis host> user=<postgis user> password=<postgis password> dbname=<postgis database>" OCI:"<oracle user>/<oracle password>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port number>)))(CONNECT_DATA = (SID =<sid name>))):<oracle schema>.<oracle table>"

<srs> = in the EPSG format e.g EPSG:27700

This uses ogr2ogr to connect to an Oracle database, pull a table from a schema and write it to a defined schema in PostGIS database.