Spatial analysis functions

Spatial analysis functions are used to perform complex spatial processing and analysis, such as buffer analysis, merging, and clipping.

OceanBase Database currently supports the following spatial analysis functions: ST_Length(), ST_Centroid(), and _ST_PointOnSurface().

ST_Length

The ST_Length(ls[,unit]) function is used to calculate the length of a linear geometry (such as a line segment, polyline, or curve). This function is typically applicable to LINESTRING and MULTILINESTRING geometry types.

To enable unit conversion, the geometry ls must be associated with a spatial reference system ID (SRID) that is not 0 (SRID = 0 usually indicates no spatial reference).

The unit used must be a valid unit supported by the database, and the database must be able to recognize the conversion factor corresponding to the unit.

The following table lists all units and their corresponding conversion factors:

+--------------------------------------+---------------------+
| UNIT_NAME                            | CONVERSION_FACTOR   |
+--------------------------------------+---------------------+
| British chain (Benoit 1895 A)        |          20.1167824 |
| British chain (Benoit 1895 B)        |  20.116782494375872 |
| British chain (Sears 1922 truncated) |           20.116756 |
| British chain (Sears 1922)           |  20.116765121552632 |
| British foot (1865)                  | 0.30480083333333335 |
| British foot (1936)                  |        0.3048007491 |
| British foot (Benoit 1895 A)         |  0.3047997333333333 |
| British foot (Benoit 1895 B)         | 0.30479973476327077 |
| British foot (Sears 1922 truncated)  | 0.30479933333333337 |
| British foot (Sears 1922)            |  0.3047994715386762 |
| British link (Benoit 1895 A)         |         0.201167824 |
| British link (Benoit 1895 B)         |  0.2011678249437587 |
| British link (Sears 1922 truncated)  |          0.20116756 |
| British link (Sears 1922)            |  0.2011676512155263 |
| British yard (Benoit 1895 A)         |           0.9143992 |
| British yard (Benoit 1895 B)         |  0.9143992042898124 |
| British yard (Sears 1922 truncated)  |            0.914398 |
| British yard (Sears 1922)            |  0.9143984146160288 |
| centimetre                           |                0.01 |
| chain                                |             20.1168 |
| Clarke's chain                       |       20.1166195164 |
| Clarke's foot                        |        0.3047972654 |
| Clarke's link                        |      0.201166195164 |
| Clarke's yard                        |        0.9143917962 |
| fathom                               |              1.8288 |
| foot                                 |              0.3048 |
| German legal metre                   |        1.0000135965 |
| Gold Coast foot                      |  0.3047997101815088 |
| Indian foot                          | 0.30479951024814694 |
| Indian foot (1937)                   |          0.30479841 |
| Indian foot (1962)                   |           0.3047996 |
| Indian foot (1975)                   |           0.3047995 |
| Indian yard                          |  0.9143985307444408 |
| Indian yard (1937)                   |          0.91439523 |
| Indian yard (1962)                   |           0.9143988 |
| Indian yard (1975)                   |           0.9143985 |
| kilometre                            |                1000 |
| link                                 |            0.201168 |
| metre                                |                   1 |
| millimetre                           |               0.001 |
| nautical mile                        |                1852 |
| Statute mile                         |            1609.344 |
| US survey chain                      |   20.11684023368047 |
| US survey foot                       | 0.30480060960121924 |
| US survey link                       |  0.2011684023368047 |
| US survey mile                       |  1609.3472186944375 |
| yard                                 |              0.9144 |
+--------------------------------------+---------------------+

Syntax:

ST_Length(ls[,unit])

Parameters:

• ls: the input parameter, representing the linear geometry whose length is to be calculated.
• unit: (optional) specifies the unit of length. If not provided, the default unit is meter (metre). Depending on the function implementation and the spatial database system, this parameter can accept various length units, such as 'foot' (foot) or 'centimetre' (centimeter).

Example:

obclient> SET @ls = ST_GeomFromText('LineString(1 1,2 2,3 3)', 4326);
Query OK, 0 rows affected

obclient> SELECT ST_Length(@ls, "foot");
+------------------------+
| ST_Length(@ls, "foot") |
+------------------------+
|     1029205.9131247795 |
+------------------------+
1 row in set

obclient> SELECT ST_Length(@ls);
+-------------------+
| ST_Length(@ls)    |
+-------------------+
| 313701.9623204328 |
+-------------------+
1 row in set

