Geometry property functions

A geometry property function is used to access qualitative or quantitative properties of a geometric object. OceanBase Database supports the following geometry property functions in the current version: ST_SRID(), ST_Latitude(), ST_Latitude(), ST_X(), and ST_Y(). For a point consisting of X and Y coordinates, you can use the ST_X() and ST_Y() functions to get the X and Y coordinates, respectively. For Point objects in a geographic spatial reference system (SRS), you can use the ST_Longitude() and ST_Latitude() functions to get the longitude and latitude, respectively.

ST_SRID

The ST_SRID() function uses a single argument g that represents a valid geometric object. ST_SRID() returns an integer that indicates the ID of the SRS associated with the g argument. Syntax:

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 geometric 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 geometric SRID value without converting its coordinates.

• ST_Transform() not only changes the SRID value, but also converts its geometric 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 of the latitude value of a valid Point object p with a geographic SRS. Syntax:

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

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

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

The ST_X() function 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. Syntax:

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()

The ST_Y() function 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. Syntax:

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

Parameters

The return value for a geometric argument in a geometry property function is non-NULL, except in the following cases:

• If any argument is NULL or any geometric argument is an empty geometric object, the return value is NULL.
• If any geometric argument is in an incorrect syntax format, the ER_GIS_INVALID_DATA error is returned.
• If any geometric 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 limits on the ST_Latitude(), ST_Longitude(), ST_X(), and ST_Y() functions:

• If any geometric argument is a valid geometric 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.