I really want the geography support in PostGIS to be good, so when I get tickets on it, I take the time to investigate, particularly when they say there are problems with correctness.

This recent ticket took a little while to visualize and confirm, and it’s worth showing so hopefully other folks can debug their issues on their own. The submitter basically said, “look, I have two squares, and I know the smaller one is contained in the larger, and I know that they both contain a marker, and yet when I run containment tests, they say the marker is only contained by the smaller square, but if the larger square contains the smaller it must logically also contain the marker!” And, helpfully, he included the SQL for the tests, which included the squares.

You can see where his concern comes from. The smaller square is inside the larger.

And the marker is inside both.

However, this is a visualization in mercator. The lines between vertices are being drawn as linear interpolations in planar space. The geography calculations involve great circles between vertices in spherical space. What does that look like?

Hm, the lower lines don’t actually line up.

And the marker is actually between the lower lines. It’s contained by the smaller square, but not by the larger one. Just like the geography tests said.

The moral of the story is, we aren’t good at visually reasoning about boundaries on a sphere. Our practice is all cartesian and it causes us to make mistakes.

This is something I learned during the development of geography, when I spent many hours debugging test cases that the code was getting “wrong” only to find in the end that the problem was entirely between my ears. (Example, does POINT(0 1) fall on LINESTRING(-1 1, 1 1)? If you said “yes”, you too have problems between your ears.)

So, plug your problem into KML LineStrings (not Polygons, they aren’t rendered as great circles!) and have a look before reporting correctness issues in geography, please!

Tags: geography, postgis