WL#11453: ST_Latitude and ST_Longitude

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."