Spatial constructor functions

Spatial constructor functions are used to construct new spatial data types and geometric shapes.

OceanBase Database currently supports the _ST_MakeEnvelope spatial constructor function.

_ST_MakeEnvelope

The _ST_MakeEnvelope function constructs a rectangle by using the coordinates of the lower-left and upper-right corners of the rectangle. This rectangle is commonly used as a bounding box for spatial queries.

Syntax:

_ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer srid=unknown)

Parameters:

• float xmin: the x-coordinate of the lower-left corner of the rectangle (usually the longitude).
• float ymin: the y-coordinate of the lower-left corner of the rectangle (usually the latitude).
• float xmax: the x-coordinate of the upper-right corner of the rectangle (usually the longitude).
• float ymax: the y-coordinate of the upper-right corner of the rectangle (usually the latitude).
• integer srid: the identifier of the spatial reference system (Spatial Reference System Identifier), which is a valid identifier used to specify the spatial reference system for the coordinates. The default value unknown indicates that no spatial reference system is specified.

Limitations:

• The coordinate values must be valid. xmin must be less than xmax, and ymin must be less than ymax to form a valid rectangle.

Example 1:

obclient> SELECT ST_AsText(_ST_MakeEnvelope(10, 10, 11, 11, 4326));

In this example, a rectangle is created with the lower-left corner at (10, 10) and the upper-right corner at (11, 11).

The result is as follows:

+-----------------------------------------------------+
| ST_AsText( _ST_MakeEnvelope(10, 10, 11, 11, 4326) ) |
+-----------------------------------------------------+
| POLYGON((10 10,11 10,11 11,10 11,10 10))            |
+-----------------------------------------------------+
1 row in set

Example 2:

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, a rectangle is first created with the lower-left corner at (0, 0) and the upper-right corner at (10, 10) as the clipping range. Then, the _ST_ClipByBox2D function is used to clip the original polygon POLYGON((-2 -2, -2 11, 11 11, 11 -2, -2 -2)) with the rectangle.

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