Geometry attribute functions

Geometry attribute functions are used to access the qualitative or quantitative properties of geometry objects. The geometry attribute functions supported in the current version of OceanBase Database include ST_Area, ST_SRID(), ST_Latitude(), ST_Longitude(), ST_X(), and ST_Y().

A point is composed of X and Y coordinates, which can be obtained separately using the ST_X() and ST_Y() functions. For a Point object with a geographic spatial reference system (SRS), the longitude and latitude can be obtained separately using the ST_Longitude() and ST_Latitude() functions.

ST_Area

The ST_Area() function calculates the area of Polygon and MultiPolygon spatial objects in a spatial coordinate system. It does not support other types of spatial objects. Syntax:

ST_Area({poly|mpoly})

Example:

obclient> SELECT ST_Area(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))'));
+-----------------------------------------------------------+
| ST_Area(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))')) |
+-----------------------------------------------------------+
|                                                       0.5 |
+-----------------------------------------------------------+
1 row in set

The ST_Area() function does not support calculating the area of 0-dimensional (point) or 1-dimensional (line segment) spatial objects.

obclient> SELECT ST_Area(ST_GEOMFROMTEXT('LINESTRING(0 0, 0 1)'));
ERROR 3516 (22S01): POLYGON/MULTIPOLYGON value is a geometry of unexpected type LINESTRING in st_area.

For a MultiPolygon spatial object, the ST_Area() function returns the sum of the areas of the Polygon objects in the collection.

obclient> SELECT ST_Area(ST_GEOMFROMTEXT('MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))'));
+-----------------------------------------------------------------------------------------+
| st_area(ST_GEOMFROMTEXT('MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))')) |
+-----------------------------------------------------------------------------------------+
|                                                                                       8 |
+-----------------------------------------------------------------------------------------+
1 row in set

ST_SRID

The ST_SRID() function returns an integer indicating the ID of the spatial reference system (SRS) associated with g. Syntax:

ST_SRID(g [, srid])

The optional srid parameter specifies a valid SRID value. The return value of ST_SRID() is of the same type as its first parameter and has an SRID value equal to the second parameter. This function only sets the SRID value of the object and does not perform any coordinate value conversion. For a single parameter, ST_SRID() returns the SRID of the geometry, even if it references an undefined SRS, without generating an ER_SRS_NOT_FOUND error.

The difference between ST_SRID(g, target_srid) and ST_Transform(g, target_srid) is as follows:

• ST_SRID() changes the SRID of the geometry without converting its coordinates.

• ST_Transform() changes the SRID of the geometry and converts its coordinates.

obclient [test]> SET @geo = ST_GeomFromText('LineString(1 1,2 2)', 0);
Query OK, 0 rows affected

obclient [test]> SELECT ST_SRID(@geo);
+---------------+
| ST_SRID(@geo) |
+---------------+
|             0 |
+---------------+
1 row in set

obclient [test]> SET @geo = ST_SRID(@geo, 4230);
Query OK, 0 rows affected

obclient [test]> SELECT ST_SRID(@geo);
+---------------+
| ST_SRID(@geo) |
+---------------+
|          4230 |
+---------------+
1 row in set

ST_Latitude

The ST_Latitude() function returns a double-precision number indicating the latitude of a valid Point object p with a geographic spatial reference system (SRS). Syntax:

ST_Latitude(p [, new_latitude_val])

The optional new_latitude_val parameter specifies a valid latitude value. If this parameter is used, ST_Latitude() returns a Point object whose latitude is equal to the second parameter.

If the Point object is valid but does not have a geographic SRS, an ER_SRS_NOT_GEOGRAPHIC error is returned.

obclient [test]> SET @pit = ST_GeomFromText('POINT(45 90)', 4326);
Query OK, 0 rows affected

obclient [test]> SELECT ST_Latitude(@pit);
+-------------------+
| ST_Latitude(@pit) |
+-------------------+
|                45 |
+-------------------+
1 row in set

obclient [test]> SELECT ST_AsText(ST_Latitude(@pit, 30));
+----------------------------------+
| ST_AsText(ST_Latitude(@pit, 30)) |
+----------------------------------+
| POINT(30 90)                     |
+----------------------------------+
1 row in set

ST_Longitude

The ST_Longitude() function returns a double-precision number indicating the longitude of a valid Point object p with a geographic spatial reference system (SRS). Syntax:

