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