Spatial analysis functions are used to perform complex spatial processing and analysis tasks, such as buffer analysis, merging, and clipping.
The spatial analysis functions currently supported by OceanBase AI Database include ST_Length(), ST_Centroid(), and _ST_PointOnSurface().
ST_Length
The ST_Length(ls[,unit]) function calculates the length of a linear geometric object, such as a line segment, polyline, or curve. This function is typically applicable to geometric objects of the LINESTRING and MULTILINESTRING types.
For unit conversions to take effect, the geometry object ls must have an associated Spatial Reference System Identifier (SRID), and that SRID must not be 0 (an SRID of 0 typically 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 that unit.
The following table lists all units and their 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 |
+--------------------------------------+---------------------+
The syntax is as follows:
ST_Length(ls[,unit])
Parameter description:
ls: Input parameter, specifying the linear geometric object whose length is to be calculated.unit: The unit of length. If not specified, the default unit is metre. Depending on the function implementation and the spatial database system, this parameter can accept various units such as 'foot' (foot), 'centimetre' (centimeter), etc.
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, the ST_GeomFromText function is first used to create a linear geometry (LineString) consisting of three points with coordinates (1,1), (2,2), and (3,3).
Then, the ST_Length function is used to calculate the length of this LineString. First, the length unit is specified as feet, and the resulting length is 1029205.9131247795 feet. Then, the ST_Length function is called again without specifying a unit, so the default unit meter is used, and the resulting length is 313701.9623204328 meters.
ST_Centroid
ST_Centroid(geometry A) is a spatial function that calculates the centroid (geometric center) of a given geometry A. The centroid is the balance point of all parts of the geometry and can be considered its central location. For two-dimensional geometries, such as polygons, the centroid is the average position of all points.
- If
Ais an empty geometry (contains no points), the function may returnNULLor an invalid result. - If
Ais a complex geometry, calculating its centroid can become complex and computationally intensive, especially when the geometry has many vertices or internal holes. - The calculated centroid may not lie on the original geometry, especially when the geometry itself has an irregular shape or concave parts. For example, for a "U"-shaped polygon, the centroid might be located in the external space of the depression.
- For linear geometric shapes (such as line segments or polygons), the centroid is the average position of all points on the shape. However, for complex line shapes (such as self-intersecting or curved polygons), the position of the centroid may be difficult to interpret intuitively.
The syntax is as follows:
ST_Centroid(geometry A)
An example is as follows:
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 a polygon. This point typically represents the location of the entire geometry and is guaranteed to be inside the polygon. The ST_Centroid function calculates the centroid of a polygon. It is the balance point of all parts of the polygon but does not necessarily lie inside the polygon. The results are displayed in two columns. The first column, pt_onsurf, contains the coordinates of points on the polygon's surface, and the second column, centroid, contains the coordinates of the calculated centroid of the polygon. As shown in the query result, a point on the polygon's surface is located at (1, 5), and the centroid is located at (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
The _ST_PointOnSurface(geometry a) function returns a point from the input geometry a that is guaranteed to lie on its surface. It can return a point that is precisely inside the geometry (for polygons and other planar geometries), rather than just the geometric or arithmetic center of the geometry.
The _ST_PointOnSurface function is generally meaningful only for polygonal geometries, as the concept of interior points may not be well-defined or applicable for linear or point geometries.
This function may not return any of the multiple interior points, but instead returns a specific point that represents the entire geometry (for example, a vertex of a polygon or a point on an edge).
The syntax is as follows:
_ST_PointOnSurface(geometry g1)
For 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 first created from a Well-Known Text (WKT) string. Then, two functions are applied to this polygon: _ST_PointOnSurface and ST_Centroid.
The _ST_PointOnSurface function returns a point that is guaranteed to lie on the surface of the polygon, meaning it is either on the boundary or inside the polygon. In contrast, the ST_Centroid function calculates and returns the geometric center of the polygon.
Finally, the query result uses two columns to display the coordinates of these two points: pt_onsurf represents the point on the surface, and centroid represents the centroid's coordinates. In the provided example, pt_onsurf returns a point with coordinates (1 5), while centroid returns a point with coordinates (4.076923076923077 5).
The return result is as follows:
+------------+----------------------------+
| pt_on_surf | centroid |
+------------+----------------------------+
| POINT(1 5) | POINT(4.076923076923077 5) |
+------------+----------------------------+
1 row in set