Archive for the ‘PostGIS’ Category
Polygons from PostGIS to Processing
There are plenty of ways to get spatial data from a PostGIS database into a Processing sketch.
You can export to CSV or SVG and load it from there; you can query the database directly; or, depending on context, you might choose to generate Processing commands directly, which is the route I went to display a background map of the UK in a recent visualization project.
Approximating kernel-weighted proportions in PostGIS
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.
Simple PostGIS nearest neighbour function
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).
as_kmldoc: easily visualise PostGIS queries as KML in Google Earth
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.
Using OS Code-Point Polygons in PostGIS
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:
- import the data files into a PostGIS database; and
- 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
Importing the National Statistics Postcode Directory into PostGIS
This isn’t a very difficult task, but creating the table is somewhat laborious, so perhaps you’ll find the following SQL helpful.