WL#14127: Explicit cast between geometry types

Affects: Server-8.0 — Status: Complete

Description
Requirements
High Level Architecture
Low Level Design

This WL adds the capability to use explicit CAST to convert geometries into other compatible types, e.g., MULTIPOINT to LINESTRING and LINESTRING to MULTIPOINT.

The possible casts are

POINT to MULTIPOINT
POINT to GEOMCOLLECTION
LINESTRING to POLYGON (*)
LINESTRING to MULTIPOINT (*)
LINESTRING to MULTILINESTRING
LINESTRING to GEOMCOLLECTION
POLYGON to LINESTRING (*)
POLYGON to MULTILINESTRING (*)
POLYGON to MULTIPOLYGON
POLYGON to GEOMCOLLECTION
MULTIPOINT to POINT
MULTIPOINT to LINESTRING (*)
MULTIPOINT to GEOMCOLLECTION
MULTILINESTRING to LINESTRING
MULTILINESTRING to POLYGON (*)
MULTILINESTRING to MULTIPOLYGON (*)
MULTILINESTRING to GEOMCOLLECTION
MULTIPOLYGON to POLYGON
MULTIPOLYGON to MULTILINESTRING (*)
MULTIPOLYGON to GEOMECOLLECTION
GEOMCOLLECTION to POINT
GEOMCOLLECTION to LINESTRING
GEOMCOLLECTION to POLYGON
GEOMCOLLECTION to MULTIPOINT
GEOMCOLLECTION to MULTILINESTRING
GEOMCOLLECTION to MULTIPOLYGON

(*) Extension to SQL/MM defined casts

General requirements
Cast to POINT
Cast to LINESTRING
Cast to POLYGON
Cast to MULTIPOINT
Cast to MULTILINESTRING
Cast to MULTIPOLYGON
Cast to GEOMETRYCOLLECTION

General requirements

F-1a: Casting to instantiable geometry types MUST be allowed using both the CAST(source value AS target type) and CONVERT(source value, target type) syntaxes. The result of the cast MUST be the same regardless of the syntax used.
F-1b: Casting to geometry types using the JSON_VALUE function with a RETURNING clause specifying a geometry type MUST NOT be allowed. (Same behavior as before.)
F-1c: Casting to ARRAYs of geometry types MUST NOT be allowed. (Same behavior as before.)
F-1d: Non-instantiable geometry types (i.e., GEOMETRY) MUST NOT be allowed as the target type. (Same behavior as before.)
F-1e: Casting to non-geometry target types MUST NOT change as a result of this WL. (Same behavior as before.)
F-2: If the source value is NULL, the function MUST return NULL.
F-3: If the target type is an instantiable geometry type and the source argument is not a syntactically well-formed geometry, the function MUST raise ER_GIS_INVALID_DATA during function evaluation.
F-4: If the target type is an instantiable geometry type and the source argument argument is a well-formed geometry in an undefined SRS, the function MUST raise ER_SRS_NOT_FOUND during function evaluation.
F-5: Casting between geometry types MUST NOT change coordinate values or order.
F-6a: The result of a cast MUST be in the same SRS as the source geometry.
F-6b: If the geometry argument is a geometry in a geographic SRS and a longitude value is not in the range (-180,180] (in degrees -- other limits in other units), the function MUST raise ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE. (*)
F-6c: If the geometry argument is a geometry in a geographic SRS and a latitude value is not in the range [-90,90] (in degrees -- other limits in other units), the function MUST raise ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE. (*)

Cast to POINT

F-7a: If the target type is POINT and the source value is a well-formed geometry of type POINT, the function MUST return that point.
F-7b: If the target type is POINT and the source value is a well-formed geometry of type MULTIPOINT containing a single point, the function MUST return that point. If the source value contains more than one point, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-7c: If the target type is POINT and the source value is a well-formed geometry of type GEOMCOLLECTION containing only a single point, the function MUST return that point. If the source value is empty, contains more than one point, or contains other types of geometries, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-7d: If the target type is POINT and the source value is a well-formed geometry other than POINT, MULTIPOINT or GEOMCOLLECTION, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.

Cast to LINESTRING

