############################################################################### # # # CAST(... AS ) allows casting between # # certain geometry types. # # # # Creation Date: 2020-08-02 # # Author: Magnus Rushfeldt # # # ############################################################################### --echo ##################################### --echo # Testing of general requirements # --echo ##################################### --echo # --echo #################################################################### --echo # Requirement F-1a: # --echo # Casting using CONVERT(..., ) must be allowed # --echo # and give the same result as CAST(... AS ). # --echo #################################################################### --echo # --echo # Casting using CONVERT(..., POINT) must give the same result --echo # as CAST(... AS POINT). --echo # Must return 1. SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1))') AS POINT), CONVERT(ST_GEOMFROMTEXT('MULTIPOINT((1 1))'), POINT)); --echo # --echo # Casting using CONVERT(..., LINESTRING) must give the same result --echo # as CAST(... AS LINESTRING). --echo # Must return 1. SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2))') AS LINESTRING), CONVERT(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2))'), LINESTRING)); --echo # --echo # Casting using CONVERT(..., POLYGON) must give the same result --echo # as CAST(... AS POLYGON). --echo # Must return 1. SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)))') AS POLYGON), CONVERT(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)))'), POLYGON)); --echo # --echo # Casting using CONVERT(..., MULTIPOINT) must give the same result --echo # as CAST(... AS MULTIPOINT). --echo # Must return 1. SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS MULTIPOINT), CONVERT(ST_GEOMFROMTEXT('POINT(1 1)'), MULTIPOINT)); --echo # --echo # Casting using CONVERT(..., MULTILINESTRING) must give the same result --echo # as CAST(... AS MULTILINESTRING). --echo # Must return 1. SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS MULTILINESTRING), CONVERT(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)'), MULTILINESTRING)); --echo # --echo # Casting using CONVERT(..., MULTIPOLYGON) must give the same result --echo # as CAST(... AS MULTIPOLYGON). --echo # Must return 1. SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS MULTIPOLYGON), CONVERT(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), MULTIPOLYGON)); --echo # --echo # Casting using CONVERT(..., GEOMETRYCOLLECTION) must give the same result --echo # as CAST(... AS GEOMETRYCOLLECTION). --echo # Must return 1. SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION), CONVERT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))'), GEOMETRYCOLLECTION)); --echo # --echo #################################################################### --echo # Requirement F-1b: # --echo # Casting to geometry types using # --echo # JSON_VALUE(... RETURNING ) must not be allowed. # --echo #################################################################### --echo # --echo # Casting to POINT using JSON_VALUE(... RETURNING POINT) must raise error. --error ER_INVALID_CAST_TO_GEOMETRY DO JSON_VALUE('{"geometry": {"type" : "Point", "coordinates": [1.0, 1.0]}}', '$.geometry' RETURNING POINT); --echo # --echo # Casting to LINESTRING using JSON_VALUE(... RETURNING LINESTRING) must raise error. --error ER_INVALID_CAST_TO_GEOMETRY DO JSON_VALUE('{"geometry": {"type": "LineString", "coordinates": [[1.0, 1.0], [2.0, 2.0]]}}', '$.geometry' RETURNING LINESTRING); --echo # --echo # Casting to POLYGON using JSON_VALUE(... RETURNING POLYGON) must raise error. --error ER_INVALID_CAST_TO_GEOMETRY DO JSON_VALUE('{"geometry": {"type": "Polygon", "coordinates": [[[1.0, 1.0], [4.0, 1.0], [4.0, 4.0], [1.0, 4.0], [1.0, 1.0]]]}}', '$.geometry' RETURNING POLYGON); --echo # --echo # Casting to MULTIPOINT using JSON_VALUE(... RETURNING MULTIPOINT) must raise error. --error ER_INVALID_CAST_TO_GEOMETRY DO JSON_VALUE('{"geometry": {"type": "MultiPoint", "coordinates": [[1.0, 1.0], [2.0, 2.0]]}}', '$.geometry' RETURNING MULTIPOINT); --echo # --echo # Casting to MULTILINESTRING using JSON_VALUE(... RETURNING MULTILINESTRING) must raise error. --error ER_INVALID_CAST_TO_GEOMETRY DO JSON_VALUE('{"geometry": {"type": "MultiLineString", "coordinates": [[[1.0, 1.0], [2.0, 2.0]], [[3.0, 3.0], [4.0, 4.0]]]}}', '$.geometry' RETURNING MULTILINESTRING); --echo # --echo # Casting to MULTIPOLYGON using JSON_VALUE(... RETURNING MULTIPOLYGON) must raise error. --error ER_INVALID_CAST_TO_GEOMETRY DO JSON_VALUE('{"geometry": {"type": "{"type": "MultiPolygon", "coordinates": [[[[1.0, 1.0], [4.0, 1.0], [4.0, 4.0], [1.0, 4.0], [1.0, 1.0]]], [[[2.0, 2.0], [2.0, 3.0], [3.0, 3.0], [3.0, 2.0], [2.0, 2.0]]]]}}', '$.geometry' RETURNING MULTIPOLYGON); --echo # --echo # Casting to GEOMETRYCOLLECTION using JSON_VALUE(... RETURNING GEOMETRYCOLLECTION) must raise error. --error ER_INVALID_CAST_TO_GEOMETRY DO JSON_VALUE('{"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Point", "coordinates": [1.0, 1.0]}, {"type": "LineString", "coordinates": [[2.0, 2.0], [3.0, 3.0]]}]}}', '$.geometry' RETURNING GEOMETRYCOLLECTION); --echo # --echo #################################################################### --echo # Requirement F-1c: # --echo # Casting to ARRAY of geometry types must not be allowed. # --echo #################################################################### --echo # --echo # Casting using CAST(... AS POINT ARRAY) must raise error. --error ER_NOT_SUPPORTED_YET DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT ARRAY); --echo # --echo # Casting using CAST(... AS LINESTRING ARRAY) must raise error. --error ER_NOT_SUPPORTED_YET DO CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING ARRAY); --echo # --echo # Casting using CAST(... POLYGON ARRAY) must raise error. --error ER_NOT_SUPPORTED_YET DO CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON ARRAY); --echo # --echo # Casting using CAST(... MULTIPOINT ARRAY) must raise error. --error ER_NOT_SUPPORTED_YET DO CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT ARRAY); --echo # --echo # Casting using CAST(... MULTILINESTRING ARRAY) must raise error. --error ER_NOT_SUPPORTED_YET DO CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING ARRAY); --echo # --echo # Casting using CAST(... MULTIPOLYGON ARRAY) must raise error. --error ER_NOT_SUPPORTED_YET DO CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON ARRAY); --echo # --echo # Casting using CAST(... GEOMETRYCOLLECTION ARRAY) must raise error. --error ER_NOT_SUPPORTED_YET DO CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION ARRAY); --echo # --echo #################################################################### --echo # Requirement F-1d: # --echo # Non-instantiable target types must not be allowed. # --echo #################################################################### --echo # --echo # Casting to GEOMETRY must raise error. --error ER_PARSE_ERROR DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS GEOMETRY)); --echo # --echo # Casting to CURVE must raise error. --error ER_PARSE_ERROR DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS CURVE)); --echo # --echo # Casting to SURFACE must raise error. --error ER_PARSE_ERROR DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS SURFACE)); --echo # --echo # Casting to MULTICURVE must raise error. --error ER_PARSE_ERROR DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS MULTICURVE)); --echo # --echo # Casting to MULTISURFACE must raise error. --error ER_PARSE_ERROR DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS MULTISURFACE)); --echo # --echo #################################################################### --echo # Requirement F-1e: # --echo # Casting to non-geometry types must give same result as before. # --echo #################################################################### --echo # --echo # Casting geometry to SIGNED must raise error. --error ER_WRONG_ARGUMENTS DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS SIGNED); --echo # --echo # Casting geometry to UNSIGNED must raise error. --error ER_WRONG_ARGUMENTS DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS UNSIGNED); --echo # --echo # Casting geometry to YEAR must raise error. --error ER_WRONG_ARGUMENTS DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS YEAR); --echo # --disable_warnings --echo # Casting geometry to DATE must return NULL. SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DATE); --echo # --echo # Casting geometry to TIME must return NULL. SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS TIME); --echo # --echo # Casting geometry to DATETIME must return NULL. SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DATETIME); --echo # --echo # Casting geometry to BINARY must return SWKB. SELECT HEX(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS BINARY)); --echo # --echo # Casting geometry to DECIMAL must return 0. SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DECIMAL); --echo # --echo # Casting geometry to FLOAT must return 0. SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS FLOAT); --echo # --echo # Casting geometry to DOUBLE must return 0. SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DOUBLE); --echo # --enable_warnings --echo # Casting geometry to JSON must return JSON-string. SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS JSON); --echo # --echo #################################################################### --echo # Requirement F-2: # --echo # Casting NULL to geometry types must return NULL. # --echo #################################################################### --echo # SELECT CAST(NULL AS POINT); --echo # --echo #################################################################### --echo # Requirement F-3: # --echo # Casting geometries that are not syntactically well-formed # --echo # must not be allowed. # --echo #################################################################### --echo # --echo # Casting 'POINT(1)' to POINT must raise error. --error ER_GIS_INVALID_DATA DO CAST(0x000000000101000000000000000000F03F AS POINT); --echo # --echo #################################################################### --echo # Requirement F-4: # --echo # Casting geometry with undefined SRS must raise error. # --echo #################################################################### --echo # --error ER_SRS_NOT_FOUND DO CAST(ST_GEOMFROMTEXT('POINT(1 1)', 256) AS POINT); --echo # --echo #################################################################### --echo # Requirement F-5: # --echo # Casting must not change coordinate values or order. # --echo #################################################################### --echo # --echo # Must return 1. SELECT ST_EQUALS(ST_GEOMFROMTEXT('POINT(0 1)'), CAST(ST_GEOMFROMTEXT('POINT(0 1)') AS POINT)); --echo # --echo # Must return 1. SELECT ST_EQUALS(ST_GEOMFROMTEXT('LINESTRING(1 2, 3 4)'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))') AS LINESTRING)); --echo # --echo # Must return 1. SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 4 1, 4 4, 1 4, 1 1)') AS POLYGON)); --echo # --echo # Must return 1. SELECT ST_EQUALS(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 2, 3 4)') AS MULTIPOINT)); --echo # --echo # Must return 1. SELECT ST_EQUALS(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'), CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))') AS MULTILINESTRING)); --echo # --echo # Must return 1. SELECT ST_EQUALS(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 3 2, 3 3, 2 3, 2 2)))'), CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2))') AS MULTIPOLYGON)); --echo # --echo # Must return 1. SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 2), POINT(3 4))'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))') AS GEOMETRYCOLLECTION)); --echo # --echo #################################################################### --echo # Requirement F-6a: # --echo # Casting must not change the SRS of the geometry. # --echo #################################################################### --echo # --echo # Must return 0. SELECT ST_SRID(CAST(ST_GEOMFROMTEXT('POINT(1 1)', 0) AS POINT)); --echo # --echo # Must return 4326. SELECT ST_SRID(CAST(ST_GEOMFROMTEXT('POINT(45 90)', 4326) AS POINT)); --echo # --echo #################################################################### --echo # Requirement F-6b: # --echo # Casting geographic geometry with longitude outside of # --echo # (-180, 180] must not be allowed. # --echo #################################################################### --echo # --echo # Casting POINT(0 180.000000001) with SRID=4326 to POINT must raise error. --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE DO CAST(0xE6100000010100000070890000008066400000000000000000 AS POINT); --echo # --echo # Casting POINT(0 -180.000000001) with SRID=4326 to POINT must raise error. --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE DO CAST(0xE6100000010100000070890000008066C00000000000000000 AS POINT); --echo # --echo #################################################################### --echo # Requirement F-6c: # --echo # Casting geographic geometry with latitude outside of # --echo # [-90, 90] must not be allowed. # --echo #################################################################### --echo # --echo # Casting POINT(90.000000001 0) with SRID=4326 to POINT must raise error. --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE DO CAST(0xE610000001010000000000000000000000E112010000805640 AS POINT); --echo # --echo # Casting POINT(-90.000000001 0) with SRID=4326 to POINT must raise error. --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE DO CAST(0xE610000001010000000000000000000000E1120100008056C0 AS POINT); --echo # --echo #################################################################### --echo # Queries with EXPLAIN should return appropiate function name # --echo #################################################################### --echo # --echo # Should return /* select#1 */ select cast(st_geomfromtext('point(1 1)') as point) AS `CAST(ST_GEOMFROMTEXT('POINT((1 1))') AS POINT)' EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT); --echo # --echo # Should return /* select#1 */ select cast(st_geomfromtext('LINESTRING(1 1, 2 2)') as linestring) AS `CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING)` EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING); --echo # --echo # Should return /* select#1 */ select cast(st_geomfromtext('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') as polygon) AS `CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON)` EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON); --echo # --echo # Should return /* select#1 */ select cast(st_geomfromtext('MULTIPOINT((1 1), (2 2))') as multipoint) AS `CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT)` EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT); --echo # --echo # Should return /* select#1 */ select cast(st_geomfromtext('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') as multilinestring) AS `CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING)` EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING); --echo # --echo # Should return /* select#1 */ select cast(st_geomfromtext('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') as multipolygon) AS `CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON)` EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON); --echo # --echo # Should return /* select#1 */ select cast(st_geomfromtext('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') as geometrycollection) AS `CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION)` EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION); --echo #################################################################### --echo # SELECT ... UNION SELECT ... with # --echo # CAST(... AS ) columns should work as expected # --echo #################################################################### --echo # --echo # Should return Point. WITH g(g1) AS ( SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT) UNION SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT)) SELECT ST_ASTEXT(g1) FROM g; --echo # --echo # Should return Linestring. WITH g(g1) AS ( SELECT CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING) UNION SELECT CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING)) SELECT ST_ASTEXT(g1) FROM g; --echo # --echo # Should return Polygon. WITH g(g1) AS ( SELECT CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON) UNION SELECT CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON)) SELECT ST_ASTEXT(g1) FROM g; --echo # --echo # Should return Multipoint. WITH g(g1) AS ( SELECT CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT)) SELECT ST_ASTEXT(g1) FROM g; --echo # --echo # Should return Multilinestring. WITH g(g1) AS ( SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING)) SELECT ST_ASTEXT(g1) FROM g; --echo # --echo # Should return Multipolygon. WITH g(g1) AS ( SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON)) SELECT ST_ASTEXT(g1) FROM g; --echo # --echo # Should return Geometrycollection. WITH g(g1) AS ( SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION) UNION SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION) UNION SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION) UNION SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION)) SELECT ST_ASTEXT(g1) FROM g;