Geometry attribute functions are used to access qualitative or quantitative properties of a geometric object. The current version of OceanBase AI Database supports the following geometry attribute functions: ST_Area, ST_SRID(), ST_Latitude(), ST_Longitude(), ST_X(), and ST_Y().
A point consists of X and Y coordinates, which can be obtained using the ST_X() and ST_Y() functions, respectively. For a Point object with a spatial reference system (SRS), the longitude and latitude can be obtained using the ST_Longitude() and ST_Latitude() functions, respectively.
ST_Area
The ST_Area() function calculates the area of a Polygon or MultiPolygon spatial object in a spatial coordinate system. Other types of spatial objects are not supported. The syntax is as follows:
ST_Area({poly|mpoly})
Here is an 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 zero-dimensional (point) or one-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 polygons within the set.
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
ST_SRID() returns an integer that indicates the ID of the spatial reference system (SRS) associated with g. The syntax is as follows:
ST_SRID(g [, srid])
The optional srid parameter specifies a valid SRID value. The return value of ST_SRID() has the same type as its first parameter and an SRID value equal to the second parameter. This function only sets the object's SRID value without performing any coordinate value conversion. For a single-parameter call, ST_SRID() returns a geometry SRID; even if it references an undefined SRS, no ER_SRS_NOT_FOUND error is raised.
The differences between ST_SRID(g, target_srid) and ST_Transform(g, target_srid) are as follows:
ST_SRID()changes the geometric SRID value without converting its coordinates.ST_Transform()converts geometric coordinates in addition to changing their SRID values.
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 representing the latitude of a valid Point object p with a spatial reference system (SRS). The syntax is as follows:
ST_Latitude(p [, new_latitude_val])
The new_latitude_val parameter is optional and represents a valid latitude value. If this parameter is used, ST_Latitude() returns a Point object whose latitude is equal to the second parameter.
If a Point object is valid but lacks a geographic SRS, an error ER_SRS_NOT_GEOGRAPHIC will occur.
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 representing the longitude value of a valid Point object p with a spatial reference system (SRS). The syntax is as follows:
ST_Longitude(p [, new_longitude_val])
The new_longitude_val parameter is optional and represents a valid longitude value. If this parameter is used, ST_Longitude() returns a Point object whose longitude is equal to the second parameter. If a Point object is valid but lacks a geographic SRS, an error ER_SRS_NOT_GEOGRAPHIC will occur.
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 the double-precision value of the X-coordinate of a valid Point object p. The X coordinate is considered the first axis in the definition of a point spatial reference system (SRS). The syntax is as follows:
ST_X(p [, new_x_val])
The new_x_val parameter is optional. If used, ST_X() returns a Point object whose X coordinate equals the second parameter. If the Point object has a geographic SRS, the second parameter must be within the valid range of longitude or latitude values.
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 the double-precision value of the Y-coordinate of a valid Point object p. The Y-coordinate is considered the second axis in the definition of a point spatial reference system (SRS). The syntax is as follows:
ST_Y(p [, new_y_val])
The new_y_val parameter is optional. If used, ST_Y() returns a Point object whose Y coordinate equals the value of the second parameter. If the Point object has a geographic SRS, the second parameter must be within the valid range for 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 value of a geometric parameter in a geometry attribute-related function is never NULL, except in the following cases:
- If any parameter is
NULLor any geometric parameter is an empty geometry, the return value isNULL. - If the syntax of any geometric parameter is incorrect, the
ER_GIS_INVALID_DATAerror is returned. - If any geometric parameter belongs to an undefined Spatial Reference System (SRS), the
ER_SRS_NOT_FOUNDerror is reported. - If any SRID parameter is outside the range of 32-bit unsigned integers, an error
ER_DATA_OUT_OF_RANGEoccurs. - If any SRID parameter references an undefined SRS, the
ER_SRS_NOT_FOUNDerror occurs.
The following considerations also apply to the ST_Latitude(), ST_Longitude(), ST_X(), and ST_Y() functions:
- If any geometry parameter is a valid geometry but not a Point object, the error
ER_UNEXPECTED_GEOMETRY_TYPEoccurs. - If the X or Y coordinate parameter is provided and its value is
-inf,+inf, orNaN, an errorER_DATA_OUT_OF_RANGEwill occur. - An error occurs if the longitude or latitude value exceeds the range (the range is in degrees; if SRS uses other units, the corresponding values in those units are used).
- If the longitude value is not within the range (-180, 180], the
ER_LONGITUDE_OUT_OF_RANGEerror occurs. - If the latitude value is not within the range of [−90, 90], an error
ER_LATITUDE_OUT_OF_RANGEwill be returned.
- If the longitude value is not within the range (-180, 180], the