| 1 |
############################################################################### |
| 2 |
# # |
| 3 |
# This test tests the ST_Intersection function. # |
| 4 |
# ST_Intersection returns the intersection geometry between two geometries. # |
| 5 |
# The function is defined by OGC standard documents. # |
| 6 |
# # |
| 7 |
############################################################################### |
| 8 |
|
| 9 |
--echo ##################################################################################### |
| 10 |
--echo # Creating a spatial Geometry object |
| 11 |
--echo ##################################################################################### |
| 12 |
USE test; |
| 13 |
CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY); |
| 14 |
# Geometric elements constructing a star |
| 15 |
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)))'; |
| 16 |
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))'; |
| 17 |
--echo # INSERT star |
| 18 |
--disable_warnings |
| 19 |
INSERT INTO gis_geometrycollection VALUES (103,ST_GEOMFROMTEXT(@star_of_elems)); |
| 20 |
--enable_warnings |
| 21 |
CREATE TABLE gis_geometrycollection_2 SELECT fid as fid2,g as g2 FROM gis_geometrycollection; |
| 22 |
--echo # Checking the integrity of the above create/insert statements |
| 23 |
--echo # 1 row. |
| 24 |
SELECT count(g IS NULL) FROM gis_geometrycollection; |
| 25 |
--echo # 1 row. |
| 26 |
SELECT count(g2 IS NULL) FROM gis_geometrycollection_2; |
| 27 |
|
| 28 |
--echo ##################################################################################### |
| 29 |
--echo # Testing ST_Intersection() with different ways to pass input geometries |
| 30 |
--echo ##################################################################################### |
| 31 |
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT(@star_collection_elems))); |
| 32 |
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'))); |
| 33 |
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'))); |
| 34 |
SELECT ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT(@star_of_elems))) FROM gis_geometrycollection WHERE fid=103; |
| 35 |
SELECT ST_ASTEXT(ST_INTERSECTION(g,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103; |
| 36 |
SELECT ST_ASTEXT(ST_INTERSECTION(g,g2)) FROM gis_geometrycollection,gis_geometrycollection_2 WHERE fid=103 and fid2=103; |
| 37 |
|
| 38 |
--echo ##################################################################################### |
| 39 |
--echo # Verify ST_Intersection() returns NULL if at least one of its arguments are NULL. |
| 40 |
--echo ##################################################################################### |
| 41 |
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT(@star_of_elems),NULL)); |
| 42 |
SELECT ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT(@star_of_elems))); |
| 43 |
SELECT ST_ASTEXT(ST_INTERSECTION(NULL,ST_GEOMFROMTEXT('POINT(0 0)'))); |
| 44 |
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),NULL)); |
| 45 |
SELECT ST_ASTEXT(ST_INTERSECTION(g,NULL)) FROM gis_geometrycollection WHERE fid=103; |
| 46 |
SELECT ST_ASTEXT(ST_INTERSECTION(NULL,NULL)); |
| 47 |
SELECT ST_ASTEXT(ST_INTERSECTION(fid,NULL)) FROM gis_geometrycollection WHERE fid=103; |
| 48 |
|
| 49 |
--echo ##################################################################################### |
| 50 |
--echo # WL#8579 Spatial Reference Systems |
| 51 |
--echo ##################################################################################### |
| 52 |
--echo # SRID 0 (should pass) |
| 53 |
SELECT ST_ASTEXT(ST_INTERSECTION( |
| 54 |
ST_GEOMFROMTEXT('POINT(0 0)', 0), |
| 55 |
ST_GEOMFROMTEXT('POINT(0 0)', 0) |
| 56 |
)); |
| 57 |
--echo # Projected SRS (should pass) |
| 58 |
SELECT ST_ASTEXT(ST_INTERSECTION( |
| 59 |
ST_GEOMFROMTEXT('POINT(0 0)', 2000), |
| 60 |
ST_GEOMFROMTEXT('POINT(0 0)', 2000) |
| 61 |
)); |
| 62 |
echo # Geographic SRS (should pass) |
| 63 |
SELECT ST_ASTEXT(ST_INTERSECTION( |
| 64 |
ST_GEOMFROMTEXT('POINT(0 0)', 4326), |
| 65 |
ST_GEOMFROMTEXT('POINT(0 0)', 4326) |
| 66 |
)); |
| 67 |
|
| 68 |
--echo ##################################################################################### |
| 69 |
--echo # Invalid function calls |
| 70 |
--echo ##################################################################################### |
| 71 |
--echo # Undefined SRS' in the input geometries should return ER_SRS_NOT_FOUND. |
| 72 |
# Assume SRID 1000000000 is undefined. |
| 73 |
--error ER_SRS_NOT_FOUND |
| 74 |
DO ST_ASTEXT(ST_INTERSECTION(x'40420f00010100000000000000000000000000000000000000',x'40420f00010100000000000000000000000000000000000000')); |
| 75 |
|
| 76 |
--echo # Different SRIDs in the input geometries should return ER_GIS_DIFFERENT_SRIDS. |
| 77 |
--error ER_GIS_DIFFERENT_SRIDS |
| 78 |
DO ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),ST_GEOMFROMTEXT('POINT(0 1)',4326))); |
| 79 |
--error ER_GIS_DIFFERENT_SRIDS |
| 80 |
DO ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)',4145),ST_GEOMFROMTEXT('POINT(0 1)',4326))); |
| 81 |
|
| 82 |
--echo # Not syntactically well-formed geometry input should result in ER_GIS_INVALID_DATA. |
| 83 |
--error ER_GIS_INVALID_DATA |
| 84 |
SELECT ST_ASTEXT(ST_INTERSECTION(fid,ST_GEOMFROMTEXT('POINT(0 0)'))) FROM gis_geometrycollection WHERE fid=103; |
| 85 |
--error ER_GIS_INVALID_DATA |
| 86 |
SELECT ST_ASTEXT(ST_INTERSECTION(ST_GEOMFROMTEXT('POINT(0 0)'),fid)) FROM gis_geometrycollection WHERE fid=103; |
| 87 |
|
| 88 |
--echo # Return ER_GEOMETRY_PARAM_(LONGITUDE/LATITUDE)_OUT_OF_RANGE if longitude and latitude values are not |
| 89 |
--echo # within their respective ranges (-180,180] and [-90,90]). |
| 90 |
SET @pt = ST_GeomFromText('POINT(0 0)', 4326); |
| 91 |
|
| 92 |
# SRID=4326, long=0, lat=-91. |
| 93 |
SET @pt_lat_minus_91 = x'E6100000010100000000000000000000000000000000C056C0'; |
| 94 |
|
| 95 |
# SRID=4326, long=0, lat=91. |
| 96 |
SET @pt_lat_plus_91 = x'E6100000010100000000000000000000000000000000C05640'; |
| 97 |
|
| 98 |
# SRID=4326, long=-181, lat=0. |
| 99 |
SET @pt_long_minus_181 = x'E610000001010000000000000000A066C00000000000000000'; |
| 100 |
|
| 101 |
# SRID=4326, long=181, lat=0. |
| 102 |
SET @pt_long_plus_181 = x'E610000001010000000000000000A066400000000000000000'; |
| 103 |
|
| 104 |
--error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE |
| 105 |
DO ST_INTERSECTION(@pt_lat_minus_91, @pt); |
| 106 |
--error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE |
| 107 |
DO ST_INTERSECTION(@pt_lat_plus_91, @pt); |
| 108 |
--error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE |
| 109 |
DO ST_INTERSECTION(@pt_long_minus_181, @pt); |
| 110 |
--error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE |
| 111 |
DO ST_INTERSECTION(@pt_long_plus_181, @pt); |
| 112 |
|
| 113 |
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))'))); |
| 114 |
|
| 115 |
--echo #clean up |
| 116 |
DROP TABLE gis_geometrycollection; |
| 117 |
DROP TABLE gis_geometrycollection_2; |