Spatial operation functions

Spatial operation functions are used to generate geometry. seekdb currently supports the following spatial operation functions: ST_Buffer(), ST_Buffer_Strategy(), ST_Transform(), ST_Difference(), ST_Union(), ST_SymDifference(), and _ST_ClipByBox2D().

ST_Buffer

ST_Buffer() returns all points within a distance d of the geometry value g. The result is in the same spatial reference system (SRS) as the geometry parameter. The syntax is as follows:

ST_Buffer(g, d [, strategy1 [, strategy2 [, strategy3]]])

The d parameter cannot be less than 0.1. Otherwise, an error is returned. If the parameter is NULL, ST_Buffer() returns NULL. If the distance is 0, ST_Buffer() returns the geometry parameter g.

Here is an example:

SET @pt = ST_GeomFromText('POINT(1 1)');
Query OK, 0 rows affected (0.000 sec)

SELECT ST_AsText(ST_Buffer(@pt, 0));
+------------------------------+
| ST_AsText(ST_Buffer(@pt, 0)) |
+------------------------------+
| POINT(1 1)                   |
+------------------------------+
1 row in set (0.001 sec)

ST_Buffer() allows up to three optional strategy parameters after the distance parameter d. The strategy affects the buffer calculation. These parameters are byte string values generated by the ST_Buffer_Strategy() function and are used for point, join, and end strategies:

• The point strategy applies to point and multipoint geometries. If not specified, the default is ST_Buffer_Strategy('point_circle', 32).
• The join strategy applies to LineString, MultiLineString, Polygon, and MultiPolygon geometries. If not specified, the default is ST_Buffer_Strategy('join_round', 32).
• The end strategy applies to LineString and MultiLineString geometries. If not specified, the default is ST_Buffer_Strategy('end_round', 32).

At most one strategy can be specified for each type, and the order does not matter.

If the buffer strategy is invalid, an error ER_WRONG_ARGUMENTS is returned. The strategy is invalid in any of the following cases:

• Multiple strategies (point, join, or end) are specified for a given type.
• A value that is not a strategy (such as any binary string or number) is passed as the strategy parameter.
• A Point strategy is passed, but the geometry does not contain a Point or MultiPoint value.
• An end or join strategy is passed, but the geometry does not contain a LineString, Polygon, MultiLinestring, or MultiPolygon value.

Here is an example:

SET @pt_strategy = ST_Buffer_Strategy('point_square');
Query OK, 0 rows affected (0.000 sec)

SELECT ST_AsText(ST_Buffer(@pt, 1, @pt_strategy));
+--------------------------------------------+
| ST_AsText(ST_Buffer(@pt, 1, @pt_strategy)) |
+--------------------------------------------+
| POLYGON((-1 -1,1 -1,1 1,-1 1,-1 -1))       |
+--------------------------------------------+
1 row in set (0.001 sec)

SET @ls = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');
Query OK, 0 rows affected (0.000 sec)

SET @end_strategy = ST_Buffer_Strategy('end_flat');
Query OK, 0 rows affected (0.000 sec)

SET @join_strategy = ST_Buffer_Strategy('join_round', 5);
Query OK, 0 rows affected (0.000 sec)

SELECT ST_AsText(ST_Buffer(@ls,3, @end_strategy, @join_strategy));
+------------------------------------------------------------------------------------+
| ST_AsText(ST_Buffer(@ls,3, @end_strategy, @join_strategy))                         |
+------------------------------------------------------------------------------------+
| POLYGON((3 2,5 2,5 8,0 8,-2.1213203435596424 7.121320343559643,-3 5,-3 0,3 0,3 2)) |
+------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

ST_Buffer_Strategy

The ST_Buffer_Strategy() function returns a string that specifies the strategy for buffer calculation in ST_Buffer().

ST_Buffer_Strategy(strategy [, points_per_circle])

The first parameter must be a string indicating the strategy option:

• For point strategies, the allowed values are point_circle and point_square.
• For join strategies, the allowed values are join_round and join_miter.
• For end strategies, the allowed values are end_round and end_flat.

If the first parameter is point_circle, join_round, join_miter, or end_round, the points_per_circle parameter must be a positive value. The maximum value for points_per_circle is the value of the max_points_in_geometry system variable.

The parameters of ST_Buffer_Strategy() must be noted as follows:

• If any parameter is invalid, an error ER_WRONG_ARGUMENTS is returned.
• If the first parameter is point_square or end_flat, the points_per_circle parameter cannot be specified. Otherwise, an error ER_WRONG_ARGUMENTS is returned.

ST_Transform

The ST_Transform() function converts a geometry object from one spatial reference system (SRS) to another. The returned value is a geometry object of the same type as the input geometry object, with all coordinates converted to the target SRID, which is target_srid. The function only supports conversion between geographic SRSs. If the SRID of the geometry parameter is the same as the target SRID value, the input object with a valid SRID is directly returned.

