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
|
#####################################################################################
# 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(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_Intersection() with different ways to pass input geometries
#####################################################################################
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)));
ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems)))
GEOMETRYCOLLECTION(POLYGON((15 25,11 15,13.8 15,15 18,16.2 15,19 15,15 25)),POLYGON((30 15,19 15,17.133333333333333 13,21 13,18 10,20 6,15 8,10.957446808510639 6.382978723404255,5 0,15 5,25 0,22 10,30 15)),LINESTRING(13 7.2,12.179487179487179 7.6923076923076925),LINESTRING(15 18,15 15),LINESTRING(15 10.714285714285715,15 10))
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
GEOMETRYCOLLECTION EMPTY
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')));
ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))')))
GEOMETRYCOLLECTION EMPTY
SELECT ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT(@star_of_elems)))
MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((11 15,19 15,15 25,11 15)))
SELECT ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT('POINT(0 0)')))
GEOMETRYCOLLECTION EMPTY
SELECT ST_ASTEXT(ST_INTERSECTION(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103;
ST_ASTEXT(ST_INTERSECTION(g,g2))
MULTIPOLYGON(((19 15,5 0,15 5,25 0,22 10,30 15,19 15)),((11 15,19 15,15 25,11 15)))
#####################################################################################
# Verify ST_Intersection() returns NULL if at least one of its arguments are NULL.
#####################################################################################
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),NULL));
ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),NULL))
NULL
SELECT ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT(@star_of_elems)));
ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT(@star_of_elems)))
NULL
SELECT ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')));
ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT('POINT(0 0)')))
NULL
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL));
ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL))
NULL
SELECT ST_ASTEXT(ST_INTERSECTION(g,NULL)) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_INTERSECTION(g,NULL))
NULL
SELECT ST_ASTEXT(ST_INTERSECTION(NULL,NULL));
ST_ASTEXT(ST_INTERSECTION(NULL,NULL))
NULL
SELECT ST_ASTEXT(ST_INTERSECTION(fid,NULL)) FROM gis_geometrycollection WHERE fid=103;
ST_ASTEXT(ST_INTERSECTION(fid,NULL))
NULL
#####################################################################################
# WL#8579 Spatial Reference Systems
#####################################################################################
# SRID 0 (should pass)
SELECT ST_ASTEXT(ST_INTERSECTION(
ST_GEOMFROMTEXT('POINT(0 0)', 0),
ST_GEOMFROMTEXT('POINT(0 0)', 0)
));
ST_ASTEXT(ST_INTERSECTION(
ST_GEOMFROMTEXT('POINT(0 0)', 0),
ST_GEOMFROMTEXT('POINT(0 0)', 0)
))
POINT(0 0)
# Projected SRS (should pass)
SELECT ST_ASTEXT(ST_INTERSECTION(
ST_GEOMFROMTEXT('POINT(0 0)', 2000),
ST_GEOMFROMTEXT('POINT(0 0)', 2000)
));
ST_ASTEXT(ST_INTERSECTION(
ST_GEOMFROMTEXT('POINT(0 0)', 2000),
ST_GEOMFROMTEXT('POINT(0 0)', 2000)
))
POINT(0 0)
# Geographic SRS (should pass)
SELECT ST_ASTEXT(ST_INTERSECTION(
ST_GEOMFROMTEXT('POINT(0 0)', 4326),
ST_GEOMFROMTEXT('POINT(0 0)', 4326)
))
#####################################################################################
# Invalid function calls
#####################################################################################
# Undefined SRS' in the input geometries should return ER_SRS_NOT_FOUND.
DO ST_ASTEXT(ST_INTERSECTION(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_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
ERROR HY000: Binary geometry function st_intersection given two geometries of different srids: 0 and 4326, which should have been identical.
DO ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)',4145),ST_GEOMFROMTEXT('POINT(0 1)',4326)));
ERROR HY000: Binary geometry function st_intersection 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_INTERSECTION(fid,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103;
ERROR 22023: Invalid GIS data provided to function st_intersection.
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),fid)) FROM gis_geometrycollection WHERE fid=103;
ERROR 22023: Invalid GIS data provided to function st_intersection.
# 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_INTERSECTION(@pt_lat_minus_91, @pt);
ERROR 22S03: A parameter of function st_intersection contains a geometry with latitude -91.000000, which is out of range. It must be within [-90.000000, 90.000000].
DO ST_INTERSECTION(@pt_lat_plus_91, @pt);
ERROR 22S03: A parameter of function st_intersection contains a geometry with latitude 91.000000, which is out of range. It must be within [-90.000000, 90.000000].
DO ST_INTERSECTION(@pt_long_minus_181, @pt);
ERROR 22S02: A parameter of function st_intersection contains a geometry with longitude -181.000000, which is out of range. It must be within (-180.000000, 180.000000].
DO ST_INTERSECTION(@pt_long_plus_181, @pt);
ERROR 22S02: A parameter of function st_intersection contains a geometry with longitude 181.000000, which is out of range. It must be within (-180.000000, 180.000000].
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1.5 1,1 2,0 2,0 1))')));
ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'),ST_GEOMFROMTEXT('POLYGON((0 1,1.5 1,1 2,0 2,0 1))')))
POLYGON((1 2,1 1,1.5 1,1 2))
#clean up
DROP TABLE gis_geometrycollection;
DROP TABLE gis_geometrycollection_2;
|
