Geometry property functions

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 converting its coordinates.

• ST_Transform() not only changes the SRID value, but also converts 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 (0.001 sec)

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 NULL or any geometry argument is an empty geometry object, the return value is NULL.
• If any geometry argument is in an incorrect syntax format, the ER_GIS_INVALID_DATA error is returned.
• If any geometry argument belongs to an undefined SRS, the ER_SRS_NOT_FOUND error is returned.
• If any SRID argument is not in the range of 32-bit unsigned integers, the ER_DATA_OUT_OF_RANGE error is returned.
• If any SRID argument references an undefined SRS, the ER_SRS_NOT_FOUND error 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_TYPE error is returned.
• If an X or Y coordinate argument is provided and its value is -inf, +inf, or NaN, the ER_DATA_OUT_OF_RANGE error 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_RANGE error is returned.
  • If the latitude value is not within the range of [−90, 90], the ER_LATITUDE_OUT_OF_RANGE error is returned.