How the ST_CONTAINS() function works in MariaDB?

The ST_Contains() function in MariaDB is used to determine whether a given geometry value contains another geometry value. This function is particularly useful for spatial analysis tasks, such as identifying spatial relationships between geometries, performing spatial queries, and validating spatial data integrity.

Syntax

The syntax for the MariaDB ST_Contains() function is as follows:

ST_Contains(geom1, geom2)

• geom1: The geometry value that is being tested for containing the geom2 geometry.
• geom2: The geometry value that is being tested for being contained within geom1.

The function returns 1 (true) if geom2 is entirely contained within geom1, and 0 (false) otherwise.

Examples

Example 1: Test if a Point is contained within a Polygon

This example demonstrates how to test if a Point geometry is contained within a Polygon geometry.

SET @point = ST_GeomFromText('POINT(5 5)');
SET @poly = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
SELECT ST_Contains(@poly, @point);

The following is the output:

1

The output indicates that the Point geometry at (5, 5) is contained within the square Polygon geometry.

Example 2: Test if a LineString is contained within a Polygon

This example shows how to test if a LineString geometry is contained within a Polygon geometry.

SET @line = ST_GeomFromText('LINESTRING(2 2, 8 8)');
SET @poly = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
SELECT ST_Contains(@poly, @line);

The following is the output:

1

The output confirms that the LineString geometry connecting points (2, 2) and (8, 8) is entirely contained within the square Polygon geometry.

Example 3: Test if a Polygon is contained within another Polygon

This example demonstrates how to test if a Polygon geometry is contained within another Polygon geometry.

SET @poly1 = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
SET @poly2 = ST_GeomFromText('POLYGON((2 2, 8 2, 8 8, 2 8, 2 2))');
SELECT ST_Contains(@poly1, @poly2);

The following is the output:

1

The output shows that the smaller Polygon geometry (@poly2) is entirely contained within the larger square Polygon geometry (@poly1).

Example 4: Test if a MultiPolygon is contained within a Polygon

This example shows how to test if a MultiPolygon geometry is contained within a Polygon geometry.

SET @multipoly = ST_GeomFromText('MULTIPOLYGON(((2 2, 4 2, 4 4, 2 4, 2 2)), ((6 6, 8 6, 8 8, 6 8, 6 6)))');
SET @poly = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
SELECT ST_Contains(@poly, @multipoly);

The following is the output:

1

The output confirms that the MultiPolygon geometry, consisting of two smaller polygons, is entirely contained within the larger square Polygon geometry.

Example 5: Test if a geometry from a table is contained within another geometry

This example demonstrates how to test if a geometry stored in a table is contained within another geometry.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly GEOMETRY);
INSERT INTO polygons (id, poly) VALUES
    (1, ST_GeomFromText('POLYGON((2 2, 8 2, 8 8, 2 8, 2 2))'));
SET @poly = ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');
SELECT id, ST_Contains(@poly, poly) AS contained FROM polygons;

The following is the output:

+----+-----------+
| id | contained |
+----+-----------+
|  1 |         1 |
+----+-----------+

In this example, a table named polygons is created with an id column and a poly column of type GEOMETRY. A polygon geometry is inserted into the table, and then the ST_Contains() function is used to test if the stored geometry is contained within the larger square Polygon geometry (@poly) for each row in the table.

Related Functions

The following are some functions related to the MariaDB ST_Contains() function:

• The MariaDB ST_Within() function is used to determine if a geometry is entirely within another geometry.
• The MariaDB ST_Intersects() function is used to test if two geometries intersect or overlap.
• The MariaDB ST_Crosses() function is used to test if two geometries cross each other.
• The MariaDB ST_Disjoint() function is used to test if two geometries are disjoint (do not intersect or touch).

Conclusion

The ST_Contains() function in MariaDB is a powerful spatial function that allows you to determine if a geometry is entirely contained within another geometry. This function is essential for various spatial analysis tasks, such as identifying spatial relationships, performing spatial queries, and validating spatial data integrity. By understanding the behavior of the ST_Contains() function for different geometry types and combinations, you can effectively work with spatial data and perform necessary operations to analyze and process spatial information in your applications.