WL#11453: ST_Latitude and ST_Longitude

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Cartesian coordinates are X and Y. SFA-CA and SQL/MM specify functions ST_X and ST_Y to get and set the X and Y coordinates of points. MySQL has implemented these.

Geographic coordinates are latitude and longitude. SFA-CA and SQL/MM do not define special functions to get and set these coordinates, but assume that ST_X and ST_Y should be used. However, it is not well defined which geographic coordinate maps to which axis.

This WL implements two new functions, ST_Latitude and ST_Longitude, to get and set the latitude and longitude coordinates of geographic points.

This WL also defines that ST_X operates on the first axis and ST_Y on the second axis, with axes ordered in the order they appear in the SRS definition.

ST_Latitude and ST_Longitude

F-1.1
ST_Latitude and ST_Longitude MUST return NULL if either argument is NULL.
F-1.2
ST_Latitude and ST_Longitude MUST NOT return NULL if neither argument is NULL.
F-1.3
If the geometry argument is not a valid geometry, ST_Latitude/ST_Longitude MUST raise ER_GIS_INVALID_DATA during function evaluation.
F-1.4a
If a second parameter is provided and the value is not within the range [-90,90] (in degrees, other limits in other units), ST_Latitude MUST raise ER_LATITUDE_OUT_OF_RANGE.
F-1.4b
If a second parameter is provided and the value is not within the range (-180,180] (in degrees, other limits in other units), ST_Longitude MUST raise ER_LONGITUDE_OUT_OF_RANGE.
F-1.5
If the geometry argument is a valid point in a geographic SRS and no second argument is provided, ST_Latitude/ST_Longitude MUST return the coordinate value as a double value.
F-1.6
If the geometry argument is a valid point in a geographic SRS, a second parameter is provided, and the new coordinate value is a valid coordinate value, ST_Latitude/ST_Longitude MUST return a point.
F-1.7
If the geometry argument is a valid point, but not in a geographic SRS, ST_Latitude/ST_Longitude MUST raise ER_SRS_NOT_GEOGRAPHIC.
F-1.8
If the geometry argument is a valid geometry, but not a point, ST_Latitude/ST_Longitude MUST raise ER_UNEXPECTED_GEOMETRY_TYPE.

ST_X and ST_Y

F-2.1a
If the first argument to ST_X is a geometry in a geographic SRS, the function MUST return/modify the coordinate value of the axis that appears first in the SRS definition.
F-2.1b
If the first argument to ST_Y is a geometry in a geographic SRS, the function MUST return/modify the coordinate value of the axis that appears second in the SRS definition.
F-2.2a
If the function is setting the latitude of a point in a geographic SRS and the value is not within the range [-90,90] (in degrees, other limits in other units), ST_X/Y MUST raise ER_LATITUDE_OUT_OF_RANGE.
F-2.2b
If the function is setting the longitude of a point in a geographic SRS and the value is not within the range (-180,180] (in degrees, other limits in other units), ST_X/Y MUST raise ER_LONGITUDE_OUT_OF_RANGE.
I-1
No new files.
I-2
Interface SQL01 is extended with two new functions: ST_Latitude and ST_Longitude with the following syntax:
ST_Latitude(geometry) -- Get value of latitude coordinate
ST_Latitude(geometry, coordinate value) -- Set value of latitude coordinate
ST_Longitude(geometry) -- Get value of longitude coordinate
ST_Longitude(geometry, coordinate value) -- Set value of longitude coordinate
I-3
No new commands.
I-4
No new tools.
I-5
Changes to existing functionality: ST_X and ST_Y will return/set the first and second coordinate, respectively, of the axes in the order they are defined in the SRS definition. The functions will raise ER_LATITUDE_OUT_OF_RANGE or ER_LONGITUDE_OUT_OF_RANGE if the user attempts to set latitudes or longitudes (respectively) that are out of range.
I-6
Interface ERR01 is extended with one new error message:
ER_SRS_NOT_GEOGRAPHIC, SQLSTATE 22S00, "Function %s is only defined for geographic spatial reference systems, but one of its arguments is in SRID %u, which is not geographic."