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
|
-- source include/have_innodb.inc
-- source include/have_geometry.inc
#
# Spatial objects
#
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
select 1, ST_Intersects(ST_GeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))'), ST_GeomFromText('POLYGON((10 10,30 10,30 30,10 30,10 10))'));
select 0, ST_Intersects(ST_GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), ST_GeomFromText('POLYGON((10 40, 40 50, 20 70, 10 40))'));
select 1, ST_Intersects(ST_GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), ST_GeomFromText('POINT(10 10)'));
select 1, ST_Intersects(ST_GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), ST_GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
select 0, ST_Within(ST_GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), ST_GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
select 1, ST_Within(ST_GeomFromText('POLYGON((1 1,20 10,10 30, 1 1))'), ST_GeomFromText('POLYGON((0 0,30 5,10 40, 0 0))'));
create table t1 (g point)engine=innodb;
insert into t1 values
(ST_GeomFromText('POINT(2 2)')), (ST_GeomFromText('POINT(2 4)')), (ST_GeomFromText('POINT(2 6)')), (ST_GeomFromText('POINT(2 8)')),
(ST_GeomFromText('POINT(4 2)')), (ST_GeomFromText('POINT(4 4)')), (ST_GeomFromText('POINT(4 6)')), (ST_GeomFromText('POINT(4 8)')),
(ST_GeomFromText('POINT(6 2)')), (ST_GeomFromText('POINT(6 4)')), (ST_GeomFromText('POINT(6 6)')), (ST_GeomFromText('POINT(6 8)')),
(ST_GeomFromText('POINT(8 2)')), (ST_GeomFromText('POINT(8 4)')), (ST_GeomFromText('POINT(8 6)')), (ST_GeomFromText('POINT(8 8)'));
select ST_astext(g) from t1 where ST_Within(g, ST_GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'));
select 'Contains';
select ST_astext(g) from t1 where ST_Contains(ST_GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'), g);
select 'Intersects';
select ST_astext(g) from t1 where ST_Intersects(ST_GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'), g);
select 'Contains';
select ST_astext(g) from t1 where ST_Contains(ST_GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'), g);
select 'Contains2';
select ST_astext(g) from t1 where ST_Contains(ST_GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1), (5.01 3.01, 6 5, 9 5, 8 3, 5.01 3.01))'), g);
DROP TABLE t1;
select 0, ST_Within(ST_GeomFromText('LINESTRING(15 15, 50 50, 60 60)'), ST_GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
select 1, ST_Within(ST_GeomFromText('LINESTRING(15 15, 16 16)'), ST_GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
select 1, ST_Intersects(ST_GeomFromText('LINESTRING(15 15, 50 50)'), ST_GeomFromText('LINESTRING(50 15, 15 50)'));
select 1, ST_Intersects(ST_GeomFromText('LINESTRING(15 15, 50 50)'), ST_GeomFromText('LINESTRING(16 16, 51 51)'));
select 1, ST_Intersects(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('POLYGON((50 5, 55 10, 0 45, 50 5))'));
select ST_astext(ST_Union(ST_geometryfromtext('point(1 1)'), ST_geometryfromtext('polygon((0 0, 2 0, 1 2, 0 0))')));
select ST_astext(ST_Intersection(ST_geometryfromtext('point(1 1)'), ST_geometryfromtext('polygon((0 0, 2 0, 1 2, 0 0))')));
select ST_Intersects(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('POLYGON((50 5, 55 10, 0 45, 50 5))'));
select ST_contains(ST_GeomFromText('MULTIPOLYGON(((0 0, 0 5, 5 5, 5 0, 0 0)), ((6 6, 6 11, 11 11, 11 6, 6 6)))'), ST_GeomFromText('POINT(5 10)'));
select ST_Disjoint(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), ST_GeomFromText('POLYGON((10 10, 10 15, 15 15, 15 10, 10 10))'));
select ST_Disjoint(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), ST_GeomFromText('POLYGON((10 10, 10 4, 4 4, 4 10, 10 10))'));
select ST_Overlaps(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), ST_GeomFromText('POLYGON((10 10, 10 4, 4 4, 4 10, 10 10))'));
select ST_Overlaps(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), ST_GeomFromText('POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))'));
# Distance tests
select ST_DISTANCE(ST_geomfromtext('polygon((0 0, 1 2, 2 1, 0 0))'), ST_geomfromtext('polygon((2 2, 3 4, 4 3, 2 2))'));
select ST_DISTANCE(ST_geomfromtext('polygon((0 0, 1 2, 2 1, 0 0))'), ST_geomfromtext('linestring(0 1, 1 0)'));
select ST_DISTANCE(ST_geomfromtext('polygon((0 0, 3 6, 6 3, 0 0))'), ST_geomfromtext('polygon((2 2, 3 4, 4 3, 2 2))'));
select ST_DISTANCE(ST_geomfromtext('polygon((0 0, 3 6, 6 3, 0 0),(2 2, 3 4, 4 3, 2 2))'), ST_geomfromtext('point(3 3)'));
select ST_DISTANCE(ST_geomfromtext('linestring(0 0, 3 6, 6 3, 0 0)'), ST_geomfromtext('polygon((2 2, 3 4, 4 3, 2 2))'));
# Operations tests
--replace_result 23.85542168674699 23.855421686746986 7.999999999999999 8
select ST_astext(ST_Intersection(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('POLYGON((50 5, 55 10, 0 45, 50 5))')));
--replace_result 23.85542168674699 23.855421686746986
select ST_astext(ST_Intersection(ST_GeomFromText('LINESTRING(0 0, 50 45, 40 50, 0 0)'), ST_GeomFromText('LINESTRING(50 5, 55 10, 0 45, 50 5)')));
select ST_astext(ST_Intersection(ST_GeomFromText('LINESTRING(0 0, 50 45, 40 50)'), ST_GeomFromText('LINESTRING(50 5, 55 10, 0 45)')));
select ST_astext(ST_Intersection(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('POINT(20 20)')));
select ST_astext(ST_Intersection(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('LINESTRING(-10 -10, 200 200)')));
--replace_result 7.999999999999999 8
select ST_astext(ST_Intersection(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('LINESTRING(-10 -10, 200 200, 199 201, -11 -9)')));
--replace_result 7.999999999999999 8
select ST_astext(ST_UNION(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('LINESTRING(-10 -10, 200 200, 199 201, -11 -9)')));
select ST_astext(ST_intersection(ST_geomfromtext('polygon((0 0, 1 0, 0 1, 0 0))'), ST_geomfromtext('polygon((0 0, 1 1, 0 2, 0 0))')));
select ST_astext(ST_symdifference(ST_geomfromtext('polygon((0 0, 1 0, 0 1, 0 0))'), ST_geomfromtext('polygon((0 0, 1 1, 0 2, 0 0))')));
--replace_result 7.999999999999999 8
select ST_astext(ST_UNION(ST_GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), ST_GeomFromText('LINESTRING(-10 -10, 200 200, 199 201, -11 -9)')));
# Buffer() tests
--replace_regex /([0-9]+\.[0-9]{4})[0-9]*/\1/
select ST_astext(ST_buffer(ST_geometryfromtext('point(1 1)'), 1));
create table t1(geom geometrycollection)engine=innodb;
# --error ER_CANT_CREATE_GEOMETRY_OBJECT
# insert into t1 values (ST_geomfromtext('POLYGON((0 0, 10 10, 0 8, 0 0))'));
# --error ER_CANT_CREATE_GEOMETRY_OBJECT
# insert into t1 values (ST_geomfromtext('POLYGON((1 1, 10 10, 0 8, 1 1))'));
--replace_regex /([0-9]+\.[0-9]{4})[0-9]*/\1/
select ST_astext(geom), ST_area(geom),ST_area(ST_buffer(geom,2)) from t1;
select ST_NUMPOINTS(ST_EXTERIORRING(ST_buffer(geom,2))) from t1;
set @geom=ST_geomfromtext('LINESTRING(2 1, 4 2, 2 3, 2 5)');
set @buff=ST_buffer(@geom,1);
select ST_NUMPOINTS(ST_EXTERIORRING(@buff)) from t1;
# cleanup
DROP TABLE t1;
#ST_Touches tests
select st_touches(ST_geomfromtext('point(0 0)'), ST_geomfromtext('point(1 1)'));
select st_touches(ST_geomfromtext('point(1 1)'), ST_geomfromtext('point(1 1)'));
select st_touches(ST_geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), ST_geomfromtext('point(1 1)'));
select st_touches(ST_geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), ST_geomfromtext('point(1 0)'));
select st_touches(ST_geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), ST_geomfromtext('point(1 2)'));
select st_touches(ST_geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), ST_geomfromtext('polygon((1 1.2, 1 0, 2 0, 1 1.2))'));
select st_touches(ST_geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), ST_geomfromtext('polygon((1 1, 1 0, 2 0, 1 1))'));
#Equals test
SELECT ST_Equals(ST_PolyFromText('POLYGON((67 13, 67 18, 67 18, 59 18, 59 13, 67 13) )'),ST_PolyFromText('POLYGON((67 13, 67 18, 59 19, 59 13, 59 13, 67 13) )')) as result;
SELECT ST_Equals(ST_PolyFromText('POLYGON((67 13, 67 18, 67 18, 59 18, 59 13, 67 13) )'),ST_PolyFromText('POLYGON((67 13, 67 18, 59 18, 59 13, 59 13, 67 13) )')) as result;
SELECT ST_Equals(ST_PointFromText('POINT (12 13)'),ST_PointFromText('POINT (12 13)')) as result;
--echo #
--echo # BUG#11755628/47429: INTERSECTION FUNCTION CRASHED MYSQLD
--echo # BUG#11759650/51979: UNION/INTERSECTION OF POLYGONS CRASHES MYSQL
--echo #
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((525000 183300,525400
183300,525400 18370, 525000 183700,525000 183300))'),
ST_geomfromtext('POLYGON((525298.67 183511.53,525296.57
183510.39,525296.42 183510.31,525289.11 183506.62,525283.17
183503.47,525280.98 183502.26,525278.63 183500.97,525278.39
183500.84,525276.79 183500,525260.7 183491.55,525263.95
183484.75,525265.58 183481.95,525278.97 183488.73,525276.5
183493.45,525275.5 183495.7,525280.35 183498.2,525282.3
183499.1,525282.2 183499.3,525283.55 183500,525301.75
183509.35,525304.45 183504.25,525307.85 183504.95,525304.5
183510.83,525302.81 183513.8,525298.67 183511.53),(525275.06
183489.89,525272.06 183488.37,525268.94 183494.51,525271.94
183496.03,525275.06 183489.89),(525263.26 183491.55,525266.15
183493.04,525269.88 183485.82,525266.99 183484.33,525263.26
183491.55))'))) st_u;
SET @a=0x0000000001030000000200000005000000000000000000000000000000000000000000000000002440000000000000000000000000000024400000000000002440000000000000000000000000000024400000000000000000000000000000000000000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040000000000000F03F0000000000000040000000000000F03F000000000000F03F;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASTEXT(ST_TOUCHES(@a, ST_GEOMFROMTEXT('point(0 0)'))) t;
--source include/gis_debug.inc
|
