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
|
#####################################################################################
# Creating a spatial Geometry object
#####################################################################################
USE test;
CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);
SET @star_of_elems='MULTIPOLYGON(((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),((19 15,15 25,11 15,19 15)))';
SET @star_collection_elems='GEOMETRYCOLLECTION(MULTIPOLYGON(((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),((19 15,15 25,11 15,19 15))),POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0),(10 6,10 10,7 12,13 13,15 18,17 13,21 13,18 10,20 6,15 8,10 6)),MULTILINESTRING((25 0,0 15,15 30,0 5)),LINESTRING(15 25,15 10),MULTIPOINT(5 0,25 0),POINT(15 25))';
# INSERT star
INSERT INTO gis_geometrycollection VALUES (103,ST_GEOMFROMTEXT(@star_of_elems));
CREATE TABLE gis_geometrycollection_2 SELECT fid as fid2,g as g2 FROM gis_geometrycollection;
# Checking the integrity of the above create/insert statements
# 1 row.
SELECT count(g IS NULL) FROM gis_geometrycollection;
count(g IS NULL)
1
# 1 row.
SELECT count(g2 is NULL) FROM gis_geometrycollection_2;
count(g2 is NULL)
1
#####################################################################################
# Testing ST_Union() with different ways to pass input geometries
#####################################################################################
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)));
ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)))
GEOMETRYCOLLECTION(POLYGON((15 25,10 15,0 15,7 10,5 0,15 5,25 0,22 10,30 15,20 15,15 25),(16.2 15,17 13,17.133333333333333 13,15.074704491725768 10.794326241134751,10.957442978734894 6.382977191493957,10 6,10 10,7 12,13 13,13.8 15,16.2 15)),LINESTRING(12.179486097659181 7.692308341404491,10 9),LINESTRING(0 15,15 30,6 15),LINESTRING(4.2 12,0 5),LINESTRING(15 15,15 10.714285760942136))
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),((19 15,15 25,11 15,19 15)))
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
GEOMETRYCOLLECTION(POLYGON((1 1,2 1,2 2,1 2,1 1)),POINT(0 0))
SELECT ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT(@star_of_elems)))
MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((19 15,15 25,11 15,19 15)))
SELECT ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_UNION(g,ST_GEOMFROMTEXT('POINT(0 0)')))
GEOMETRYCOLLECTION(POLYGON((5 0,15 5,25 0,22 10,30 15,19 15,5 0)),POLYGON((19 15,15 25,11 15,19 15)),POINT(0 0))
SELECT ST_ASTEXT(ST_UNION(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
ST_ASTEXT(ST_UNION(g,g2))
MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((19 15,15 25,11 15,19 15)))
#####################################################################################
# Verify ST_Union() returns NULL if at least one of its arguments are NULL.
#####################################################################################
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),NULL));
ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT(@star_of_elems),NULL))
NULL
SELECT ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT(@star_of_elems)));
ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT(@star_of_elems)))
NULL
SELECT ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')));
ST_ASTEXT(ST_UNION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')))
NULL
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL));
ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL))
NULL
SELECT ST_ASTEXT(ST_UNION(g,NULL)) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_UNION(g,NULL))
NULL
SELECT ST_ASTEXT(ST_UNION(NULL,NULL));
ST_ASTEXT(ST_UNION(NULL,NULL))
NULL
SELECT ST_ASTEXT(ST_UNION(fid,NULL)) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_UNION(fid,NULL))
NULL
#####################################################################################
# WL#8579 Spatial Reference Systems
#####################################################################################
# SRID 0 (should pass)
SELECT ST_ASTEXT(ST_UNION(
ST_GEOMFROMTEXT('POINT(0 0)', 0),
ST_GEOMFROMTEXT('POINT(0 0)', 0)
));
ST_ASTEXT(ST_UNION(
ST_GEOMFROMTEXT('POINT(0 0)', 0),
ST_GEOMFROMTEXT('POINT(0 0)', 0)
))
POINT(0 0)
# Projected SRS (should pass)
SELECT ST_ASTEXT(ST_UNION(
ST_GEOMFROMTEXT('POINT(0 0)', 2000),
ST_GEOMFROMTEXT('POINT(0 0)', 2000)
));
ST_ASTEXT(ST_UNION(
ST_GEOMFROMTEXT('POINT(0 0)', 2000),
ST_GEOMFROMTEXT('POINT(0 0)', 2000)
))
POINT(0 0)
# Geographic SRS (should pass)
SELECT ST_ASTEXT(ST_UNION(
ST_GEOMFROMTEXT('POINT(0 0)', 4326),
ST_GEOMFROMTEXT('POINT(0 0)', 4326)
));
ST_ASTEXT(ST_UNION(
ST_GEOMFROMTEXT('POINT(0 0)', 4326),
ST_GEOMFROMTEXT('POINT(0 0)', 4326)
))
POINT(0 0)
#####################################################################################
# Invalid function calls
#####################################################################################
# Undefined SRS' in the input geometries should return ER_SRS_NOT_FOUND.
DO ST_ASTEXT(ST_UNION(x'40420f00010100000000000000000000000000000000000000',x'40420f00010100000000000000000000000000000000000000'));
ERROR SR001: There's no spatial reference system with SRID 1000000.
# Different SRIDs in the input geometries should return ER_GIS_DIFFERENT_SRIDS.
DO ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
ERROR HY000: Binary geometry function st_union given two geometries of different srids: 0 and 4326, which should have been identical.
DO ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)',4145),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
ERROR HY000: Binary geometry function st_union given two geometries of different srids: 4145 and 4326, which should have been identical.
# Not syntactically well-formed geometry input should result in ER_GIS_INVALID_DATA.
SELECT ST_ASTEXT(ST_UNION(fid,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
ERROR 22023: Invalid GIS data provided to function st_union.
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POINT(0 0)'),fid)) FROM gis_geometrycollection WHERE fid=103;
ERROR 22023: Invalid GIS data provided to function st_union.
# Return ER_GEOMETRY_PARAM_(LONGITUDE/LATITUDE)_OUT_OF_RANGE if longitude and latitude values are not
# within their respective ranges (-180,180] and [-90,90]).
SET @pt = ST_GeomFromText('POINT(0 0)', 4326);
SET @pt_lat_minus_91 = x'E6100000010100000000000000000000000000000000C056C0';
SET @pt_lat_plus_91 = x'E6100000010100000000000000000000000000000000C05640';
SET @pt_long_minus_181 = x'E610000001010000000000000000A066C00000000000000000';
SET @pt_long_plus_181 = x'E610000001010000000000000000A066400000000000000000';
DO ST_UNION(@pt_lat_minus_91, @pt);
ERROR 22S03: A parameter of function st_union contains a geometry with latitude -91.000000, which is out of range. It must be within [-90.000000, 90.000000].
DO ST_UNION(@pt_lat_plus_91, @pt);
ERROR 22S03: A parameter of function st_union contains a geometry with latitude 91.000000, which is out of range. It must be within [-90.000000, 90.000000].
DO ST_UNION(@pt_long_minus_181, @pt);
ERROR 22S02: A parameter of function st_union contains a geometry with longitude -181.000000, which is out of range. It must be within (-180.000000, 180.000000].
DO ST_UNION(@pt_long_plus_181, @pt);
ERROR 22S02: A parameter of function st_union contains a geometry with longitude 181.000000, which is out of range. It must be within (-180.000000, 180.000000].
# Cartesian SRS
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1 1,1 2,0 2,0 1))')));
ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1 1,1 2,0 2,0 1))')))
POLYGON((2 2,0 2,0 1,1 1,2 1,2 2))
# Geographic SRS
SELECT ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,1 2,2 2,2 1,1 1))', 4326),ST_GEOMFROMTEXT('POLYGON((0 1,0 2,1 2,1 1,0 1))', 4326)));
ST_ASTEXT(ST_UNION(ST_GEOMFROMTEXT('POLYGON((1 1,1 2,2 2,2 1,1 1))', 4326),ST_GEOMFROMTEXT('POLYGON((0 1,0 2,1 2,1 1,0 1))', 4326)))
POLYGON((0 2,2 2,2 1,1 1,0 1,0 2))
#clean up
DROP TABLE gis_geometrycollection;
DROP TABLE gis_geometrycollection_2;
|
