How the ST_INTERSECTS() function works in MariaDB?

The ST_INTERSECTS() function in MariaDB is used to determine whether two given geometries intersect or overlap with each other. It is a part of the spatial extension in MariaDB, which provides a set of functions and operations for working with spatial data types.

Syntax

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

ST_INTERSECTS(g1, g2)

• g1: The first geometry value to be checked for intersection.
• g2: The second geometry value to be checked for intersection.

The function returns 1 if the two geometries intersect or overlap, and 0 if they do not.

Examples

Example 1: Checking intersection between two points

This example demonstrates how to check if two points intersect, which is not possible since points are 0-dimensional geometries.

SELECT ST_INTERSECTS(POINT(1, 1), POINT(2, 2));

The following is the output:

0

Since points are 0-dimensional geometries, they cannot intersect with each other, and the function returns 0.

Example 2: Checking intersection between a point and a polygon

This example shows how to check if a point lies within a polygon.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly POLYGON);
INSERT INTO polygons (id, poly) VALUES (1, ST_POLYGONFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));

SELECT ST_INTERSECTS(POINT(5, 5), (SELECT poly FROM polygons WHERE id = 1));

The following is the output:

1

The point (5, 5) lies within the polygon defined by the coordinates (0 0, 10 0, 10 10, 0 10, 0 0), so the function returns 1, indicating that they intersect.

Example 3: Checking intersection between two polygons

This example demonstrates how to check if two polygons intersect or overlap.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly POLYGON);
INSERT INTO polygons (id, poly) VALUES
    (1, ST_POLYGONFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')),
    (2, ST_POLYGONFROMTEXT('POLYGON((5 5, 15 5, 15 15, 5 15, 5 5))'));

SELECT ST_INTERSECTS((SELECT poly FROM polygons WHERE id = 1),
                     (SELECT poly FROM polygons WHERE id = 2));

The following is the output:

1

The two polygons defined by the coordinates (0 0, 10 0, 10 10, 0 10, 0 0) and (5 5, 15 5, 15 15, 5 15, 5 5) overlap with each other, so the function returns 1.

Example 4: Checking intersection between a linestring and a polygon

This example shows how to check if a linestring intersects with a polygon.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly POLYGON);
INSERT INTO polygons (id, poly) VALUES (1, ST_POLYGONFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));

SELECT ST_INTERSECTS(ST_GEOMFROMTEXT('LINESTRING(5 5, 15 15)'), (SELECT poly FROM polygons WHERE id = 1));

The following is the output:

1

The linestring defined by the coordinates (5 5, 15 15) intersects with the polygon defined by the coordinates (0 0, 10 0, 10 10, 0 10, 0 0), so the function returns 1.

Example 5: Checking intersection between two geometries with different spatial reference systems (SRSs)

This example demonstrates how to check if two geometries with different spatial reference systems (SRSs) intersect.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly POLYGON SRID 4326);
INSERT INTO polygons (id, poly) VALUES (1, ST_POLYGONFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));

SELECT ST_INTERSECTS(ST_GEOMFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 3857),
                     (SELECT poly FROM polygons WHERE id = 1));

The following is the output:

0

The two polygons are defined with different spatial reference systems (SRIDs): 3857 and 4326. Since the geometries have different SRSs, they are considered non-intersecting, and the function returns 0.

Related Functions

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

• MariaDB ST_CONTAINS() function is used to check if one geometry contains another geometry.
• MariaDB ST_CROSSES() function is used to check if two geometries cross each other.
• MariaDB ST_DISJOINT() function is used to check if two geometries are disjoint (do not intersect or touch each other).
• MariaDB ST_EQUALS() function is used to check if two geometries are spatially equal.
• MariaDB ST_OVERLAPS() function is used to check if two geometries overlap with each other.
• MariaDB ST_TOUCHES() function is used to check if two geometries touch each other.
• MariaDB ST_WITHIN() function is used to check if one geometry is within another geometry.

Conclusion

The ST_INTERSECTS() function in MariaDB is a powerful tool for working with spatial data. It allows you to determine whether two geometries intersect or overlap with each other. By understanding the syntax and examples provided, you can effectively utilize this function in your spatial queries and analyses.