How the EQUALS() function works in Mariadb?

The EQUALS() function is a spatial function that compares two geometries and returns 1 if they are equal, 0 if they are not equal, or NULL if either geometry is NULL.

Posted on

The EQUALS() function is a spatial function that compares two geometries and returns 1 if they are equal, 0 if they are not equal, or NULL if either geometry is NULL. The EQUALS() function is equivalent to the <=> operator for spatial types. In this article, we will learn how to use the EQUALS() function in Mariadb with some examples.

Syntax

The syntax of the EQUALS() function is as follows:

EQUALS(g1, g2)

The EQUALS() function takes two arguments:

  • g1 and g2 are geometries that can be specified as literals or columns of spatial types.

The EQUALS() function returns an integer value of 1, 0, or NULL.

Examples

Let’s see some examples of using the EQUALS() function in Mariadb.

Example 1: Comparing two point geometries

In this example, we will compare two point geometries using the EQUALS() function. We will use the Point() function to create the point geometries from the coordinates.

SET @p1 = Point(0, 0);
SET @p2 = Point(1, 1);
SELECT EQUALS(@p1, @p1), EQUALS(@p1, @p2);

The output is:

+------------------+------------------+
| EQUALS(@p1, @p1) | EQUALS(@p1, @p2) |
+------------------+------------------+
|                1 |                0 |
+------------------+------------------+

The EQUALS() function returns 1 when the two point geometries are equal, and 0 when they are not equal.

Example 2: Comparing two linestring geometries

In this example, we will compare two linestring geometries using the EQUALS() function. We will use the LineString() function to create the linestring geometries from the points.

SET @l1 = LineString(Point(0, 0), Point(1, 1));
SET @l2 = LineString(Point(0, 0), Point(1, 2));
SELECT EQUALS(@l1, @l1), EQUALS(@l1, @l2);

The output is:

+------------------+------------------+
| EQUALS(@l1, @l1) | EQUALS(@l1, @l2) |
+------------------+------------------+
|                1 |                0 |
+------------------+------------------+

The EQUALS() function returns 1 when the two linestring geometries are equal, and 0 when they are not equal.

Example 3: Comparing two polygon geometries

In this example, we will compare two polygon geometries using the EQUALS() function. We will use the Polygon() function to create the polygon geometries from the linestrings.

SET @poly1 = Polygon(LineString(Point(0, 0), Point(0, 1), Point(1, 1), Point(1, 0), Point(0, 0)));
SET @poly2 = Polygon(LineString(Point(0, 0), Point(0, 2), Point(2, 2), Point(2, 0), Point(0, 0)));
SELECT EQUALS(@poly1, @poly1), EQUALS(@poly1, @poly2);

The output is:

+------------------------+------------------------+
| EQUALS(@poly1, @poly1) | EQUALS(@poly1, @poly2) |
+------------------------+------------------------+
|                      1 |                      0 |
+------------------------+------------------------+

The EQUALS() function returns 1 when the two polygon geometries are equal, and 0 when they are not equal.

Example 4: Comparing two geometry collections

In this example, we will compare two geometry collections using the EQUALS() function. We will use the GeometryCollection() function to create the geometry collections from the geometries.

SET @gc1 = GeometryCollection(Point(0, 0), LineString(Point(0, 0), Point(1, 1)));
SET @gc2 = GeometryCollection(Point(0, 0), LineString(Point(0, 0), Point(1, 2)));
SELECT EQUALS(@gc1, @gc1), EQUALS(@gc1, @gc2);

The output is:

+--------------------+--------------------+
| EQUALS(@gc1, @gc1) | EQUALS(@gc1, @gc2) |
+--------------------+--------------------+
|                  1 |                  0 |
+--------------------+--------------------+

The EQUALS() function returns 1 when the two geometry collections are equal, and 0 when they are not equal.

Example 5: Comparing different types of geometries

In this example, we will compare different types of geometries using the EQUALS() function. We will use the ST_GeomFromText() function to create the geometries from the well-known text (WKT) representation.

SET @g1 = ST_GeomFromText('POINT(0 0)');
SET @g2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
SET @g3 = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
SELECT EQUALS(@g1, @g2), EQUALS(@g2, @g3), EQUALS(@g3, @g1);

The output is:

+------------------+------------------+------------------+
| EQUALS(@g1, @g2) | EQUALS(@g2, @g3) | EQUALS(@g3, @g1) |
+------------------+------------------+------------------+
|                0 |                0 |                0 |
+------------------+------------------+------------------+

The EQUALS() function returns 0 when the two geometries are of different types, even if they share some common points.

There are some other functions that are related to the EQUALS() function in Mariadb. Here are some of them:

  • The <=> operator is equivalent to the EQUALS() function for spatial types. For example, g1 <=> g2 is the same as EQUALS(g1, g2).
  • The ST_Equals() function is an alias for the EQUALS() function. For example, ST_Equals(g1, g2) is the same as EQUALS(g1, g2).
  • The ST_Relate() function returns a string that indicates the spatial relationship between two geometries. For example, ST_Relate(g1, g2, 'T*F**FFF*') returns 1 if g1 and g2 are equal, and 0 otherwise.
  • The ST_Within() function returns 1 if the first geometry is completely within the second geometry, and 0 otherwise. For example, ST_Within(Point(0, 0), Polygon((0 0, 0 1, 1 1, 1 0, 0 0))) returns 1, but ST_Within(Point(0, 0), LineString(Point(0, 0), Point(1, 1))) returns 0.

Conclusion

In this article, we learned how to use the EQUALS() function in Mariadb to compare two geometries and return 1 if they are equal, 0 if they are not equal, or NULL if either geometry is NULL. We also saw some examples of using the EQUALS() function with different types of geometries and some related functions. The EQUALS() function is a useful function for spatial analysis and queries in Mariadb.