A geometry property function is used to access qualitative or quantitative properties of a geometry object. OceanBase Database supports the following geometry property functions in the current version: ST_Area(), ST_SRID(), ST_Latitude(), ST_Longitude(), ST_X(), and ST_Y().
You can use the ST_X() and ST_Y() functions to get the X and Y coordinates of a point, respectively. For Point objects that have a geographic spatial reference system (SRS), you can use the ST_Longitude() and ST_Latitude() functions to get the longitude and latitude, respectively.
ST_Area
ST_Area() returns the area of a Polygon or MultiPolygon object in a spatial coordinate system. This function does not support other types of spatial objects. 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 cannot return the area of a 0-dimensional spatial object (point) or a 1-dimensional spatial object (line).
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 object, the ST_Area() function returns the sum of the areas of the polygons 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
ST_SRID() returns an integer to indicate the ID of the SRS associated with g. The syntax is as follows:
ST_SRID(g [, srid])
The optional srid argument represents a valid SRID value. The ST_SRID() function returns a value that is of the same type as its first argument and with the SRID value specified in the second argument. This function only sets the SRID value of the object and does not convert coordinate values. For a single argument, the ST_SRID() function returns the geometry SRID. Even if the function references an undefined SRS, the ER_SRS_NOT_FOUND error will not occur.
The differences between ST_SRID(g, target_srid) and ST_Transform(g, target_srid) are as follows:
ST_SRID()changes the geometry SRID value without transforming its coordinates.ST_Transform()not only changes the SRID value, but also transforms its geometry 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
ST_Latitude() returns a double-precision number of the latitude value of a valid Point object p with a geographic SRS. The syntax is as follows:
ST_Latitude(p [, new_latitude_val])
The argument new_latitude_val is optional and indicates a valid latitude value. If this argument is used, the ST_Latitude() function returns a Point object whose latitude is equal to the value of the second argument.
If the Point object is valid but does not have a geographic SRS, the 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
ST_Longitude() returns a double-precision number of the longitude value of a valid Point object p with a geographic SRS. The syntax is as follows:
ST_Longitude(p [, new_longitude_val])
The argument new_longitude_val is optional and indicates a valid longitude value. If this argument is used, the ST_Longitude() function returns a Point object whose longitude is equal to the value of the second argument. If the Point object is valid but does not have a geographic SRS, the 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
ST_X() returns the double-precision number of the X coordinate value for a valid point project p. The X coordinate is considered the first axis defined in a point SRS. The syntax is as follows:
ST_X(p [, new_x_val])
The argument new_x_val is optional. If this argument is used, the ST_X() function returns a Point object whose X coordinate is equal to the value of the second argument. If the Point object has a geographic SRS, the second argument 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
ST_Y() returns the double-precision number of the Y coordinate value for a valid Point project p. The Y coordinate is considered the second axis defined in a point SRS. The syntax is as follows:
ST_Y(p [, new_y_val])
The argument new_y_val is optional. If this argument is used, the ST_Y() function returns a Point object whose Y coordinate is equal to the value of the second argument. If the Point object has a geographic SRS, the second argument must be within the valid range of longitude or latitude values.
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
Considerations
The return value for a geometry argument in a geometry property function is non-NULL, except in the following cases:
- If any argument is
NULLor any geometry argument is an empty geometry object, the return value isNULL. - If any geometry argument is in an incorrect syntax format, the
ER_GIS_INVALID_DATAerror is returned. - If any geometry argument belongs to an undefined SRS, the
ER_SRS_NOT_FOUNDerror is returned. - If any SRID argument is not in the range of 32-bit unsigned integers, the
ER_DATA_OUT_OF_RANGEerror is returned. - If any SRID argument references an undefined SRS, the
ER_SRS_NOT_FOUNDerror is returned.
Take note of the following limitations on the ST_Latitude(), ST_Longitude(), ST_X(), and ST_Y() functions:
- If any geometry argument is a valid geometry object but not a Point object, the
ER_UNEXPECTED_GEOMETRY_TYPEerror is returned. - If an X or Y coordinate argument is provided and its value is
-inf,+inf, orNaN, theER_DATA_OUT_OF_RANGEerror is returned. - If the longitude or latitude value exceeds the valid range in degrees or in other units used by the SRS, an error is returned.
- If the longitude value is not within the range of (-180, 180], the
ER_LONGITUDE_OUT_OF_RANGEerror is returned. - If the latitude value is not within the range of [−90, 90], the
ER_LATITUDE_OUT_OF_RANGEerror is returned.
- If the longitude value is not within the range of (-180, 180], the