6

Stemming from my other question: When would a PostGIS geometry compare using ~= return true when ST_Equals returns false?, what is the PostGIS 2.x equivalent of the pre-PostGIS 1.5 ~= operator?

It appears that from 1.4 to 1.5, the ~= operator changed from an "exactly equal" to an "bnd equal":

For example, in v1.3:
http://postgis.net/docs/manual-1.3/ch06.html#id439812
The "~=" operator is the "same as" operator. It tests actual geometric equality of two features. So if A and B are the same feature, vertex-by-vertex, the operator returns true.

and in 1.5 and later:
http://postgis.net/docs/manual-1.5/ST_Geometry_Same.html
Returns TRUE if A's bounding box is the same as B's.

I'm mostly interested in PostGIS2.0, but I'm not tagging a specific version and leaving it open.

  • It's worth noting that both ST_Equals and ST_OrderingEquals provide a very similar operation to what you're asking for but, from the examples they provide, clearly indicate that the comparison is not made vertex-to-vertex. However, is it possible they suit your needs? – Arthur Feb 3 '14 at 19:52
  • Actually, ST_Equals suits my needs, but asked the question for future reference/curiosity. I didn't know about ST_OrderingEquals, but that looks more practical than a vertex-by-vertex comparison. – Jay Cummins Feb 3 '14 at 21:01
  • Mind turning this knowledge into an answer, @Arthur ? – Martin F Feb 4 '14 at 23:01
3

It does seem that the "[test] for actual geometric equality of the two features" is missing from later versions, but according to this article from Boundless (formerly OpenGeo), the function ST_Equals can be thought of as testing for "exact equality" even though, according to the documentation, it does not make this comparison vertex-to-vertex.

For instance, in this example, from the PostGIS 2.0 documentation, these linestrings are considered equal even though there is one vertex not shared between them. Presumably, in any case where such vertices fall on the same, shared boundary, the geometries are considered equal.

SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
        ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
 st_equals
-----------
 t
(1 row)

ST_OrderingEquals provides the same result with the same example, but this function is actually more constrained: it tests both for "equal geometry" and that the "points are in the same directional order." The article on Equality from Boundless is recommended.

2

To expand on Arthur's answer, I was able to compare using md5 hashes:

SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
         ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)')) 
        ,(md5(ST_AsEWKB(ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'))) 
          = md5(ST_AsEWKB(ST_GeomFromText('LINESTRING(0 0, 10 10)')))) md5hashcompare

enter image description here

MD5 collisions may be an issue. I didn't see any SHA functions, but maybe there's a crypto extension to PostgreSQL that has it (which would reduce but not entirely eliminate the chance of a collision).

1

ST_Equals is not the same as the old ~=. The equivalent to the old ~= is ST_OrderingEquals.

ST_Equals requires your geometries be valid and will return true as long as they occupy the same space. ST_OrderingEquals (and the ~= operator) were more like binary equality (not quite), that utilize spatial index.

  • I don't think ST_OrderingEquals behaves exactly as the old ~=. Look at the second example here: postgis.net/docs/ST_OrderingEquals.html. ST_OrderingEquals returns true in this case, old ~= returns false. – Jay Cummins Feb 6 '14 at 3:44
  • Jay, are you comparing the current ~= with ST_OrderingEquals? They are no longer the same. The behavior of ~= changed around 1.4 (and I remember it because I was particularly annoyed by the change). In 1.3 and around 1.4 they meant the same thing. Then the recheck was taken out so the new ~= really became a bounding box equality (except it used an index unlike (=) (only if you weren't upgrading from 1.3). So there was a period around 1.4-1.5 (where you may get the old behavior if you did a soft upgrade and you got the new behavior if you did a hard upgrade or installed a fresh pot). – LR1234567 Feb 11 '14 at 7:57
  • On closer inspection I think I misread your comment. Are you saying in the old for SELECT ST_GeomFromText('LINESTRING(0 0, 10 10)') ~= ST_GeomFromText('LINESTRING(0 0, 0 0, 10 10)'); you are getting false? Unfortunately I don't have a 1.3 version to test. That could be some implementation change of the actual ST_OrderingEquals. – LR1234567 Feb 11 '14 at 8:08

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.