Spatial analysis functions

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

Currently, seekdb supports the following spatial analysis functions: ST_Length(), ST_Centroid(), and _ST_PointOnSurface().

ST_Length

The ST_Length(ls[,unit]) function calculates the length of a linear geometry, such as a line segment, polyline, or curve. This function is typically used for 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 units used must be valid and supported by the database, and the database must be able to recognize the conversion factor for the specified 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 |
+--------------------------------------+---------------------+

The syntax is as follows:

ST_Length(ls[,unit])

Here are the 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 meters (metre). Depending on the function implementation and the spatial database system, this parameter can accept various length units, such as 'foot' (foot) or 'centimetre' (centimetre).

Here is an example:

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

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

SELECT ST_Length(@ls);
+-------------------+
| ST_Length(@ls)    |
+-------------------+
| 313701.9623204328 |
+-------------------+
1 row in set (0.001 sec)

In this example, the ST_GeomFromText function is used to create a linear geometry (LineString) consisting of three points with coordinates (1,1), (2,2), and (3,3).

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 a unit, using the default unit of meters, resulting in a length of 313701.9623204328 meters.

ST_Centroid

The ST_Centroid(geometry A) function is a spatial function that calculates the centroid (geometric center) of a given geometry A. The centroid is the balance point of the geometry and can be considered the central position 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.

The syntax is as follows:

ST_Centroid(geometry A)

Here is an example:

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 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 within 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 within 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 of the polygon is located at (1, 5), and 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 (0.001 sec)

_ST_PointOnSurface

The _ST_PointOnSurface(geometry a) function returns 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), not just the geometric or arithmetic center.

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 (for example, a corner point or a point on an edge of the polygon).

The syntax is as follows:

_ST_PointOnSurface(geometry g1)

Here is an example:

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) formatted string. Then, the two functions _ST_PointOnSurface and ST_Centroid are applied to this polygon.

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 boundary or inside the polygon. 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), and 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 (0.001 sec)