Category: PostGIS

Every Person in Great Britain Mapped

A follow up to my previous post: Every Person in Scotland on the Map. Winner of the 2016 OS OpenData Award for Excellence in the use of OpenData from the British Cartographic Society.

Full size interactive map.

The mapping process is pretty straightforward, and not accurate. I don’t know where you live. But I can make an educated guess.

I simply amalgamate the two sets of census data from the NRS (National Records of Scotland) for Scotland (2011 census) and the ONS (Office of National Statistics) for England and Wales (2010 census).

Postcodes were then created based on the ONS Postcode Directory, filtering for postcodes that were live in 2011 (which is the latest census data). The postcode centroids were turned into polygons using voronoi polygons.

Then we simply select all of the buildings in a postcode from Ordnance Survey, Open Map product, filtering out most schools and hospitals. Then we put a random point in a random building for each person in that postcode.

I would have loved to include Northern Ireland, but the Ordnance Survey of Northern Ireland do not have an equivalent open building outline dataset, like Open Map from the Ordnance Survey.

Rendered with: QGIS tile writer python script. Processing done 100% in PostGIS.

Copying Rasters in PostGIS

I ran into a process where I wanted to create copies of rasters in PostgreSQL. While seemingly a simple process this took me a bit of work to figure out.

For my workflow I had three rasters, which all have the same size, and I want to load them into the same PostGIS table with three raster geometry columns. I don’t think this will work for different sized rasters since the rid’s will not match.

Three rasters:
raster1
raster2
raster3

Which I want to copy into:
merged_raster

First to create the merged raster table:

CREATE TABLE merged_raster

(

  rid serial NOT NULL,

  raster1 raster,

  raster2 raster,

  raster3 raster

);

Then to add the rid’s. These are the id’s of the tiles that the raster was split into when loading. If your tile size is large enough then you may only have one.

INSERT INTO merged_raster(rid)

(SELECT rid FROM raster1);

Then copying the actual data is straighforward (this assumes the raster column in the raster1 datasets is called rast):

UPDATE merged_raster m

SET raster1 = r.rast

FROM raster1 r

WHERE r.rid = m.rid;



UPDATE merged_raster m

SET raster2 = r.rast

FROM raster2 r

WHERE r.rid = m.rid;



UPDATE merged_raster m

SET raster3 = r.rast

FROM raster3 r

WHERE r.rid = m.rid;

Now I still have an issue that QGIS will not load these layers. It will always load the initial raster column no matter what is chosen.