How the INTERSECTS() function works in Mariadb?

The MariaDB INTERSECTS() function is used to determine whether two geometries intersect. It is a crucial function in spatial databases, allowing for the analysis of spatial relationships between different geometries, such as points, lines, and polygons.

Syntax

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

INTERSECTS(geometry1, geometry2)

• geometry1 and geometry2 are the geometries to be tested for intersection.

The function returns 1 (true) if geometry1 intersects with geometry2, and 0 (false) otherwise.

Examples

Example 1: Intersection of Two LineStrings

This example checks if two LineStrings intersect.

SELECT INTERSECTS(LineString(Point(0,0), Point(3,3)), LineString(Point(0,3), Point(3,0))) result;

The output for this statement is:

+--------+
| result |
+--------+
|      1 |
+--------+

This result indicates that the two LineStrings intersect.

Example 2: Intersection of Polygon and Point

Determining if a Point is within a Polygon.

SELECT INTERSECTS(PolygonFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'), Point(2, 2)) result;

The output for this statement is:

+--------+
| result |
+--------+
|      1 |
+--------+

The Point lies within the Polygon, hence they intersect.

Example 3: Non-Intersecting Geometries

Checking the intersection of non-intersecting geometries.

SELECT INTERSECTS(LineString(Point(0,0), Point(1,1)), LineString(Point(2,2), Point(3,3))) result;

The output for this statement is:

+--------+
| result |
+--------+
|      0 |
+--------+

The LineStrings do not intersect, so the function returns 0.

Example 4: Intersection with Table Data

Using INTERSECTS() to check intersections of geometries stored in a table.

DROP TABLE IF EXISTS geometries;
CREATE TABLE geometries (id INT, shape GEOMETRY);
INSERT INTO geometries (id, shape) VALUES (1, Point(2, 2)), (2, PolygonFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'));

SELECT g1.id, g2.id, INTERSECTS(g1.shape, g2.shape) AS intersects FROM geometries g1, geometries g2 WHERE g1.id = 1 AND g2.id = 2;

The output for this statement is:

+------+------+------------+
| id   | id   | intersects |
+------+------+------------+
|    1 |    2 |          1 |
+------+------+------------+

The Point and Polygon from the table intersect.

Example 5: Complex Intersection

Evaluating the intersection of complex geometries.

SELECT INTERSECTS(PolygonFromText('POLYGON((0 0, 5 0, 5 5, 0 5, 0 0),(1 1, 2 1, 2 2, 1 2, 1 1))'), PolygonFromText('POLYGON((3 3, 6 3, 6 6, 3 6, 3 3))')) result;

The output for this statement is:

+--------+
| result |
+--------+
|      1 |
+--------+

Related Functions

Below are a few functions related to the MariaDB INTERSECTS() function:

• MariaDB CONTAINS() function is used to determine if one geometry contains another.
• MariaDB WITHIN() function is used to determine if one geometry is within another.
• MariaDB TOUCHES() function is used to determine if two geometries have at least one point in common but their interiors do not intersect.

Conclusion

The INTERSECTS() function in MariaDB is an essential tool for spatial analysis, enabling the assessment of whether two geometries have any points in common. It is widely used in geographic information systems (GIS) and other applications that require spatial data processing. Mastery of this function, along with its related functions, can significantly enhance the capabilities of spatial databases in providing insights into spatial relationships.