Spatial operators are used to generate geometric shapes. OceanBase AI Database currently supports the following spatial operators: ST_BUFFER(), ST_BUFFER_STRATEGY(), ST_Transform(), ST_difference(), ST_union(), ST_sym_difference(), and _ST_ClipByBox2D().
ST_Buffer
ST_BUFFER() returns all points that are at a distance less than or equal to d from the geometric value g. The result and the geometric parameter are in the same spatial reference system (SRS). The syntax is as follows:
ST_Buffer(g, d [, strategy1 [, strategy2 [, strategy3]]])
The value of the d parameter must be greater than or equal to 0.1; otherwise, an error is returned. If the parameter is empty, ST_BUFFER() returns a null value. If the distance is 0, ST_BUFFER() returns the geometric 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 buffer calculation. These parameters are byte-string values generated by the ST_BUFFER_Strategy() function, used for the point, connection, 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 concatenation strategy applies to LineString, MultiLineString, Polygon, and MultiPolygon. If not specified, the default is
ST_BUFFER_STRATEGY('join_round', 32'). - The end strategy applies to LineString and MultiLineString. If not specified, the default is
ST_BUFFER_STRATEGY('end_round', 32').
You can specify at most one policy for each type, and there is no specific order.
If the buffer strategy is invalid, an ER_wrong_ARGUMENTS error is returned. The strategy is invalid in any of the following cases:
- Multiple strategies (point, join, or end) are specified for a given type.
- Values that are not strategies (such as arbitrary binary strings or numbers) are passed as the
strategyparameter. - A
Pointstrategy is passed, and the geometry does not containPointorMultiPointvalues. - The end or connection strategy is passed, and the value does not contain
LineString,Polygon,MultiLinestring, orMultiPolygon.
An example is as follows:
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 representing a buffer strategy for use with ST_BUFFER().
ST_Buffer_Strategy(strategy [, points_per_circle])
The first parameter must be a string indicating the policy options:
- For the point strategy, the allowed values are
point_circleandpoint_square. - For the join strategy, the allowed values are
join_roundandjoin_miter. - For the termination strategy, the allowed values are
end_roundandend_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.
Special attention should be paid to the parameters of ST_BUFFER_STRATEGY():
- If any parameter is invalid, the
ER_wrong_ARGUMENTSerror is returned. - If the first parameter is
point_squareorend_flat, you cannot specify thepoints_per_circleparameter; otherwise, anER_wrong_ARGUMENTSerror occurs.
ST_Transform
The ST_Transform() function is used to transform a geometric object from one spatial reference system (SRS) to another. The return value is a geometric object of the same type as the input object, with all coordinates transformed to the target SRID, which is target_srid. The transformation supported by this function is limited to geographic SRSs. An input object with a valid SRID is returned directly only when the SRID of the geometric parameter matches the target SRID value.
Note the following considerations for the parameters of ST_Transform():
- Geometric parameters with an SRID value of a geographic SRS will not cause errors.
- If the geometry or target SRID parameter has an SRID value that references an undefined Spatial Reference System (SRS), the
ER_SRS_NOT_FOUNDerror is reported. - If the geometric object is located in an SRS that cannot be transformed by
ST_Transform(), the errorER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTEDis returned. - If the target SRID is not within the set of SRSs that
ST_Transform()can convert to, an errorER_TRANSFORM_TARGET_SRS_NOT_SUPPORTEDis returned. - If the geometry object is located in a SRS that is not WGS 84 and lacks the TOWGS84 clause, an error
ER_TRANSFORM_SOURCE_SRS_MISSING_TOWGS84will be reported. - If the target SRID is located in a SRS that is not WGS 84 and lacks a TOWGS84 clause, an error
ER_TRANSFORM_TARGET_SRS_MISSING_TOWGS84will be reported.
The differences between ST_SRID(g, target_srid) and ST_Transform(g, target_srid) are as follows:
ST_SRID()changes the geometric SRID value without converting its coordinates.ST_Transform()not only changes the SRID value but also transforms the geometric coordinates.
For 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 is used to subtract the part of a geometric object g1 that intersects with another geometric object g2, which is equivalent to A - ST_Intersection(A, B). It returns the part of geometric object g1 that does not intersect with geometric object g2.
Limitations:
- The geometric objects
g1andg2must be in the same coordinate space. g1andg2must be valid geometric objects.- If
g1andg2do not intersect, thenST_Difference(g1, g2)will return a complete copy ofg1. - If
g1is completely contained ing2, thenST_Difference(g1, g2)returns an empty geometry object.
The syntax is as follows:
ST_Difference(geometry g1, geometry g2)
For 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 geometric objects. Here, 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 area, which is from point (50, 100) to point (50, 150).
After subtracting the second line segment from the first, what remains is the line segment from point (50, 150) to point (50, 200). This is because this part of the line segment only exists in the first LineString and not in the second. The query result is converted into a WKT-formatted string using the ST_AsText function.
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
The ST_Union(g1, g2) function merges the spatial contents of two geometric objects g1 and g2 and returns their union. 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 contains all points from g1 and g2. If g1 and g2 are intersecting or adjacent polygons, the result is a merged polygon. If they are sets of lines or points, the result includes all lines and points.
Limitations:
g1andg2must be in the same coordinate space.g1andg2must be valid geometric objects, meaning they have no self-intersections or other invalid geometries.- If
g1andg2are discontinuous geometric objects, the result will be a MultiGeometry, such asMULTIPOLYGONorMULTILINESTRING.
The syntax is as follows:
ST_Union(geometry g1, geometry g2)
An example is shown below:
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 compute 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 overlap from point (50 100) to point (50 150).
The ST_Union function merges these two line segments and returns a MULTILINESTRING geometric object containing two non-overlapping line segments: one from (50 100) to (50 200) and another from (50 50) to (50 100). These two line segments together cover the spatial range originally covered by the line segments. The query result is converted into a WKT-formatted string using the ST_AsText function.
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
The ST_SymDifference function calculates the symmetric difference between two geometric objects, geomA and geomB, and returns the non-overlapping part. The symmetric difference is the set of all points that belong to either geomA or geomB, but not to their intersection.
The result of this function is equivalent to first calculating the union of geomA and geomB (ST_Union(geomA, geomB)) and then subtracting their intersection (ST_Intersection(geomA, geomB)) from the union, that is, ST_Difference(ST_Union(geomA, geomB), ST_Intersection(geomA, geomB)).
Limitations:
geomAandgeomBmust use the same Coordinate Reference System (CRS).geomAandgeomBmust be valid geometric objects without self-intersections or other topological errors.
The symmetric difference operation may produce results that are different from the original geometric types. For example, the symmetric difference of two overlapping polygons may be two separate polygons or a multipolygon.
If there is no overlap between geomA and geomB (i.e., their intersection is empty), the symmetric difference is equivalent to their simple union.
If geomA and geomB are identical, the symmetric difference will be an empty geometric object.
The syntax is as follows:
ST_SymDifference(geometry geomA, geometry geomB)
For 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, the ST_GeomFromText function is first used to create two geometry objects of type MULTIPOINT, which are stored in the variables @g1 and @g2. The two geometry objects are MULTIPOINT(5 0,15 10,15 25) and MULTIPOINT(1 1,15 10,15 25) respectively.
Then, the ST_SymDifference function is used to compute the symmetric difference between @g1 and @g2, which identifies the points that are not shared by 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 set.
Finally, the ST_AsText function is used to convert the geometric objects to WKT format. The query result is MULTIPOINT((1 1),(5 0)), which indicates that the only difference between @g1 and @g2 lies in the points (1, 1) and (5, 0), which are the points they do not share.
The return 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. It intersects the input geometric object geom with a two-dimensional bounding box (box2d) box and returns the intersection as the new geometric object. This operation is similar to using "scissors" to cut the geometric object along the boundary box.
Because _ST_ClipByBox2D is a fast clipping function designed to provide high-performance clipping operations, it also introduces 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, this function does not guarantee that the output geometry is valid. Therefore, the output geometric object may require further processing to ensure its validity. Rapid clipping may not be suitable for scenarios that require precise clipping or where the clipped result must be a legal geometric shape.
The syntax is as follows:
_ST_ClipByBox2D(geometry geom, box2d box);
Parameter description:
geom: The original geometric object to be clipped.box: The 2D bounding box used for clipping. Although the parameter is namedbox2d, it can actually accept any geometric object, and the function will convert it into its corresponding bounding box.
For 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, the ST_GeomFromText function is used to create a polygonal geometry object. The polygon has four sides: from (-2, -2) to (-2, 11), then to (11, 11), then to (11, -2), and finally closes at (-2, -2).
Then, _ST_MakeEnvelope is used to create a bounding box, which defines a rectangle 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 that intersects with the box.
Finally, the ST_AsTex function converts the clipped polygon into a WKT string. The query result shows that the clipped polygon is also a rectangle, whose sides coincide with the bounding box, specifically a rectangle with its lower-left corner at (0, 0) and upper-right corner 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