Spatial operation functions

A spatial operation function is used to generate geometric objects. OceanBase Database supports the following spatial operation functions: ST_Buffer(), ST_Buffer_Strategy(), and ST_Transform().

ST_Buffer

ST_Buffer() returns all points within the distance d from the geometric value g. The results are in the same spatial reference system (SRS) as the geometric arguments. Syntax:

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 a null value. If the distance is 0, ST_Buffer() returns the geometric value g.

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, theER_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.

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 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, theER_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

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

When you set the arguments for the ST_Transform() function, take note of the following limits:

• For geometric arguments with SRID values of a geographic SRS, errors are not returned.
• If the geometric or target SRID argument has an SRID value that references an undefined SRS, the ER_SRS_NOT_FOUND error is returned.
• If the geometric object is in an SRS that the ST_Transform() function cannot convert, the ER_TRANSFORM_SOURCE_SRS_NOT_SUPPORTED error is returned.
• If the target SRID is in an SRS that the ST_Transform() function cannot convert to, the ER_TRANSFORM_TARGET_SRS_NOT_SUPPORTED error is returned.
• If the geometric 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 geometric SRID value without converting its coordinates.
• ST_Transform() not only changes the SRID value, but also converts its geometric coordinates.

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