F-8a: If the target type is LINESTRING and the source value is a well-formed geometry of type LINESTRING, the function MUST raturn that linestring.
F-8b: If the target type is LINESTRING and the source value is a well-formed geometry of type POLYGON that has no inner rings, the function MUST return a LINESTRING containing the points of the outer ring in the same order. If the source value has inner rings, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-8c: If the target type is LINESTRING and the source value is a well-formed geometry of type MULTIPOINT containing at least two points, the function MUST return a LINESTRING containing the points of the multipoint in the order they appear in the source value. If the source value contains only one point the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-8d: If the target type is LINESTRING and the source value is a well-formed geometry of type MULTILINESTRING containing a single linestring, the function MUST return that linestring. If the source value contains more than one linestring, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-8e: If the target type is LINESTRING and the source value is a well-formed geometry of type GEOMCOLLECTION containing only a single linestring, the function MUST return that linestring. If the source value is empty, contains more than one linestring, or contains other types of geometries, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-8f: If the target type is LINESTRING and the source value is a well-formed geometry other than LINESTRING, POLYGON, MULTIPOING, MULTILINESTRING or GEOMCOLLECTION, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.

Cast to POLYGON

F-9a: If the target type is POLYGON and the source value is a well-formed geometry of type LINESTRING that is a ring (i.e, the start and end points are the same), the function MUST return a POLYGON with an outer ring consisting of the points of the linestring in the same order. If the source value is not a ring, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation. If the ring is not in the correct order (exterior ring must be counter-clockwise), the function MUST raise ER_INVALID_CAST_POLYGON_RING_DIRECTION during function evaluation.
F-9b: If the target type is POLYGON and the source values is a well-formed geometry of type POLYGON, the function MUST return that polygon.
F-9c: If the target type is POLYGON and the source value is a well-formed geometry of type MULTILINESTRING where all elements are rings, the function MUST return a POLYGON with the first linestring as outer ring and any further linestrings as inner rings. If any element of the source value is not a ring, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation. If any ring is not in the correct order (exterior ring must be counter-clockwise, interior rings must be clockwise), the function MUST raise ER_INVALID_CAST_POLYGON_RING_DIRECTION during function evaluation.
F-9d: If the target type is POLYGON and the source value is a well-formed geometry of type MULTIPOLYGON containing a single polygon, the function MUST return that polygon. If the source value contains more than one polygon, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-9e: If the target type is POLYGON and the source value is a well-formed geometry of type GEOMCOLLECTION containing only a single polygon, the function MUST return that polygon. If the source value is empty, contains more than one polygon, or contains other types of geometries, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-9f: If the target type is POLYGON and the source value is a well-formed geometry other than LINESTRING, POLYGON, MULTILINESTRING, MULTIPOLYGON or GEOMCOLLECTION, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.

Cast to MULTIPOINT

F-10a: If the target type is MULTIPOINT and the source value is a well-formed geometry of type POINT, the function MUST return a MULTIPOINT containing that point as the sole element.
F-10b: If the target type is MULTIPOINT and the source value is a well-formed geometry of type LINESTRING, the function MUST return a MULTIPOINT containing the points of the linestring in the same order.
F-10c: If the target type is MULTIPOINT and the source value is a well-formed geometry of type MULTIPOINT, the function MUST return that multipoint.
F-10d: If the target type is MULTIPOINT and the source value is a well-formed geometry of type GEOMCOLLECTION containing only points, the function MUST return a MULTIPOINT containing those points. If the geomcollection is empty or contains other types of geometries, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-10e: If the target type is MULTIPOINT and the source value is a well-formed geometry other than POINT, LINESTRING, MULTIPOINT or GEOMCOLLECTION, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.

Cast to MULTILINESTRING

F-11a: If the target type is MULTILINESTRING and the source value is a well-formed geometry of type LINESTRING, the function MUST return a MULTILINESTRING containing that linestring as the sole element.
F-11b: If the target type is MULTILINESTRING and the source value is a well-formed geometry of type POLYGON, the function MUST return a MULTILINESTRING containing the outer ring of the polygon as the first element and any inner rings as further elements in the order they appear in the source value.
F-11c: If the target type is MULTILINESTRING and the source value is a well-formed geometry of type MULTILINESTRING, the function MUST return that multilinestring.
F-11d: If the target type is MULTILINESTRING and the source value is a well-formed geometry of type MULTIPOLYGON containing only polygons without inner rings, the function MUST return a MULTILINESTRING containing the polygon rings in the order they appear in the source value. If the source value contains any polygons with inner rings, the function MUST raise ER_WRONG_PARAMETERS_TO_STORED_FUCT during function evaluation.
F-11e: If the target type is MULTILINESTRING and the source value is a well-formed geometry of type GEOMCOLLECTION containing only linestrings, the function MUST return a MULTILINESTRING containing those linestrings. If the source value is empty or contains other types of geometries, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-11f: If the target type is MULTILINESTRING and the source value is a well-formed geometry other than LINESTRING, POLYGON, MULTILINESTRING, MULTIPOLYGON or GEOMCOLLECTION, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.

