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