postgis postgresql

Boston OSGeo Code sprint is planned for Thursday March 25th to March 28th 2013 right here in Boston. We've already got 21 code sprinters signed up and 4 sponsor pledges. As far as sprinters go, if you are involved in an OSGeo project as a documenter, package maintainers, coder, or tester we'd love to have you here even if you can't attend all days. Participants represent many of the core OSGeo projects and related projects (MapServer, PostGIS and pgRouting, GDAL, and PDAL)

Even if you do not plan to attend but rely on OSGeo projects heavily for your work, you can still show your appreciation with a $750+ donation for the sprint. Sponsorship monies will go toward Conference room, hotel, snacks, events and also subsidizing some of the costs for travel, particularly of core contributors who are coming from far away and wouldn't otherwise be able to make it. Whatever is left will go back into the general pool fund for future code sprints. If you'd like to be a sponsor, please drop us a line at lr at pcorp.us. Similar to last year, we are planning to have show case slots for Sponsors and code sprint attendees to demonstrate what they are doing with OSGeo tools.

We're particularly looking forward to talking with folks working on Point Cloud (PDAL) and working toward more seamlessly integrating pgRouting, if nothing more than the distribution of pgRouting with PostGIS. As windows packagers we are hoping to, and hopefully even before the sprint, have pgRouting packages available with the windows PostGIS packages we build for 2+ releases.

If anyone has ever seen any of Paul's presentations or slides, you'll remember his famous Intro To PostGIS and slide Is MySQL a spatial database?, answer: Not Really. The functions only work against bounding boxes.. (page 5). And Paul's They keep on threatening us. Well Paul, I'm afraid you'll have to change that slide. They have finally made good on their threats and we PostGIS folks need to now keep a watchful eye.

We've lost interest for the most part in what MySQL does these days, except for the few customers we have that are still running MySQL that the migration to something else isn't worth the bother. We've lost interest mostly because MySQL movement in areas we care about have been so slow and PostgreSQL movement has been so fast. I did come across this tidbit reading Comparing Open Source GIS Implementations and to my shock Oh my god! Stop the presses, MySQL 5.6 has true Spatial relationship support.

The other thought I had is thank goodness PostGIS legacied all those non-ST functions like Contains(), Intersects etc, as that would cause people who use both databases even more confusion. Here are some important take-away points from MySQL 5.6 Spatial support:

• The old MBR alias functions Contains, Overlaps, etc, still only work against the bounding box. To get true checks, you need to use the ST_ variants
• While you can create geometries on InnoDb tables now, you can only create R-tree spatial indexes on MyISAM tables.
• I could be missing something, but don't see an ST_Distance function or anything like it. I don't have 5.6 handy to test, so if anyone knows, please correct me if I am wrong.
• General footnote, MariaDb also supports this new functionality in its 5.3+ versions

PostgreSQL 9.2beta3 just came out this week and it sounds like 9.2.0 is only about 2 months away from release. For windows users, we don't have PostGIS for 9.2 yet on stackbuilder, but we have something perhaps even better. We have automated windows builds of the PostGIS 2.1 branch for 9.2 for both 32-bit and 64-bit Windows. We still have to revise these to build against beta3, but the version compiled against beta2 seems to work fine against the EnterpriseDb beta3 install. We also have the build bot building for 9.1 windows 64-bit and 9.0 64-bit. You can get these at http://www.postgis.org/download/windows/pg92/buildbot. If you want to try out the new PL/V8JS and PL/CoffeeScript languages that are great companions for PostgreSQL 9.2 integrated json support. We happen to have windows plv8 binaries for those too which you can use with the Windows 9.2.0 beta 3 binaries which you can just copy in your PostgreSQL 9.2 installs and install with a quickie:

CREATE EXTENSION plv8js;
CREATE EXTENSION plcoffee;

You can follow our PL/Javascript and PL/CoffeeScript series. We are still getting our feet wet, but hope to show some nice integraton features with PostGIS JSON support in later parts.

Supporting software the challenges

Sadly it looks like 9.2 will be out before PostGIS 2.1. We are planning to drag our feet long-enough so that we don't have to bother releasing a windows PostGIS 2.0 build for 9.2. We have many reasons for this laziness.

