246 lines
9.7 KiB
Plaintext
246 lines
9.7 KiB
Plaintext
###############################################################################
|
|
# #
|
|
# This test is aimed to focus on the properties and attributes of the #
|
|
# Mutator ST_X and ST_Y #
|
|
# point Geometry classes, and the functionality of ST_X() and ST_Y(). #
|
|
# #
|
|
# #
|
|
# Creation Date: 2016-07-25 #
|
|
# Author : Jens Even Berg Blomsøy #
|
|
# #
|
|
###############################################################################
|
|
|
|
|
|
###############################################################################
|
|
# Creating the spatial objects #
|
|
###############################################################################
|
|
|
|
--echo # Creating the spatial objects
|
|
|
|
CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
|
|
|
|
|
|
###############################################################################
|
|
# Inserting POINT Geometry values #
|
|
###############################################################################
|
|
|
|
--echo # Inserting POINT Geometry Values
|
|
|
|
INSERT INTO gis_point VALUES
|
|
(101, ST_POINTFROMTEXT('POINT(0 0)')),
|
|
(102, ST_POINTFROMTEXT('POINT(1 0)')),
|
|
(103, ST_POINTFROMTEXT('POINT(0 1)')),
|
|
(104, ST_POINTFROMTEXT('POINT(1 1)')),
|
|
(105, ST_POINTFROMTEXT('POINT(-1 1)'));
|
|
|
|
INSERT INTO gis_point VALUES
|
|
(106, ST_POINTFROMWKB(ST_ASWKB(POINT(0,0)))),
|
|
(107, ST_POINTFROMWKB(ST_ASWKB(POINT(10,0)))),
|
|
(108, ST_POINTFROMWKB(ST_ASWKB(POINT(0,10)))),
|
|
(109, ST_POINTFROMWKB(ST_ASWKB(POINT(-10,0)))),
|
|
(110, ST_POINTFROMWKB(ST_ASWKB(POINT(0,-10))));
|
|
|
|
INSERT INTO gis_point VALUES
|
|
(111, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1 1)')))),
|
|
(112, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 1e308)')))),
|
|
(113, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 -1e308)')))),
|
|
(114, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 1e308)')))),
|
|
(115, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 -1e308)'))));
|
|
|
|
--echo # Displaying the inserted POINT Geometry Data
|
|
SELECT fid, ST_ASTEXT(g) FROM gis_point;
|
|
|
|
###############################################################################
|
|
# POINT geometry property functions #
|
|
###############################################################################
|
|
|
|
--echo #=======================================================================
|
|
--echo # ST_X(point)
|
|
--echo #=======================================================================
|
|
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(0 0)'),5));
|
|
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(10 10)'), 5));
|
|
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(1e308 1e308)'), 1e205));
|
|
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1.1111,-1.1111))), 5.5555));
|
|
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(10.0000,10.0000))), 50.0000));
|
|
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(-1e308,-1e308))), -1e205));
|
|
|
|
--echo #=======================================================================
|
|
--echo # ST_Y(point)
|
|
--echo #=======================================================================
|
|
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(0 0)'),5));
|
|
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(10 10)'), 5));
|
|
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(1e308 1e308)'), 1e205));
|
|
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1.1111,-1.1111))), 5.5555));
|
|
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(10.0000,10.0000))), 50.0000));
|
|
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(-1e308,-1e308))), -1e205));
|
|
|
|
--echo #=======================================================================
|
|
--echo # ST_X(point)
|
|
--echo #=======================================================================
|
|
|
|
########################################
|
|
# Null value testing #
|
|
########################################
|
|
|
|
SELECT ST_ASTEXT(ST_X(NULL, 5));
|
|
SELECT ST_ASTEXT(ST_X(POINT(4, 5), NULL));
|
|
SELECT ST_ASTEXT(ST_X(NULL, NULL));
|
|
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(NULL), NULL));
|
|
|
|
--error ER_PARSE_ERROR
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), 5)));
|
|
|
|
--error ER_PARSE_ERROR
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), )));
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(1, 2)), a)));
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMWKB(ST_ASWKB(POINT(a,b)), c)));
|
|
|
|
--error ER_GIS_INVALID_DATA
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('NULL'), NULL));
|
|
|
|
--error ER_GIS_INVALID_DATA
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT()'), 5));
|
|
|
|
--error ER_GIS_INVALID_DATA
|
|
SELECT ST_ASTEXT(ST_X(ST_POINTFROMTEXT('POINT(1)'), 5));
|
|
|
|
--echo #=======================================================================
|
|
--echo # ST_Y(point)
|
|
--echo #=======================================================================
|
|
|
|
########################################
|
|
# Null value testing #
|
|
########################################
|
|
|
|
SELECT ST_ASTEXT(ST_Y(NULL, 5));
|
|
SELECT ST_ASTEXT(ST_Y(POINT(4, 5), NULL));
|
|
SELECT ST_ASTEXT(ST_Y(NULL, NULL));
|
|
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(NULL), NULL));
|
|
|
|
--error ER_PARSE_ERROR
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), 5)));
|
|
|
|
--error ER_PARSE_ERROR
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1)), )));
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(1, 2)), a)));
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMWKB(ST_ASWKB(POINT(a,b)), c)));
|
|
|
|
--error ER_GIS_INVALID_DATA
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('NULL'), NULL));
|
|
|
|
--error ER_GIS_INVALID_DATA
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT()'), 5));
|
|
|
|
--error ER_GIS_INVALID_DATA
|
|
SELECT ST_ASTEXT(ST_Y(ST_POINTFROMTEXT('POINT(1)'), 5));
|
|
|
|
|
|
###############################################################################
|
|
# Invalid range testing #
|
|
###############################################################################
|
|
--echo # Try to use ST_X and ST_Y with out of range point values.
|
|
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ST_X(POINT(pow(2, 2048), 4), 5);
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ST_Y(POINT(pow(2, 2048), 4), 5);
|
|
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ST_Y(POINT(4, pow(2, 2048)), 5);
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ST_X(POINT(4, pow(2, 2048)), 5);
|
|
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ST_X(POINT(4, 5), pow(2, 2048));
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ST_Y(POINT(4, 5), pow(2, 2048));
|
|
|
|
###############################################################################
|
|
# Invalid geometry testing #
|
|
###############################################################################
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_X(ST_GeomFromText('LINESTRING(0 0, 1 1)'), 5);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_Y(ST_GeomFromText('LINESTRING(0 0, 1 1)'), 5);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_X(ST_GEOMFROMTEXT('POLYGON((0 0,2 2,0 2,0 0))'), -1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_Y(ST_GEOMFROMTEXT('POLYGON((0 0,2 2,0 2,0 0))'), -1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_X(ST_GEOMFROMTEXT('MULTIPOINT(10 40, 40 30, 20 20, 30 10)'), 1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_Y(ST_GEOMFROMTEXT('MULTIPOINT(10 40, 40 30, 20 20, 30 10)'), 1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_X(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
|
|
"(40 40, 30 30, 40 20, 30 10))"), 1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_Y(ST_GEOMFROMTEXT("MULTILINESTRING((10 10, 20 20, 10 40),"
|
|
"(40 40, 30 30, 40 20, 30 10))"), 1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_X(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
|
|
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)))"), -1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_Y(ST_GEOMFROMTEXT("MULTIPOLYGON(((40 40, 20 45, 45 30, 40 40)),"
|
|
"((20 35, 10 30, 10 10, 30 5, 45 20, 20 35)))"), -1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_X(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)))"
|
|
")"
|
|
), 1024);
|
|
|
|
--error ER_UNEXPECTED_GEOMETRY_TYPE
|
|
SELECT ST_Y(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)))"
|
|
")"
|
|
), 1024);
|
|
|
|
--echo # Final cleanup
|
|
DROP TABLE gis_point;
|