Which measurement functions are supported - Lindorm - Alibaba Cloud Documentation Center

This topic describes the measurement functions that are supported by Lindorm GanosBase.

Applicable engines and versions

The measurement functions described in this topic are applicable only to LindormTable.
The aggregate functions described in this topic are supported by LindormTable 2.6.5 and later versions. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Important

The Lindorm SQL version must be 2.6.8 or later. For more information about how to view the Lindorm SQL version, see SQL versions.

Functions

The following table describes the measurement functions supported by Lindorm GanosBase.

Function	Description
ST_Area	Calculates the area of the specified polygon object.
ST_Distance	Calculates the 2D Euclidean distance between two specified geometry objects.
ST_DistanceSphere	Calculates the minimum spherical distance between two specified geometry objects. The returned distance is measured in meters.
ST_IsValid	Checks whether a geometry object is valid.
ST_Length	Calculates the 2D length of the specified geometry object.
ST_LengthSphere	Calculates the spherical length of the specified geometry object.

ST_Area

The ST_Area function calculates the area of the specified polygon object.

Syntax

double ST_Area(geometry g)

Parameters

Parameter	Description
g	The geometry object.

Note

You can specify a geometry object of the following types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
If you specify a geometry object, this function returns the 2D Cartesian area of the geometry object in the unit that is specified by the spatial reference system of the geometry object. Lindorm GanosBase SQL uses the spatial reference system with the SRID 4326. In this system, distances are measured in degrees.
If the points of the specified polygon object enclose multiple closed figures, this function calculates the difference between the areas of the closed figures enclosed by the points clockwise and counterclockwise, and returns the absolute value of the difference.
If you do not specify a polygon object, the value 0 is returned.

Examples

Example 1: Calculate the area of a polygon object.

SELECT ST_Area(ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))')) AS area;

The following result is returned:

+------+
| area |
+------+
| 36.0 |
+------+

Example 2: Calculate the area of a LineString object.

SELECT ST_Area(ST_GeomFromText('LINESTRING(1 3,5 6)')) AS area;

The following result is returned:

+------+
| area |
+------+
| 0.0  |
+------+

ST_Distance

The ST_Distance function calculates the 2D Euclidean distance between two specified geometry objects. The returned distance is measured in degrees.

Syntax

double ST_Distance(geometry geomA, geometry geomB)

Parameters

Parameter	Description
geomA	The first geometry object.
geomB	The second geometry object.

Note

You can specify a geometry object of the following types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
If one of the geometry object is empty, the value 0 is returned.

Examples

Example 1

SELECT ST_Distance(ST_GeomFromText('POINT(-72.1235 42.3521)'),ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)')) AS d;

The following result is returned:

+-----------------------+
|           d           |
+-----------------------+
| 0.0015056772638228177 |
+-----------------------+

Example 2

SELECT ST_Distance(ST_GeomFromText('POINT(-72.1235 42.3521)'),ST_GeomFromText('LINESTRING EMPTY')) AS d;

The following result is returned:

+---+
| d |
+---+
|0.0|
+---+

ST_DistanceSphere

The ST_DistanceSphere function calculates the minimum spherical distance between two specified geometry objects. The returned distance is measured in meters.

Syntax

double ST_DistanceSphere(geometry geomA, geometry geomB)

Parameters

Parameter	Description
geomA	The first geometry object.
geomB	The second geometry object.

Note

You can specify a geometry object of the following types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.
If one of the geometry object is empty, the value 0 is returned.
If one of the specified geometry object is a polygon or LineString object, the results may have an error of centimeters

Examples

Example 1

SELECT ST_DistanceSphere(ST_GeomFromText('POINT(-72.1235 42.3521)'),ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)')) AS d;

The following result is returned:

+--------------------+
|         d          |
+--------------------+
| 124.53287523764577 |
+--------------------+

Example 2

SELECT ST_DistanceSphere(ST_GeomFromText('POINT(-72.1235 42.3521)'),ST_GeomFromText('LINESTRING EMPTY')) AS d;

The following result is returned:

+---+
| d |
+---+
|0.0|
+---+

ST_IsValid

The ST_IsValid function checks whether a geometry object is valid. Invalid geometry objects include self-intersecting polygons, MultiPolygon objects that include overlapped polygons, and other objects that do not comply with their definitions.

Syntax

boolean ST_IsValid(geometry g)

Parameters

Parameter	Description
g	The geometry object.

Examples

Check whether the geometry object whose ID is 110000 is valid.

SELECT ST_IsValid(geom) FROM mapdata WHERE id=110000;

The following result is returned:

+------------------+
| st_isvalid(geom) |
+------------------+
| true             |
+------------------+

The returned result is true, which indicates that the geometry object whose ID is 110000 is valid. If false is returned, the geometry object whose ID is 110000 is invalid.

ST_Length

The ST_Length function calculates the 2D length of the specified geometry object. The returned length is measured in degrees.

Syntax

double ST_Length(geometry g)

Parameters

Parameter	Description
g	The geometry object.

Note

If the specified geometry object is a LineString object, the 2D length of the object in a Cartesian coordinate system is returned.
If the specified geometry object is a polygon object, the value 0 is returned.

Examples

Example 1

SELECT ST_Length(ST_GeomFromText('LINESTRING(1 3,5 6)')) AS length;

The following result is returned:

+--------+
| length |
+--------+
| 5.0    |
+--------+

Example 2

SELECT ST_Length(ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))')) AS length;

The following result is returned:

+--------+
| length |
+--------+
| 0.0    |
+--------+

ST_LengthSphere

The ST_LengthSphere function calculates the spherical length of the specified geometry object. The returned length is measured in meters.

Syntax

double ST_LengthSphere(geometry g)

Parameters

Parameter	Description
g	The geometry object.

Note

If the specified geometry object is a LineString object, the spherical length of the object is returned.
If the specified geometry object is a polygon object, the value 0 is returned.

Examples

Example 1

SELECT ST_LengthSphere(ST_GeomFromText('LINESTRING(1 3,5 6)')) AS length;

The following result is returned:

+------------------+
|      length      |
+------------------+
| 554137.283806292 |
+------------------+

Example 2

SELECT ST_LengthSphere(ST_GeomFromText('POLYGON((2 2, 2 8, 8 8, 8 2, 2 2))')) AS length;

The following result is returned:

+--------+
| length |
+--------+
| 0.0    |
+--------+