Debian Codegraph @

mysql-test/suite/gis/t/st_lineinterpolate.test

raw
1 # Test the functionality of the LINEINTERPOLATEPOINT and ST_LINEINTERPOLATEPOINTS functions
2
3 Use test;
4
5 --echo # Test all 3 functions within stored procedure
6 DELIMITER ||;
7
8 CREATE PROCEDURE geom_lineinterpolate()
9 BEGIN
10 SET @g1 = ST_GeomFromText('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)', 4326);
11 SELECT ST_ASTEXT(ST_PointAtDistance(@g1,0.0));
12 SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,0.0));
13 SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,0.0));
14 SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,NULL));
15 SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,NULL));
16 SELECT ST_ASTEXT(ST_PointAtDistance(@g1,NULL));
17 END ||
18
19 DELIMITER ;||
20
21 CALL geom_lineinterpolate();
22
23 # clean up
24 DROP PROCEDURE geom_lineinterpolate;
25
26 --echo # Test all 3 functions in UPDATE and WHERE and SELECT Clauses
27
28 CREATE TABLE tab(c1 INT,c2 LINESTRING,c3 POINT,c4 GEOMETRY);
29
30 CREATE VIEW view1 AS SELECT * FROM tab;
31
32 SET @ls = ST_GEOMFROMTEXT('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)');
33
34 INSERT INTO tab(c1,c2) VALUES(1,@g1);
35
36 SET @pt = ST_GEOMFROMTEXT('POINT(10 10)',4326);
37
38 # should be 0 records are displayed
39 SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 0;
40 SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 1;
41 # should be 0 records are displayed
42 SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 0;
43 SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 1;
44 SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 1;
45 # should be 0 records are displayed
46 SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 0;
47 UPDATE tab SET c3 = ST_LINEINTERPOLATEPOINT(@ls,0.0) WHERE c1 = 1;
48 SELECT ST_ASTEXT(c3) FROM tab;
49 UPDATE tab SET c3 = ST_POINTATDISTANCE(@ls,0.1) WHERE c1 = 1;
50 SELECT ST_ASTEXT(c3) FROM tab;
51 UPDATE tab SET c4 = ST_LINEINTERPOLATEPOINTS(@ls,0.0) WHERE c1 = 1;
52 SELECT ST_ASTEXT(c4) FROM tab;
53 # clean up
54 DROP VIEW view1;
55 DROP TABLE tab;
56
57 --echo # The geometry must be a linestring.
58 --echo # Test ST_LINEINTERPOLATEPOINT with Cartesian SRID 0
59 --error ER_UNEXPECTED_GEOMETRY_TYPE
60 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1);
61 --error ER_UNEXPECTED_GEOMETRY_TYPE
62 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1);
63 --error ER_UNEXPECTED_GEOMETRY_TYPE
64 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1);
65 --error ER_UNEXPECTED_GEOMETRY_TYPE
66 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1);
67 --error ER_UNEXPECTED_GEOMETRY_TYPE
68 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1);
69 --error ER_UNEXPECTED_GEOMETRY_TYPE
70 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1);
71
72 --echo # Test ST_LINEINTERPOLATEPOINT with Geographic
73 --error ER_UNEXPECTED_GEOMETRY_TYPE
74 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1);
75 --error ER_UNEXPECTED_GEOMETRY_TYPE
76 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1);
77 --error ER_UNEXPECTED_GEOMETRY_TYPE
78 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1);
79 --error ER_UNEXPECTED_GEOMETRY_TYPE
80 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1);
81 --error ER_UNEXPECTED_GEOMETRY_TYPE
82 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1);
83 --error ER_UNEXPECTED_GEOMETRY_TYPE
84 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1);
85
86 --echo # Test ST_LINEINTERPOLATEPOINTS with Cartesian SRID 0
87 --error ER_UNEXPECTED_GEOMETRY_TYPE
88 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1);
89 --error ER_UNEXPECTED_GEOMETRY_TYPE
90 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1);
91 --error ER_UNEXPECTED_GEOMETRY_TYPE
92 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1);
93 --error ER_UNEXPECTED_GEOMETRY_TYPE
94 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1);
95 --error ER_UNEXPECTED_GEOMETRY_TYPE
96 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1);
97 --error ER_UNEXPECTED_GEOMETRY_TYPE
98 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1);
99
100 --echo # Test ST_LINEINTERPOLATEPOINTS with Geographic
101 --error ER_UNEXPECTED_GEOMETRY_TYPE
102 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1);
103 --error ER_UNEXPECTED_GEOMETRY_TYPE
104 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1);
105 --error ER_UNEXPECTED_GEOMETRY_TYPE
106 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1);
107 --error ER_UNEXPECTED_GEOMETRY_TYPE
108 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1);
109 --error ER_UNEXPECTED_GEOMETRY_TYPE
110 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1);
111 --error ER_UNEXPECTED_GEOMETRY_TYPE
112 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1);
113
114 --echo # Test ST_LINEINTERPOLATEPOINTS with NULL
115 SET @g1 = ST_GeomFromText('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)', 4326);
116 SELECT ST_LINEINTERPOLATEPOINTS(NULL, NULL);
117 SELECT ST_LINEINTERPOLATEPOINTS(@g1, NULL);
118
119 --echo # Test ST_LINEINTERPOLATEPOINT with NULL
120 SET @g1 = ST_GeomFromText('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)', 4326);
121 SELECT ST_LINEINTERPOLATEPOINT(NULL, NULL);
122 SELECT ST_LINEINTERPOLATEPOINT(@g1, NULL);
123
124 --echo # Test ST_LINEINTERPOLATEPOINT function with Latitude values must be within range [-90, 90].
125 --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
126 SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1);
127 --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
128 SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1);
129 --echo # Longitude values must be within range [-180, 180].
130 --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
131 SELECT ST_LINEINTERPOLATEPOINT(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1);
132 --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
133 SELECT ST_LINEINTERPOLATEPOINT(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1);
134
135 --echo # Test ST_LINEINTERPOLATEPOINTS function with Latitude values must be within range [-90, 90].
136 --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
137 SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1);
138 --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
139 SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1);
140 --echo # Longitude values must be within range [-180, 180].
141 --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
142 SELECT ST_LINEINTERPOLATEPOINTS(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1);
143 --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
144 SELECT ST_LINEINTERPOLATEPOINTS(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1);
145