How the ST_DIFFERENCE() function works in MariaDB?

The ST_DIFFERENCE() function in MariaDB is used to calculate the geometric difference between two spatial objects. It returns a geometry representing the portion of the first object that does not intersect with the second object.

Syntax

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

ST_DIFFERENCE(g1, g2)

• g1: A geometry value representing the first spatial object.
• g2: A geometry value representing the second spatial object.

The function returns a geometry representing the portion of g1 that does not intersect with g2. If the geometries do not overlap or intersect, it returns the original geometry g1. If g1 is completely contained within g2, it returns an empty geometry.

Examples

Example 1: Difference between Polygons

This example demonstrates the difference between two polygons.

SET @poly1 = ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))');
SET @poly2 = ST_GeomFromText('POLYGON((2 2, 6 2, 6 6, 2 6, 2 2))');

SELECT ST_AsText(ST_DIFFERENCE(@poly1, @poly2));

Output:

POLYGON((0 0, 0 4, 2 4, 2 2, 0 2, 0 0))

The output shows the portion of the first polygon that does not intersect with the second polygon.

Example 2: Difference between a Polygon and a Line

This example calculates the difference between a polygon and a line.

SET @poly = ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))');
SET @line = ST_GeomFromText('LINESTRING(2 1, 2 3)');

SELECT ST_AsText(ST_DIFFERENCE(@poly, @line));

Output:

MULTIPOLYGON(((0 0, 0 4, 2 4, 2 3, 2 1, 0 1, 0 0)), ((2 3, 4 3, 4 4, 2 4, 2 3)))

The output shows the portion of the polygon that does not intersect with the line, which is a multi-polygon.

Example 3: Difference between Geometries from a Table

This example demonstrates the difference between geometries stored in a table.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT PRIMARY KEY, poly GEOMETRY);

INSERT INTO polygons (id, poly) VALUES
  (1, ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'));

INSERT INTO polygons (id, poly) VALUES
  (2, ST_GeomFromText('POLYGON((2 2, 6 2, 6 6, 2 6, 2 2))'));

SELECT ST_AsText(ST_DIFFERENCE(
  (SELECT poly FROM polygons WHERE id = 1),
  (SELECT poly FROM polygons WHERE id = 2)
)) AS diff;

Output:

POLYGON((0 0, 0 4, 2 4, 2 2, 0 2, 0 0))

The output shows the portion of the first polygon that does not intersect with the second polygon.

Example 4: Difference between a Polygon and a Point

This example calculates the difference between a polygon and a point.

SET @poly = ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))');
SET @point = ST_GeomFromText('POINT(2 2)');

SELECT ST_AsText(ST_DIFFERENCE(@poly, @point));

Output:

POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))

The output shows the original polygon, as the point is completely contained within the polygon.

Example 5: Difference between a Polygon and a Multi-Polygon

This example demonstrates the difference between a polygon and a multi-polygon.

SET @poly = ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))');
SET @multipoly = ST_GeomFromText('MULTIPOLYGON(((1 1, 3 1, 3 3, 1 3, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))');

SELECT ST_AsText(ST_DIFFERENCE(@poly, @multipoly));

Output:

MULTIPOLYGON(((0 0, 0 4, 1 4, 1 3, 1 1, 0 1, 0 0)), ((3 3, 3 4, 4 4, 4 0, 3 0, 3 1, 3 3)))

The output shows the portions of the original polygon that do not intersect with the multi-polygon.

Related Functions

The following are some functions related to MariaDB ST_DIFFERENCE():

• MariaDB ST_UNION() function is used to combine two spatial objects into a single geometry.
• MariaDB ST_INTERSECTS() function is used to determine whether two spatial objects intersect each other.
• MariaDB ST_INTERSECTION() function is used to calculate the intersection between two spatial objects.
• MariaDB ST_SYMDIFFERENCE() function is used to calculate the symmetric difference between two spatial objects.

Conclusion

The ST_DIFFERENCE() function in MariaDB is a powerful tool for performing geometric operations on spatial data. It allows you to calculate the portion of one spatial object that does not intersect with another object, which can be useful in various spatial analyses and applications.