Great Circle Flight Lines in PostGIS

There is an excellent post by Anita Graser about creating Great Circles in PostGIS.

However as of PostGIS version 2.1 this can be done in a different (better) way, using the geography functions.

PostGIS Great Circles

For more information about geography, see:
Introduction to PostGIS – Geography

This allows us to create the great circles without having to add in a new projection.

So we first need to create our three tables in PostGIS:

The data itself can be found at:

We can then load our data through PGAdminIII. You can just right click on a table and select import. Remember to not load the “uid” column, because it is our primary key which will be populated automatically and not in the original data. You will also want to define it as the primary key.

Now we need a geometry column in the airports dataset.

We can define our geometry in the airports dataset from the Latitude and Longitude columns.

And create a spatial index.

Then we can create a flights table.

This table will have a source geometry and a destination geometry along with a few other attributes. I added a primary key to this table as well.

To filter out a specific airport, for example Honolulu we use the “Airport ID”.

Then we add in the actual line geometry column.

And populating the great circle geomtrey:

This is works great to an extent, but QGIS still has some trouble with lines that cross the date-line.


We can fix this using a Pacific centered projection like EPSG:3832.


We can either set QGIS to the projection. Or we could have set our geometry the this projection initially when creating the flight lines.

Thanks to:
The World Is A Village – PostGIS: using latitude and longitude to create geometry

Helsinki Airport the Gateway to the East

The shortest route between two points on the earth is a great circle. This is a straight line on a globe, but ultimately looks like half-circle when projected.

Due to Helsinki Airports location, it is an ideal hub for East Asia travel. Starting at practically any European city and travelling to Far East Asia via Helsinki. The final route will almost be a straight line.

Helsinki Flights

Population of Scotland Mapped

One random point on the map for each person within a postcode in Scotland.

OS Code-Point Open points.
Voronoi polygons from the postcodes.
Join 2011 Scottish Census postcode population counts to Voronoi polygons.
Clip the resulting polygons to the Scottish coastline (using PostGIS for time saving).
Intersect the lakes out of the resulting polygons.
Random point in polygon into the postcode Voronoi polygons (minus lakes), using the census counts.

Population of Scotland Mapped

An easier approach would have been to use the NRS supplied postcode areas for Scotland mentioned in previous posts. A better display of this data would be through a web mapping environment, which is working on my home environment but lacking hosting.

Scotland Gender Split

Based on 2011 Census data. We can see a clear majority of the population is Female.

The raw numbers are:
Population total:
Male total:
Female total:
Male total %:
Female total %:
Top 5 Male by %:
Shetland Islands – 50.77
Aberdeenshire – 49.52
Orkney Islands – 49.49
Aberdeen City – 49.42
Na h-Eileanan an Iar (Western Isles) – 49.37
Top 5 Female by %
West Dunbartonshire – 52.40
North Ayrshire – 52.37
South Ayrshire – 52.36
East Renfrewshire – 52.34
Inverclyde – 52.14

And the split by local authority:

Scotland Gender Split

X Percent of the Population of Scotland Lives Within Y Miles of Glasgow

I have often heard that X percent of the population Scotland live within Y miles of Glasgow. With the X and the Y varying between claimant.

This is a pretty easy question to answer, using the 2011 Scottish Census population results and the Census Output Area Population Weighted Centroids. Then we get the extents of Glasgow City Council from OS Boundary Line.

The results are:

Pop. Count: %
Scotland 5295403 100
Glasgow 593245 11.2
25 km 2002431 37.8
50 km 2839583 53.6
50 miles 3776701 71.3
100 km 4201860 79.3
100 miles 4483330 84.7

Pretty interesting results, especially the within 50 miles query.

To see how these boundaries look on a map:

Population buffers around Glasgow

A few caveats:
We are using the population weighted centroids, which will produce some minor inaccuracies, but is a very good generalisation.
Also we are using euclidean buffers on the British National Grid plain, so these are not geodesic buffers. The difference will likely be small at these distances.

UK Postcode Polygon Accuracy Comparison Part 2

So we have seen from the previous comparing the raw polygon accuracy between Voronoi generated polygons and NRS generated postcode polygons: Results.

The physical results are interesting, and a visual examination can provide a useful overall comparison, but how does this actually impact me?

I have a CAG from GCC and I just want to attach a postcode to it. How different will my results be between a true postcode boundary dataset from the NRS, and a generated Voronoi dataset from the OS?

I’m glad you are still with me, it might be useful to explain how postcodes actually work in this context:

Lets take a postcode of G31 2XT how does it break down?
Area: G
District: G31
Sector: G31 2
Unit: G31 2XT

So then we can compare how an actual address dataset, like the Glasgow CAG, spatially joined to two postcode datasets compare:

Assuming the NRS dataset is correct (a good assumption) how accurate is a postcode based on an OS Code-Point Open generated Voronoi polygon based on Glasgow City Council residentially classified properties as of 16/11/2014:

Total number of properties:
245096     100%
Correct Area:
245096     100%
Correct District
243650     99.4%
Correct Sector
240956     98.3%
Correct Unit
174344     71.1%

We can see that up to a sector level a Voronoi polygon can produce an extremely accurate results. A visual comparison of how this plays out in Glasgow can be seen here, with the legend best read from the bottom:

UK Postcode Comparison

GIS DAY – Post your Workstation

In honor of GIS day I have posted my current home GIS workstation:

CPU: Intel Xeon E3-1230 V3 3.3GHz Quad-Core Processor
CPU Cooler: Cooler Master Hyper 212 EVO 82.9 CFM Sleeve Bearing CPU Cooler
Motherboard: Gigabyte GA-H97M-D3H Micro ATX LGA1150 Motherboard
Memory: Crucial Ballistix Sport 8GB (1 x 8GB) DDR3-1600 Memory
Memory: Crucial Ballistix Sport 8GB (1 x 8GB) DDR3-1600 Memory
Storage: Crucial MX100 512GB 2.5″ Solid State Drive
Case: Silverstone TJ08B-E MicroATX Mini Tower Case
Power Supply: Corsair CSM 550W 80+ Gold Certified Semi-Modular ATX Power Supply
Monitor: Asus VX279Q 60Hz 27.0″ Monitor

My GIS Battlestation

I love the SSD for speed and the CPU is more that enough for my use.

And for a GIS server I use:
HP 704941-421 ProLiant Micro Server
Running Xubuntu. 3x3tb in a Raid 5 array, with 8gb extra memory.