WL#11000: ST_Distance with unit

ST_Distance currently takes only two parameters -- the geometries to compute the distance between. This WL implements a third, optional, parameter that names the unit of length to use in the result, conforming with SQL/MM Part 3 Spatial (ISO/IEC 13249-3:2015), Sect. 5.1.41.

F-1

ST_Distance MUST return NULL if the unit parameter is NULL.

F-2

ST_Distance MUST NOT return NULL if none of its parameters are NULL or an empty geometry.

F-3 a)

If a linear unit is specified, the unit is supported by MySQL (*), and the SRID is 0, ST_Distance MUST raise ER_GEOMETRY_IN_UNKNOWN_LENGTH_UNIT.

F-3 b)

If a linear unit is specified, the unit is supported by MySQL (*), and the SRID is not 0, the result of ST_Distance MUST be in that unit.

F-4

If the unit is specified, but is not supported by MySQL (*), ST_Distance MUST raise ER_UNIT_NOT_FOUND.

F-5

If a unit is not specified and the geometry parameters are in a Cartesian SRS, the result of ST_Distance MUST be in the unit of the SRS of the geometries.

F-6

If a unit is not specified and the geometry parameters are in a geographic SRS, the result of ST_Distance MUST be in meters.

F-7

The INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE MUST list all units understood by ST_Distance with the correct name and conversion factor.

See also functional requirements of WL#9347 for further behavior description.

(*) See LLD for a list of supported units and their conversion factors.

I-1

No new files.

I-2

No new syntax.

I-3

No new commands.

I-4

No new tools.

I-5

Interface SQL01 is extended: Function ST_Distance is extended with a new, optional, parameter specifying a linear unit of measure (as a text string). If specified, the result will be in this unit. This is a pure extension. Existing behavior is not affected.

I-6

Interface ERR01 is extended with 2 new error message:
ER_UNIT_NOT_FOUND, SQLSTATE SU001, "There's no unit of measure named '%s'."
ER_GEOMETRY_IN_UNKNOWN_LENGTH_UNIT, SQLSTATE SU001, "The geometry passed to function %s is in SRID 0, which doesn't specify a length unit. Can't convert to '%s'."

I-7

Interface INSTR03 is extended with a new view, INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE.

ST_Distance

Extend the item and structures in item_create.cc to take an optional third parameter, using SQL_FN_V_LIST. See, e.g., ST_Distance_Sphere, which also has a optional second parameter.

List of units

The list of units will be hard coded. Later, the list of units will be made dynamic and moved to the data dictionary.

INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE

This view lists all units. It is generated from the same hard-coded list as Item_func_distance uses. The view is created using a JSON table function.

The columns in the view are:

• UNIT_NAME CHARACTER VARYING(ST_MaxUnitLength) NOT NULL
• UNIT_TYPE CHARACTER VARYING(ST_MaxUnitTypeLength) NOT NULL
• CONVERSION_FACTOR DOUBLE PRECISION NOT NULL
• DESCRIPTION CHARACTER VARYING(ST_MaxDescriptionLength)

(See SQL/MM Part 3, Sect. 20.4 for details.)

This is defined in the new files sql/dd/impl/system_views/st_units_of_measure.{h,cc}.

Generate the view by looping through the same list/map used by ST_Distance, so that there is only one definition of each unit.