Geometry attribute functions

Geometry attribute functions are used to access the qualitative or quantitative attributes of geometry objects. The geometry attribute functions supported in the current version of seekdb 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 using the ST_X() and ST_Y() functions, respectively. For a Point object with a geographic 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 Polygon and MultiPolygon spatial objects in a spatial coordinate system and does not support other types of spatial objects as input. The syntax is as follows:

ST_Area({poly|mpoly})

Here is an example:

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

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

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 within the collection.

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

ST_SRID

The ST_SRID() function returns an integer indicating 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 SRID value of the object and does not perform any coordinate value conversion. For a single parameter, ST_SRID() returns the geometry SRID, even if it references an undefined SRS, without triggering the 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 geometry SRID value without converting its coordinates.

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

SET @geo = ST_GeomFromText('LineString(1 1,2 2)', 0);
Query OK, 0 rows affected

SELECT ST_SRID(@geo);
+---------------+
| ST_SRID(@geo) |
+---------------+
|             0 |
+---------------+
1 row in set (0.001 sec)

SET @geo = ST_SRID(@geo, 4230);
Query OK, 0 rows affected

SELECT ST_SRID(@geo);
+---------------+
| ST_SRID(@geo) |
+---------------+
|          4230 |
+---------------+
1 row in set (0.001 sec)

ST_Latitude

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

ST_Latitude(p [, new_latitude_val])

The parameter new_latitude_val is optional and 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, the ER_SRS_NOT_GEOGRAPHIC error occurs.

SET @pit = ST_GeomFromText('POINT(45 90)', 4326);
Query OK, 0 rows affected (0.001 sec)

SELECT ST_Latitude(@pit);
+-------------------+
| ST_Latitude(@pit) |
+-------------------+
|                45 |
+-------------------+
1 row in set (0.001 sec)

SELECT ST_AsText(ST_Latitude(@pit, 30));
+----------------------------------+
| ST_AsText(ST_Latitude(@pit, 30)) |
+----------------------------------+
| POINT(30 90)                     |
+----------------------------------+
1 row in set (0.001 sec)

ST_Longitude

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

ST_Longitude(p [, new_longitude_val])

The parameter new_longitude_val is optional and 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, the ER_SRS_NOT_GEOGRAPHIC error occurs.

SET @pit = ST_GeomFromText('POINT(45 90)', 4326);
Query OK, 0 rows affected (0.001 sec)

SELECT ST_Longitude(@pit);
+--------------------+
| ST_Longitude(@pit) |
+--------------------+
|                 90 |
+--------------------+
1 row in set (0.001 sec)

SELECT ST_AsText(ST_Longitude(@pit, 30));
+-----------------------------------+
| ST_AsText(ST_Longitude(@pit, 30)) |
+-----------------------------------+
| POINT(45 30)                      |
+-----------------------------------+
1 row in set (0.001 sec)

ST_X

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

ST_X(p [, new_x_val])

The parameter new_x_val is optional. 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 values.

SELECT ST_X(Point(53.7, 56.34));
+--------------------------+
| ST_X(Point(53.7, 56.34)) |
+--------------------------+
|                     53.7 |
+--------------------------+
1 row in set (0.001 sec)

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

ST_Y()

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

ST_Y(p [, new_y_val])

The parameter new_y_val is optional. 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 values.

SELECT ST_Y(Point(53.7, 56.34));
+--------------------------+
| ST_Y(Point(53.7, 56.34)) |
+--------------------------+
|                    56.34 |
+--------------------------+
1 row in set (0.001 sec)

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

Parameters

The return values of geometry parameters for 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, the ER_GIS_INVALID_DATA error occurs.
• If any geometry parameter belongs to an undefined spatial reference system (SRS), the ER_SRS_NOT_FOUND error occurs.
• If any SRID parameter is outside the range of 32-bit unsigned integers, the ER_DATA_OUT_OF_RANGE error occurs.
• If any SRID parameter references an undefined SRS, the ER_SRS_NOT_FOUND error occurs.

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, the ER_UNEXPECTED_GEOMETRY_TYPE error occurs.
• If an X or Y coordinate parameter is provided and the value is -inf, +inf, or NaN, the ER_DATA_OUT_OF_RANGE error occurs.
• If the longitude or latitude value is out of range (in degrees, or in the corresponding unit if the SRS uses a different unit), the following errors occur:
  • If the longitude value is not in the range (-180, 180], the ER_LONGITUDE_OUT_OF_RANGE error occurs.
  • If the latitude value is not in the range [−90, 90], the ER_LATITUDE_OUT_OF_RANGE error occurs.