Spatial operation functions

A spatial operation function is used to generate geometry objects. 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 within the distance d from the geometry value g. The results are in the same spatial reference system (SRS) as the geometry arguments. The syntax is as follows:

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

The d argument cannot be less than 0.1. Otherwise, an error is reported. If the argument is empty, ST_Buffer() returns an empty geometry. If the distance is 0, ST_Buffer() returns the geometry value 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 you to append up to three optional strategy arguments to the distance argument d. These arguments manipulate the buffer calculation. These arguments are byte string values generated by the ST_Buffer_Strategy() function for point, join, and end strategies:

• A point strategy is applicable to the Point and MultiPoint data types. If no point strategy is specified, the default value is ST_Buffer_Strategy('point_circle', 32).
• A join strategy applies to the LineString, MultiLineString, Polygon, and MultiPolygon data types. If no join strategy is specified, the default value is ST_Buffer_Strategy('join_round', 32).
• The end strategy applies to the LineString and MultiLineString data types. If no end strategy is specified, the default value is ST_Buffer_Strategy('end_round', 32).

You can specify up to one strategy for each type, without specific sequence requirements.

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

• You have specified multiple strategies (point, join, or end) for a given type.
• Values that are not subject to a strategy, such as arbitrary binary strings or numbers, are passed as a strategy.
• You have passed a Point strategy, but the geometry does not contain Point or MultiPoint values.
• You have passed an end or join strategy. However, the geometry does not contain LineString, Polygon, MultiLineString, or MultiPolygon values.

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

ST_Buffer_Strategy() returns a strategy string for buffer calculation by using ST_Buffer().

ST_Buffer_Strategy(strategy [, points_per_circle])

The first argument must be a string that indicates the strategy option.

• For a point strategy, the value can be point_circle or point_square.
• For a join strategy, the value can be join_round or join_miter.
• For an end strategy, the value can be end_round or end_flat.

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

Pay special attention to the arguments of the ST_Buffer_Strategy() function:

• If any argument is invalid, the ER_WRONG_ARGUMENTS error is returned.
• If the first argument is point_square or end_flat, do not specify the points_per_circle argument; otherwise, the ER_WRONG_ARGUMENTS error occurs.

ST_Transform

ST_Transform() transforms a geometry object from one SRS to another. The return value is a geometry object of the same type as the input geometry object. All coordinates are transformed to the target SRID, namely the value specified by target_srid. This function implements the transformation only in terms of geographic SRSs. Only when the SRID of the geometry argument is the same as the target SRID value, the function directly returns the input object with the valid SRID.

Take note of the following considerations for ST_Transform() arguments:

• For geometry arguments with SRID values of a geographic SRS, errors are not returned.
• If the geometry or target SRID argument has an SRID value that references an undefined SRS, the ER_SRS_NOT_FOUND error is returned.
• If the geometry object is in an SRS that the ST_Transform() function cannot transform, the ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED error is returned.
• If the target SRID is in an SRS that the ST_Transform() function cannot transform to, the ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED error is returned.
• If the geometry object is in an SRS that is not World Geodetic System 1984 (WGS84) and does not have a TOWGS84 clause, the ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84 error is returned.
• If the target SRID is in an SRS that is not WGS 84 and does not have the TOWGS84 clause, the ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84 error is returned.

The differences between ST_SRID(g, target_srid) and ST_Transform(g, target_srid) are as follows:

• ST_SRID() changes the geometry SRID value without transforming its coordinates.
• ST_Transform() not only changes the SRID value, but also transforms its geometry 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

ST_Difference(g1, g2) subtracts the intersection of geometry objects g1 and g2 from g1. It is equivalent to A - ST_Intersection(A, B). ST_Difference(g1, g2) returns a geometry object that represents the difference of g1 and g2.

Limitations are as follows:

• g1 and g2 must be in the same coordinate space.
• g1 and g2 must be valid geometry objects.
• If g1 and g2 do not intersect, ST_Difference(g1, g2) returns the complete g1.
• If g1 is fully contained in 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, ST_Difference() calculates the difference of two LineString objects. The first LineString object goes from point (50 100) to point (50 200), and the second LineString object goes from point (50 50) to point (50 150). The two LineString objects have an overlapping segment from point (50 100) to point (50 150).

When the second LineString object is subtracted from the first LineString object, the return result is a line segment that goes from point (50 150) to point (50 200). This is because the line segment exists only in the first LineString object and not in the second LineString object. ST_AsText() converts the result into a WKT string.

The return 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

ST_Union(g1, g2) returns a geometry object that represents the union of geometry objects g1 and g2. The union is a set of points in g1, g2, or both.

