WL#10999: ST_Distance_Sphere for geographic geometries

Currently, ST_Distance_Sphere expects its input to be X=longitude in degrees and Y=latitude in degrees, regardless of SRID. This made sense in 5.7 when MySQL didn't know anything about spatial reference systems. However, with the introduction of geography, it is not natural for geographic coordinates to be interpreted in any other way than the way they're specified in the SRS definition. E.g., a point specified in radians should be interpreted as in radians, not as degrees. Also, coordinates in projected SRSs should not be treated as degrees, since they're explicitly in meters.

This WL extends ST_Distance_Sphere to also accept parameters in geographic SRSs (still limited to points and multipoints). This way, ST_Distance_Sphere can be used as a performance optimization for geographical distance, if the user can accept less accurate results.

The WL also forbids the user from doing ST_Distance_Sphere on geometries in projected SRSs since those coordinates don't make sense on a sphere.

For geographic parameters, the radius of the sphere will default to the semi-major axis of the ellipsoid of the SRS of the parameters. The default value can be overridden by the third parameter to the function, just as it is today.

F-1

The function MUST return NULL if at least one of its parameters is NULL.

F-2

The function MUST NOT return NULL if none of its parameters are NULL.

F-3

If one or more geometry arguments are not syntactically well-formed geometries, the function MUST raise ER_GIS_INVALID_DATA during function evaluation.

F-4

If the geometry arguments are not in the same SRID, the function MUST raise ER_GIS_DIFFERENT_SRIDS.

F-5

If the geometry arguments are in an undefined SRS, the function MUST raise ER_SRS_NOT_FOUND during function evaluation.

F-6

If both geometry parameters are valid Cartesian points or multipoints in SRID 0, the function MUST return the shortest distance between the two geometries on a sphere with radius 6370986.0 meters, interpreting the X coordinate as longitude in degrees and the Y coordinate as latitude in degrees. The result MUST be in meters.

F-7

If both geometry parameters are valid geographic points or multipoints, the function MUST return the shortest distance between the two geometries on a sphere with the provided radius, or if none is provided, the radius equal to (2a+b)/3, where a is the semi-major axis and b is the semi-minor axis of the SRS. The result MUST be in meters.

F-8

If the geometry parameters are in a projected SRS, the function must raise ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS.

F-9 a)

The function MUST raise ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS if at least one of the geometries is neither POINT or MULTIPOINT, and the SRID is 0.

F-9 b)

The function MUST raise ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS if at least one of the geometries is neither POINT or MULTIPOINT, and the SRID refers to a geographic SRS.

F-9 c)

The function MUST raise ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS if the SRID refers to a projected SRS.

F-10

If any geometry parameter has a longitude value that is not in the range (-180,180] (in degrees -- other limits in other units), ST_Distance MUST raise ER_LONGITUDE_OUT_OF_RANGE.(*)

F-11

If any geometry parameter has a latitude value that is not in the range [-90,90] (in degrees -- other limits in other units), ST_Distance_Sphere MUST raise ER_LATITUDE_OUT_OF_RANGE.(*)

F-12

If the radius parameter is less than or equal to 0.0, ST_Distance_Sphere MUST raise ER_NONPOSITIVE_RADIUS.

F-13

If the distance exceeds what can be represented as a finite double precision floating point number, ST_Distance_Sphere MUST raise ER_STD_OVERFLOW_ERROR.

(*) The exact limits will deviate slightly because of floating point arithmetics.

I-1

No new files.

I-2

No new syntax.

I-3

No new commands.

I-4

No new tools.

I-5

The semantics of interface SQL01 are changed: If the geometries are in a geographic SRS, and the geometries are points or multipoints (in any combination), ST_Distance_Sphere will return the spherical distance between the geometries, computed on a sphere with radius equal to the semi-major axis of the geographical SRS, unless an explicit radius was specified. The coordinates of parameters will be interpreted in the unit they are in (used to be hard-coded to degrees).

ST_Distance_Sphere will start raising ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS if the SRS of the arguments is projected. For parameters in SRID 0, computation will be as it used to be.

For all SRSs, different error messages are returned if longitude or latitude coordinates are out of range.

The error returned if the arguments are not points/multipoints is changed from ER_GIS_UNSUPPORTED_ARGUMENT to ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS/ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS (depending on SRS type).

I-6

Interface ERR01 is extended with 2 new error messages:
ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS, SQLSTATE 22S00, "%.192s(%.40s, %.40s) has not been implemented for projected spatial reference systems.".
ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS, SQLSTATE 22S00, "%.192s(%.40s, %.40s) has not been implemented for Cartesian spatial reference systems.".
ER_NONPOSITIVE_RADIUS, SQLSTATE 22003, "Invalid radius provided to function %s: Radius must be greater than zero."

Rewrite ST_Distance_Sphere after the pattern of ST_Distance, using the new gis::Geometry hierarchy. Only point-point, point-multipoint, multipoint-point and multipoint-multipoint is supported. Multipoint-multipoint may not be supported by BG. In that case, the solution is to loop through the first multipoint and call bg::distance several times.

To get spherical computaions, pass a haversine strategy to bg::distance: bg::strategy::distance::haversine<double> spherical_strategy(earth_radius);