In this example, a linear geometry (LineString) is created using the ST_GeomFromText function. The geometry consists of three points with coordinates (1,1), (2,2), and (3,3), respectively.

The ST_Length function is then used to calculate the length of this LineString. First, the length is specified in feet, resulting in a length of 1029205.9131247795 feet. Then, the ST_Length function is called again without specifying the unit, using the default unit of meter, resulting in a length of 313701.9623204328 meters.

ST_Centroid

The ST_Centroid(geometry A) function is a spatial function used to calculate the centroid (geometric center) of a given geometry A. The centroid is the balance point of the geometry, which can be considered the center of the geometry. For two-dimensional geometries, such as polygons, the centroid is the average position of all points.

• If A is an empty geometry (with no points), the function may return NULL or an invalid result.
• If A is a complex geometry, the centroid calculation can become complex and computationally intensive, especially when the geometry has many vertices or internal holes.
• The calculated centroid point may not lie on the original geometry, especially when the geometry has an irregular shape or concave parts. For example, for a "U"-shaped polygon, the centroid may be located in the external space of the concave part.
• For linear geometries (such as line segments or polylines), the centroid is the average position of all points on the line. However, for complex linear geometries (such as self-intersecting or curved polylines), the centroid's position may be difficult to interpret intuitively.

Syntax:

ST_Centroid(geometry A)

Example:

obclient> SELECT ST_AsText(_ST_PointOnSurface(geom)) AS pt_on_surf, ST_AsText(ST_Centroid(geom)) AS centroid FROM (SELECT ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 8, 2 8, 2 2, 10 2, 10 0, 0 0))') AS geom) AS t;

In this example, a polygon geometry with a complex shape is defined, and two spatial analysis operations are performed on it:

The _ST_PointOnSurface function is used to obtain a point located on the surface of the polygon. This point is typically used to represent the position of the entire geometry and is guaranteed to be located inside the polygon. The ST_Centroid function is used to calculate the centroid of the polygon, which is the balance point of the polygon's parts, but it is not guaranteed to be located inside the polygon. The results are displayed in two columns: the first column pt_on_surf shows the coordinates of the point on the polygon's surface, and the second column centroid shows the coordinates of the calculated centroid. The query results show that the point on the surface is located at (1, 5), while the centroid's coordinates are (4.076923076923077, 5).

The returned result is as follows:

+------------+----------------------------+
| pt_on_surf | centroid                   |
+------------+----------------------------+
| POINT(1 5) | POINT(4.076923076923077 5) |
+------------+----------------------------+
1 row in set

_ST_PointOnSurface

The _ST_PointOnSurface(geometry a) function is used to return a point that is guaranteed to be located on the surface of the input geometry a. It can return a point that is exactly inside the geometry (for polygons and other areal geometries), rather than just the geometric or arithmetic center of the geometry.

The _ST_PointOnSurface function is typically meaningful only for areal geometries (such as polygons), because for linear or point geometries, the concept of an internal point may be unclear or not applicable.

The function may not return any of the internal points, but instead returns a specific point that can represent the entire geometry (such as a corner point or a point on an edge of the polygon).

Syntax:

_ST_PointOnSurface(geometry g1)

Example:

obclient> SELECT ST_AsText(_ST_PointOnSurface(geom)) AS pt_on_surf, ST_AsText(ST_Centroid(geom)) AS centroid FROM (SELECT ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 8, 2 8, 2 2, 10 2, 10 0, 0 0))') AS geom) AS t;

In this example, a polygon geometry with a specific shape is created from a string in Well-Known Text (WKT) format. Then, two functions are applied to this polygon: _ST_PointOnSurface and ST_Centroid.

The _ST_PointOnSurface function returns a point that is guaranteed to be located on the surface of the polygon, meaning the point is either on the polygon's boundary or inside it. The ST_Centroid function calculates and returns the geometric center of the polygon.

Finally, the query results display the coordinates of these two points in two columns: pt_on_surf for the point on the surface, and centroid for the centroid. In the provided example, pt_on_surf returns the point with coordinates (1 5), while centroid returns the point with coordinates (4.076923076923077 5).

The returned result is as follows:

+------------+----------------------------+
| pt_on_surf | centroid                   |
+------------+----------------------------+
| POINT(1 5) | POINT(4.076923076923077 5) |
+------------+----------------------------+
1 row in set