4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/suite/gis/t/geojson_functions.test

4081 lines
171 KiB
Plaintext
Raw Permalink Blame History

#####################################################################
# #
# This test is for validating the functionality of the different #
# GeoJSON functions. #
# #
# Creation Date: 2014-07-08 #
# Author : Erik Frøseth #
# #
#####################################################################
#####################################################################
# ST_ASGEOJSON()
#####################################################################
--echo # Checking all valid GEOMETRY types
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(-10 -11)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E+308)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E-308 1.7976931348623157E-308)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("LINESTRING(30 10, 10 30, 40 40)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("LINESTRING(0 0, 0 10, 10 10, 10 0)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POLYGON((30 10, 40 40, 20 40, 10 20, 30 10))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POLYGON((35 10, 45 45, 15 40, 10 20, 35 10),"
"(20 30, 35 35, 30 20, 20 30))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOINT(10 40, 40 30, 20 20, 30 10)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOINT(0 0, 10 10, 20 20, 30 30, 40 40)"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10),"
"(10 10, 20 20, 50 50, 100 100))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35),"
"(30 20, 20 15, 20 25, 30 20)))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION()"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(10 20)))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))"));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"GEOMETRYCOLLECTION(POINT(10 20)),"
"GEOMETRYCOLLECTION(LINESTRING(0 0, 100 100)),"
"GEOMETRYCOLLECTION(POLYGON((0 0, 0 10, 10 10, 0 0)))"
")"
));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"POINT(4 6),"
"LINESTRING(4 6,7 10),"
"POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)),"
"MULTIPOINT (10 40, 40 30, 20 20, 30 10),"
"MULTILINESTRING ((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10)),"
"MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),"
"((15 5, 40 10, 10 20, 5 10, 15 5)))"
")"
));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"POINT(4 6),"
"GEOMETRYCOLLECTION("
"POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)),"
"MULTIPOINT (10 40, 40 30, 20 20, 30 10),"
"MULTILINESTRING ((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10)),"
"MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),"
"((15 5, 40 10, 10 20, 5 10, 15 5)))"
"),"
"POINT(6 4)"
")"
));
--echo # Checking NULL as one or more of the arguments.
SELECT ST_ASGEOJSON(NULL);
SELECT ST_ASGEOJSON(NULL, NULL);
SELECT ST_ASGEOJSON(NULL, 10);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), NULL);
SELECT ST_ASGEOJSON(NULL, 10, 0);
SELECT ST_ASGEOJSON(NULL, NULL, 0);
SELECT ST_ASGEOJSON(NULL, 10, NULL);
SELECT ST_ASGEOJSON(NULL, NULL, NULL);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), NULL, 0);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), 10, NULL);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), NULL, NULL);
--echo # Checking options argument and maxdecimaldigits argument
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4326), 100, 2);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4326), 100, 4);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4326), 100, 6);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 0), 100, 5);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 0), 100, 2);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", -1), 100, 4);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", -1000), 100, 6);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4294967296), 100, 6);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4294967295123), 100, 7);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), 0, 0);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), 4, 7);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), 10, 0);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), 0);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), 4);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), 10);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.123456789)", 0), 10000);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.123456789)", 0), 1000000);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.123456789)", 0), 2147483647);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 0);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 2);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 3);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 4);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 5);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 6);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, 7);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", "0"), 100, 1);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", "-1"), 100, 2);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", "-1000"), 100, 3);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", "4294967296"), 100, 5);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", "4294967295123"), 100, 6);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", (CAST(4326 AS CHAR))), 100, 7);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), "0", 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), "4", 2);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.123456789 11.123456789)", 0), "10", 3);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), "10000", 4);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), "1000000", 5);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), "2147483647", 6);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), (CAST(20 AS CHAR)), 7);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10.1234567890123456789 11.1234567890123456789)", 0), 10, (CAST(7 AS CHAR)));
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", " 4326.34 "), 100, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", "*&$%4326"), 100, 2);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 0), "12.34", 3);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4326), "*&$%$123", 4);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4236), 10, "06.34");
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4326), 10, "*&$%$123");
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35),"
"(30 20, 20 15, 20 25, 30 20)))"), 10, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"GEOMETRYCOLLECTION(POINT(10 20)),"
"GEOMETRYCOLLECTION(LINESTRING(0 0, 100 100)),"
"GEOMETRYCOLLECTION(POLYGON((0 0, 0 10, 10 10, 0 0)))"
")"
), 5, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION("
"POINT(4 6),"
"GEOMETRYCOLLECTION("
"POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)),"
"MULTIPOINT (10 40, 40 30, 20 20, 30 10),"
"MULTILINESTRING ((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10)),"
"MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),"
"((15 5, 40 10, 10 20, 5 10, 15 5)))"
"),"
"POINT(6 4)"
")"
), 10, 1 );
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION()"), 10, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E-308)"), 0, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E-308)"), 10, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E-308)"), 20, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E-308)"), 30, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E-308)"), 40, 1);
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E-308)"), 1000, 1);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT('POINT(1.2345678901234567890 2.345678901234567890)'), SUM(3));
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT('POINT(1.2345678901234567890 2.345678901234567890)'), AVG(3));
# NOTE: Since ST_AsGeoJSON now ouputs the new JSON type,
# the character set will be decided by the JSON to_string
# function. As of writing, this is utf8mb4 regardless of the
# client character set.
--echo # Check that function outputs correct character set
SET NAMES 'ascii';
SELECT CHARSET(ST_ASGEOJSON(POINT(-1.42, 283.12)));
SET NAMES 'utf8';
SELECT CHARSET(ST_ASGEOJSON(POINT(-1.42, 283.12)));
--echo # Invalid function calls
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("1"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT()"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10)"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(a b)"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 b)"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(a 11)"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10, 11)"));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POLYGON((40 40, 20 45, 45 30, 40 40)"), 10);
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35),"), 10, 2);
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"GEOMETRYCOLLECTION(POINT(10 20)),"
"GEOMETRYCOLLECTION(LINESTRING(0 0, 100 100)),"
"GEOMETRYCOLLECTION(POLYGON((0 0, 0 10, 10 10, 0 0))),"
")"
));
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOINT()"), 10);
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTILINESTRING(())"), 10);
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOLYGON((()))"), 10);
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("LINESTRING(1)"), 10);
--error ER_GIS_INVALID_DATA
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTILINESTRING((0 0, 10 10),())"), 10);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, -1);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, -1000);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, 8);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, 1000);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, "-1");
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, "-1000");
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, "8");
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2, "1000");
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)", 4236), 10, "16.34");
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), -1, 0);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), -1000, 0);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2147483648, 0);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2147483647000, 0);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), "-1", 0);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), "-1000", 0);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), "2147483648", 0);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), "2147483647000", 0);
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASGEOJSON();
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), 10, 0, 0);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(1);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(1, 2);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(1, 0, 1);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 2.1, 0);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 10, 0.1);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), (CAST(10 AS BINARY)), 0);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 10, (CAST(4 AS BINARY)));
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), (CAST(10 AS DECIMAL)), 0);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 20)"), 10, (CAST(4 AS DECIMAL)));
--error ER_BAD_FIELD_ERROR
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), abc, 0);
--error ER_BAD_FIELD_ERROR
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), 0, abc);
--error ER_BAD_FIELD_ERROR
SELECT ST_ASGEOJSON("*&$%$123", abc, 0);
PREPARE stmt FROM "SELECT ST_ASGEOJSON(?, 10, 0)";
PREPARE stmt FROM "SELECT ST_ASGEOJSON(POINT(10, 10), ?, 0)";
PREPARE stmt FROM "SELECT ST_ASGEOJSON(POINT(10, 10), 10, ?)";
PREPARE stmt FROM "SELECT ST_ASGEOJSON(?, ?, 0)";
PREPARE stmt FROM "SELECT ST_ASGEOJSON(?, 10, ?)";
PREPARE stmt FROM "SELECT ST_ASGEOJSON(POINT(10, 10), ?, ?)";
PREPARE stmt FROM "SELECT ST_ASGEOJSON(?, ?, ?)";
CREATE TABLE t1 (g GEOMETRY);
--echo # Check that packet overflow is handled correctly.
SET @old_max_allowed_packet= @@global.max_allowed_packet;
INSERT INTO t1 VALUES (ST_GEOMFROMTEXT(
" LINESTRING(1 2, 3 4, 5 6, 7 8, 9 10, 11 12, 13 14, 15 16, 17 18, 19 20, "
" 21 22, 23 24, 25 26, 27 28, 29 30, 31 32, 33 34, 35 36, 37 38, 39 40, "
" 41 42, 43 44, 45 46, 47 48, 49 50) "
));
SELECT ST_ASTEXT(g) FROM t1;
UPDATE t1 SET g = GEOMETRYCOLLECTION(g, g, g, g, g, g, g, g, g, g);
UPDATE t1 SET g = GEOMETRYCOLLECTION(g, g, g, g, g, g, g, g, g, g);
SELECT LENGTH(ST_ASTEXT(g)) FROM t1;
SELECT LENGTH(ST_ASGEOJSON(g)) FROM t1;
SET @@global.max_allowed_packet = 27648;
# Make a new connection where max_allowed_packet takes effect
connect (con1, localhost, root,,);
SELECT LENGTH(ST_ASGEOJSON(g)) FROM t1;
SELECT ST_ASGEOJSON(g) FROM t1;
# Cleanup
disconnect con1;
--source include/wait_until_disconnected.inc
connection default;
SET @@global.max_allowed_packet = @old_max_allowed_packet;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES ("");
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x00000000010200000000000000000024400000000000002440);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x00000000010300000000000000000024400000000000002440);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x00000000010300000000000000000024400000000000002440);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x0000000001040000000100000001010000000000000000000000000000);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x000000000107000000010000000107000000010000000101000000000000000000244000000000000024);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x00000000010300000001000000050000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E4000000000000024);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x00000000010300000001000000000000000000000000003E4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003E400000000000002440);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x00000000010200000001000000000000000000244000000000000024);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x0000000001050000000000000001020000000100000000000000000024400000000000003440);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x0000000001050000000200000001020000000100000000000000000024400000000000003440);
SELECT ST_ASGEOJSON(g) FROM t1;
DELETE FROM t1;
--error ER_CANT_CREATE_GEOMETRY_OBJECT
INSERT INTO t1 VALUES (0x000000000107000000010000000107000000010000000107000000010000000107000000010000000107000000010000000107000000010000000107000000);
SELECT ST_ASGEOJSON(g) FROM t1;
DROP TABLE t1;
#####################################################################
# ST_GEOMFROMGEOJSON()
#####################################################################
--echo # Checking all valid GeoJSON types
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"LineString\", \"coordinates\": "
"[ [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0] ] }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"MultiPoint\", \"coordinates\": "
"[ [-105.01, 39.57],[-80.66, 35.0] ] }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"MultiLineString\", \"coordinates\": "
" [ "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.23, 39.2415], "
" [-101.749, 39.7984], "
" [-101.5, 39.011] "
" ], "
" [ "
" [-99.23, 38.6605], "
" [-99.56, 38.727], "
" [-99.25, 38.018] "
" ], "
" [ "
" [-98.499, 38.913], "
" [-98.499, 38.913], "
" [-98.38, 38.15], "
" [-97.5, 38.629] "
" ] "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [41.83, 71.01], "
" [56.95, 33.75], "
" [21.79, 36.56], "
" [41.83, 71.01] "
" ] "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [41.83, 71.01], "
" [56.95, 33.75], "
" [21.79, 36.56], "
" [41.83, 71.01] "
" ], "
" [ "
" [15.40, 15.40], "
" [15.40, 25.40], "
" [25.40, 25.40], "
" [15.40, 15.40] "
" ] "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"MultiPolygon\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"MultiPolygon\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ], "
" [ "
" [1.0, 0.0], "
" [0.0, 0.0], "
" [0.0, 1.0], "
" [1.0, 0.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ ] "
" } "
" ] "
" } "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" } "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.0] "
" } "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"MultiPoint\", \"coordinates\": "
" [ "
" [-105.01, 39.57], "
" [-80.66, 35.0] "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [-105.01621, 39.57422] "
" }, "
" { \"type\": \"MultiPoint\", \"coordinates\": "
" [ "
" [-105.01, 39.57], "
" [-80.66, 35.05] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.64, 39.2415] "
" ] "
" }, "
" { \"type\": \"MultiLineString\", \"coordinates\": "
" [ "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.23, 39.2415], "
" [-101.749, 39.7984], "
" [-101.5, 39.011] "
" ], "
" [ "
" [-99.23, 38.6605], "
" [-99.56, 38.727], "
" [-99.25, 38.018] "
" ], "
" [ "
" [-98.499, 38.913], "
" [-98.499, 38.913], "
" [-98.38, 38.15], "
" [-97.5, 38.629] "
" ] "
" ] "
" }, "
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [41.83,71.01], "
" [56.95,33.75], "
" [21.79,36.56], "
" [41.83,71.01] "
" ] "
" ] "
" }, "
" { \"type\": \"MultiPolygon\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" } "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"id\": \"stadium\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"FeatureCollection\", \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"FeatureCollection\", \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" null, "
" \"properties\": "
" { "
" \"descr\": \"A NULL/EMPTY POINT\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-87.83827000459532, 42.0] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }"));
--echo # Checking that E notation is supported.
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [1.23e3, 2.2e-1]}", 1, 0));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [1e-300, 1e300]}", 1, 0));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [1.7976931348623157E+308, -1.7976931348623157E+308]}", 1, 0));
--echo # Checking for NULL arguments and GeoJSON null objects
SELECT ST_GEOMFROMGEOJSON(NULL);
SELECT ST_GEOMFROMGEOJSON(NULL, 1);
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}",
NULL);
SELECT ST_GEOMFROMGEOJSON(NULL, NULL, NULL);
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}",
NULL, NULL);
SELECT ST_GEOMFROMGEOJSON(NULL, 1, NULL);
SELECT ST_GEOMFROMGEOJSON(NULL, NULL, 4326);
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}",
1, NULL);
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}",
NULL, 4326);
SELECT ST_GEOMFROMGEOJSON(NULL, 1, 4326);
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Feature\", \"geometry\": null, \"properties\": "
" { \"foo\": \"bar\" } "
" }");
--echo # Checking for case sensitivity/insensitivity
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"GeOmEtRy\": "
" { \"TYPE\": \"Point\", \"COORDINATES\": "
" [-104.99404, 39.75621] "
" }, "
" \"PrOPERtiES\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"TyPe\": \"Point\", \"cOoRdInAtEs\": [102.0, 0.0]}"));
--error ER_SRS_NOT_FOUND
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"CRS\": "
" { \"TYPE\": \"name\", \"PROPERTIES\": "
" { \"NAME\": \"urn:ogc:def:crs:EPSG::1234\" } "
" } "
" }");
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:crs84\" } "
" } "
" }", 1));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:crs84\" } "
" } "
" }", 1, 10));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"point\", \"coordinates\": [102.0, 0.0]}"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"multiPoint\", \"coordinates\": [102.0, 0.0]}"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Linestring\", \"coordinates\": [102.0, 0.0]}"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"multiLinestring\", \"coordinates\": [102.0, 0.0]}"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"polygon\", \"coordinates\": [102.0, 0.0]}"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"MultiPolyGoN\", \"coordinates\": [102.0, 0.0]}"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"GEOMETRYCOLLECTION\", \"coordinates\": [102.0, 0.0]}"));
--echo # Checking invalid/incorrect JSON and GeoJSON inputs
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{}");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("()");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("[]");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("NULL");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"FeatureCollection\", "
" \"features\": [ "
" { "
" \"type\": \"FeatureCollection\", "
" \"features\": [] "
" }, "
" { "
" \"type\": \"Feature\", "
" \"geometry\": { \"type\": \"Point\", \"coordinates\": [10, 10] }, "
" \"properties\": null "
" } "
" ] "
"}");
--error ER_INCORRECT_TYPE
SELECT ST_GEOMFROMGEOJSON(CAST(
"{ "
" \"type\": \"FeatureCollection\", "
" \"features\": [ "
" { "
" \"type\": \"FeatureCollection\", "
" \"features\": [] "
" }, "
" { "
" \"type\": \"Feature\", "
" \"geometry\": { \"type\": \"Point\", \"coordinates\": [10, 10] }, "
" \"properties\": null "
" } "
" ] "
"}" AS BINARY));
--error ER_INCORRECT_TYPE
SELECT ST_GEOMFROMGEOJSON(CAST(
"{ "
" \"type\": \"FeatureCollection\", "
" \"features\": [ "
" { "
" \"type\": \"FeatureCollection\", "
" \"features\": [] "
" }, "
" { "
" \"type\": \"Feature\", "
" \"geometry\": { \"type\": \"Point\", \"coordinates\": [10, 10] }, "
" \"properties\": null "
" } "
" ] "
"}" AS DATE));
--echo # Checking correct handling of different coordinate dimensions (option one (default option))
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }"));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }"));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", 1));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", 1));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", 1));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", 1));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", "1"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", "1"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "1"));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", "1"));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", "1"));
--echo # Checking correct handling of different coordinate dimensions (option two)
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", 2));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", 2));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 2));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", 2));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", 2));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", "2"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", "2"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "2"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", "2"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", "2"));
--echo # Checking correct handling of different coordinate dimensions (option three)
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", 3));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", 3));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 3));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", 3));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", 3));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", "3"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", "3"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "3"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", "3"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", "3"));
--echo # Checking correct handling of different coordinate dimensions (option four)
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", 4));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", 4));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 4));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", 4));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", 4));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [] }", "4"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10] }", "4"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "4"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", "4"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", "4"));
--echo # Checking with valid ST_SRID value
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, 0));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", 2, 0));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20] }", 3, 0));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15, 20, 25] }", 4, 0));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, "0"));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}", SUM(1), SUM(4326)));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}", AVG(1), AVG(4326)));
-- echo # Various GeoJSON specification details
# Name must be a string.
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ type: \"Point\", \"coordinates\": [42, 42] }");
# Root must be geometry, feature or feature collection.
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
" { \"label\": \"foo\", \"data\": "
" { \"type\": \"Point\", \"coordinates\": "
" [10, 12] "
" } "
" }");
# Objects MUST have member with name "type" of type string.
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"type\": Point, \"coordinates\": [10, 10] }");
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON("{ \"type\": [ \"Point\" ], \"coordinates\": [10, 10] }");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{ \"typ\": \"Point\", \"coordinates\": [10, 10] }");
--echo # Checking "type" member with invalid string
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"point\", \"coordinates\": [10, 10] }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"POINT\", \"coordinates\": [10, 10] }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"linestring\", \"coordinates\": "
"[ [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0] ] }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"LINESTRING\", \"coordinates\": "
"[ [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0] ] }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"polygon\", \"coordinates\": "
" [ "
" [ "
" [41.83, 71.01], "
" [56.95, 33.75], "
" [21.79, 36.56], "
" [41.83, 71.01] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"POLYGON\", \"coordinates\": "
" [ "
" [ "
" [41.83, 71.01], "
" [56.95, 33.75], "
" [21.79, 36.56], "
" [41.83, 71.01] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"multipoint\", \"coordinates\": "
"[ [-105.01,39.57],[-80.66,35.0] ] }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"MULTIPOINT\", \"coordinates\": "
"[ [-105.01,39.57],[-80.66,35.0] ] }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"multilinestring\", \"coordinates\": "
" [ "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.23, 39.2415], "
" [-101.749, 39.7984], "
" [-101.5, 39.011] "
" ], "
" [ "
" [-99.23, 38.6605], "
" [-99.56, 38.727], "
" [-99.25, 38.018] "
" ], "
" [ "
" [-98.499, 38.913], "
" [-98.499, 38.913], "
" [-98.38, 38.15], "
" [-97.5, 38.629] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"MULTILINESTRING\", \"coordinates\": "
" [ "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.23, 39.2415], "
" [-101.749, 39.7984], "
" [-101.5, 39.011] "
" ], "
" [ "
" [-99.23, 38.6605], "
" [-99.56, 38.727], "
" [-99.25, 38.018] "
" ], "
" [ "
" [-98.499, 38.913], "
" [-98.499, 38.913], "
" [-98.38, 38.15], "
" [-97.5, 38.629] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"multipolygon\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"MULTIPOLYGON\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"geometrycollection\", \"geometries\": "
" [ "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" } "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"GEOMETRYCOLLECTION\", \"geometries\": "
" [ "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" } "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"feature\", \"id\": \"stadium\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"FEATURE\", \"id\": \"stadium\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"featurecollection\", \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"FEATURECOLLECTION\", \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }"));
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Feature\", \"coordinates\": [10, 10] }");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"FeatureCollection\", \"coordinates\": [10, 10] }");
# Geometry other than GeometryCollection must have member "coordinates" which is
# always an array.
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\",\"coordinates\": \"[42, 42]\" }");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\",\"cordinates\": [42, 42] }");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\",\"cordinates\": \"42, 42\" }");
# A geometry collection must have a member with the name "geometries". The value
# corresponding to "geometries" is an array. Each element in this array is a
# GeoJSON geometry object.
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometrie\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [0, 0] "
" } "
" ] "
" }");
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"GeometryCollection\", \"geometries\": { \"type\": \"Point\", \"coordinates\": [0, 0] } }");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Feature\", \"geometry\": { \"type\": \"Point\", \"coordinates\": [0, 0] } }");
# Test CRS parsing
# If no CRS found, set ST_SRID to 4326
--echo # Testing CRS Parsing and ST_SRID handling
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 20] }"));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::1234\" } "
" } "
" }"));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::1\" } "
" } "
" }"));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::4294967295\" } "
" } "
" }"));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:234\" } "
" } "
" }"));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:1\" } "
" } "
" }"));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:4294967295\" } "
" } "
" }"));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": null "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:4294967296\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:0\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:-1\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:-2147483648\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::0\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::-1\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::-2147483648\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG:4326\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"ogc:def:crs:EPSG::4326\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"ogc:def:crs:EPSG::\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG=1\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \":1\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPS:1\" } "
" } "
" }");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"link\", \"properties\": "
" { \"href\": \"http://example.com/crs/42\", "
" \"type\": \"proj4\" "
" } "
" } "
" }");
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPS:1\" } "
" } "
" } ", 1 , 4326 ));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::1\" } "
" } "
" }", 1, 0));
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::0\" } "
" } "
" }", 1, 4326));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:CRS100\" } "
" } "
" }", 1));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.30:CRS84\" } "
" } "
" }", 1));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { : \"urn:ogc:def:crs:OGC:1.3:CRS84\" } "
" } "
" }", 1));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": { \"urn:ogc:def:crs:OGC:1.3:CRS84\" } } "
" } "
" }", 1));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:CRS84\" } "
" } "
" }", 1));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:CRS84\" } "
" } "
" }", 1));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \" \" } "
" } "
" }", 1));
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" [ "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::1\" } "
" } "
" ] "
" }", 1));
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { } "
" } "
" }", 1));
--error ER_INVALID_GEOJSON_CRS_NOT_TOP_LEVEL
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::2000\" } "
" } "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::4326\" } "
" } "
" } "
" ] "
" }"));
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 20], \"crs\": { } }");
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, \"20\"] } ");
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_GEOMFROMGEOJSON("", -9223372036854775808);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_GEOMFROMGEOJSON("", 9223372036854775807);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_GEOMFROMGEOJSON("", 9223372036854775808);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_GEOMFROMGEOJSON("", 1, -9223372036854775808);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_GEOMFROMGEOJSON("", 1, 9223372036854775807);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_GEOMFROMGEOJSON("", 1, 9223372036854775808);
--echo # Checking invalid/incorrect option parameter input
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 0));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", -10));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", -1000));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 5));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 100));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 10000));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "0"));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "-10"));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "-1000"));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "5"));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "100"));
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", "10000"));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1.1));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", (CAST(1 AS DECIMAL))));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", (CAST(1 AS BINARY))));
--error ER_BAD_FIELD_ERROR
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", a));
--echo # Checking invalid/incorrect ST_SRID parameter input
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, -1));
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, -1000));
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, 4294967296));
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, 4294967295000));
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, "-1"));
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, "-1000"));
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, "4294967296"));
--error ER_DATA_OUT_OF_RANGE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, "4294967295000"));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, 1.1));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, (CAST(1 AS DECIMAL))));
--error ER_INCORRECT_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, (CAST(1 AS BINARY))));
--error ER_BAD_FIELD_ERROR
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, a));
--echo # Invalid function calls
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON("{ \"type\": \"Feature\", \"geometry\": [10, 20] }");
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON());
--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 15] }", 1, 0, 2));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\":: \"Point\", \"coordinates\": [10, 15] }", 1, 0));
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": [ \"Point\" ], \"coordinates\": [10, 15] }", 1, 0));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": }", 1, 0));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Geometry\", \"coordinates\": [10, 15] }", 1, 0));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": { \"Point\" }, \"coordinates\": [10, 15] }", 1, 0));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ \"type\": \"LineString\", \"coordinates\": [[100.0, 0.0, 1.0, 2.0], [101.0, 0.0]] }"));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [100.0, 0.0, 1.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"Point\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"name\": , \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", \"abc\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"type\": \"Point\", \"coordinates\": "
" } "
" }"));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" } "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" [2, 2] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" [2, 2], "
" [3, 3] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" [1, 4], "
" [4, 4], "
" [1, 1] "
" ], "
" [ "
" [2, 2] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" [1, 4], "
" [4, 4], "
" [1, 1] "
" ], "
" [ "
" [2, 2], "
" [2, 3] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" [1, 4], "
" [4, 4], "
" [1, 1] "
" ], "
" [ "
" [2, 2], "
" [2, 3], "
" [3, 3] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" 1, "
" [4, 4], "
" [1, 1] "
" ], "
" [ "
" [2, 2], "
" [2, 3], "
" [3, 3] "
" ] "
" ] "
" }"));
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" [1, 4], "
" [4, 4], "
" [1, 1] "
" ], "
" [ "
" [2, 2], "
" 0, "
" [3, 3] "
" ] "
" ] "
" }"));
--error ER_DIMENSION_UNSUPPORTED
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [1, 1], "
" [2, 2, 2], "
" [3, 3, 3, 3], "
" [1, 1] "
" ] "
" ] "
" }"));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\" : \"firstName\": \"John\" "
"} "
);
PREPARE stmt FROM "SELECT ST_GEOMFROMGEOJSON(?, 0, 0)";
PREPARE stmt FROM "SELECT ST_GEOMFROMGEOJSON('', ?, 0)";
PREPARE stmt FROM "SELECT ST_GEOMFROMGEOJSON('', 0, ?)";
PREPARE stmt FROM "SELECT ST_GEOMFROMGEOJSON(?, ?, 0)";
PREPARE stmt FROM "SELECT ST_GEOMFROMGEOJSON(?, 0, ?)";
PREPARE stmt FROM "SELECT ST_GEOMFROMGEOJSON('', ?, ?)";
PREPARE stmt FROM "SELECT ST_GEOMFROMGEOJSON(?, ?, ?)";
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{}foo");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("[12 foo 13]");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"type\": nul123 } ");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{ \"true\": true, \"false\": false } ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"true\": tru123 } ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"false\": fals123 } ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"hex\": \"\\u0Aa\" } ");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{ \"hex\": \"\\\\0000b\" } ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"hex\": \"\\uD811b\" } ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"hex\": \"\\uD811\\uDB99\" } ");
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON("{ \"hex\": \"\\uD811\\uDC01\" } ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"hex\": \"\\e000G\" } ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"test ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"\nabel\": 123 ");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("[ -2147483651, 4294967395 ] ");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("[ -9223372036854775900, 184467440737095516700] ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("["
"900000000000000000000000000000000000000000000000000000000000000000000000000000"
"000000000000000000000000000000000000000000000000000000000000000000000000000000"
"000000000000000000000000000000000000000000000000000000000000000000000000000000"
"000000000000000000000000000000000000000000000000000000000000000000000000000"
"]");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("[ 1. ] ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("[ 1e1000 ] ");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("[ 1e ] ");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("[ -92233720368547759, 1844674407370955167] ");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("[ -214748365, 429496739 ] ");
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON("[ -0 ]");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"\\u0000\" }");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"\\u06FF\" }");
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON("{ \"\\uFFFF\" }");
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON("{ \"type\": \"Point\", \"coordinates\": [4294967295, 9223372036854775807] }", 1, 0));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON("{ \"type\": \"Point\", \"coordinates\": [18446744073709551615, 9223372036854775807] }", 1, 0));
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_AsGeoJSON(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[2e308,-0]}'));
#####################################################################
# BUG#19341961
#####################################################################
--echo # Checking that Point with wrong coordinate array depth are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [ ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [ [ 1, 2 ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [ [ ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [ [ [ ] ] ] "
"} "
);
--echo # Checking that LineString with wrong coordinate
--echo # array depth are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"LineString\", "
" \"coordinates\": [ ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"LineString\", "
" \"coordinates\": [ [ ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"LineString\", "
" \"coordinates\": [ 101.0, 1.0 ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"LineString\", "
" \"coordinates\": [ [ [ 101.0, 1.0 ] ] ] "
"} "
);
--echo # Checking that Polygon with wrong coordinate
--echo # array depth are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ [ ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ [ [ ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ [ [ [ ] ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ 100.0, 0.0 ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ [ 100.0, 0.0 ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ [ [ [ 100.0, 0.0 ] ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ [ [ [ ] ] ] ] "
"} "
);
--echo # Checking that Polygon rings with less than four points are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": [ [ [ 100.0, 0.0 ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": "
" [ [ "
" [ 100.0, 0.0 ], "
" [ 100.0, 0.0 ] "
" ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": "
" [ [ "
" [ 100.0, 0.0 ], "
" [ 100.0, 0.0 ], "
" [ 100.0, 0.0 ] "
" ] ] "
"} "
);
--echo # Checking that Polygons with unclosed rings are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": "
" [ [ "
" [ 100.0, 0.0 ], "
" [ 101.0, 0.0 ], "
" [ 102.0, 0.0 ], "
" [ 100.1, 0.0 ] "
" ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": "
" [ [ "
" [ 100.0, 0.0 ], "
" [ 101.0, 0.0 ], "
" [ 102.0, 0.0 ], "
" [ 100.0, 0.1 ] "
" ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ 100.0, 0.0 ], "
" [ 101.0, 0.0 ], "
" [ 102.0, 0.0 ], "
" [ 100.0, 0.0 ] "
" ], "
" [ "
" [ 10.0, 2.0 ], "
" [ 11.0, 2.2 ], "
" [ 12.0, 2.3 ], "
" [ 10.0, 2.1 ] "
" ] "
" ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Polygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ 100.0, 0.0 ], "
" [ 101.0, 0.0 ], "
" [ 102.0, 0.0 ], "
" [ 100.0, 0.0 ] "
" ], "
" [ "
" [ 10.0, 2.0 ], "
" [ 11.0, 2.2 ], "
" [ 12.0, 2.3 ], "
" [ 10.1, 2.0 ] "
" ] "
" ] "
"} "
);
--echo # Checking that MultiPoint with wrong coordinate
--echo # array depth are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPoint\", "
" \"coordinates\": [ ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPoint\", "
" \"coordinates\": [ [ ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPoint\", "
" \"coordinates\": [ [ [ ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPoint\", "
" \"coordinates\": [ 100.0, 0.0 ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPoint\", "
" \"coordinates\": [ [ [ 100.0, 0.0 ] ] ] "
"} "
);
--echo # Checking that MultiLineString with wrong coordinate
--echo # array depth are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiLineString\", "
" \"coordinates\": [ ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiLineString\", "
" \"coordinates\": [ [ ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiLineString\", "
" \"coordinates\": [ [ [ ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiLineString\", "
" \"coordinates\": [ [ [ [ ] ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiLineString\", "
" \"coordinates\": [ 100.0, 0.0 ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiLineString\", "
" \"coordinates\": [ [ 100.0, 0.0 ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiLineString\", "
" \"coordinates\": [ [ [ [ 100.0, 0.0 ] ] ] ] "
"} "
);
--echo # Checking that MultiPolygon with wrong coordinate
--echo # array depth are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": [ ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": [ [ ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": [ [ [ ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": [ [ [ [ ] ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": [ 102.0, 2.0 ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": "
" [ "
" [ 102.0, 2.0 ], "
" [ 103.0, 2.0 ], "
" [ 103.0, 3.0 ], "
" [ 102.0, 3.0 ], "
" [ 102.0, 2.0 ], "
" [ 100.0, 0.0 ], "
" [ 101.0, 0.0 ], "
" [ 101.0, 1.0 ], "
" [ 100.0, 1.0 ], "
" [ 100.0, 0.0 ] "
" ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ 102.0, 2.0 ], "
" [ 103.0, 2.0 ], "
" [ 103.0, 3.0 ], "
" [ 102.0, 3.0 ], "
" [ 102.0, 2.0 ] "
" ], "
" [ "
" [ 100.0, 0.0 ], "
" [ 101.0, 0.0 ], "
" [ 101.0, 1.0 ], "
" [ 100.0, 1.0 ], "
" [ 100.0, 0.0 ] "
" ] "
" ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ "
" [ "
" [ 102.0, 2.0 ], "
" [ 103.0, 2.0 ], "
" [ 103.0, 3.0 ], "
" [ 102.0, 3.0 ], "
" [ 102.0, 2.0 ] "
" ] "
" ] "
" ], "
" [ "
" [ 100.0, 0.0 ], "
" [ 101.0, 0.0 ], "
" [ 101.0, 1.0 ], "
" [ 100.0, 1.0 ], "
" [ 100.0, 0.0 ] "
" ] "
" ] "
"} "
);
--echo # Checking that MultiPolygon rings with less than four points
--echo # are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": [ [ [ [ 102.0, 2.0 ] ] ] ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ "
" [ 103.0, 3.0 ], "
" [ 102.0, 2.0 ] "
" ] "
" ] "
" ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ "
" [ 102.0, 2.0 ], "
" [ 103.0, 3.0 ], "
" [ 102.0, 2.0 ] "
" ] "
" ] "
" ] "
"} "
);
--echo # Checking that MultiPolygon with unclosed rings are invalidated.
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ "
" [ 102.0, 2.0 ], "
" [ 103.0, 2.0 ], "
" [ 103.0, 3.0 ], "
" [ 102.1, 2.0 ] "
" ] "
" ] "
" ] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"MultiPolygon\", "
" \"coordinates\": "
" [ "
" [ "
" [ "
" [ 102.0, 2.0 ], "
" [ 103.0, 2.0 ], "
" [ 103.0, 3.0 ], "
" [ 102.0, 2.1 ] "
" ] "
" ] "
" ] "
"} "
);
#####################################################################
# Bug#19342661
#####################################################################
--echo # Checking that GeometryCollection with non-objects in array
--echo # are invalidated.
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"GeometryCollection\", "
" \"geometries\": [ [ ] ] "
"} "
);
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"GeometryCollection\", "
" \"geometries\": [10, 20] "
"} "
);
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"GeometryCollection\", "
" \"geometries\": [\"foo\", \"bar\"] "
"} "
);
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"GeometryCollection\", "
" \"geometries\": [ foo ] "
"} "
);
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"GeometryCollection\", "
" \"geometries\": [ null ] "
"} "
);
--error ER_INVALID_GEOJSON_WRONG_TYPE
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"GeometryCollection\", "
" \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": [1, 2] }, "
" [ ] "
" ] "
"} "
);
--echo # Nested GeoJSON functions
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(10 11)"), 5, 0)));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(-10 -11)"), 5, 1)));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E+308 1.7976931348623157E+308)"), 100, 2), 1, 0));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("POINT(1.7976931348623157E-308 1.7976931348623157E-308)"), 100, 3), 1, 0));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("LINESTRING(30 10, 10 30, 40 40)"), 10, 4)));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("LINESTRING(0 0, 0 10, 10 10, 10 0)"), 10, 5)));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("POLYGON((30 10, 40 40, 20 40, 10 20,30 10))"), 20, 6)));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("POLYGON((35 10, 45 45, 15 40, 10 20, 35 10),"
"(20 30, 35 35, 30 20,20 30))"), 20, 7)));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOINT(10 40, 40 30, 20 20, 30 10)"))));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOINT(0 0, 10 10, 20 20, 30 30, 40 40)"))));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10))"))));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10),"
"(10 10, 20 20, 50 50, 100 100))")), 1, 0));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)))"))));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35),"
"(30 20, 20 15, 20 25, 30 20)))"))));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION()"))));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(POINT(10 20)))"))));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION(GEOMETRYCOLLECTION(GEOMETRYCOLLECTION()))"))));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"GEOMETRYCOLLECTION(POINT(10 20)),"
"GEOMETRYCOLLECTION(LINESTRING(0 0, 100 100)),"
"GEOMETRYCOLLECTION(POLYGON((0 0, 0 10, 10 10, 0 0)))"
")"
)), 1, 0));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"POINT(4 6),"
"LINESTRING(4 6,7 10),"
"POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)),"
"MULTIPOINT (10 40, 40 30, 20 20, 30 10),"
"MULTILINESTRING ((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10)),"
"MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),"
"((15 5, 40 10, 10 20, 5 10, 15 5)))"
")"
))));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(ST_ASGEOJSON(ST_GEOMFROMTEXT("GEOMETRYCOLLECTION( "
"POINT(4 6),"
"GEOMETRYCOLLECTION("
"POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)),"
"MULTIPOINT (10 40, 40 30, 20 20, 30 10),"
"MULTILINESTRING ((10 10, 20 20, 10 40),"
"(40 40, 30 30, 40 20, 30 10)),"
"MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),"
"((15 5, 40 10, 10 20, 5 10, 15 5)))"
"),"
"POINT(6 4)"
")"
))));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [102.0, 0.0]}"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
"{ \"type\": \"LineString\", \"coordinates\": "
"[ [102.0, 0.0], [103.0, 1.0], [104.0, 0.0], [105.0, 1.0] ] }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
"{ \"type\": \"MultiPoint\", \"coordinates\": "
"[ [-105.01, 39.57],[-80.66, 35.0] ] }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"MultiLineString\", \"coordinates\": "
" [ "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.23, 39.2415], "
" [-101.749, 39.7984], "
" [-101.5, 39.011] "
" ], "
" [ "
" [-99.23, 38.6605], "
" [-99.56, 38.727], "
" [-99.25, 38.018] "
" ], "
" [ "
" [-98.499, 38.913], "
" [-98.499, 38.913], "
" [-98.38, 38.15], "
" [-97.5, 38.629] "
" ] "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [41.83, 71.01], "
" [56.95, 33.75], "
" [21.79, 36.56], "
" [41.83, 71.01] "
" ] "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [41.83, 71.01], "
" [56.95, 33.75], "
" [21.79, 36.56], "
" [41.83, 71.01] "
" ], "
" [ "
" [15.40, 15.40], "
" [15.40, 25.40], "
" [25.40, 25.40], "
" [15.40, 15.40] "
" ] "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"MultiPolygon\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"MultiPolygon\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ], "
" [ "
" [1.0, 0.0], "
" [0.0, 0.0], "
" [0.0, 1.0], "
" [1.0, 0.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ ] "
" } "
" ] "
" } "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" } "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [102.0, 0.0], "
" [103.0, 1.0], "
" [104.0, 0.0], "
" [105.0, 1.0] "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.0] "
" } "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"MultiPoint\", \"coordinates\": "
" [ "
" [-105.01, 39.57], "
" [-80.66, 35.0] "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }, "
" { \"type\": \"Point\", \"coordinates\": "
" [102.0, 0.5] "
" } "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"GeometryCollection\", \"geometries\": "
" [ "
" { \"type\": \"Point\", \"coordinates\": "
" [-105.01621, 39.57422] "
" }, "
" { \"type\": \"MultiPoint\", \"coordinates\": "
" [ "
" [-105.01, 39.57], "
" [-80.66, 35.05] "
" ] "
" }, "
" { \"type\": \"LineString\", \"coordinates\": "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.64, 39.2415] "
" ] "
" }, "
" { \"type\": \"MultiLineString\", \"coordinates\": "
" [ "
" [ "
" [-101.5, 39.662], "
" [-101.75, 39.2415], "
" [-101.23, 39.2415], "
" [-101.749, 39.7984], "
" [-101.5, 39.011] "
" ], "
" [ "
" [-99.23, 38.6605], "
" [-99.56, 38.727], "
" [-99.25, 38.018] "
" ], "
" [ "
" [-98.499, 38.913], "
" [-98.499, 38.913], "
" [-98.38, 38.15], "
" [-97.5, 38.629] "
" ] "
" ] "
" }, "
" { \"type\": \"Polygon\", \"coordinates\": "
" [ "
" [ "
" [41.83,71.01], "
" [56.95,33.75], "
" [21.79,36.56], "
" [41.83,71.01] "
" ] "
" ] "
" }, "
" { \"type\": \"MultiPolygon\", \"coordinates\": "
" [ "
" [ "
" [ "
" [102.0, 2.0], "
" [103.0, 2.0], "
" [103.0, 3.0], "
" [102.0, 3.0], "
" [102.0, 2.0] "
" ] "
" ], "
" [ "
" [ "
" [100.0, 0.0], "
" [101.0, 0.0], "
" [101.0, 1.0], "
" [100.0, 1.0], "
" [100.0, 0.0] "
" ] "
" ] "
" ] "
" } "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { "
" \"type\": \"Feature\", \"id\": \"stadium\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-104.99404, 39.75621] "
" }, "
" \"properties\": "
" { "
" \"name\": \"Coors Field\", "
" \"amenity\": \"Baseball Stadium\", "
" \"popupContent\": \"This is where the Rockies play!\" "
" } "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"FeatureCollection\", \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }"));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"FeatureCollection\", \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" null, "
" \"properties\": "
" { "
" \"descr\": \"A NULL/EMPTY POINT\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-87.83827000459532, 42.0] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }"));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [1.23e3, 2.2e-1]}", 1, 0));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [1e-300, 1e300]}", 1, 0));
# Force SRID 0 to avoid coordinate range check.
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [1.7976931348623157E+308, -1.7976931348623157E+308]}", 1, 0));
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
"{ \"type\": \"Point\", \"coordinates\": [10, 20] }"));
--error ER_SRS_NOT_FOUND
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::1234\" } "
" } "
" }"), 5, 0);
--error ER_SRS_NOT_FOUND
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::1\" } "
" } "
" }"), 5, 1);
--error ER_SRS_NOT_FOUND
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:EPSG::4294967295\" } "
" } "
" }"), 5, 2);
--error ER_SRS_NOT_FOUND
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:234\" } "
" } "
" }"), 5, 3);
--error ER_SRS_NOT_FOUND
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:1\" } "
" } "
" }"), 5, 5);
--error ER_SRS_NOT_FOUND
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:4294967295\" } "
" } "
" }"), 5, 7);
SELECT ST_ASGEOJSON(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": null "
" }"));
--echo # Check that GeomFromGeoJSON can read data from a JSON field.
CREATE TABLE json_tbl
(
id INT NOT NULL AUTO_INCREMENT,
json_value JSON,
PRIMARY KEY (id)
);
INSERT INTO json_tbl (json_value) VALUES
('{ "type": "Point", "coordinates": [100.0, 0.0] }'),
('{
"type": "LineString",
"coordinates":
[ [100.0, 0.0], [101.0, 1.0] ]
}'),
('{
"type": "Polygon",
"coordinates":
[ [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ] ]
}'),
('{
"type": "Polygon",
"coordinates": [
[ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ],
[ [100.2, 0.2], [100.8, 0.2], [100.8, 0.8], [100.2, 0.8], [100.2, 0.2] ]
]
}'),
('{
"type": "MultiPoint",
"coordinates": [ [100.0, 0.0], [101.0, 1.0] ]
}'),
('{ "type": "Point", "coordinates": [-12.144, 13.736489] }');
SELECT ST_AsText(ST_GeomFromGeoJSON(json_value)) FROM json_tbl;
DROP TABLE json_tbl;
#####################################################################
# Bug#19342661
#####################################################################
--echo # Checking that member and type names with extra ending does not get
--echo # accepted/validated.
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type1234567890\": \"Point\", "
" \"coordinates\": [102, 11] "
"} "
);
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type \": \"Point\", "
" \"coordinates\": [102, 11] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point \", "
" \"coordinates\": [102, 11] "
"} "
);
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Pointt\", "
" \"coordinates\": [102, 11] "
"} "
);
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates \": [102, 11] "
"} "
);
--error ER_INVALID_GEOJSON_MISSING_MEMBER
SELECT ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinatess\": [102, 11] "
"} "
);
#####################################################################
# Bug#19499584
#####################################################################
--echo # Check that rapidjson parses decimal numbers with trailing zeroes
--echo # correctly.
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [102.0000, 11] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [102.00000, 11] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [102.000000, 11] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [10.0000, 11] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [10.00000, 11] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [10.000000, 11] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [10.00000000000000000000000000000000000, 11] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [102, 11.0000] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [102, 11.00000] "
"} "
));
SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON(
"{ "
" \"type\": \"Point\", "
" \"coordinates\": [102, 11.000000] "
"} "
));
#####################################################################
# Bug#19657747
#####################################################################
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), -9223372036854775808);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), 10, -9223372036854775808);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), 18446744073709551615);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), 10, 18446744073709551615);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', 1, -9223372036854775808);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', -9223372036854775808);
--error ER_DATA_OUT_OF_RANGE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', 1, 18446744073709551615);
--error ER_WRONG_VALUE_FOR_TYPE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', 18446744073709551615);
# These will be of incorrect type, since they are recogniced as decimals.
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), -9223372036854775809);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), 10, -9223372036854775809);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), 18446744073709551616);
--error ER_INCORRECT_TYPE
SELECT ST_ASGEOJSON(POINT(1, 1), 10, 18446744073709551616);
--error ER_INCORRECT_TYPE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', 1, -9223372036854775809);
--error ER_INCORRECT_TYPE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', -9223372036854775809);
--error ER_INCORRECT_TYPE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', 1, 18446744073709551616);
--error ER_INCORRECT_TYPE
SELECT ST_GEOMFROMGEOJSON('{"type":"Point", "coordinates":[10, 20]}', 18446744073709551616);
#########################
# Bug#20416705 #
#########################
CREATE TABLE geojson(
gid INT NOT NULL AUTO_INCREMENT,
geojson_value TEXT,
PRIMARY KEY (`gid`)
);
INSERT INTO geojson (geojson_value) VALUES
(ST_AsGeoJSON(ST_GeomFromText('POINT(10 11)', 4326), 100, 2)),
(ST_AsGeoJSON(ST_GeomFromText('POINT(-1.324 66.231)', 0), 100, 2)),
(ST_AsGeoJSON(ST_GeomFromText('POINT(98.344 -23.001)', 0), 100, 2)),
(ST_AsGeoJSON(ST_GeomFromText('POINT(0.0 0.0)', 0), 100, 2));
SELECT ST_AsText(ST_GeomFromGeoJSON(geojson_value)) FROM geojson;
SELECT ST_SRID(ST_GeomFromGeoJSON(geojson_value)) FROM geojson;
DROP TABLE geojson;
--echo #
--echo # Bug#21384048 ASSERTION FAILED: N >= 0 && N <= 308
--echo # IN RAPIDJSON::INTERNAL::FASTPATH
--echo #
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON('1E-36181012216111515851075235238');
--error ER_INVALID_GEOJSON_UNSPECIFIED
SELECT ST_GEOMFROMGEOJSON('-1E-36181012216111515851075235238');
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON('1E+36181012216111515851075235238');
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GEOMFROMGEOJSON('-1E+36181012216111515851075235238');
--echo #
--echo # Bug#21389101 ST_GEOMFROMGEOJSON: STACK OVERFLOW IN
--echo # RAPIDJSON::GENERICREADER
--echo #
--error ER_JSON_DOCUMENT_TOO_DEEP
SELECT ST_GeomFromGeoJSON(REPEAT('[', 1000));
--error ER_JSON_DOCUMENT_TOO_DEEP
SELECT ST_GeomFromGeoJSON(REPEAT('{"foo":', 1000));
--echo #
--echo # Bug#21616810 CONNECTION HANG OR ASSERTION 0 IN
--echo # THD::SEND_STATEMENT_STATUS, SQL_CLASS.CC
--echo #
CREATE TABLE t (a INT) ENGINE=InnoDB;
INSERT INTO t (a) VALUES (0), (1);
SELECT 1 FROM t GROUP BY ST_AsGeoJSON(NULL, "1", NULL);
SELECT 1 FROM t GROUP BY ST_AsGeoJSON(NULL, 5, 0);
DROP TABLE t;
--echo #
--echo # Bug#22804853 ST_GEOMFROMGEOJSON: ASSERTION FAILED: 0, FILE FILESORT.CC
--echo #
CREATE TABLE t (a INT) ENGINE=InnoDB;
INSERT INTO t VALUES (1), (2);
--error ER_GIS_INVALID_DATA
SELECT DISTINCT SQL_BIG_RESULT ST_GeomFromGeoJSON('1', 1, ST_SRID(a)) FROM t;
DROP TABLE t;
--echo #
--echo # Bug#22912800 ST_ASGEOJSON() DOESN'T WORK WITH SESSION VARIABLES
--echo #
SET @geometry := ST_GeomFromText("POINT(4.9 52.366667)");
SELECT ST_AsGeoJSON(@geometry);
--echo #
--echo # Bug#22930020 INVALID DATA FOR ST_GEOMFROMGEOJSON() IS HARD TO DIAGNOSE
--echo #
CREATE TABLE t1 (col1 JSON);
INSERT INTO t1 (col1) VALUES
('{ "type": "Feature", "geometry": null, "properties": {} }');
ALTER TABLE t1 ADD COLUMN `col2` geometry
GENERATED ALWAYS AS (ST_GeomFromGeoJSON(col1->'$.geometry')) VIRTUAL;
SELECT * FROM t1;
SELECT ST_GeomFromGeoJSON(CAST('null' AS JSON));
SELECT ST_GeomFromGeoJSON('null');
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT ST_GeomFromGeoJSON('Null');
DROP TABLE t1;
--echo #
--echo # WL#8579 Spatial Reference Systems
--echo #
--echo # SRID 0 (should pass)
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT('POINT(0 0)', 0));
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:crs84\" } "
" } "
" }", 1, 0));
--echo # Projected SRS (should pass)
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT('POINT(0 0)', 2000));
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:crs84\" } "
" } "
" }", 1, 2000));
--echo # Geographic SRS (should pass)
SELECT ST_ASGEOJSON(ST_GEOMFROMTEXT('POINT(0 0)', 4326));
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"Point\", \"coordinates\": [10, 20], "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"urn:ogc:def:crs:OGC:1.3:crs84\" } "
" } "
" }", 1, 4326));
--echo #
--echo # Bug #26941370 ST_GEOMFROMGEOJSON ALLOWS OUT-OF-RANGE COORDINATES
--echo #
--error ER_LONGITUDE_OUT_OF_RANGE
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[-180.0, 0.0]}')
);
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[-179.99999999999, 0.0]}')
);
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[180.0, 0.0]}')
);
--error ER_LONGITUDE_OUT_OF_RANGE
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[180.000000000001, 0.0]}')
);
--error ER_LATITUDE_OUT_OF_RANGE
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[0.0, -90.000000000001]}')
);
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[0.0, -90.0]}')
);
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[0.0, 90.0]}')
);
--error ER_LATITUDE_OUT_OF_RANGE
SELECT ST_AsText(
ST_GeomFromGeoJSON('{"type": "Point", "coordinates":[0.0, 90.0000000000001]}')
);
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"FeatureCollection\", "
" \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" null, "
" \"properties\": "
" { "
" \"descr\": \"A NULL/EMPTY POINT\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:4326\" } "
" }, "
" \"coordinates\": "
" [-87.83827000459532, 42.0] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }")) AS srid;
--error ER_INVALID_GEOJSON_CRS_NOT_TOP_LEVEL
SELECT ST_SRID(ST_GEOMFROMGEOJSON(
" { \"type\": \"FeatureCollection\", "
" \"features\": "
" [ "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83775386582222, 35.24980190252168] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS CENTER\", "
" \"address\": \"1326 WOODWARD AV\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", \"coordinates\": "
" [-80.83827000459532, 35.25674709224663] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" null, "
" \"properties\": "
" { "
" \"descr\": \"A NULL/EMPTY POINT\" "
" } "
" }, "
" { \"type\": \"Feature\", \"geometry\": "
" { \"type\": \"Point\", "
" \"crs\": "
" { \"type\": \"name\", \"properties\": "
" { \"name\": \"EPSG:2000\" } "
" }, "
" \"coordinates\": "
" [-87.83827000459532, 42.0] "
" }, "
" \"properties\": "
" { "
" \"name\": \"DOUBLE OAKS NEIGHBORHOOD PARK\", "
" \"address\": \"2605 DOUBLE OAKS RD\" "
" } "
" } "
" ] "
" }")) AS srid;
Reference in New Issue View Git Blame Copy Permalink