I’ve recently been playing with PostGIS. This post will summarise a simple attempt to manipulate data and draw it on top of Open Streetmap. I wanted to produce a Brighton version of Steven Kay’s Pub Ley Lines. As you will see, the outcome was less interesting than the process.

What follows is, basically, a How-to-Draw-An-Owl tutorial. It summarises the steps I usually look up and is intended to share with a few specific people. If you find yourself here via Google and want more information, leave a comment and I will add more detail.

1 – Download data from OSM

The first thing I needed was the data from Open Streetmap, which contains pub locations among the points-of-interest. There are a number of options. Downloading direct from OSM failed when I last tried it, but I had an older version of the data available.

2 – Set up Postgis and osm2pgsql

I’d previously installed Postgresql and Postgis on my laptop but somehow the installation has become broken and won’t be easily repaired or uninstalled. I should fix this, but I wanted to get on with this experiment. I’ve been meaning to set up AWS for some time, and using a micro instance on Amazon allowed me to get a version of PostGIS running very quickly.

I created a new EC2 micro instance based on the basic ubuntu instance, ran ‘sudo apt-get update’ followed by ‘sudo apt-get install osm2pgsql’ and I had everything I needed.

AWS is awesome, and I love being able to run up an instance for a small task and throw it away once I’m done.

3 – Create postGIS user and table

Setting up a new user on postgres is certainly less of a hassle than doing it on MySQL, but there are a few gotchas – such as needing to add a line to the /etc/postgresql/9.3/main/pg_hba.conf file. Also, when creating a new database, remember to enable PostGIS with the command “CREATE EXTENSION POSTGIS”

4 – Load the OSM data in Postgres

Having transferred my PBF file to the AWS instance using scp, I could then load the data. The command here is a little different to the one I used previously because of this server’s limitations:

osm2pgsql -U ley -d ley  --slim --cache-strategy sparse --number-processes 4 brighton.pbf

5 – Create a table containing all of the ley lines

I pretty-much followed the recipe given by Stephen Kay here.

6 -Extract the data

This time, rather than a tab-separated format, I selected the WKT (well-known text) format for all the ley-lines with more than 8 pubs. I’m sure there are better ways to extract this. The query used was:

psql -U ley -w ley -c "COPY (select st_astext(st_transform(geom,4674)) from leys where ct> 8) TO STDOUT WITH CSV" > lines.txt

This may not be the most portable format for the data but I can bully it into something Openlayers can use.

7 – Create a page to display the data

Since the data is constant and will be low-traffic, I am using OSM via static files to display the results.  The linestring WKT representations of the leys have been copied into the javascript file rather than being loaded from a file. All quick-and-dirty, but it has worked. The source is on github and the results are online.