ST_Longitude(p [, new_longitude_val])

The optional new_longitude_val parameter specifies a valid longitude value. If this parameter is used, ST_Longitude() returns a Point object whose longitude is equal to the second parameter. If the Point object is valid but does not have a geographic SRS, an ER_SRS_NOT_GEOGRAPHIC error is returned.

obclient [test]> SET @pit = ST_GeomFromText('POINT(45 90)', 4326);
Query OK, 0 rows affected

obclient [test]> SELECT ST_Longitude(@pit);
+--------------------+
| ST_Longitude(@pit) |
+--------------------+
|                 90 |
+--------------------+
1 row in set

obclient [test]> SELECT ST_AsText(ST_Longitude(@pit, 30));
+-----------------------------------+
| ST_AsText(ST_Longitude(@pit, 30)) |
+-----------------------------------+
| POINT(45 30)                      |
+-----------------------------------+
1 row in set

ST_X

The ST_X() function returns a double-precision number indicating the X coordinate of a valid Point object p. The X coordinate is considered to be the first axis in the point's spatial reference system (SRS) definition. Syntax:

ST_X(p [, new_x_val])

The optional new_x_val parameter specifies a valid X coordinate. If this parameter is used, ST_X() returns a Point object whose X coordinate is equal to the second parameter. If the Point object has a geographic SRS, the second parameter must be within the valid range of longitude or latitude.

obclient [test]> SELECT ST_X(Point(53.7, 56.34));
+--------------------------+
| ST_X(Point(53.7, 56.34)) |
+--------------------------+
|                     53.7 |
+--------------------------+
1 row in set

obclient [test]> SELECT ST_AsText(ST_X(Point(53.7, 56.34), 15.5));
+-------------------------------------------+
| ST_AsText(ST_X(Point(53.7, 56.34), 15.5)) |
+-------------------------------------------+
| POINT(15.5 56.34)                         |
+-------------------------------------------+
1 row in set

ST_Y()

The ST_Y() function returns a double-precision number indicating the Y coordinate of a valid Point object p. The Y coordinate is considered to be the second axis in the point's spatial reference system (SRS) definition. Syntax:

ST_Y(p [, new_y_val])

The optional new_y_val parameter specifies a valid Y coordinate. If this parameter is used, ST_Y() returns a Point object whose Y coordinate is equal to the second parameter. If the Point object has a geographic SRS, the second parameter must be within the valid range of longitude or latitude.

obclient [test]> SELECT ST_Y(Point(53.7, 56.34));
+--------------------------+
| ST_Y(Point(53.7, 56.34)) |
+--------------------------+
|                    56.34 |
+--------------------------+
1 row in set

obclient [test]> SELECT ST_AsText(ST_Y(Point(53.7, 56.34), 15.5));
+-------------------------------------------+
| ST_AsText(ST_Y(Point(53.7, 56.34), 15.5)) |
+-------------------------------------------+
| POINT(53.7 15.5)                          |
+-------------------------------------------+
1 row in set

Parameters

The return values of geometry parameters in geometry attribute functions are not NULL, except in the following cases:

• If any parameter is NULL or any geometry parameter is an empty geometry, the return value is NULL.
• If the syntax of any geometry parameter is incorrect, an ER_GIS_INVALID_DATA error is returned.
• If any geometry parameter belongs to an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error is returned.
• If any SRID parameter is outside the range of 32-bit unsigned integers, an ER_DATA_OUT_OF_RANGE error is returned.
• If any SRID parameter references an undefined SRS, an ER_SRS_NOT_FOUND error is returned.

For the ST_Latitude(), ST_Longitude(), ST_X(), and ST_Y() functions, note the following:

• If any geometry parameter is a valid geometry but not a Point object, an ER_UNEXPECTED_GEOMETRY_TYPE error is returned.
• If the X or Y coordinate parameter is provided and its value is -inf, +inf, or NaN, an ER_DATA_OUT_OF_RANGE error is returned.
• If the longitude or latitude value is out of range (in degrees, or in the corresponding units of the SRS if other units are used), an error is returned.
  • If the longitude value is not in the range (-180, 180], an ER_LONGITUDE_OUT_OF_RANGE error is returned.
  • If the latitude value is not in the range [−90, 90], an ER_LATITUDE_OUT_OF_RANGE error is returned.