6

There are two methods of determining equality in PostGIS.

  1. The equals operator x.geom = y.geom
  2. ST_Equals(x.geom,y.geom)

Obviously these two methods work different, but the docs on ST_Equals are not clear why,

Returns TRUE if the given Geometries are "spatially equal". Use this for a 'better' answer than '='. Note by spatially equal we mean ST_Within(A,B) = true and ST_Within(B,A) = true and also mean ordering of points can be different but represent the same geometry structure. To verify the order of points is consistent, use ST_OrderingEquals (it must be noted ST_OrderingEquals is a little more stringent than simply verifying order of points are the same).

This seems to mean there is two criterion for ST_Equals, and that is that

  1. ST_Within(A,B) = true
  2. ST_Within(B,A) = true

However, that ordering of points does not matter with ST_Equals. Presumably, that would matter with the equal operator then =? But, that doesn't match up with my observation.

So as three in the same questions..

  1. What does the equal operator (=) do?
  2. What does the ST_Equals() do?
  3. What criteria does spatially equal include that = does not include?
8

= operator means bounding box equality. So you could for example compare a linestring and a polygon and if they have the same bounding box, = will return true. So it's not a true equality that people think of. We've had debates about changing it, but since it's used in group by , order by etc internally by PostgreSQL, and for historical reasons was mapped to that per performance and has been like that since the beginning of PostGIS, it would require a great deal of testing to change it.

ST_Equals is the SQL-MM standard idea of equality, which is based on the DEI9M intersection matrix. Which means if two geometries share exactly the same space, they are equal. So you could have a MULTILINESTRING with just one linestring that is ST_Equal to a LINESTRING. You could have one LINESTRING going right to left and anotehr going left to right that are equal because they share the same space.

A side affect of ST_Equals depending on the intersection matrix is that if you compare two invalid geometries, even if they are exactly the same, they will probably not come back as equal.

ST_OrderingEquals is a bit of a bastard. It's not quite the same as binary equality, but it's close. The internal details always baffled me. You can use it for invalid geometries, and in cases where ST_Equals returns true, it may not return true (like if the direction of linestrings is different, or you are comparing a multi with single). As I recall (don't hold me too it), you can for example represent the same linestring with different points and as long as the direction is the same, it will consider them ordering equal.

  • I'm glad I asked this question. For completeness, could you expand with any official doc references you know of for bounding box equality and =? – Evan Carroll Jan 5 '17 at 5:05
  • 1
    Well we explain it in the PostGIS In Action books. There's a whole section about caveats and how people manage to screw themselves with it. We probably should add it to the official docs. Feel free to ticket it - postgis.net/support – LR1234567 Jan 5 '17 at 5:10
  • I actually have that book, and it is the best tech book I've ever read. I'll re-read it again. – Evan Carroll Jan 5 '17 at 5:14
  • 1
    ~= used to be short hand for ST_OrderingEquals and then it changed back and forth because some people didn't like the idea of an operator comparing geometries (and not boxes). I tend to avoid it just because depending how you upgraded your database, it may behave the old way or the new way. With a fresh new instal, I think it means same as = (bounding box equality) except it can use a spatial index, and = can't (unless you have a btree index on your geometry). = is a btree operator – LR1234567 Jan 5 '17 at 5:15
  • 1
    Just a note - this behavior changed in 2.4.0, so = no longer means bounding box but is true geometric representation. Read blog.cleverelephant.ca/2017/09/postgis-operators.html – LR1234567 Oct 18 '17 at 6:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.