How the ST_INTERSECTION() function works in MariaDB?

The ST_INTERSECTION() function in MariaDB is used to calculate the geometric intersection between two geometry values. It returns a geometry representing the shared portion of the input geometries.

Syntax

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

ST_INTERSECTION(geom1, geom2)

• geom1: The first geometry value.
• geom2: The second geometry value.

The function returns a geometry value representing the intersection of geom1 and geom2. If the two geometries do not intersect, it returns an empty geometry. If either of the input geometries is NULL, it returns NULL.

Examples

Example 1: Intersecting two Polygons

This example demonstrates how to calculate the intersection between two Polygon geometries.

SELECT ST_INTERSECTION(
    ST_GeometryFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'),
    ST_GeometryFromText('POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))')
);

The output is as follows:

POLYGON((1 1,1 2,2 2,2 1,1 1))

This calculates the intersection between the two Polygons, which is another Polygon.

Example 2: Intersecting a LineString and a Polygon

This example demonstrates how to calculate the intersection between a LineString and a Polygon.

SELECT ST_INTERSECTION(
    ST_GeometryFromText('LINESTRING(0 0, 3 3)'),
    ST_GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))')
);

The output is as follows:

LINESTRING(1 1,2 2)

This calculates the intersection between the LineString and the Polygon, which is a LineString segment.

Example 3: Intersecting a Point and a Polygon

This example demonstrates how to calculate the intersection between a Point and a Polygon.

SELECT ST_INTERSECTION(
    ST_GeometryFromText('POINT(1.5 1.5)'),
    ST_GeometryFromText('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))')
);

The output is as follows:

POINT(1.5 1.5)

This calculates the intersection between the Point and the Polygon. Since the Point falls within the Polygon, the result is the Point itself.

Example 4: Handling non-intersecting geometries

This example demonstrates how the function handles non-intersecting geometries.

SELECT ST_INTERSECTION(
    ST_GeometryFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'),
    ST_GeometryFromText('POLYGON((3 3, 3 5, 5 5, 5 3, 3 3))')
);

The output is as follows:

GEOMETRYCOLLECTION()

This returns an empty GeometryCollection because the two Polygons do not intersect.

Example 5: Handling invalid input

This example demonstrates how the function handles invalid input.

SELECT ST_INTERSECTION(
    ST_GeometryFromText('POINT(1 1)'),
    NULL
);

The output is as follows:

NULL

This returns NULL because one of the input geometries is NULL.

Related Functions

The following are a few functions related to the MariaDB ST_INTERSECTION() function:

• MariaDB ST_Union() function is used to calculate the geometric union between two geometry values.
• MariaDB ST_Difference() function is used to calculate the geometric difference between two geometry values.
• MariaDB ST_SymDifference() function is used to calculate the symmetric difference between two geometry values.
• MariaDB ST_Intersects() function is used to check if two geometry values intersect.

Conclusion

The ST_INTERSECTION() function in MariaDB is a powerful tool for spatial data analysis and manipulation. It allows you to calculate the geometric intersection between two geometry values, which can be useful in various scenarios, such as spatial queries, data processing, and spatial analysis. The provided examples demonstrate how to use this function with different geometry types, including Polygons, LineStrings, and Points. Additionally, the examples cover handling non-intersecting geometries and invalid input. Overall, ST_INTERSECTION() is an essential function for working with spatial data in MariaDB.