The ST_Union() function creates a new geometry object that includes all points in g1 and g2. If g1 and g2 are intersecting or adjacent polygons, the function returns a merged polygon. If g1 and g2 are lines or point sets, the function returns all lines and points.

Limitations are as follows:

• g1 and g2 must be in the same coordinate space.
• g1 and g2 must be valid geometry objects. That is, they are not self-intersecting or in any other invalid geometry structures.
• If g1 and g2 are discontiguous geometry objects, the function returns a MultiGeometry object, such as a MULTIPOLYGON or MULTILINESTRING object.

The syntax is as follows:

ST_Union(geometry g1, geometry g2)

Here is an example:

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

In this example, ST_Union() calculates the union of two LineString objects. The first LineString object goes from point (50 100) to point (50 200), and the second LineString object goes from point (50 50) to point (50 150). The two LineString objects have an overlapping segment from point (50 100) to point (50 150).

The ST_Union() function merges the two LineString objects and returns a MultiLineString object that contains two non-overlapping LineString objects: one from (50 100) to (50 200) and the other from (50 50) to (50 100). These two LineString objects together constitute the space covered by the original LineString objects. ST_AsText() converts the result into a WKT string.

The return 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

ST_SymDifference() returns a geometry object that represents the symmetric difference of geometry objects geomA and geomB. The symmetric difference is the set of points in geomA or geomB but not in their intersection.

This function is equivalent to ST_Difference(ST_Union(geomA, geomB), ST_Intersection(geomA, geomB)), which first uses (ST_Union(geomA, geomB)) to calculate the union of geomA and geomB and then subtracts their intersection calculated by using (ST_Intersection(geomA, geomB)) from the union.

Limitations are as follows:

• geomA and geomB must be in the same coordinate reference system (CRS).
• geomA and geomB must be valid geometry objects that do not intersect themselves or have other topological errors.

The symmetric difference may be geometry objects of a different type from the original type. For example, the symmetric difference of two overlapping polygons may be two separate polygons, or a MULTIPOLYGON object.

If geomA and geomB do not overlap (that is, their intersection is empty), the symmetric difference is equivalent to a simple combination of them.

If geomA and geomB are exactly the same, the symmetric difference is an empty geometry object.

The syntax is as follows:

ST_SymDifference(geometry geomA, geometry geomB)

Here is an example:

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, ST_GeomFromText() creates two geometry objects of the MULTIPOINT type, which are stored in the @g1 and @g2 variables. The two geometry objects are MULTIPOINT(5 0,15 10,15 25) and MULTIPOINT(1 1,15 10,15 25).

Then, ST_SymDifference() calculates the symmetric difference of @g1 and @g2, which means to find points not shared by the two MULTIPOINT objects. In this example, points (15 10) and (15 25) are shared, so they are not included in the symmetric difference.

Finally, ST_AsText() converts the result into a WKT string. The query result is MULTIPOINT((1 1),(5 0)), indicating that the differences between @ g1 and @ g2 are points (1 1) and (5 0), which are exactly the points that they don't share.

The return result is as follows:

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

_ST_ClipByBox2D

_ST_ClipByBox2D() clips a geometry object. This function returns the intersection of the input geometry object geom and 2D bounding box (box2d) as a new geometry object. The operation is similar to using a pair of scissors to clip a geometry object along the bounding box.

As a fast clipping function, _ST_ClipByBox2D() is designed to provide high-performance clipping operations, but this also brings some limitations and considerations.

Limitations are as follows:

The function does not validate the input geometry object geom. Therefore, if the input geometry object is invalid, the output result may also be invalid. Similarly, the function does not guarantee the validity of the output geometry object. Therefore, further processing may be required to make the output a valid geometry object. As a fast clipping function, _ST_ClipByBox2D() may not be suitable for scenarios that require precise clipping or validity of the clipping result.

The syntax is as follows:

_ST_ClipByBox2D(geometry geom, box2d box);

where:

• geom specifies the original geometry object to be clipped.
• box specifies the 2D bounding box for clipping. Although the parameter type is box2d, the function actually permits any geometry objects and will convert them into corresponding bounding boxes.

Here is an example:

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, ST_GeomFromText() creates a polygon with four vertices: points (-2 -2), (-2 11), (11 11), and (11 -2) in sequence.

Then, _ST_MakeEnvelope() creates a bounding box, which defines a rectangle between coordinates (0, 0) and (10, 10).

_ST_ClipByBox2D() clips the polygon against the bounding box, leaving only the part of the polygon that intersects the bounding box.

Finally, ST_AsText() converts the generated polygon into a WKT string. The query result shows that the generated polygon is a rectangle whose sides coincide with the bounding box, that is, a rectangle with the lower-left vertex at (0, 0) and the upper-right vertex at (10, 10).

The return 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