How the DISJOINT() function works in Mariadb?

The DISJOINT() function is a built-in function in Mariadb that returns 1 or 0 to indicate whether two geometry values are spatially disjoint, which means they do not intersect or touch each other.

Posted on

The DISJOINT() function is a built-in function in Mariadb that returns 1 or 0 to indicate whether two geometry values are spatially disjoint, which means they do not intersect or touch each other. The DISJOINT() function is based on the original MySQL implementation and uses object bounding rectangles, while the ST_DISJOINT() function uses object shapes and follows the standard SQL/MM specification.

Syntax

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

DISJOINT(g1, g2)

The parameters g1 and g2 are geometry values. The function returns 1 if g1 is spatially disjoint from g2, or 0 otherwise. If either g1 or g2 is NULL or not a valid geometry value, the function returns NULL.

Examples

Here are some examples of using the DISJOINT() function with different types of geometries.

Example 1: Points

Two points are disjoint if they have different coordinates. For example, the following query returns 1, because the two points are not the same.

SELECT DISJOINT(ST_GeomFromText('POINT (1 2)'), ST_GeomFromText('POINT (3 4)'));

The output is:

+--------------------------------------------------------------------------+
| DISJOINT(ST_GeomFromText('POINT (1 2)'), ST_GeomFromText('POINT (3 4)')) |
+--------------------------------------------------------------------------+
| 1                                                                        |
+--------------------------------------------------------------------------+

However, the following query returns 0, because the two points are the same.

SELECT DISJOINT(ST_GeomFromText('POINT (1 2)'), ST_GeomFromText('POINT (1 2)'));

The output is:

+--------------------------------------------------------------------------+
| DISJOINT(ST_GeomFromText('POINT (1 2)'), ST_GeomFromText('POINT (1 2)')) |
+--------------------------------------------------------------------------+
| 0                                                                        |
+--------------------------------------------------------------------------+

Example 2: LineStrings

Two line strings are disjoint if they do not share any common point. For example, the following query returns 1, because the two line strings are parallel and do not intersect.

SELECT DISJOINT(ST_GeomFromText('LINESTRING (0 0, 1 1)'), ST_GeomFromText('LINESTRING (0 1, 1 2)'));

The output is:

+----------------------------------------------------------------------------------------------+
| DISJOINT(ST_GeomFromText('LINESTRING (0 0, 1 1)'), ST_GeomFromText('LINESTRING (0 1, 1 2)')) |
+----------------------------------------------------------------------------------------------+
| 1                                                                                            |
+----------------------------------------------------------------------------------------------+

However, the following query returns 0, because the two line strings intersect at the point (1, 1).

SELECT DISJOINT(ST_GeomFromText('LINESTRING (0 0, 1 1)'), ST_GeomFromText('LINESTRING (0 1, 1 0)'));

The output is:

+----------------------------------------------------------------------------------------------+
| DISJOINT(ST_GeomFromText('LINESTRING (0 0, 1 1)'), ST_GeomFromText('LINESTRING (0 1, 1 0)')) |
+----------------------------------------------------------------------------------------------+
| 0                                                                                            |
+----------------------------------------------------------------------------------------------+

Example 3: Polygons

Two polygons are disjoint if they do not overlap or touch each other. For example, the following query returns 1, because the two polygons are separated by some distance.

SET @poly1 = 'POLYGON ((0 0, 0 5, 5 5, 5 0, 0 0))';
SET @poly2 = 'POLYGON ((10 10, 10 15, 15 15, 15 10, 10 10))';
SELECT DISJOINT(ST_GeomFromText(@poly1), ST_GeomFromText(@poly2));

The output is:

+------------------------------------------------------------+
| DISJOINT(ST_GeomFromText(@poly1), ST_GeomFromText(@poly2)) |
+------------------------------------------------------------+
| 1                                                          |
+------------------------------------------------------------+

