How the CONTAINS() function works in Mariadb?

The CONTAINS() function is a spatial function that tests whether a geometry contains another geometry.

Posted on

The CONTAINS() function is a spatial function that tests whether a geometry contains another geometry. The function returns 1 if the first geometry completely contains the second geometry, 0 if not, or NULL if either argument is NULL or not a valid geometry.

Syntax

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

CONTAINS(g1, g2)

The function takes two arguments, g1 and g2, which are the geometries to be tested. The function follows the OpenGIS specifications for the ST_Contains() function.

Examples

Example 1: Testing whether a polygon contains a point

The following example uses the CONTAINS() function to test whether a polygon contains a point. The polygon is defined by four points: (0, 0), (0, 10), (10, 10), and (10, 0). The point is defined by the coordinates (5, 5).

SELECT CONTAINS(
  ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'),
  ST_GeomFromText('POINT(5 5)')
);

The output is:

+---------------------------------------------------------------+
| CONTAINS(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'), |
|         ST_GeomFromText('POINT(5 5)'))                         |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+

The output shows that the polygon contains the point, as expected.

Example 2: Testing whether a polygon contains a line

The following example uses the CONTAINS() function to test whether a polygon contains a line. The polygon is the same as in the previous example. The line is defined by two points: (1, 1) and (9, 9).

SELECT CONTAINS(
  ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'),
  ST_GeomFromText('LINESTRING(1 1,9 9)')
);

The output is:

+---------------------------------------------------------------+
| CONTAINS(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'), |
|         ST_GeomFromText('LINESTRING(1 1,9 9)'))                |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+

The output shows that the polygon contains the line, as expected.

Example 3: Testing whether a polygon contains another polygon

The following example uses the CONTAINS() function to test whether a polygon contains another polygon. The first polygon is the same as in the previous examples. The second polygon is defined by four points: (2, 2), (2, 8), (8, 8), and (8, 2).

SELECT CONTAINS(
  ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'),
  ST_GeomFromText('POLYGON((2 2,2 8,8 8,8 2,2 2))')
);

The output is:

+---------------------------------------------------------------+
| CONTAINS(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'), |
|         ST_GeomFromText('POLYGON((2 2,2 8,8 8,8 2,2 2))'))     |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+

The output shows that the first polygon contains the second polygon, as expected.

Example 4: Testing whether a polygon contains a point on its boundary

The following example uses the CONTAINS() function to test whether a polygon contains a point on its boundary. The polygon is the same as in the previous examples. The point is defined by the coordinates (0, 5).

SELECT CONTAINS(
  ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'),
  ST_GeomFromText('POINT(0 5)')
);

The output is:

+---------------------------------------------------------------+
| CONTAINS(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'), |
|         ST_GeomFromText('POINT(0 5)'))                         |
+---------------------------------------------------------------+
|                                                             0 |
+---------------------------------------------------------------+

The output shows that the polygon does not contain the point, as expected. According to the OpenGIS specifications, a geometry does not contain its boundary.

Example 5: Testing whether a polygon contains an invalid geometry

The following example uses the CONTAINS() function to test whether a polygon contains an invalid geometry. The polygon is the same as in the previous examples. The invalid geometry is defined by an empty string.

SELECT CONTAINS(
  ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'),
  ST_GeomFromText('')
);

The output is:

+---------------------------------------------------------------+
| CONTAINS(ST_GeomFromText('POLYGON((0 0,0 10,10 10,10 0,0 0))'), |
|         ST_GeomFromText(''))                                   |
+---------------------------------------------------------------+
|                                                          NULL |
+---------------------------------------------------------------+

The output shows that the function returns NULL, as expected. The function returns NULL if either argument is NULL or not a valid geometry.

There are some other functions that are related to the CONTAINS() function in Mariadb. They are:

  • WITHIN(): This function is the inverse of the CONTAINS() function. It tests whether a geometry is within another geometry. It returns 1 if the first geometry is completely within the second geometry, 0 if not, or NULL if either argument is NULL or not a valid geometry.
  • INTERSECTS(): This function tests whether two geometries intersect. It returns 1 if the geometries share any portion of space, 0 if not, or NULL if either argument is NULL or not a valid geometry.
  • DISJOINT(): This function tests whether two geometries are disjoint. It returns 1 if the geometries do not share any portion of space, 0 if not, or NULL if either argument is NULL or not a valid geometry.
  • OVERLAPS(): This function tests whether two geometries overlap. It returns 1 if the geometries share some but not all portions of space, 0 if not, or NULL if either argument is NULL or not a valid geometry.

Conclusion

The CONTAINS() function is a useful function to test whether a geometry contains another geometry. It can handle different types of geometries, such as points, lines, and polygons. It follows the OpenGIS specifications for the ST_Contains() function. It is similar to the WITHIN() function, but with the arguments reversed. It is also related to some other functions that test the spatial relationships between geometries.