gis.test 6.26 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
#
# Spatial objects
#

--disable_warnings
DROP TABLE IF EXISTS pt, ls, p, mpt, mls, mp, gc, geo;
--enable_warnings

CREATE TABLE pt  (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
CREATE TABLE ls  (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
CREATE TABLE p   (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
CREATE TABLE mpt (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
CREATE TABLE mls (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
CREATE TABLE mp  (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
CREATE TABLE gc  (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
CREATE TABLE geo (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY);

unknown's avatar
unknown committed
18 19 20 21 22 23 24 25 26 27
SHOW FIELDS FROM pt;
SHOW FIELDS FROM ls;
SHOW FIELDS FROM p;
SHOW FIELDS FROM mpt;
SHOW FIELDS FROM mls;
SHOW FIELDS FROM mp;
SHOW FIELDS FROM gc;
SHOW FIELDS FROM geo;


unknown's avatar
unknown committed
28 29 30 31
INSERT INTO pt VALUES 
(101, PointFromText('POINT(10 10)')),
(102, PointFromText('POINT(20 10)')),
(103, PointFromText('POINT(20 20)')),
unknown's avatar
unknown committed
32
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
unknown's avatar
unknown committed
33 34 35 36

INSERT INTO ls VALUES
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
37
(107, LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))));
unknown's avatar
unknown committed
38 39 40 41

INSERT INTO p VALUES
(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
42
(110, PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))));
unknown's avatar
unknown committed
43 44 45 46

INSERT INTO mpt VALUES
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
47
(113, MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))));
unknown's avatar
unknown committed
48 49 50 51

INSERT INTO mls VALUES
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
52
(116, MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))));
unknown's avatar
unknown committed
53 54 55 56 57


INSERT INTO mp VALUES
(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)))')),
(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)))')),
58
(119, MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))));
unknown's avatar
unknown committed
59 60 61

INSERT INTO gc VALUES
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
unknown's avatar
unknown committed
62
(121, GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))));
unknown's avatar
unknown committed
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84

INSERT into geo SELECT * FROM pt;
INSERT into geo SELECT * FROM ls;
INSERT into geo SELECT * FROM p;
INSERT into geo SELECT * FROM mpt;
INSERT into geo SELECT * FROM mls;
INSERT into geo SELECT * FROM mp;
INSERT into geo SELECT * FROM gc;

SELECT fid, AsText(g) FROM pt;
SELECT fid, AsText(g) FROM ls;
SELECT fid, AsText(g) FROM p;
SELECT fid, AsText(g) FROM mpt;
SELECT fid, AsText(g) FROM mls;
SELECT fid, AsText(g) FROM mp;
SELECT fid, AsText(g) FROM gc;
SELECT fid, AsText(g) FROM geo;

SELECT fid, Dimension(g) FROM geo;
SELECT fid, GeometryType(g) FROM geo;
SELECT fid, IsEmpty(g) FROM geo;
SELECT fid, AsText(Envelope(g)) FROM geo;
85
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from geo;
unknown's avatar
unknown committed
86 87 88

SELECT fid, X(g) FROM pt;
SELECT fid, Y(g) FROM pt;
89
explain extended select X(g),Y(g) FROM pt;
unknown's avatar
unknown committed
90 91 92 93 94 95 96

SELECT fid, AsText(StartPoint(g)) FROM ls;
SELECT fid, AsText(EndPoint(g)) FROM ls;
SELECT fid, GLength(g) FROM ls;
SELECT fid, NumPoints(g) FROM ls;
SELECT fid, AsText(PointN(g, 2)) FROM ls;
SELECT fid, IsClosed(g) FROM ls;
97
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM ls;
unknown's avatar
unknown committed
98 99 100 101 102 103

SELECT fid, AsText(Centroid(g)) FROM p;
SELECT fid, Area(g) FROM p;
SELECT fid, AsText(ExteriorRing(g)) FROM p;
SELECT fid, NumInteriorRings(g) FROM p;
SELECT fid, AsText(InteriorRingN(g, 1)) FROM p;
104
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM p;
unknown's avatar
unknown committed
105

106 107 108 109 110
SELECT fid, IsClosed(g) FROM mls;

SELECT fid, AsText(Centroid(g)) FROM mp;
SELECT fid, Area(g) FROM mp;

unknown's avatar
unknown committed
111 112 113 114
SELECT fid, NumGeometries(g) from mpt;
SELECT fid, NumGeometries(g) from mls;
SELECT fid, NumGeometries(g) from mp;
SELECT fid, NumGeometries(g) from gc;
115
explain extended SELECT fid, NumGeometries(g) from mpt;
unknown's avatar
unknown committed
116 117 118 119 120

SELECT fid, AsText(GeometryN(g, 2)) from mpt;
SELECT fid, AsText(GeometryN(g, 2)) from mls;
SELECT fid, AsText(GeometryN(g, 2)) from mp;
SELECT fid, AsText(GeometryN(g, 2)) from gc;
121
explain extended SELECT fid, AsText(GeometryN(g, 2)) from mpt;
unknown's avatar
unknown committed
122 123 124 125 126 127

SELECT g1.fid as first, g2.fid as second,
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gc g1, gc g2 ORDER BY first, second;
128 129 130 131 132
explain extended SELECT g1.fid as first, g2.fid as second,
Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
FROM gc g1, gc g2 ORDER BY first, second;
unknown's avatar
unknown committed
133 134

DROP TABLE pt, ls, p, mpt, mls, mp, gc, geo;
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153

#
# Check that ALTER TABLE doesn't loose geometry type
#
CREATE TABLE g1 (
  pt  point,
  ln  linestring,
  pg  polygon,
  mpt multipoint,
  mln multilinestring,
  mpg multipolygon,
  gc  geometrycollection,
  gm  geometry
);

SHOW FIELDS FROM g1;
ALTER TABLE g1 ADD fid INT NOT NULL;
SHOW FIELDS FROM g1;
DROP TABLE g1;
unknown's avatar
unknown committed
154 155

SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
156 157 158 159 160 161
explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))));
SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
#select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6)),issimple(PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),issimple(GeometryFromText('POINT(1 4)')), issimple(AsWKB(GeometryFromText('POINT(1 4)')));
explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6));