Spatial operation functions

Spatial operation functions are used to generate geometry. OceanBase Database 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 that are at a distance less than or equal to d from 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 will be returned. If the parameter is NULL, ST_Buffer() returns a NULL value. If the distance is 0, ST_Buffer() returns the geometry parameter g.

Here is an example:

obclient [test]> SET @pt = ST_GeomFromText('POINT(1 1)');
Query OK, 0 rows affected

obclient [test]> SELECT ST_AsText(ST_Buffer(@pt, 0));
+------------------------------+
| ST_AsText(ST_Buffer(@pt, 0)) |
+------------------------------+
| POINT(1 1)                   |
+------------------------------+
1 row in set

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:

• Point strategies apply to point and multi-point geometries. If no point strategy is specified, the default is ST_Buffer_Strategy('point_circle', 32).
• Join strategies apply to LineString, MultiLineString, Polygon, and MultiPolygon geometries. If no join strategy is specified, the default is ST_Buffer_Strategy('join_round', 32).
• End strategies apply to LineString and MultiLineString geometries. If no end strategy is specified, the default is ST_Buffer_Strategy('end_round', 32).

Each type can have at most one strategy, and there is no order requirement.

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

• Multiple strategies (point, join, or end) are specified for a given type.
• A value that is not a strategy (such as an arbitrary 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:

obclient [test]> SET @pt_strategy = ST_Buffer_Strategy('point_square');
Query OK, 0 rows affected

obclient [test]> 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

obclient [test]> SET @ls = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');
Query OK, 0 rows affected

obclient [test]> SET @end_strategy = ST_Buffer_Strategy('end_flat');
Query OK, 0 rows affected

obclient [test]> SET @join_strategy = ST_Buffer_Strategy('join_round', 5);
Query OK, 0 rows affected

obclient [test]> 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

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 that indicates 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 of points_per_circle is the value of the max_points_in_geometry system variable.

Additional considerations for the parameters of ST_Buffer_Strategy():

• If any parameter is invalid, an error ER_WRONG_ARGUMENTS will be 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 will be returned.

ST_Transform

The ST_Transform() function is used to convert 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 conversion is supported only for geographic SRSs. If the SRID of the geometry parameter is the same as the target SRID, the input object with the valid SRID is directly returned.

Consider the following when using the parameters of ST_Transform():

• Geometries with geographic SRSs do not produce errors.
• If the geometry or target SRID parameter has an SRID value that references an undefined spatial reference system (SRS), an error ER_SRS_NOT_FOUND will be returned.
• If the geometry object is in an SRS that ST_Transform() cannot convert, an error ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED will be returned.
• If the target SRID is in an SRS that ST_Transform() cannot convert to, an error ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED will be 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 will be 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 will be 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 also converts its coordinates.

Here is an example:

obclient [test]> SET @pt = ST_GeomFromText('POINT(52.381389 13.064444)', 4326);
Query OK, 0 rows affected (

obclient [test]> SELECT ST_AsText(@pt);
+----------------------------+
| ST_AsText(@pt)             |
+----------------------------+
| POINT(52.381389 13.064444) |
+----------------------------+
1 row in set

obclient [test]> SET @pt = ST_Transform(@pt, 4230);
Query OK, 0 rows affected

obclient [test]> SELECT ST_AsText(@pt);
+---------------------------------------------+
| ST_AsText(@pt)                              |
+---------------------------------------------+
| POINT(52.38208611407426 13.065520672345304) |
+---------------------------------------------+
1 row in set

ST_Difference

The ST_Difference(g1, g2) function subtracts the intersecting part of another geometry object g2 from a geometry object g1, 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 and considerations:

• The geometry objects g1 and g2 must be in the same coordinate space.
• The g1 and g2 should 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:

obclient > 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 case, 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 have an overlapping region 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 returned 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

ST_Union

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

The ST_Union function creates a new geometric object that includes all the 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 the lines and points.

Limitations:

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

Syntax:

ST_Union(geometry g1, geometry g2)

Examples:

obclient > 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 geometric 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 have an overlapping region from point (50 100) to point (50 150).

The ST_Union function merges these two line segments and returns a MULTILINESTRING geometric object that contains two non-overlapping line segments: one from (50 100) to (50 200), and another from (50 50) to (50 100). Together, these two line segments cover the same spatial extent as the original line 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

ST_SymDifference

The ST_SymDifference function computes the symmetric difference between two geometric 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 this function is equivalent to first computing the union of geomA and geomB using ST_Union(geomA, geomB), and then subtracting their intersection from the union, which can be expressed as ST_Difference(ST_Union(geomA, geomB), ST_Intersection(geomA, geomB)).

Limitations:

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

The symmetric difference may produce results of a different geometric type than the original. For example, the symmetric difference of two overlapping polygons could result in two separate polygons or a multi-polygon (MULTIPOLYGON).

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

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

Syntax:

ST_SymDifference(geometry geomA, geometry geomB)

Examples:

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

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

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

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

The ST_SymDifference function is then used to compute the symmetric difference between @g1 and @g2. This means identifying the points that are not shared between the two MULTIPOINT geometric 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 geometric object to WKT format. The query result is MULTIPOINT((1 1),(5 0)), indicating that the only differences between @g1 and @g2 are 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

_ST_ClipByBox2D

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

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

Limitations:

This function does not validate the input geometric object geom, so if the input geometric object is invalid, the output result may also be invalid. Similarly, the function does not guarantee that the output geometric object is valid, so the output geometric object may require further processing to ensure its validity. Due to its fast clipping nature, it may not be suitable for scenarios requiring precise clipping or where the clipping result must be a valid geometric object.

Syntax:

_ST_ClipByBox2D(geometry geom, box2d box);

Parameters:

• geom: The original geometric object to be clipped.
• box: The 2D bounding box used for clipping. Although the parameter name is box2d, it can accept any geometric object, which will be converted to the corresponding bounding box.

Examples:

obclient > 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 geometric object with four sides. The sides go from (-2 -2) to (-2 11), then to (11 11), then to (11 -2), and finally back to (-2 -2).

Next, the _ST_MakeEnvelope function is used to create a bounding box. This bounding box is defined between the coordinates (0, 0) and (10, 10).

Then, 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 into a WKT string. The query result shows that the clipped polygon is also a rectangle, with its sides aligned 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