Cast to MULTIPOLYGON

F-12a: If the target type is MULTIPOLYGON and the source value is a well-formed geometry of type POLYGON, the function MUST return a MULTIPOLYGON containing the polygon as the sole element.
F-12b: If the target type is MULTIPOLYGON and the source value is a well-formed geometry of type MULTILINESTRING where all elements are rings, the function MUST return a MULTIPOLYGON containing a polygon with only an outer ring for each element of the source value. If any element is not a ring, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation. If any ring is not in the correct order (exterior ring must be counter-clockwise), the function MUST raise ER_INVALID_CAST_POLYGON_RING_DIRECTION during function evaluation.
F-12c: If the target type is MULTIPOLYGON and the source value is a well-formed geometry of type MULTIPOLYGON, the function MUST return that multipolygon.
F-12d: If the target type is MULTIPOLYGON and the source value is a well-formed geometry of type GEOMCOLLECTION containing only polygons, the function MUST return a MULTIPOLYGON containing those polygons. If the source value is empty or contains other types of geometries, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.
F-12e: If the target type is MULTIPOLYTGON and the source value is a well-formed geometry other than POLYGON, MULTILINESTRING, MULTIPOLYGON or GEOMCOLLECTION, the function MUST raise ER_INVALID_CAST_TO_GEOMETRY during function evaluation.

Cast to GEOMETRYCOLLECTION

F-13a: The term "GEOMETRYCOLLECTION" MUST be accepted as a synonym for the target type "GEOMCOLLECTION" in cast expressions.
F-13b: If the target type is GEOMCOLLECTION and the source value is a well-formed geometry of type POINT, the function MUST return a GEOMCOLLECTION containing that POINT as the sole element.
F-13c: If the target type is GEOMCOLLECTION and the source value is a well-formed geometry of type LINESTRING, the function MUST return a GEOMCOLLECTION containing that LINESTRING as the sole element.
F-13d: If the target type is GEOMCOLLECTION and the source value is a well-formed geometry of type POLYGON, the function MUST return a GEOMCOLLECTION containing the polygon as the sole element.
F-13e: If the target type is GEOMCOLLECTION and the source value is a well-formed geometry of type MULTIPOINT, the function MUST return a GEOMCOLLECTION containing the points in the order they appear in the source value.
F-13f: If the target type is GEOMCOLLECTION and the source value is a well-formed geometry of type MULTILINESTRING, the function MUST return a GEOMCOLLECTION containing the linestrings in the order they appear in the source value.
F-13g: If the target type is GEOMCOLLECTION and the source value is a well-formed geometry of type MULTIPOLYGON, the function MUST return a GEOMCOLLECTION containing the elements of the MULTIPOLYGON in the order they appear in the source value.
F-13h: If the target type is GEOMCOLLECTION and the source value is a well-formed geometry of type GEOMCOLLECTION, the function MUST return that geomcollection.

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

I-1: No new syntax.
I-2: The semantics of interface SQL01 are changed: If the cast source value and target type are one of the geometric combination mentioned above , the cast will return a new value instead of an error.
I-3: Interface ERR01 is extended with 2 new error messages:
ER_INVALID_CAST_TO_GEOMETRY, SQLSTATE 22S01, "Invalid cast from %s to %s." (e.g. "Invalid cast from LINESTRING to POINT.)
ER_INVALID_CAST_POLYGON_RING_DIRECTION, SQLSTATE 22S04, "Invalid cast from %s to %s. A polygon ring is in the wrong direction."
I-4: No new warnings.

This WL extends the existing CAST (source_value AS taget_type) implementation.

Items

New item classes are created in sql/item_geofunc.{h,cc} for each target type:

Item_typecast_point
Item_typecast_linestring
Item_typecast_polygon
Item_typecast_multipoint
Item_typecast_multilinestring
Item_typecast_multipolygon
Item_typecast_geomcollection

All inherit from a common ancestor Item_typecast_geometry.

create_func_cast

Function create_func_cast (sql/item_create.cc) is extended to handle the new target types.

MTR tests testing the functional requirements are placed in mysql-test/suite/gis/t/cast_as_<target_type>.t.