The parameters of ST_Transform() must be noted as follows:

• A geometry parameter with an SRID value of a geographic SRS does not generate an error.
• If the geometry or target SRID parameter has an SRID value that refers to an undefined spatial reference system (SRS), an error ER_SRS_NOT_FOUND is returned.
• If the geometry object is in an SRS that cannot be converted by ST_Transform(), an error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED is returned.
• If the target SRID is in an SRS that cannot be converted to by ST_Transform(), an error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED is returned.
• If the geometry object is in an SRS that is not WGS 84 and does not have a TOWGS84 clause, an error ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 is returned.
• If the target SRID is in an SRS that is not WGS 84 and does not have a TOWGS84 clause, an error ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 is returned.

The difference between ST_SRID(g, target_srid) and ST_Transform(g, target_srid) is as follows:

• ST_SRID() changes the SRID value of the geometry without converting its coordinates.
• ST_Transform() changes the SRID value of the geometry and converts its coordinates.

Here is an example:

SET @pt = ST_GeomFromText('POINT(52.381389 13.064444)', 4326);
Query OK, 0 rows affected (0.000 sec)

SELECT ST_AsText(@pt);
+----------------------------+
| ST_AsText(@pt)             |
+----------------------------+
| POINT(52.381389 13.064444) |
+----------------------------+
1 row in set (0.001 sec)

SET @pt = ST_Transform(@pt, 4230);
Query OK, 0 rows affected (0.000 sec)

SELECT ST_AsText(@pt);
+---------------------------------------------+
| ST_AsText(@pt)                              |
+---------------------------------------------+
| POINT(52.38208611407426 13.065520672345304) |
+---------------------------------------------+
1 row in set (0.001 sec)

ST_Difference

The ST_Difference(g1, g2) function subtracts the part of the geometry object g1 that intersects with the geometry object g2, which is equivalent to A - ST_Intersection(A, B). It returns the part of the geometry object g1 that does not intersect with the geometry object g2.

Limitations:

• The geometry objects g1 and g2 must be in the same coordinate space.
• The g1 and g2 must be valid geometry objects.
• If g1 and g2 do not intersect, ST_Difference(g1, g2) returns a complete copy of g1.
• If g1 is completely contained within g2, ST_Difference(g1, g2) returns an empty geometry object.

The syntax is as follows:

ST_Difference(geometry g1, geometry g2)

Here is an example:

SELECT ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)')));

In this example, the ST_Difference function is used to calculate the difference between two linear geometry objects. In this example, the first LineString starts at point (50 100) and ends at point (50 200), and the second LineString starts at point (50 50) and ends at point (50 150). These two line segments overlap from point (50 100) to point (50 150).

After subtracting the second line segment from the first, the remaining segment is from point (50 150) to point (50 200). This is because this part of the line segment exists only in the first LineString and not in the second LineString. The query result is converted to a Well-Known Text (WKT) string using the ST_AsText function.

The result is as follows:

+----------------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)'))) |
+----------------------------------------------------------------------------------------------------------------------+
| LINESTRING(50 150,50 200)                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

ST_Union

The ST_Union(g1, g2) function merges the spatial content of two geometry objects g1 and g2 and returns their union. The union is the set of all points that belong to g1, g2, or both.

The ST_Union function creates a new geometry object that includes all points from g1 and g2. If g1 and g2 are intersecting or adjacent polygons, the result will be a merged polygon. If they are line or point sets, the result will include all lines and points.

Limitations:

• g1 and g2 must be in the same coordinate space.
• g1 and g2 must be valid geometry objects, meaning they do not have self-intersections or other invalid geometry structures.
• If g1 and g2 are disjoint, the result will be a MultiGeometry object, such as MULTIPOLYGON or MULTILINESTRING.

Syntax:

ST_Union(geometry g1, geometry g2)

Examples:

SELECT ST_AsText(ST_Union(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)')));

In this example, the ST_Union function is used to calculate the union of two linear geometry objects. The first LineString goes from point (50 100) to point (50 200), and the second LineString goes from point (50 50) to point (50 150). These two line segments overlap from point (50 100) to point (50 150).

The ST_Union function merges these two line segments and returns a MULTILINESTRING geometry object containing two non-overlapping line segments: one from (50 100) to (50 200), and the other from (50 50) to (50 100). Together, these two line segments cover the same spatial extent as the original overlapping segments. The query result is converted to a WKT string using the ST_AsText function.

The result is as follows:

+-----------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_Union(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)'))) |
+-----------------------------------------------------------------------------------------------------------------+
| MULTILINESTRING((50 100,50 200),(50 50,50 100))                                                                 |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

ST_SymDifference

The ST_SymDifference function calculates the symmetric difference between two geometry objects geomA and geomB. The symmetric difference is the set of all points that belong to geomA or geomB but not to their intersection.

The result of the ST_SymDifference function is equivalent to first calculating the union of geomA and geomB using ST_Union(geomA, geomB), and then subtracting their intersection from the union using ST_Difference(ST_Union(geomA, geomB), ST_Intersection(geomA, geomB)).

Limitations:

• geomA and geomB must use the same coordinate reference system (CRS).
• geomA and geomB must be valid geometry objects without self-intersections or other topological errors.

The symmetric difference can result in a geometry type different from the original. For example, the symmetric difference of two overlapping polygons can result in two separate polygons or a MULTIPOLYGON.

If geomA and geomB do not overlap (i.e., their intersection is empty), the symmetric difference is simply the combination of the two geometries.

If geomA and geomB are identical, the symmetric difference will be an empty geometry object.

Syntax:

ST_SymDifference(geometry geomA, geometry geomB)

Examples:

SET @g1 = ST_GeomFromText('MULTIPOINT(5 0,15 10,15 25)');
Query OK, 0 rows affected (0.001 sec)

SET @g2 = ST_GeomFromText('MULTIPOINT(1 1,15 10,15 25)');
Query OK, 0 rows affected (0.001 sec)

SELECT ST_AsText(ST_SymDifference(@g1, @g2));

In this example, the ST_GeomFromText function is used to create two MULTIPOINT geometry objects, which are stored in variables @g1 and @g2. The two geometry objects are MULTIPOINT(5 0,15 10,15 25) and MULTIPOINT(1 1,15 10,15 25).

The ST_SymDifference function is then used to calculate the symmetric difference between @g1 and @g2, which means identifying the points that are not shared between the two MULTIPOINT geometry objects. In this example, the points (15 10) and (15 25) are shared, so they are not included in the symmetric difference.

Finally, the ST_AsText function is used to convert the geometry object to a WKT string. The query result is MULTIPOINT((1 1),(5 0)), indicating that the only difference between @g1 and @g2 is the presence of the points (1 1) and (5 0), which are not shared.

The result is as follows:

+---------------------------------------+
| ST_AsText(ST_SymDifference(@g1, @g2)) |
+---------------------------------------+
| MULTIPOINT((1 1),(5 0))               |
+---------------------------------------+
1 row in set (0.001 sec)

_ST_ClipByBox2D

The _ST_ClipByBox2D function is used for geometry clipping. This function intersects the input geometry object geom with a 2D bounding box (box2d) and returns the intersecting part as a new geometry object. This operation is similar to using a "scissors" to clip the geometry along the bounding box.

Since _ST_ClipByBox2D is a fast clipping function, it is designed to provide high-performance clipping operations, but this also brings some limitations and considerations:

Limitations:

This function does not check the validity of the input geometry geom. Therefore, if the input geometry is invalid, the output may also be invalid. Similarly, the function does not guarantee that the output geometry is valid, so the output geometry may need further processing to ensure its validity. Due to the fast clipping nature, this function may not be suitable for scenarios that require precise clipping or where the clipping result must be a valid geometry.

Syntax:

_ST_ClipByBox2D(geometry geom, box2d box);

Parameter explanations:

• geom: The original geometry object to be clipped.
• box: The 2D bounding box used for clipping. Although the parameter name is box2d, the function accepts any geometry object and converts it to the corresponding bounding box.

Examples:

SELECT ST_ASTEXT(_ST_ClipByBox2D(ST_GEOMFROMTEXT('POLYGON((-2 -2, -2 11, 11 11, 11 -2, -2 -2))'), _ST_MakeEnvelope(0,0,10,10)));

In this example, the ST_GeomFromText function is used to create a polygon geometry object with four sides. The polygon starts at (-2 -2), goes to (-2 11), then to (11 11), then to (11 -2), and finally closes back to (-2 -2).

The _ST_MakeEnvelope function is then used to create a bounding box defined by the coordinates (0, 0) and (10, 10).

The _ST_ClipByBox2D function uses the created bounding box to clip the polygon, retaining only the part of the polygon that intersects with the bounding box.

Finally, the ST_AsText function is used to convert the clipped polygon to a WKT string. The query result shows that the clipped polygon is also a rectangle, with its sides coinciding with the bounding box, specifically a rectangle with its lower-left corner at (0, 0) and upper-right corner at (10, 10).

The result is as follows:

+--------------------------------------------------------------------------------------------------------------------------+
| ST_ASTEXT(_ST_ClipByBox2D(ST_GEOMFROMTEXT('POLYGON((-2 -2, -2 11, 11 11, 11 -2, -2 -2))'), _ST_MakeEnvelope(0,0,10,10))) |
+--------------------------------------------------------------------------------------------------------------------------+
| POLYGON((0 0,0 10,10 10,10 0,0 0))                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)