• We build 8.4,9.0,9.1 (both 32-bit and 64-bit) for PostGIS 2.0 for windows. Adding 9.2 will add another 2 (for total of 7 versions) and also lengthen the time we have to maintain support for 2.0.
• 9.2 has SP-GIST, Paul has intimated he'll be putting SP-GIST support in 2.1 for 9.2 users, so why would you bother using 2.0 if you are on 9.2? I'm all for not giving users stupid options to consider and encouraging the PostGIS development clan to get that SP-GIST in there.
• If you are a developer of any software -- number one that should be on your mind is how to get users off old releases of your sofware.

  Back-porting fixes is time-consuming/expensive and takes away precious time from new-development and documenting new features. The more users you have running older versions of your software, the more you are thought of by the limitations of your past. Regardless of if you develop open source or proprietary software, you have a contract (expectation) from customers about how long you will support patches for a version. If nobody is using an old version, you don't need to provide bug-fixes to it except possibly security fixes.

  Of course you can't just cut the chord. You want people to naturally stop using old versions and encourage hosting companies to provide newer options (and stop providing old options). The best way to do that is, more binary distributions of newer releases and beta releases available. Heavily tested beta versions, which happens naturally if you make binary distributions available and minimize on breaking changes. Great usability features that are easy for a user to upgrade from one version to the next and possible within reason backwards compatibility so that people don't have to do a lot of testing of their old apps to be sure it works with the new version.

With all that in mind, it's my personal challenge to make it easy for current PostGIS 2.0 users to test out the latest 2.1 changes on real workflows and be able to upgrade often. The upgrade issues I discussed in the last article, are for the time being resolved and users should be able to run the minor upgrade script to upgrade to the latest 2.1.0SVN. For extension users, you can employ the hack described in upgrade to non-released PostGIS, except I have the files now being autobuilt as part of the extension build.

If you are on 2.0.0 or 2.0.1 then: ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";

If you are on some version of 2.1.0 then:

ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";
ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";

I love PostgreSQL extensions model except for one small little problem. When you want to work with not yet released versions of PostGIS, upgrading the extensions is not as simple as:

The problem is there is no migration path to go from: 2.1.0SVN to 2.1.0SVN and PostGIS is not anal enough to maintain micro-micro version scripts.

If you try something like:

When you are already at 2.1.0SVN, you'll get a polite message: NOTICE: version "2.1.0SVN" of extension "postgis" is already installed.

Specifying such a migration is in fact illegal probably because it creates all sorts of cyclical redundancy issues.

However there is an easy get out of jail free card for this issue. The free card hinges on two fundamental axioms:

• PostgreSQL extensions don't care about version numbers per se, but only about migration paths and the migration path are all convention based on the file names of the extension files.
• PostGIS minor upgrade scripts can be run a trillion times and not cause any damage. They will merely skip over or redo what's already been done.

Get out of Jail Free Card

So here is the trick - hack, free card however you want to call it, but before I tell you this, I must mention This will not work going from PostGIS 2.0.something to PostGIS 2.1.something, but will work going from PostGIS 2.0.someting to unreleased PostGIS 2.0.something. This is a bug in PostGIS 2.1.0 more than anything because we haven't revised even the standard upgrade scripts to recognize structural additions added in 2.1. That issue we hope to have fixed shortly so that people can actually upgrade with the generated upgrade scripts.

Let us say you are on extension version PostGIS 2.1.0SVN of some PostGIS 2.1.0SVN, and you just compiled and built and now have a version PostGIS 2.1.0SVN r10154 which is inconveniently enough called PostGIS 2.1.0SVN. How do you go from 2.1.0SVN to 2.1.0SVN. Simple you walk forward and then you walk back. At least that's the little lie you tell extensions to appease it. How do you do that?

• Make a copy of the currently badly named postgis--2.0.0--2.1.0SVN.sql and name it postgis--2.1.0SVN.sql--2.1.0SVNnext.sql
• Make another copy of the same file and call it postgis--2.1.0SVNnext.sql--2.1.0SVN.sql

Copy these new files into your PostgreSQL share/extension folder.

Now you can get away with doing this:

ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";
ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";

The second step is really there just so you can easily upgrade in the future and you can safely delete these interim files.