Ubuntu upgrade to PostGIS 2 with data preservation

I thought it’d be “fun” to upgrade from PostGIS 1.5 to 2.x on my Ubuntu box. I forgot how byzantine Postgres extensions are and the fundamental rule of database upgrades; you have to take a dump with the old version first. I didn’t take careful notes, but here’s what sort of worked for me.

  • Read the PostGIS instructions on upgrading, in particular the notes about doing a “hard upgrade”. Create a dump before changing PostGIS 1.5.
  • Follow these instructions on installing PostGIS 2.x from sharpie’s PPA. You really need postgis-nightly; he has a PPA called postgis-stable too but I don’t think it’s fully populated.
  • Restore the dump with pg_restore, to a new database name. Pay close attention to stderr.
  • I got an error about ST_* functions being “not unique”. This report suggested that the restore brought over some 1.5 versions of functions and the solution is to run postgis_upgrade_20_minor.sql. Running that threw errors because I had a view depending on some of the functions; I ended up just dropping the view.

Honestly this is all pretty messy and I don’t have full confidence that PostGIS 2 is really installed properly. There’s too many spooky places PostGIS stuff gets loaded into the database. But all my old code still seems to work and gives the same results (at least, visually) so maybe it worked out.

I tried playing with ST_ConcaveHull that I wanted. It’s very, very slow on a GPS track with 50 points in it. Like 1.5 seconds to do a single track to a target percent of 0.5. I guess it’s doing some optimization algorithm? target percent of 0 doesn’t even work, throws a TopologyException. This function may not be what I need to do my calculation.

ST_NPoints(ST_Simplify(wkb_geometry, 0.0002)),
ST_ConcaveHull(ST_Simplify(wkb_geometry, 0.0002), 0.5) as wkb_geometry,
from tracks
limit 1;