How spatial comparisons work

There are two methods you can use to test whether a geometry is equal to another geometry: ST_Equals, and ST_OrderingEquals. These methods perform the comparison differently, and return a different result.

ST_Equals The order in which points are specified does not matter, and point comparison takes tolerance into account. Geometries are considered equal if they occupy the same space, within tolerance. For example, if two linestrings occupy the same space, yet one is defined with more points, they are still considered equal.
ST_OrderingEquals With ST_OrderingEquals, the two geometries must contain the same hierarchy of objects with the exact same points in the same order to be considered equal under ST_OrderingEquals. That is, the two geometries need to be exactly the same.

To illustrate the difference in results when comparisons are made using ST_Equals versus ST_OrderingEquals, consider the following lines. ST_Equals considers them all equal (assuming line C is within tolerance). However, ST_OrderingEquals does not consider any of them equal.

Line A and B have the same two end points, but they are specified in different order. Line C looks like line A, but at a slight angle. Line D has three points defined for the line, but consumes the same space as lines A and B.

How SQL Anywhere performs comparisons of geometries

The database server uses ST_OrderingEquals to perform operations such as GROUP BY and DISTINCT.

For example, when processing the following query the server considers two rows to be equal if the two shape expressions have ST_OrderingEquals() = 1:

SELECT DISTINCT Shape FROM SpatialShapes;

SQL statements can compare two geometries using the equal to operator (=), or not equal to operator (<> or !=), including search conditions with a subquery and the ANY or ALL keyword.. Geometries can also be used in an IN search condition. For example, geom1 IN (geom-expr1, geom-expr2, geom-expr3). For all of these search conditions, equality is evaluated using the ST_OrderingEquals semantics.

You cannot use other comparison operators to determine if one geometry is less than or greater than another (for example, geom1 < geom2 is not accepted). This means you cannot include geometry expressions in an ORDER BY clause. However, you can test for membership in a set.

Discuss this page in DocCommentXchange.