WL#11000: ST_Distance with unit

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

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.

EPSG Code Unit name b c Conversion factor
1025 millimetre 1 1000 0.001
1033 centimetre 1 100 0.01
9001 metre 1 1 1
9002 foot 0.3048 1 0.3048
9003 US survey foot 12 39.37 0.30480060960121924
9005 Clarke's foot 0.3047972654 1 0.3047972654
9014 fathom 1.8288 1 1.8288
9030 nautical mile 1852 1 1852
9031 German legal metre 1.0000135965 1 1.0000135965
9033 US survey chain 792 39.37 20.11684023368047
9034 US survey link 7.92 39.37 0.2011684023368047
9035 US survey mile 63360 39.37 1609.3472186944375
9036 kilometre 1000 1 1000
9037 Clarke's yard 0.9143917962 1 0.9143917962
9038 Clarke's chain 20.1166195164 1 20.1166195164
9039 Clarke's link 0.201166195164 1 0.201166195164
9040 British yard (Sears 1922) 36 39.370147 0.9143984146160287
9041 British foot (Sears 1922) 12 39.370147 0.3047994715386762
9042 British chain (Sears 1922) 792 39.370147 20.116765121552632
9043 British link (Sears 1922) 7.92 39.370147 0.2011676512155263
9050 British yard (Benoit 1895 A) 0.9143992 1 0.9143992
9051 British foot (Benoit 1895 A) 0.9143992 3 0.3047997333333333
9052 British chain (Benoit 1895 A) 20.1167824 1 20.1167824
9053 British link (Benoit 1895 A) 0.201167824 1 0.201167824
9060 British yard (Benoit 1895 B) 36 39.370113 0.9143992042898124
9061 British foot (Benoit 1895 B) 12 39.370113 0.30479973476327077
9062 British chain (Benoit 1895 B) 792 39.370113 20.116782494375872
9063 British link (Benoit 1895 B) 7.92 39.370113 0.2011678249437587
9070 British foot (1865) 0.9144025 3 0.30480083333333335
9080 Indian foot 12 39.370142 0.30479951024814694
9081 Indian foot (1937) 0.30479841 1 0.30479841
9082 Indian foot (1962) 0.3047996 1 0.3047996
9083 Indian foot (1975) 0.3047995 1 0.3047995
9084 Indian yard 36 39.370142 0.9143985307444408
9085 Indian yard (1937) 0.91439523 1 0.91439523
9086 Indian yard (1962) 0.9143988 1 0.9143988
9087 Indian yard (1975) 0.9143985 1 0.9143985
9093 Statute mile 1609.344 1 1609.344
9094 Gold Coast foot 6378300 20926201 0.3047997101815088
9095 British foot (1936) 0.3048007491 1 0.3048007491
9096 yard 0.9144 1 0.9144
9097 chain 20.1168 1 20.1168
9098 link 20.1168 100 0.201168
9099 British yard (Sears 1922 truncated) 0.914398 1 0.914398
9300 British foot (Sears 1922 truncated) 0.914398 3 0.30479933333333337
9301 British chain (Sears 1922 truncated) 20.116756 1 20.116756
9302 British link (Sears 1922 truncated) 20.116756 100 0.20116756

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.