Spatial analysis functions

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

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

ST_Length

ST_Length(ls[,unit]) calculates the length of a linear geometry, such as a line segment, polyline, or curve. This function is usually applied to LINESTRING and MULTILINESTRING objects.

The unit conversion takes effect only when the geometry ls has an associated spatial reference identifier (SRID), and the SRID is not 0. An SRID of 0 usually indicates no SRS.

The unit used must be a legal unit supported by the database, and the conversion factor of the unit must be identifiable to the database.

The units and their conversion factors are as follows:

+--------------------------------------+---------------------+
| 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 |
+--------------------------------------+---------------------+

The syntax is as follows:

ST_Length(ls[,unit])

where:

• ls indicates the input parameter that represents the linear geometry.
• unit indicates the unit of length, which is optional. If this parameter is not specified, the default unit metre is used. Various length units such as foot and centimetre are accepted, depending on function implementation and the spatial database system.

Here is an 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, ST_GeomFromText() creates a linear geometry object (LineString) consisting of three points: (1,1), (2,2), and (3,3).

Then, ST_Length() calculates the length of this LineString object. In the first function call, the length unit foot is specified. The length 1029205.9131247795 feet is returned. In the second call of ST_Length(), no unit is specified and the default unit metre is used. The length 313701.9623204328 meters is returned.

ST_Centroid

ST_Centroid(geometry A) calculates the mathematical centroid of the input geometry A. The centroid is the equilibrium point of each part of a geometry and can be regarded as the center of the geometry. For a two-dimensional geometry, such as a polygon, the centroid is the mean position of all points in the polygon.

• If A is an empty geometry with no points, the function may return NULL or invalid results.
• If A is a complex geometry, calculation of the centroid can become complex and intensive, especially for a geometry with many vertices or internal holes.
• The centroid of a geometry may be outside the geometry, especially when the geometry has an irregular shape or a concave part. For example, for a U-shaped polygon, the centroid may be in the exterior to “U”.
• For a linear geometry, such as a line segment or a polyline, the centroid is the mean position of all points on the line. However, for complex lines such as self-intersecting or curved polylines, the centroid may be difficult to intuitively interpret.

The syntax is as follows:

ST_Centroid(geometry A)

Here is an 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 with a complex shape is determined and two kinds of spatial analysis operations are performed on the polygon.

_ST_PointOnSurface() obtains a point on the surface of the polygon. This point usually represents the location of the entire geometry and is in the interior of the polygon. ST_Centroid() calculates the centroid of the polygon, which is the balance point of each part of the polygon but does not necessarily locate in the interior of the polygon. The result is displayed in two columns. The first column pt_on_surf records the coordinates of a point on the surface of the polygon. The second column centroid records the coordinates of the polygon’s centroid. The query result shows that the point on the polygon surface is (1 5), and the centroid is (4.076923076923077 5).

The return result is as follows:

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

_ST_PointOnSurface

_ST_PointOnSurface(geometry a) returns a point on the surface of the input geometry a. The function returns a point that lies exactly in the interior of a geometry (polygon or another planar geometry), rather than the geometric or arithmetic center of the geometry.

_ST_PointOnSurface() is usually meaningful only for planar geometries (such as polygons), because for linear or point geometries, the concept of interior points can be vague or inappropriate.

The function may not return any of the interior points, but a specific point that may represent the entire geometry, for example, a vertex or a boundary point of a polygon.

The syntax is as follows:

_ST_PointOnSurface(geometry g1)

Here is an 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 with a specific shape is created from a WKT string. Then, the polygon is analyzed by the _ST_PointOnSurface() and ST_Centroid() functions.

_ST_PointOnSurface() returns a point on the surface of the polygon, that is, a point on the boundary or in the interior of the polygon. ST_Centroid() returns the centroid of the polygon.

The result is displayed in two columns. The pt_on_surf column records the coordinates of a point on the surface, and the centroid column records the coordinates of the centroid. In this example, pt_on_surf records the point (1 5), while centroid records the point (4.076923076923077 5).

The return result is as follows:

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