However, the following query returns 0, because the two polygons share a common edge.

SET @poly1 = 'POLYGON ((0 0, 0 5, 5 5, 5 0, 0 0))';
SET @poly2 = 'POLYGON ((5 0, 5 5, 10 5, 10 0, 5 0))';
SELECT DISJOINT(ST_GeomFromText(@poly1), ST_GeomFromText(@poly2));

The output is:

+------------------------------------------------------------+
| DISJOINT(ST_GeomFromText(@poly1), ST_GeomFromText(@poly2)) |
+------------------------------------------------------------+
| 0                                                          |
+------------------------------------------------------------+

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

  • ST_DISJOINT(): This function is equivalent to the DISJOINT() function, but follows the standard SQL/MM specification. It uses the object shapes instead of the bounding rectangles to determine the spatial relationship. It can be used interchangeably with the DISJOINT() function, but may produce different results in some cases.
  • INTERSECTS(): This function tests the opposite relationship to DISJOINT(). It returns 1 or 0 to indicate whether two geometry values intersect or touch each other. It is based on the original MySQL implementation and uses object bounding rectangles, while the ST_INTERSECTS() function uses object shapes and follows the standard SQL/MM specification.
  • TOUCHES(): This function returns 1 or 0 to indicate whether two geometry values touch each other, but do not overlap. It is based on the original MySQL implementation and uses object bounding rectangles, while the ST_TOUCHES() function uses object shapes and follows the standard SQL/MM specification.
  • OVERLAPS(): This function returns 1 or 0 to indicate whether two geometry values overlap each other, but are not completely contained by each other. It is based on the original MySQL implementation and uses object bounding rectangles, while the ST_OVERLAPS() function uses object shapes and follows the standard SQL/MM specification.

For example, the following query shows the results of different spatial relationship functions for two polygons that share a common edge.

SET @poly1 = 'POLYGON ((0 0, 0 5, 5 5, 5 0, 0 0))';
SET @poly2 = 'POLYGON ((5 0, 5 5, 10 5, 10 0, 5 0))';
SELECT DISJOINT(@poly1, @poly2), ST_DISJOINT(@poly1, @poly2), INTERSECTS(@poly1, @poly2), ST_INTERSECTS(@poly1, @poly2), TOUCHES(@poly1, @poly2), ST_TOUCHES(@poly1, @poly2), OVERLAPS(@poly1, @poly2), ST_OVERLAPS(@poly1, @poly2);

The output is:

+--------------------------+-----------------------------+----------------------------+-------------------------------+-------------------------+----------------------------+--------------------------+-----------------------------+
| DISJOINT(@poly1, @poly2) | ST_DISJOINT(@poly1, @poly2) | INTERSECTS(@poly1, @poly2) | ST_INTERSECTS(@poly1, @poly2) | TOUCHES(@poly1, @poly2) | ST_TOUCHES(@poly1, @poly2) | OVERLAPS(@poly1, @poly2) | ST_OVERLAPS(@poly1, @poly2) |
+--------------------------+-----------------------------+----------------------------+-------------------------------+-------------------------+----------------------------+--------------------------+-----------------------------+
| 0                        | 0                           | 1                -----     | 1                             | 1                       | 1                          | 0                        | 0                           |
+--------------------------+-----------------------------+----------------------------+-------------------------------+-------------------------+----------------------------+--------------------------+-----------------------------+

Conclusion

The DISJOINT() function is a useful function in Mariadb that returns 1 or 0 to indicate whether two geometry values are spatially disjoint, which means they do not intersect or touch each other. The DISJOINT() function is based on the original MySQL implementation and uses object bounding rectangles, while the ST_DISJOINT() function uses object shapes and follows the standard SQL/MM specification. There are some other functions that are related to the DISJOINT() function, such as INTERSECTS(), TOUCHES(), and OVERLAPS(). These functions can help us to test different spatial relationships between geometry values in Mariadb.