WL#9432: Axis order in ST_AsBinary/ST_AsWKB

Import and export functions have to take axis order into consideration. Some spatial reference systems are latitude-longitude, while other are longitude-latitude.

This WL modifies ST_AsBinary/ST_AsWKB to return geographic (latitude and longitude) coordinates in the order specified in the spatial reference system. An optional options argument and an option argument value to override the default and specify a particular axis order is also added.

The axis order of geometries in projected spatial reference systems is not affected.

This WL changes the output of the functions. Output of existing queries may change, depending on which spatial reference systems are used.

User Documentation

• http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html
• http://dev.mysql.com/doc/refman/8.0/en/gis-format-conversion-functions.html#function_st-asbinary

F-1

If no options argument is specified, the functions MUST return coordinates in the order specified in the spatial reference system definition.

F-2

If an axis order is specified, the functions MUST return coordinates in that axis order.

F-3

If the options argument contains an invalid option key, the functions MUST raise ER_INVALID_OPTION_KEY.

F-4

If the options argument contains an invalid option value, the functions MUST raise ER_INVALID_OPTION_VALUE.

F-5

If the options argument is an empty string or contains only whitespace, the functions MUST use the default axis order.

F-6

If the options argument is NULL, the functions MUST return NULL.

F-7

If the options argument contains the same option key more than once, the functions MUST raise ER_DUPLICATE_OPTION_KEY.

F-8

If the options argument is badly formed, the functions MUST raise ER_INVALID_OPTION_KEY_VALUE_PAIR, ER_INVALID_OPTION_START_CHARACTER, ER_INVALID_OPTION_END_CHARACTER or ER_INVALID_OPTION_CHARACTERS.

F-9

If the geometry is in a projected spatial reference system (or SRID 0), the axes are output in the order they appear in the geometry.

F-10

If the geometry is in an undefined spatial reference system, the axes are output in the order they appear in the geometry. The functions MUST raise ER_WARN_SRS_NOT_FOUND_AXIS_ORDER.

F-11

The options argument (both option keys and values) MUST be case insensitive.

NF-1

The functions MUST be non-nullable if all arguments are non-nullable.

I-1

No new files.

I-2

Interface SQL02 is extended with new two-parameter forms of ST_AsBinary and ST_AsWKB:
<binary string> = ST_AsBinary(<geometry>, <string>)
<binary string> = ST_AsWKB(<geometry>, <string>)

I-3

No new commands.

I-4

No new tools.

I-5

No impact on existing functionality.

I-6

Interface ERR01 is extended with new errors:
ER_INVALID_OPTION_KEY, SQLSTATE 22023, "Invalid option key '%.192s' in function %.192s."
ER_INVALID_OPTION_VALUE, SQLSTATE 22023, "Invalid value '%.192s' for option '%.192s' in function %.192s."
ER_DUPLICATE_OPTION_KEY, SQLSTATE 22023, "Duplicate option key '%.192s' in function %.192s."
ER_INVALID_OPTION_KEY_VALUE_PAIR, SQLSTATE 22023, "The string '%.192s' is not a valid key=value pair in function %.192s."
ER_INVALID_OPTION_START_CHARACTER, SQLSTATE 22023, "The options argument in function %.192s starts with the invalid character '%c'."
ER_INVALID_OPTION_END_CHARACTER, SQLSTATE 22023, "The options argument in function %.192s ends with the invalid character '%c'."
ER_INVALID_OPTION_CHARACTERS, SQLSTATE 22023, "The options argument in function %.192s contains the invalid character sequence '%.192s'."

I-7

Interface ERR01 is extended with one new warning:
ER_WARN_SRS_NOT_FOUND_AXIS_ORDER, SQLSTATE 01000, "There's no spatial reference system with SRID %u. The axis order is unknown."

Overall design

ST_AsBinary and ST_AsWKB are synonyms. Both are represented by Item_func_as_wkb.

Extend Create_func_as_wkb and Item_func_as_wkb to allow an extra optional string parameter.

When evaluating the function, return WKB in the specified axis order, or if no order is specified, in the order specified by the spatial reference system. In the internal geometry representation, longitude is always stored in the first (X) coordinate, and latitude is stored in the second (Y) coordinate.

Options argument

The options argument is a string. Currently there is only one option, but in the future there may be more. Options are separated by comma. Each option is on the form "key=value". Whitespace (tabs, spaces, carriage returns and linefeeds) at the beginning and end of keys and values are ignored.

The only valid key is "axis-order". The valid values are "long-lat", "lat-long" and "srid-defined". "srid-defined" is the default if no option string is specified.

Parsing of the options argument shall fail on the first error. New errors are created for this:

SRID

If the geometry is in an invalid or undefined SRS, we don't know if the axes should be swapped or not, so we can't do anything. A new warning informs the user: