Skip To Content
Back to Blog

PostGIS 2.1, Our Showstopper


by Jerry Sievert


As we've been readying to transition from open beta to full production with our Geotrigger Service, we've been battle hardening everything, preparing for an onslaught of devices. Part of that hardening involves being up to date with our technology stack, especially when it comes to our databases.

We have been running PostGIS 2.0.x for quite a while, while this product has been in development, and seeing that 2.1.1 had been released gave us an opportunity to try the newest release of PostGIS. Unfortunately, we ran into what to us is a showstopper bug.

Our first step was to create a very simple test case that can easily be reproduced. We know from our own development (open source, as well as closed source) that having a clear test case is extremely important when tracking down an issue, so this was one of our highest priorities. We were able to distill the problem down to a single table with two rows, and two commands: INSERT and SELECT. Creating a simple test case like this is also extremely valuable both to convince ourselves that a bug actually exists and that the bug is part of PostGIS (and not, say, something like GDAL or GEOS, like we initially had pondered). This can help us easily prove that the bug exists and help point the PostGIS contributers directly to where the issue might lie within the PostGIS codebase.

With this knowledge, it was time to approach the PostGIS community with what we found. In parallel, we began a process of checking all versions of PostGIS for the issue, and determined that all versions of 2.1.x, including the beta, were affected.

A workaround was presented to us by the end of the weekend, but unfortunately caused more issues that we have yet to isolate. It is our decision that we will continue on the 2.0.x release for the time being.

The bug manifested itself via ST_Intersects(geog1, geog2), specifically against two geography types.

The PostGIS developers, especially Paul Ramsey, have responded and included a fix for this as part of the next release, 2.1.2, but we have not yet validated it for our own use.

You can reproduce the bug as follows:

test=# CREATE TABLE test (id serial, condition_geo geography);
CREATE TABLE
test=# INSERT INTO test (condition_geo) VALUES (ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0));
INSERT 0 1
test=# SELECT id FROM test WHERE ST_Intersects("condition_geo", ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)), 20.0)) IS TRUE;
 id
----
  1
(1 row)

test=# INSERT INTO test (condition_geo) VALUES (ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)),10.0));
INSERT 0 1
test=# SELECT id FROM test WHERE ST_Intersects("condition_geo", ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)), 20.0)) IS TRUE;
 id
----
  1
(1 row)

test=# SELECT id FROM test WHERE ST_Intersects("condition_geo", ST_Buffer(ST_GeogFromWKB(ST_MakePoint(20.0,30.0)), 20.0)) IS TRUE AND id = 2;
 id
----
  2
(1 row)