1SET default_storage_engine=archive; 2DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; 3CREATE TABLE gis_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT); 4CREATE TABLE gis_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g LINESTRING); 5CREATE TABLE gis_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POLYGON); 6CREATE TABLE gis_multi_point (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOINT); 7CREATE TABLE gis_multi_line (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTILINESTRING); 8CREATE TABLE gis_multi_polygon (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g MULTIPOLYGON); 9CREATE TABLE gis_geometrycollection (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRYCOLLECTION); 10CREATE TABLE gis_geometry (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g GEOMETRY); 11SHOW CREATE TABLE gis_point; 12Table Create Table 13gis_point CREATE TABLE `gis_point` ( 14 `fid` int(11) NOT NULL AUTO_INCREMENT, 15 `g` point DEFAULT NULL, 16 PRIMARY KEY (`fid`) 17) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 18SHOW FIELDS FROM gis_point; 19Field Type Null Key Default Extra 20fid int(11) NO PRI NULL auto_increment 21g point YES NULL 22SHOW FIELDS FROM gis_line; 23Field Type Null Key Default Extra 24fid int(11) NO PRI NULL auto_increment 25g linestring YES NULL 26SHOW FIELDS FROM gis_polygon; 27Field Type Null Key Default Extra 28fid int(11) NO PRI NULL auto_increment 29g polygon YES NULL 30SHOW FIELDS FROM gis_multi_point; 31Field Type Null Key Default Extra 32fid int(11) NO PRI NULL auto_increment 33g multipoint YES NULL 34SHOW FIELDS FROM gis_multi_line; 35Field Type Null Key Default Extra 36fid int(11) NO PRI NULL auto_increment 37g multilinestring YES NULL 38SHOW FIELDS FROM gis_multi_polygon; 39Field Type Null Key Default Extra 40fid int(11) NO PRI NULL auto_increment 41g multipolygon YES NULL 42SHOW FIELDS FROM gis_geometrycollection; 43Field Type Null Key Default Extra 44fid int(11) NO PRI NULL auto_increment 45g geometrycollection YES NULL 46SHOW FIELDS FROM gis_geometry; 47Field Type Null Key Default Extra 48fid int(11) NO PRI NULL auto_increment 49g geometry YES NULL 50INSERT INTO gis_point VALUES 51(101, PointFromText('POINT(10 10)')), 52(102, PointFromText('POINT(20 10)')), 53(103, PointFromText('POINT(20 20)')), 54(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); 55INSERT INTO gis_line VALUES 56(105, LineFromText('LINESTRING(0 0,0 10,10 0)')), 57(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), 58(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10)))); 59INSERT INTO gis_polygon VALUES 60(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), 61(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), 62(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))); 63INSERT INTO gis_multi_point VALUES 64(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), 65(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), 66(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10)))); 67INSERT INTO gis_multi_line VALUES 68(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), 69(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), 70(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))); 71INSERT INTO gis_multi_polygon VALUES 72(117, MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), 73(118, MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), 74(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))); 75INSERT INTO gis_geometrycollection VALUES 76(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), 77(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))); 78INSERT into gis_geometry SELECT * FROM gis_point; 79INSERT into gis_geometry SELECT * FROM gis_line; 80INSERT into gis_geometry SELECT * FROM gis_polygon; 81INSERT into gis_geometry SELECT * FROM gis_multi_point; 82INSERT into gis_geometry SELECT * FROM gis_multi_line; 83INSERT into gis_geometry SELECT * FROM gis_multi_polygon; 84INSERT into gis_geometry SELECT * FROM gis_geometrycollection; 85SELECT fid, AsText(g) FROM gis_point ORDER by fid; 86fid AsText(g) 87101 POINT(10 10) 88102 POINT(20 10) 89103 POINT(20 20) 90104 POINT(10 20) 91SELECT fid, AsText(g) FROM gis_line ORDER by fid; 92fid AsText(g) 93105 LINESTRING(0 0,0 10,10 0) 94106 LINESTRING(10 10,20 10,20 20,10 20,10 10) 95107 LINESTRING(10 10,40 10) 96SELECT fid, AsText(g) FROM gis_polygon ORDER by fid; 97fid AsText(g) 98108 POLYGON((10 10,20 10,20 20,10 20,10 10)) 99109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10)) 100110 POLYGON((0 0,30 0,30 30,0 0)) 101SELECT fid, AsText(g) FROM gis_multi_point ORDER by fid; 102fid AsText(g) 103111 MULTIPOINT(0 0,10 10,10 20,20 20) 104112 MULTIPOINT(1 1,11 11,11 21,21 21) 105113 MULTIPOINT(3 6,4 10) 106SELECT fid, AsText(g) FROM gis_multi_line ORDER by fid; 107fid AsText(g) 108114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48)) 109115 MULTILINESTRING((10 48,10 21,10 0)) 110116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7)) 111SELECT fid, AsText(g) FROM gis_multi_polygon ORDER by fid; 112fid AsText(g) 113117 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) 114118 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) 115119 MULTIPOLYGON(((0 3,3 3,3 0,0 3))) 116SELECT fid, AsText(g) FROM gis_geometrycollection ORDER by fid; 117fid AsText(g) 118120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10)) 119121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9)) 120SELECT fid, AsText(g) FROM gis_geometry ORDER by fid; 121fid AsText(g) 122101 POINT(10 10) 123102 POINT(20 10) 124103 POINT(20 20) 125104 POINT(10 20) 126105 LINESTRING(0 0,0 10,10 0) 127106 LINESTRING(10 10,20 10,20 20,10 20,10 10) 128107 LINESTRING(10 10,40 10) 129108 POLYGON((10 10,20 10,20 20,10 20,10 10)) 130109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10)) 131110 POLYGON((0 0,30 0,30 30,0 0)) 132111 MULTIPOINT(0 0,10 10,10 20,20 20) 133112 MULTIPOINT(1 1,11 11,11 21,21 21) 134113 MULTIPOINT(3 6,4 10) 135114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48)) 136115 MULTILINESTRING((10 48,10 21,10 0)) 137116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7)) 138117 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) 139118 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) 140119 MULTIPOLYGON(((0 3,3 3,3 0,0 3))) 141120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10)) 142121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9)) 143SELECT fid, Dimension(g) FROM gis_geometry ORDER by fid; 144fid Dimension(g) 145101 0 146102 0 147103 0 148104 0 149105 1 150106 1 151107 1 152108 2 153109 2 154110 2 155111 0 156112 0 157113 0 158114 1 159115 1 160116 1 161117 2 162118 2 163119 2 164120 1 165121 1 166SELECT fid, GeometryType(g) FROM gis_geometry ORDER by fid; 167fid GeometryType(g) 168101 POINT 169102 POINT 170103 POINT 171104 POINT 172105 LINESTRING 173106 LINESTRING 174107 LINESTRING 175108 POLYGON 176109 POLYGON 177110 POLYGON 178111 MULTIPOINT 179112 MULTIPOINT 180113 MULTIPOINT 181114 MULTILINESTRING 182115 MULTILINESTRING 183116 MULTILINESTRING 184117 MULTIPOLYGON 185118 MULTIPOLYGON 186119 MULTIPOLYGON 187120 GEOMETRYCOLLECTION 188121 GEOMETRYCOLLECTION 189SELECT fid, IsEmpty(g) FROM gis_geometry ORDER by fid; 190fid IsEmpty(g) 191101 0 192102 0 193103 0 194104 0 195105 0 196106 0 197107 0 198108 0 199109 0 200110 0 201111 0 202112 0 203113 0 204114 0 205115 0 206116 0 207117 0 208118 0 209119 0 210120 0 211121 0 212SELECT fid, AsText(Envelope(g)) FROM gis_geometry ORDER by fid; 213fid AsText(Envelope(g)) 214101 POLYGON((10 10,10 10,10 10,10 10,10 10)) 215102 POLYGON((20 10,20 10,20 10,20 10,20 10)) 216103 POLYGON((20 20,20 20,20 20,20 20,20 20)) 217104 POLYGON((10 20,10 20,10 20,10 20,10 20)) 218105 POLYGON((0 0,10 0,10 10,0 10,0 0)) 219106 POLYGON((10 10,20 10,20 20,10 20,10 10)) 220107 POLYGON((10 10,40 10,40 10,10 10,10 10)) 221108 POLYGON((10 10,20 10,20 20,10 20,10 10)) 222109 POLYGON((0 0,50 0,50 50,0 50,0 0)) 223110 POLYGON((0 0,30 0,30 30,0 30,0 0)) 224111 POLYGON((0 0,20 0,20 20,0 20,0 0)) 225112 POLYGON((1 1,21 1,21 21,1 21,1 1)) 226113 POLYGON((3 6,4 6,4 10,3 10,3 6)) 227114 POLYGON((10 0,16 0,16 48,10 48,10 0)) 228115 POLYGON((10 0,10 0,10 48,10 48,10 0)) 229116 POLYGON((1 2,21 2,21 8,1 8,1 2)) 230117 POLYGON((28 0,84 0,84 42,28 42,28 0)) 231118 POLYGON((28 0,84 0,84 42,28 42,28 0)) 232119 POLYGON((0 0,3 0,3 3,0 3,0 0)) 233120 POLYGON((0 0,10 0,10 10,0 10,0 0)) 234121 POLYGON((3 6,44 6,44 9,3 9,3 6)) 235explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry; 236id select_type table type possible_keys key key_len ref rows filtered Extra 2371 SIMPLE gis_geometry ALL NULL NULL NULL NULL 21 100.00 NULL 238Warnings: 239Note 1003 /* select#1 */ select st_dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry` 240SELECT fid, X(g) FROM gis_point ORDER by fid; 241fid X(g) 242101 10 243102 20 244103 20 245104 10 246SELECT fid, Y(g) FROM gis_point ORDER by fid; 247fid Y(g) 248101 10 249102 10 250103 20 251104 20 252explain extended select X(g),Y(g) FROM gis_point; 253id select_type table type possible_keys key key_len ref rows filtered Extra 2541 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00 NULL 255Warnings: 256Note 1003 /* select#1 */ select st_x(`test`.`gis_point`.`g`) AS `X(g)`,st_y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point` 257SELECT fid, AsText(StartPoint(g)) FROM gis_line ORDER by fid; 258fid AsText(StartPoint(g)) 259105 POINT(0 0) 260106 POINT(10 10) 261107 POINT(10 10) 262SELECT fid, AsText(EndPoint(g)) FROM gis_line ORDER by fid; 263fid AsText(EndPoint(g)) 264105 POINT(10 0) 265106 POINT(10 10) 266107 POINT(40 10) 267SELECT fid, GLength(g) FROM gis_line ORDER by fid; 268fid GLength(g) 269105 24.14213562373095 270106 40 271107 30 272SELECT fid, NumPoints(g) FROM gis_line ORDER by fid; 273fid NumPoints(g) 274105 3 275106 5 276107 2 277SELECT fid, AsText(PointN(g, 2)) FROM gis_line ORDER by fid; 278fid AsText(PointN(g, 2)) 279105 POINT(0 10) 280106 POINT(20 10) 281107 POINT(40 10) 282SELECT fid, IsClosed(g) FROM gis_line ORDER by fid; 283fid IsClosed(g) 284105 0 285106 1 286107 0 287explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line; 288id select_type table type possible_keys key key_len ref rows filtered Extra 2891 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00 NULL 290Warnings: 291Note 1003 /* select#1 */ select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `GLength(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line` 292SELECT fid, AsText(Centroid(g)) FROM gis_polygon ORDER by fid; 293fid AsText(Centroid(g)) 294108 POINT(15 15) 295109 POINT(25.416666666666668 25.416666666666668) 296110 POINT(20 10) 297SELECT fid, Area(g) FROM gis_polygon ORDER by fid; 298fid Area(g) 299108 100 300109 2400 301110 450 302SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon ORDER by fid; 303fid AsText(ExteriorRing(g)) 304108 LINESTRING(10 10,20 10,20 20,10 20,10 10) 305109 LINESTRING(0 0,50 0,50 50,0 50,0 0) 306110 LINESTRING(0 0,30 0,30 30,0 0) 307SELECT fid, NumInteriorRings(g) FROM gis_polygon ORDER by fid; 308fid NumInteriorRings(g) 309108 0 310109 1 311110 0 312SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon ORDER by fid; 313fid AsText(InteriorRingN(g, 1)) 314108 NULL 315109 LINESTRING(10 10,20 10,20 20,10 20,10 10) 316110 NULL 317explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon; 318id select_type table type possible_keys key key_len ref rows filtered Extra 3191 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00 NULL 320Warnings: 321Note 1003 /* select#1 */ select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon` 322SELECT fid, IsClosed(g) FROM gis_multi_line ORDER by fid; 323fid IsClosed(g) 324114 0 325115 0 326116 0 327SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon ORDER by fid; 328fid AsText(Centroid(g)) 329117 POINT(55.58852775304245 17.426536064113982) 330118 POINT(55.58852775304245 17.426536064113982) 331119 POINT(2 2) 332SELECT fid, Area(g) FROM gis_multi_polygon ORDER by fid; 333fid Area(g) 334117 1684.5 335118 1684.5 336119 4.5 337SELECT fid, NumGeometries(g) from gis_multi_point ORDER by fid; 338fid NumGeometries(g) 339111 4 340112 4 341113 2 342SELECT fid, NumGeometries(g) from gis_multi_line ORDER by fid; 343fid NumGeometries(g) 344114 2 345115 1 346116 2 347SELECT fid, NumGeometries(g) from gis_multi_polygon ORDER by fid; 348fid NumGeometries(g) 349117 2 350118 2 351119 1 352SELECT fid, NumGeometries(g) from gis_geometrycollection ORDER by fid; 353fid NumGeometries(g) 354120 2 355121 2 356explain extended SELECT fid, NumGeometries(g) from gis_multi_point; 357id select_type table type possible_keys key key_len ref rows filtered Extra 3581 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00 NULL 359Warnings: 360Note 1003 /* select#1 */ select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point` 361SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point ORDER by fid; 362fid AsText(GeometryN(g, 2)) 363111 POINT(10 10) 364112 POINT(11 11) 365113 POINT(4 10) 366SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line ORDER by fid; 367fid AsText(GeometryN(g, 2)) 368114 LINESTRING(16 0,16 23,16 48) 369115 NULL 370116 LINESTRING(2 5,5 8,21 7) 371SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon ORDER by fid; 372fid AsText(GeometryN(g, 2)) 373117 POLYGON((59 18,67 18,67 13,59 13,59 18)) 374118 POLYGON((59 18,67 18,67 13,59 13,59 18)) 375119 NULL 376SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection ORDER by fid; 377fid AsText(GeometryN(g, 2)) 378120 LINESTRING(0 0,10 10) 379121 LINESTRING(3 6,7 9) 380SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection ORDER by fid; 381fid AsText(GeometryN(g, 1)) 382120 POINT(0 0) 383121 POINT(44 6) 384explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point; 385id select_type table type possible_keys key key_len ref rows filtered Extra 3861 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00 NULL 387Warnings: 388Note 1003 /* select#1 */ select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point` 389SELECT g1.fid as first, g2.fid as second, 390Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, 391Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, 392Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r 393FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; 394first second w c o e d t i r 395120 120 1 1 0 1 0 1 1 0 396120 121 0 0 1 0 0 0 1 0 397121 120 0 0 1 0 0 0 1 0 398121 121 1 1 0 1 0 1 1 0 399explain extended SELECT g1.fid as first, g2.fid as second, 400Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, 401Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, 402Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r 403FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; 404id select_type table type possible_keys key key_len ref rows filtered Extra 4051 SIMPLE g1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 4061 SIMPLE g2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop) 407Warnings: 408Note 1003 /* select#1 */ select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,mbrwithin(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,mbrcontains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,mbrequals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `first`,`second` 409DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; 410CREATE TABLE t1 ( 411a INTEGER PRIMARY KEY AUTO_INCREMENT, 412gp point, 413ln linestring, 414pg polygon, 415mp multipoint, 416mln multilinestring, 417mpg multipolygon, 418gc geometrycollection, 419gm geometry 420); 421SHOW FIELDS FROM t1; 422Field Type Null Key Default Extra 423a int(11) NO PRI NULL auto_increment 424gp point YES NULL 425ln linestring YES NULL 426pg polygon YES NULL 427mp multipoint YES NULL 428mln multilinestring YES NULL 429mpg multipolygon YES NULL 430gc geometrycollection YES NULL 431gm geometry YES NULL 432ALTER TABLE t1 ADD fid INT; 433SHOW FIELDS FROM t1; 434Field Type Null Key Default Extra 435a int(11) NO PRI NULL auto_increment 436gp point YES NULL 437ln linestring YES NULL 438pg polygon YES NULL 439mp multipoint YES NULL 440mln multilinestring YES NULL 441mpg multipolygon YES NULL 442gc geometrycollection YES NULL 443gm geometry YES NULL 444fid int(11) YES NULL 445DROP TABLE t1; 446create table t1 (pk integer primary key auto_increment, a geometry not null); 447insert into t1 (a) values (GeomFromText('Point(1 2)')); 448insert into t1 (a) values ('Garbage'); 449ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field 450insert IGNORE into t1 (a) values ('Garbage'); 451ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field 452drop table t1; 453create table t1 (pk integer primary key auto_increment, fl geometry not null); 454insert into t1 (fl) values (1); 455ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field 456insert into t1 (fl) values (1.11); 457ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field 458insert into t1 (fl) values ("qwerty"); 459ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field 460insert into t1 (fl) values (pointfromtext('point(1,1)')); 461ERROR 23000: Column 'fl' cannot be null 462drop table t1; 463End of 4.1 tests 464CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); 465INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); 466INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); 467INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); 468INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); 469INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); 470INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); 471INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); 472INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); 473INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); 474INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); 475INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); 476INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); 477INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); 478INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); 479INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); 480SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 481mbrcontains 482center,small 483SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 484mbrdisjoint 485down3,left3,right3,up3 486SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 487mbrequal 488center 489SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrintersect FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 490mbrintersect 491big,center,down,down2,left,left2,right,right2,small,up,up2 492SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbroverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 493mbroverlaps 494down,left,right,up 495SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrtouches FROM t1 a1 JOIN t1 a2 ON MBRTouches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 496mbrtouches 497down2,left2,right2,up2 498SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 499mbrwithin 500big,center 501SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 502contains 503center,small 504SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 505disjoint 506down3,left3,right3,up3 507SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 508equals 509center 510SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersect FROM t1 a1 JOIN t1 a2 ON Intersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 511intersect 512big,center,down,down2,left,left2,right,right2,small,up,up2 513SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 514overlaps 515down,left,right,up 516SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 517touches 518down2,left2,right2,up2 519SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; 520within 521big,center 522SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))'); 523SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))'); 524SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))'); 525SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))'); 526SET @point1 = GeomFromText('POLYGON ((0 0))'); 527SET @point2 = GeomFromText('POLYGON ((-2 0))'); 528SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name; 529overlaps 530SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name; 531overlaps 532SELECT Overlaps(@horiz1, @vert1) FROM DUAL; 533Overlaps(@horiz1, @vert1) 5340 535SELECT Overlaps(@horiz1, @horiz2) FROM DUAL; 536Overlaps(@horiz1, @horiz2) 5371 538SELECT Overlaps(@horiz1, @horiz3) FROM DUAL; 539Overlaps(@horiz1, @horiz3) 5400 541SELECT Overlaps(@horiz1, @point1) FROM DUAL; 542Overlaps(@horiz1, @point1) 5430 544SELECT Overlaps(@horiz1, @point2) FROM DUAL; 545Overlaps(@horiz1, @point2) 5460 547DROP TABLE t1; 548End of 5.0 tests 549