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
146
147
148
149
150
|
Use test;
# Test all 3 functions within stored procedure
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 ||
CALL geom_lineinterpolate();
ST_ASTEXT(ST_PointAtDistance(@g1,0.0))
POINT(10 10)
ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,0.0))
POINT(10 10)
ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,0.0))
MULTIPOINT((10 10))
ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,NULL))
NULL
ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,NULL))
NULL
ST_ASTEXT(ST_PointAtDistance(@g1,NULL))
NULL
DROP PROCEDURE geom_lineinterpolate;
# 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);
SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 0;
c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0))
SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 1;
c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0))
1 POINT(10 10)
SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 0;
c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0))
SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 1;
c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0))
1 MULTIPOINT((10 10))
SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 1;
c1 ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0))
1 POINT(10 10)
SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 0;
c1 ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0))
UPDATE tab SET c3 = ST_LINEINTERPOLATEPOINT(@ls,0.0) WHERE c1 = 1;
SELECT ST_ASTEXT(c3) FROM tab;
ST_ASTEXT(c3)
POINT(10 10)
UPDATE tab SET c3 = ST_POINTATDISTANCE(@ls,0.1) WHERE c1 = 1;
SELECT ST_ASTEXT(c3) FROM tab;
ST_ASTEXT(c3)
POINT(10.070710678118655 10.070710678118655)
UPDATE tab SET c4 = ST_LINEINTERPOLATEPOINTS(@ls,0.0) WHERE c1 = 1;
SELECT ST_ASTEXT(c4) FROM tab;
ST_ASTEXT(c4)
MULTIPOINT((10 10))
DROP VIEW view1;
DROP TABLE tab;
# The geometry must be a linestring.
# Test ST_LINEINTERPOLATEPOINT with Cartesian SRID 0
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoint.
# Test ST_LINEINTERPOLATEPOINT with Geographic
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoint.
# Test ST_LINEINTERPOLATEPOINTS with Cartesian SRID 0
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoint.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoints.
# Test ST_LINEINTERPOLATEPOINTS with Geographic
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoints.
DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1);
ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoints.
# 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);
ST_LINEINTERPOLATEPOINTS(NULL, NULL)
NULL
SELECT ST_LINEINTERPOLATEPOINTS(@g1, NULL);
ST_LINEINTERPOLATEPOINTS(@g1, NULL)
NULL
# 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);
ST_LINEINTERPOLATEPOINT(NULL, NULL)
NULL
SELECT ST_LINEINTERPOLATEPOINT(@g1, NULL);
ST_LINEINTERPOLATEPOINT(@g1, NULL)
NULL
# Test ST_LINEINTERPOLATEPOINT function with Latitude values must be within range [-90, 90].
SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1);
ERROR 22S03: A parameter of function st_lineinterpolatepoint contains a geometry with latitude -90.001000, which is out of range. It must be within [-90.000000, 90.000000].
SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1);
ERROR 22S03: A parameter of function st_lineinterpolatepoint contains a geometry with latitude 90.001000, which is out of range. It must be within [-90.000000, 90.000000].
# Longitude values must be within range [-180, 180].
SELECT ST_LINEINTERPOLATEPOINT(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1);
ERROR 22S02: A parameter of function st_lineinterpolatepoint contains a geometry with longitude -180.001000, which is out of range. It must be within (-180.000000, 180.000000].
SELECT ST_LINEINTERPOLATEPOINT(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1);
ERROR 22S02: A parameter of function st_lineinterpolatepoint contains a geometry with longitude 180.001000, which is out of range. It must be within (-180.000000, 180.000000].
# Test ST_LINEINTERPOLATEPOINTS function with Latitude values must be within range [-90, 90].
SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1);
ERROR 22S03: A parameter of function st_lineinterpolatepoints contains a geometry with latitude -90.001000, which is out of range. It must be within [-90.000000, 90.000000].
SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1);
ERROR 22S03: A parameter of function st_lineinterpolatepoints contains a geometry with latitude 90.001000, which is out of range. It must be within [-90.000000, 90.000000].
# Longitude values must be within range [-180, 180].
SELECT ST_LINEINTERPOLATEPOINTS(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1);
ERROR 22S02: A parameter of function st_lineinterpolatepoints contains a geometry with longitude -180.001000, which is out of range. It must be within (-180.000000, 180.000000].
SELECT ST_LINEINTERPOLATEPOINTS(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1);
ERROR 22S02: A parameter of function st_lineinterpolatepoints contains a geometry with longitude 180.001000, which is out of range. It must be within (-180.000000, 180.000000].
|
