George MacKerron: code blog

Imagine you want compare various locations in terms of the availability of a certain type of environment, such as fresh water.

You might want to use a measure of the proximity of that environment — such as the nearest neighbour distance.

You might want to use a measure of the quantity of that environment in the vicinity — such as the proportion of land within a specific radius that is of that type.

Or you might ideally like a measure that combines both of these: one that incorporates the quantity of that environment, but gives greater weight to areas that are nearer, and lesser weight to those that are further away.

In that third case, what you probably want is a kernel-weighted proportion.

Read the rest of this entry »

The secret to getting the MySQL gem to install and function with Ruby 1.9.1/1.9.2 on Snow Leopard is:

• Install MySQL using the 64-bit .DMG package installer from dev.mysql.com
• Install Ruby using RVM (that’s Ruby 1.9.2 or 1.9.1-p378 — at the time of writing the latest 1.9.1, p429, is buggy)
• Add these to lines to ~/.bash_login or ~/.bashrc:

export PATH="/usr/local/mysql/bin:$PATH"
export DYLD_LIBRARY_PATH="/usr/local/mysql/lib:$DYLD_LIBRARY_PATH"

• In a new shell (Terminal window), type gem install mysql as normal.

I’m posting this mainly as a record for myself, having wasted a lot of time in the past trying strange incantations from comments on various other blogs posts.

Here’s a less generic and slightly different nearest-neighbour function based on Regina’s generic nearest-neighbour function at Boston GIS.

It follows the same basic idea of using series of enlarging search radii to restrict distance calculations to a manageable subset of things-that-might-be-near. The difference is that it uses a geometric progression of sizes (x, x * y, x * y^2, x * y^3, ...) instead of an arithmetic one (x, x + y, x + 2y, x + 3y, ...).

For some distributions of things-that-might-be-near, and tuned with the right parameters (x, y), this turns out substantially faster (I’ve used it to locate the nearest UK postcode to each mappiness response).

Read the rest of this entry »

PostGIS has an st_askml function. This turns geometries into fragments of KML, and thus takes you most of the way to easy visualisation of spatial queries using Google Earth. But not all the way: these fragments have then to be assembled into a complete document.

I’ve written some wrapper and aggregate functions to automate this. They’re probably deeply inefficient — I wouldn’t advocate building your next web service on them — but for one-off eyeballing of query results I find them really useful.

The key functions are called as_kmldoc; you could see them as the missing aggregate versions of st_askml.

Read the rest of this entry »

Ordnance Survey’s Code-Point with Polygons “provides a precise geographical location for each postcode unit in Great Britain”. It’s available in various formats, including ESRI .shp files.

Many UK academics can access the data via institutional subscription to EDINA Digimap. I’m using it in my research into subjective wellbeing and environmental quality.

This post shows how to:

1. import the data files into a PostGIS database; and
2. de-normalise the data into a single table, where there’s a one-to-one mapping of postcodes to rows, and each row contains either all geographical locations covered by a postcode (as a single geometry column, of type multipolygon) or the reason why no such location is available

Read the rest of this entry »

This isn’t a very difficult task, but creating the table is somewhat laborious, so perhaps you’ll find the following SQL helpful.

Read the rest of this entry »