summaryrefslogtreecommitdiff
path: root/mysql-test/suite/storage_engine/type_spatial_indexes.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/storage_engine/type_spatial_indexes.result')
-rw-r--r--mysql-test/suite/storage_engine/type_spatial_indexes.result1400
1 files changed, 1400 insertions, 0 deletions
diff --git a/mysql-test/suite/storage_engine/type_spatial_indexes.result b/mysql-test/suite/storage_engine/type_spatial_indexes.result
new file mode 100644
index 00000000000..a3c2a4d4e75
--- /dev/null
+++ b/mysql-test/suite/storage_engine/type_spatial_indexes.result
@@ -0,0 +1,1400 @@
+DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
+DROP DATABASE IF EXISTS gis_ogs;
+CREATE DATABASE gis_ogs;
+CREATE TABLE gis_point (fid <INT_COLUMN>, g POINT, <CUSTOM_INDEX> g(g(128))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_line (fid <INT_COLUMN>, g LINESTRING, <CUSTOM_INDEX> g(g(256))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_polygon (fid <INT_COLUMN>, g POLYGON, <CUSTOM_INDEX> g(g(512))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_multi_point (fid <INT_COLUMN>, g MULTIPOINT, <CUSTOM_INDEX> g(g(128))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_multi_line (fid <INT_COLUMN>, g MULTILINESTRING, <CUSTOM_INDEX> g(g(256))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_multi_polygon (fid <INT_COLUMN>, g MULTIPOLYGON) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_geometrycollection (fid <INT_COLUMN>, g GEOMETRYCOLLECTION) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_geometry (fid <INT_COLUMN>, g GEOMETRY) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+USE gis_ogs;
+CREATE TABLE lakes (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+shore POLYGON, <CUSTOM_INDEX> s(shore(64))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE road_segments (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+aliases CHAR(64) <CUSTOM_COL_OPTIONS>,
+num_lanes INT <CUSTOM_COL_OPTIONS>,
+centerline LINESTRING, <CUSTOM_INDEX> c(centerline(128))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE divided_routes (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+num_lanes INT <CUSTOM_COL_OPTIONS>,
+centerlines MULTILINESTRING, <CUSTOM_INDEX> c(centerlines(512))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE forests (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+boundary MULTIPOLYGON, <CUSTOM_INDEX> b(boundary(128))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE bridges (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+position POINT, <CUSTOM_INDEX> p(`position`(64))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE streams (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+centerline LINESTRING, <CUSTOM_INDEX> c(centerline(256))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE buildings (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+position POINT,
+footprint POLYGON, <CUSTOM_INDEX> p(`position`(64)), <CUSTOM_INDEX> f(footprint(128))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE ponds (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+type CHAR(64) <CUSTOM_COL_OPTIONS>,
+shores MULTIPOLYGON, <CUSTOM_INDEX> s(shores(256))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE named_places (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+boundary POLYGON, <CUSTOM_INDEX> b(boundary(512))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE map_neatlines (fid INT <CUSTOM_COL_OPTIONS>,
+neatline POLYGON, <CUSTOM_INDEX> n(neatline(700))) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+USE test;
+SHOW FIELDS FROM gis_point;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g point YES MUL NULL
+SHOW FIELDS FROM gis_line;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g linestring YES MUL NULL
+SHOW FIELDS FROM gis_polygon;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g polygon YES MUL NULL
+SHOW FIELDS FROM gis_multi_point;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g multipoint YES MUL NULL
+SHOW FIELDS FROM gis_multi_line;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g multilinestring YES MUL NULL
+SHOW FIELDS FROM gis_multi_polygon;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g multipolygon YES NULL
+SHOW FIELDS FROM gis_geometrycollection;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g geometrycollection YES NULL
+SHOW FIELDS FROM gis_geometry;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g geometry YES NULL
+INSERT INTO gis_point VALUES
+(101, PointFromText('POINT(10 10)')),
+(102, PointFromText('POINT(20 10)')),
+(103, PointFromText('POINT(20 20)')),
+(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
+INSERT INTO gis_line VALUES
+(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
+(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
+(107, LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));
+INSERT INTO gis_polygon 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))')),
+(110, PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
+INSERT INTO gis_multi_point VALUES
+(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
+(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
+(113, MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
+INSERT INTO gis_multi_line 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))')),
+(116, MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
+INSERT INTO gis_multi_polygon 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)))')),
+(119, MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
+INSERT INTO gis_geometrycollection VALUES
+(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
+(121, GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))),
+(122, GeomFromText('GeometryCollection()')),
+(123, GeomFromText('GeometryCollection EMPTY'));
+INSERT into gis_geometry SELECT * FROM gis_point;
+INSERT into gis_geometry SELECT * FROM gis_line;
+INSERT into gis_geometry SELECT * FROM gis_polygon;
+INSERT into gis_geometry SELECT * FROM gis_multi_point;
+INSERT into gis_geometry SELECT * FROM gis_multi_line;
+INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
+INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
+SELECT fid, AsText(g) FROM gis_point;
+fid AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+SELECT fid, AsText(g) FROM gis_line;
+fid AsText(g)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+SELECT fid, AsText(g) FROM gis_polygon;
+fid AsText(g)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+SELECT fid, AsText(g) FROM gis_multi_point;
+fid AsText(g)
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+SELECT fid, AsText(g) FROM gis_multi_line;
+fid AsText(g)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+SELECT fid, AsText(g) FROM gis_multi_polygon;
+fid AsText(g)
+117 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 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)))
+119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+SELECT fid, AsText(g) FROM gis_geometrycollection;
+fid AsText(g)
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+122 GEOMETRYCOLLECTION EMPTY
+123 GEOMETRYCOLLECTION EMPTY
+SELECT fid, AsText(g) FROM gis_geometry;
+fid AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+117 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 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)))
+119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+122 GEOMETRYCOLLECTION EMPTY
+123 GEOMETRYCOLLECTION EMPTY
+SELECT fid, Dimension(g) FROM gis_geometry;
+fid Dimension(g)
+101 0
+102 0
+103 0
+104 0
+105 1
+106 1
+107 1
+108 2
+109 2
+110 2
+111 0
+112 0
+113 0
+114 1
+115 1
+116 1
+117 2
+118 2
+119 2
+120 1
+121 1
+122 0
+123 0
+SELECT fid, GeometryType(g) FROM gis_geometry;
+fid GeometryType(g)
+101 POINT
+102 POINT
+103 POINT
+104 POINT
+105 LINESTRING
+106 LINESTRING
+107 LINESTRING
+108 POLYGON
+109 POLYGON
+110 POLYGON
+111 MULTIPOINT
+112 MULTIPOINT
+113 MULTIPOINT
+114 MULTILINESTRING
+115 MULTILINESTRING
+116 MULTILINESTRING
+117 MULTIPOLYGON
+118 MULTIPOLYGON
+119 MULTIPOLYGON
+120 GEOMETRYCOLLECTION
+121 GEOMETRYCOLLECTION
+122 GEOMETRYCOLLECTION
+123 GEOMETRYCOLLECTION
+SELECT fid, IsEmpty(g) FROM gis_geometry;
+fid IsEmpty(g)
+101 0
+102 0
+103 0
+104 0
+105 0
+106 0
+107 0
+108 0
+109 0
+110 0
+111 0
+112 0
+113 0
+114 0
+115 0
+116 0
+117 0
+118 0
+119 0
+120 0
+121 0
+122 0
+123 0
+SELECT fid, AsText(Envelope(g)) FROM gis_geometry;
+fid AsText(Envelope(g))
+101 POLYGON((10 10,10 10,10 10,10 10,10 10))
+102 POLYGON((20 10,20 10,20 10,20 10,20 10))
+103 POLYGON((20 20,20 20,20 20,20 20,20 20))
+104 POLYGON((10 20,10 20,10 20,10 20,10 20))
+105 POLYGON((0 0,10 0,10 10,0 10,0 0))
+106 POLYGON((10 10,20 10,20 20,10 20,10 10))
+107 POLYGON((10 10,40 10,40 10,10 10,10 10))
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0))
+110 POLYGON((0 0,30 0,30 30,0 30,0 0))
+111 POLYGON((0 0,20 0,20 20,0 20,0 0))
+112 POLYGON((1 1,21 1,21 21,1 21,1 1))
+113 POLYGON((3 6,4 6,4 10,3 10,3 6))
+114 POLYGON((10 0,16 0,16 48,10 48,10 0))
+115 POLYGON((10 0,10 0,10 48,10 48,10 0))
+116 POLYGON((1 2,21 2,21 8,1 8,1 2))
+117 POLYGON((28 0,84 0,84 42,28 42,28 0))
+118 POLYGON((28 0,84 0,84 42,28 42,28 0))
+119 POLYGON((0 0,3 0,3 3,0 3,0 0))
+120 POLYGON((0 0,10 0,10 10,0 10,0 0))
+121 POLYGON((3 6,44 6,44 9,3 9,3 6))
+122 GEOMETRYCOLLECTION EMPTY
+123 GEOMETRYCOLLECTION EMPTY
+SELECT fid, X(g) FROM gis_point;
+fid X(g)
+101 10
+102 20
+103 20
+104 10
+SELECT fid, Y(g) FROM gis_point;
+fid Y(g)
+101 10
+102 10
+103 20
+104 20
+SELECT fid, AsText(StartPoint(g)) FROM gis_line;
+fid AsText(StartPoint(g))
+105 POINT(0 0)
+106 POINT(10 10)
+107 POINT(10 10)
+SELECT fid, AsText(EndPoint(g)) FROM gis_line;
+fid AsText(EndPoint(g))
+105 POINT(10 0)
+106 POINT(10 10)
+107 POINT(40 10)
+SELECT fid, GLength(g) FROM gis_line;
+fid GLength(g)
+105 24.14213562373095
+106 40
+107 30
+SELECT fid, NumPoints(g) FROM gis_line;
+fid NumPoints(g)
+105 3
+106 5
+107 2
+SELECT fid, AsText(PointN(g, 2)) FROM gis_line;
+fid AsText(PointN(g, 2))
+105 POINT(0 10)
+106 POINT(20 10)
+107 POINT(40 10)
+SELECT fid, IsClosed(g) FROM gis_line;
+fid IsClosed(g)
+105 0
+106 1
+107 0
+SELECT fid, AsText(Centroid(g)) FROM gis_polygon;
+fid AsText(Centroid(g))
+108 POINT(15 15)
+109 POINT(25.416666666666668 25.416666666666668)
+110 POINT(20 10)
+SELECT fid, Area(g) FROM gis_polygon;
+fid Area(g)
+108 100
+109 2400
+110 450
+SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon;
+fid AsText(ExteriorRing(g))
+108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
+110 LINESTRING(0 0,30 0,30 30,0 0)
+SELECT fid, NumInteriorRings(g) FROM gis_polygon;
+fid NumInteriorRings(g)
+108 0
+109 1
+110 0
+SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon;
+fid AsText(InteriorRingN(g, 1))
+108 NULL
+109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+110 NULL
+SELECT fid, IsClosed(g) FROM gis_multi_line;
+fid IsClosed(g)
+114 0
+115 0
+116 0
+SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon;
+fid AsText(Centroid(g))
+117 POINT(55.58852775304245 17.426536064113982)
+118 POINT(55.58852775304245 17.426536064113982)
+119 POINT(2 2)
+SELECT fid, Area(g) FROM gis_multi_polygon;
+fid Area(g)
+117 1684.5
+118 1684.5
+119 4.5
+SELECT fid, NumGeometries(g) from gis_multi_point;
+fid NumGeometries(g)
+111 4
+112 4
+113 2
+SELECT fid, NumGeometries(g) from gis_multi_line;
+fid NumGeometries(g)
+114 2
+115 1
+116 2
+SELECT fid, NumGeometries(g) from gis_multi_polygon;
+fid NumGeometries(g)
+117 2
+118 2
+119 1
+SELECT fid, NumGeometries(g) from gis_geometrycollection;
+fid NumGeometries(g)
+120 2
+121 2
+122 0
+123 0
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
+fid AsText(GeometryN(g, 2))
+111 POINT(10 10)
+112 POINT(11 11)
+113 POINT(4 10)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line;
+fid AsText(GeometryN(g, 2))
+114 LINESTRING(16 0,16 23,16 48)
+115 NULL
+116 LINESTRING(2 5,5 8,21 7)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon;
+fid AsText(GeometryN(g, 2))
+117 POLYGON((59 18,67 18,67 13,59 13,59 18))
+118 POLYGON((59 18,67 18,67 13,59 13,59 18))
+119 NULL
+SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection;
+fid AsText(GeometryN(g, 2))
+120 LINESTRING(0 0,10 10)
+121 LINESTRING(3 6,7 9)
+122 NULL
+123 NULL
+SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection;
+fid AsText(GeometryN(g, 1))
+120 POINT(0 0)
+121 POINT(44 6)
+122 NULL
+123 NULL
+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 gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
+first second w c o e d t i r
+120 120 1 1 0 1 0 1 1 0
+120 121 0 0 1 0 0 0 1 0
+120 122 0 1 NULL 0 NULL 0 NULL 0
+120 123 0 1 NULL 0 NULL 0 NULL 0
+121 120 0 0 1 0 0 0 1 0
+121 121 1 1 0 1 0 1 1 0
+121 122 0 1 NULL 0 NULL 0 NULL 0
+121 123 0 1 NULL 0 NULL 0 NULL 0
+122 120 1 0 NULL 0 NULL 0 NULL 0
+122 121 1 0 NULL 0 NULL 0 NULL 0
+122 122 1 1 NULL 1 NULL 0 NULL 0
+122 123 1 1 NULL 1 NULL 0 NULL 0
+123 120 1 0 NULL 0 NULL 0 NULL 0
+123 121 1 0 NULL 0 NULL 0 NULL 0
+123 122 1 1 NULL 1 NULL 0 NULL 0
+123 123 1 1 NULL 1 NULL 0 NULL 0
+DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
+USE gis_ogs;
+# Lakes
+INSERT INTO lakes VALUES (
+101, 'BLUE LAKE',
+PolyFromText(
+'POLYGON(
+ (52 18,66 23,73 9,48 6,52 18),
+ (59 18,67 18,67 13,59 13,59 18)
+ )',
+101));
+# Road Segments
+INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2,
+LineFromText(
+'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101));
+INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4,
+LineFromText(
+'LINESTRING( 44 31, 56 34, 70 38 )' ,101));
+INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2,
+LineFromText(
+'LINESTRING( 70 38, 72 48 )' ,101));
+INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4,
+LineFromText(
+'LINESTRING( 70 38, 84 42 )' ,101));
+INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL,
+1,
+LineFromText(
+'LINESTRING( 28 26, 28 0 )',101));
+# DividedRoutes
+INSERT INTO divided_routes VALUES(119, 'Route 75', 4,
+MLineFromText(
+'MULTILINESTRING((10 48,10 21,10 0),
+ (16 0,16 23,16 48))', 101));
+# Forests
+INSERT INTO forests VALUES(109, 'Green Forest',
+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)))',
+101));
+# Bridges
+INSERT INTO bridges VALUES(110, 'Cam Bridge', PointFromText(
+'POINT( 44 31 )', 101));
+# Streams
+INSERT INTO streams VALUES(111, 'Cam Stream',
+LineFromText(
+'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101));
+INSERT INTO streams VALUES(112, NULL,
+LineFromText(
+'LINESTRING( 76 0, 78 4, 73 9 )', 101));
+# Buildings
+INSERT INTO buildings VALUES(113, '123 Main Street',
+PointFromText(
+'POINT( 52 30 )', 101),
+PolyFromText(
+'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 101));
+INSERT INTO buildings VALUES(114, '215 Main Street',
+PointFromText(
+'POINT( 64 33 )', 101),
+PolyFromText(
+'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 101));
+# Ponds
+INSERT INTO ponds VALUES(120, NULL, 'Stock Pond',
+MPolyFromText(
+'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ),
+ ( ( 26 44, 26 40, 28 42, 26 44) ) )', 101));
+# Named Places
+INSERT INTO named_places VALUES(117, 'Ashton',
+PolyFromText(
+'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101));
+INSERT INTO named_places VALUES(118, 'Goose Island',
+PolyFromText(
+'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101));
+# Map Neatlines
+INSERT INTO map_neatlines VALUES(115,
+PolyFromText(
+'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101));
+SELECT Dimension(shore)
+FROM lakes
+WHERE name = 'Blue Lake';
+Dimension(shore)
+2
+SELECT GeometryType(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+GeometryType(centerlines)
+MULTILINESTRING
+SELECT AsText(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(boundary)
+POLYGON((67 13,67 18,59 18,59 13,67 13))
+SELECT AsText(PolyFromWKB(AsBinary(boundary),101))
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(PolyFromWKB(AsBinary(boundary),101))
+POLYGON((67 13,67 18,59 18,59 13,67 13))
+SELECT SRID(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+SRID(boundary)
+101
+SELECT IsEmpty(centerline)
+FROM road_segments
+WHERE name = 'Route 5'
+AND aliases = 'Main Street';
+IsEmpty(centerline)
+0
+SELECT AsText(Envelope(boundary))
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(Envelope(boundary))
+POLYGON((59 13,67 13,67 18,59 18,59 13))
+SELECT X(position)
+FROM bridges
+WHERE name = 'Cam Bridge';
+X(position)
+44
+SELECT Y(position)
+FROM bridges
+WHERE name = 'Cam Bridge';
+Y(position)
+31
+SELECT AsText(StartPoint(centerline))
+FROM road_segments
+WHERE fid = 102;
+AsText(StartPoint(centerline))
+POINT(0 18)
+SELECT AsText(EndPoint(centerline))
+FROM road_segments
+WHERE fid = 102;
+AsText(EndPoint(centerline))
+POINT(44 31)
+SELECT GLength(centerline)
+FROM road_segments
+WHERE fid = 106;
+GLength(centerline)
+26
+SELECT NumPoints(centerline)
+FROM road_segments
+WHERE fid = 102;
+NumPoints(centerline)
+5
+SELECT AsText(PointN(centerline, 1))
+FROM road_segments
+WHERE fid = 102;
+AsText(PointN(centerline, 1))
+POINT(0 18)
+SELECT AsText(Centroid(boundary))
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(Centroid(boundary))
+POINT(63 15.5)
+SELECT Area(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+Area(boundary)
+40
+SELECT AsText(ExteriorRing(shore))
+FROM lakes
+WHERE name = 'Blue Lake';
+AsText(ExteriorRing(shore))
+LINESTRING(52 18,66 23,73 9,48 6,52 18)
+SELECT NumInteriorRings(shore)
+FROM lakes
+WHERE name = 'Blue Lake';
+NumInteriorRings(shore)
+1
+SELECT AsText(InteriorRingN(shore, 1))
+FROM lakes
+WHERE name = 'Blue Lake';
+AsText(InteriorRingN(shore, 1))
+LINESTRING(59 18,67 18,67 13,59 13,59 18)
+SELECT NumGeometries(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+NumGeometries(centerlines)
+2
+SELECT AsText(GeometryN(centerlines, 2))
+FROM divided_routes
+WHERE name = 'Route 75';
+AsText(GeometryN(centerlines, 2))
+LINESTRING(16 0,16 23,16 48)
+SELECT IsClosed(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+IsClosed(centerlines)
+0
+SELECT GLength(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+GLength(centerlines)
+96
+SELECT AsText(Centroid(shores))
+FROM ponds
+WHERE fid = 120;
+AsText(Centroid(shores))
+POINT(25 42)
+SELECT Area(shores)
+FROM ponds
+WHERE fid = 120;
+Area(shores)
+8
+SELECT ST_Equals(boundary,
+PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1))
+FROM named_places
+WHERE name = 'Goose Island';
+ST_Equals(boundary,
+PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1))
+1
+SELECT ST_Disjoint(centerlines, boundary)
+FROM divided_routes, named_places
+WHERE divided_routes.name = 'Route 75'
+AND named_places.name = 'Ashton';
+ST_Disjoint(centerlines, boundary)
+1
+SELECT ST_Touches(centerline, shore)
+FROM streams, lakes
+WHERE streams.name = 'Cam Stream'
+AND lakes.name = 'Blue Lake';
+ST_Touches(centerline, shore)
+1
+SELECT Crosses(road_segments.centerline, divided_routes.centerlines)
+FROM road_segments, divided_routes
+WHERE road_segments.fid = 102
+AND divided_routes.name = 'Route 75';
+Crosses(road_segments.centerline, divided_routes.centerlines)
+1
+SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines)
+FROM road_segments, divided_routes
+WHERE road_segments.fid = 102
+AND divided_routes.name = 'Route 75';
+ST_Intersects(road_segments.centerline, divided_routes.centerlines)
+1
+SELECT ST_Contains(forests.boundary, named_places.boundary)
+FROM forests, named_places
+WHERE forests.name = 'Green Forest'
+AND named_places.name = 'Ashton';
+ST_Contains(forests.boundary, named_places.boundary)
+0
+SELECT ST_Distance(position, boundary)
+FROM bridges, named_places
+WHERE bridges.name = 'Cam Bridge'
+AND named_places.name = 'Ashton';
+ST_Distance(position, boundary)
+12
+SELECT AsText(ST_Difference(named_places.boundary, forests.boundary))
+FROM named_places, forests
+WHERE named_places.name = 'Ashton'
+AND forests.name = 'Green Forest';
+AsText(ST_Difference(named_places.boundary, forests.boundary))
+POLYGON((56 34,62 48,84 48,84 42,56 34))
+SELECT AsText(ST_Union(shore, boundary))
+FROM lakes, named_places
+WHERE lakes.name = 'Blue Lake'
+AND named_places.name = 'Goose Island';
+AsText(ST_Union(shore, boundary))
+POLYGON((48 6,52 18,66 23,73 9,48 6))
+SELECT AsText(ST_SymDifference(shore, boundary))
+FROM lakes, named_places
+WHERE lakes.name = 'Blue Lake'
+AND named_places.name = 'Ashton';
+AsText(ST_SymDifference(shore, boundary))
+MULTIPOLYGON(((48 6,52 18,66 23,73 9,48 6),(59 13,59 18,67 18,67 13,59 13)),((56 30,56 34,62 48,84 48,84 30,56 30)))
+SELECT count(*)
+FROM buildings, bridges
+WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
+count(*)
+1
+DROP DATABASE gis_ogs;
+USE test;
+DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
+DROP DATABASE IF EXISTS gis_ogs;
+CREATE DATABASE gis_ogs;
+CREATE TABLE gis_point (fid <INT_COLUMN>, g POINT NOT NULL, SPATIAL INDEX(g)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_line (fid <INT_COLUMN>, g LINESTRING NOT NULL, SPATIAL INDEX(g)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_polygon (fid <INT_COLUMN>, g POLYGON NOT NULL, SPATIAL INDEX(g)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_multi_point (fid <INT_COLUMN>, g MULTIPOINT NOT NULL, SPATIAL INDEX(g)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_multi_line (fid <INT_COLUMN>, g MULTILINESTRING NOT NULL, SPATIAL INDEX(g)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_multi_polygon (fid <INT_COLUMN>, g MULTIPOLYGON NOT NULL, SPATIAL INDEX(g)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_geometrycollection (fid <INT_COLUMN>, g GEOMETRYCOLLECTION NOT NULL, SPATIAL INDEX(g)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE gis_geometry (fid <INT_COLUMN>, g GEOMETRY NOT NULL) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+USE gis_ogs;
+CREATE TABLE lakes (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+shore POLYGON NOT NULL, SPATIAL INDEX s(shore)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE road_segments (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+aliases CHAR(64) <CUSTOM_COL_OPTIONS>,
+num_lanes INT <CUSTOM_COL_OPTIONS>,
+centerline LINESTRING NOT NULL, SPATIAL INDEX c(centerline)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE divided_routes (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+num_lanes INT <CUSTOM_COL_OPTIONS>,
+centerlines MULTILINESTRING NOT NULL, SPATIAL INDEX c(centerlines)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE forests (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+boundary MULTIPOLYGON NOT NULL, SPATIAL INDEX b(boundary)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE bridges (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+position POINT NOT NULL, SPATIAL INDEX p(position)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE streams (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+centerline LINESTRING NOT NULL, SPATIAL INDEX c(centerline)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE buildings (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+position POINT NOT NULL,
+footprint POLYGON NOT NULL, SPATIAL INDEX p(position), SPATIAL INDEX f(footprint)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE ponds (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+type CHAR(64) <CUSTOM_COL_OPTIONS>,
+shores MULTIPOLYGON NOT NULL, SPATIAL INDEX s(shores)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE named_places (fid INT <CUSTOM_COL_OPTIONS>,
+name CHAR(64) <CUSTOM_COL_OPTIONS>,
+boundary POLYGON NOT NULL, SPATIAL INDEX b(boundary)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+CREATE TABLE map_neatlines (fid INT <CUSTOM_COL_OPTIONS>,
+neatline POLYGON NOT NULL, SPATIAL INDEX n(neatline)) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
+USE test;
+SHOW FIELDS FROM gis_point;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g point NO MUL NULL
+SHOW FIELDS FROM gis_line;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g linestring NO MUL NULL
+SHOW FIELDS FROM gis_polygon;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g polygon NO MUL NULL
+SHOW FIELDS FROM gis_multi_point;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g multipoint NO MUL NULL
+SHOW FIELDS FROM gis_multi_line;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g multilinestring NO MUL NULL
+SHOW FIELDS FROM gis_multi_polygon;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g multipolygon NO MUL NULL
+SHOW FIELDS FROM gis_geometrycollection;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g geometrycollection NO MUL NULL
+SHOW FIELDS FROM gis_geometry;
+Field Type Null Key Default Extra
+fid int(11) YES NULL
+g geometry NO NULL
+INSERT INTO gis_point VALUES
+(101, PointFromText('POINT(10 10)')),
+(102, PointFromText('POINT(20 10)')),
+(103, PointFromText('POINT(20 20)')),
+(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
+INSERT INTO gis_line VALUES
+(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
+(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
+(107, LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));
+INSERT INTO gis_polygon 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))')),
+(110, PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
+INSERT INTO gis_multi_point VALUES
+(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
+(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
+(113, MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
+INSERT INTO gis_multi_line 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))')),
+(116, MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
+INSERT INTO gis_multi_polygon 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)))')),
+(119, MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
+INSERT INTO gis_geometrycollection VALUES
+(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
+(121, GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))),
+(122, GeomFromText('GeometryCollection()')),
+(123, GeomFromText('GeometryCollection EMPTY'));
+INSERT into gis_geometry SELECT * FROM gis_point;
+INSERT into gis_geometry SELECT * FROM gis_line;
+INSERT into gis_geometry SELECT * FROM gis_polygon;
+INSERT into gis_geometry SELECT * FROM gis_multi_point;
+INSERT into gis_geometry SELECT * FROM gis_multi_line;
+INSERT into gis_geometry SELECT * FROM gis_multi_polygon;
+INSERT into gis_geometry SELECT * FROM gis_geometrycollection;
+SELECT fid, AsText(g) FROM gis_point;
+fid AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+SELECT fid, AsText(g) FROM gis_line;
+fid AsText(g)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+SELECT fid, AsText(g) FROM gis_polygon;
+fid AsText(g)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+SELECT fid, AsText(g) FROM gis_multi_point;
+fid AsText(g)
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+SELECT fid, AsText(g) FROM gis_multi_line;
+fid AsText(g)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+SELECT fid, AsText(g) FROM gis_multi_polygon;
+fid AsText(g)
+117 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 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)))
+119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+SELECT fid, AsText(g) FROM gis_geometrycollection;
+fid AsText(g)
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+122 GEOMETRYCOLLECTION EMPTY
+123 GEOMETRYCOLLECTION EMPTY
+SELECT fid, AsText(g) FROM gis_geometry;
+fid AsText(g)
+101 POINT(10 10)
+102 POINT(20 10)
+103 POINT(20 20)
+104 POINT(10 20)
+105 LINESTRING(0 0,0 10,10 0)
+106 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+107 LINESTRING(10 10,40 10)
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10))
+110 POLYGON((0 0,30 0,30 30,0 0))
+111 MULTIPOINT(0 0,10 10,10 20,20 20)
+112 MULTIPOINT(1 1,11 11,11 21,21 21)
+113 MULTIPOINT(3 6,4 10)
+114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))
+115 MULTILINESTRING((10 48,10 21,10 0))
+116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7))
+117 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 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)))
+119 MULTIPOLYGON(((0 3,3 3,3 0,0 3)))
+120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10))
+121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9))
+122 GEOMETRYCOLLECTION EMPTY
+123 GEOMETRYCOLLECTION EMPTY
+SELECT fid, Dimension(g) FROM gis_geometry;
+fid Dimension(g)
+101 0
+102 0
+103 0
+104 0
+105 1
+106 1
+107 1
+108 2
+109 2
+110 2
+111 0
+112 0
+113 0
+114 1
+115 1
+116 1
+117 2
+118 2
+119 2
+120 1
+121 1
+122 0
+123 0
+SELECT fid, GeometryType(g) FROM gis_geometry;
+fid GeometryType(g)
+101 POINT
+102 POINT
+103 POINT
+104 POINT
+105 LINESTRING
+106 LINESTRING
+107 LINESTRING
+108 POLYGON
+109 POLYGON
+110 POLYGON
+111 MULTIPOINT
+112 MULTIPOINT
+113 MULTIPOINT
+114 MULTILINESTRING
+115 MULTILINESTRING
+116 MULTILINESTRING
+117 MULTIPOLYGON
+118 MULTIPOLYGON
+119 MULTIPOLYGON
+120 GEOMETRYCOLLECTION
+121 GEOMETRYCOLLECTION
+122 GEOMETRYCOLLECTION
+123 GEOMETRYCOLLECTION
+SELECT fid, IsEmpty(g) FROM gis_geometry;
+fid IsEmpty(g)
+101 0
+102 0
+103 0
+104 0
+105 0
+106 0
+107 0
+108 0
+109 0
+110 0
+111 0
+112 0
+113 0
+114 0
+115 0
+116 0
+117 0
+118 0
+119 0
+120 0
+121 0
+122 0
+123 0
+SELECT fid, AsText(Envelope(g)) FROM gis_geometry;
+fid AsText(Envelope(g))
+101 POLYGON((10 10,10 10,10 10,10 10,10 10))
+102 POLYGON((20 10,20 10,20 10,20 10,20 10))
+103 POLYGON((20 20,20 20,20 20,20 20,20 20))
+104 POLYGON((10 20,10 20,10 20,10 20,10 20))
+105 POLYGON((0 0,10 0,10 10,0 10,0 0))
+106 POLYGON((10 10,20 10,20 20,10 20,10 10))
+107 POLYGON((10 10,40 10,40 10,10 10,10 10))
+108 POLYGON((10 10,20 10,20 20,10 20,10 10))
+109 POLYGON((0 0,50 0,50 50,0 50,0 0))
+110 POLYGON((0 0,30 0,30 30,0 30,0 0))
+111 POLYGON((0 0,20 0,20 20,0 20,0 0))
+112 POLYGON((1 1,21 1,21 21,1 21,1 1))
+113 POLYGON((3 6,4 6,4 10,3 10,3 6))
+114 POLYGON((10 0,16 0,16 48,10 48,10 0))
+115 POLYGON((10 0,10 0,10 48,10 48,10 0))
+116 POLYGON((1 2,21 2,21 8,1 8,1 2))
+117 POLYGON((28 0,84 0,84 42,28 42,28 0))
+118 POLYGON((28 0,84 0,84 42,28 42,28 0))
+119 POLYGON((0 0,3 0,3 3,0 3,0 0))
+120 POLYGON((0 0,10 0,10 10,0 10,0 0))
+121 POLYGON((3 6,44 6,44 9,3 9,3 6))
+122 GEOMETRYCOLLECTION EMPTY
+123 GEOMETRYCOLLECTION EMPTY
+SELECT fid, X(g) FROM gis_point;
+fid X(g)
+101 10
+102 20
+103 20
+104 10
+SELECT fid, Y(g) FROM gis_point;
+fid Y(g)
+101 10
+102 10
+103 20
+104 20
+SELECT fid, AsText(StartPoint(g)) FROM gis_line;
+fid AsText(StartPoint(g))
+105 POINT(0 0)
+106 POINT(10 10)
+107 POINT(10 10)
+SELECT fid, AsText(EndPoint(g)) FROM gis_line;
+fid AsText(EndPoint(g))
+105 POINT(10 0)
+106 POINT(10 10)
+107 POINT(40 10)
+SELECT fid, GLength(g) FROM gis_line;
+fid GLength(g)
+105 24.14213562373095
+106 40
+107 30
+SELECT fid, NumPoints(g) FROM gis_line;
+fid NumPoints(g)
+105 3
+106 5
+107 2
+SELECT fid, AsText(PointN(g, 2)) FROM gis_line;
+fid AsText(PointN(g, 2))
+105 POINT(0 10)
+106 POINT(20 10)
+107 POINT(40 10)
+SELECT fid, IsClosed(g) FROM gis_line;
+fid IsClosed(g)
+105 0
+106 1
+107 0
+SELECT fid, AsText(Centroid(g)) FROM gis_polygon;
+fid AsText(Centroid(g))
+108 POINT(15 15)
+109 POINT(25.416666666666668 25.416666666666668)
+110 POINT(20 10)
+SELECT fid, Area(g) FROM gis_polygon;
+fid Area(g)
+108 100
+109 2400
+110 450
+SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon;
+fid AsText(ExteriorRing(g))
+108 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+109 LINESTRING(0 0,50 0,50 50,0 50,0 0)
+110 LINESTRING(0 0,30 0,30 30,0 0)
+SELECT fid, NumInteriorRings(g) FROM gis_polygon;
+fid NumInteriorRings(g)
+108 0
+109 1
+110 0
+SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon;
+fid AsText(InteriorRingN(g, 1))
+108 NULL
+109 LINESTRING(10 10,20 10,20 20,10 20,10 10)
+110 NULL
+SELECT fid, IsClosed(g) FROM gis_multi_line;
+fid IsClosed(g)
+114 0
+115 0
+116 0
+SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon;
+fid AsText(Centroid(g))
+117 POINT(55.58852775304245 17.426536064113982)
+118 POINT(55.58852775304245 17.426536064113982)
+119 POINT(2 2)
+SELECT fid, Area(g) FROM gis_multi_polygon;
+fid Area(g)
+117 1684.5
+118 1684.5
+119 4.5
+SELECT fid, NumGeometries(g) from gis_multi_point;
+fid NumGeometries(g)
+111 4
+112 4
+113 2
+SELECT fid, NumGeometries(g) from gis_multi_line;
+fid NumGeometries(g)
+114 2
+115 1
+116 2
+SELECT fid, NumGeometries(g) from gis_multi_polygon;
+fid NumGeometries(g)
+117 2
+118 2
+119 1
+SELECT fid, NumGeometries(g) from gis_geometrycollection;
+fid NumGeometries(g)
+120 2
+121 2
+122 0
+123 0
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
+fid AsText(GeometryN(g, 2))
+111 POINT(10 10)
+112 POINT(11 11)
+113 POINT(4 10)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line;
+fid AsText(GeometryN(g, 2))
+114 LINESTRING(16 0,16 23,16 48)
+115 NULL
+116 LINESTRING(2 5,5 8,21 7)
+SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon;
+fid AsText(GeometryN(g, 2))
+117 POLYGON((59 18,67 18,67 13,59 13,59 18))
+118 POLYGON((59 18,67 18,67 13,59 13,59 18))
+119 NULL
+SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection;
+fid AsText(GeometryN(g, 2))
+120 LINESTRING(0 0,10 10)
+121 LINESTRING(3 6,7 9)
+122 NULL
+123 NULL
+SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection;
+fid AsText(GeometryN(g, 1))
+120 POINT(0 0)
+121 POINT(44 6)
+122 NULL
+123 NULL
+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 gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
+first second w c o e d t i r
+120 120 1 1 0 1 0 1 1 0
+120 121 0 0 1 0 0 0 1 0
+120 122 0 1 NULL 0 NULL 0 NULL 0
+120 123 0 1 NULL 0 NULL 0 NULL 0
+121 120 0 0 1 0 0 0 1 0
+121 121 1 1 0 1 0 1 1 0
+121 122 0 1 NULL 0 NULL 0 NULL 0
+121 123 0 1 NULL 0 NULL 0 NULL 0
+122 120 1 0 NULL 0 NULL 0 NULL 0
+122 121 1 0 NULL 0 NULL 0 NULL 0
+122 122 1 1 NULL 1 NULL 0 NULL 0
+122 123 1 1 NULL 1 NULL 0 NULL 0
+123 120 1 0 NULL 0 NULL 0 NULL 0
+123 121 1 0 NULL 0 NULL 0 NULL 0
+123 122 1 1 NULL 1 NULL 0 NULL 0
+123 123 1 1 NULL 1 NULL 0 NULL 0
+DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
+USE gis_ogs;
+# Lakes
+INSERT INTO lakes VALUES (
+101, 'BLUE LAKE',
+PolyFromText(
+'POLYGON(
+ (52 18,66 23,73 9,48 6,52 18),
+ (59 18,67 18,67 13,59 13,59 18)
+ )',
+101));
+# Road Segments
+INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2,
+LineFromText(
+'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101));
+INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4,
+LineFromText(
+'LINESTRING( 44 31, 56 34, 70 38 )' ,101));
+INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2,
+LineFromText(
+'LINESTRING( 70 38, 72 48 )' ,101));
+INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4,
+LineFromText(
+'LINESTRING( 70 38, 84 42 )' ,101));
+INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL,
+1,
+LineFromText(
+'LINESTRING( 28 26, 28 0 )',101));
+# DividedRoutes
+INSERT INTO divided_routes VALUES(119, 'Route 75', 4,
+MLineFromText(
+'MULTILINESTRING((10 48,10 21,10 0),
+ (16 0,16 23,16 48))', 101));
+# Forests
+INSERT INTO forests VALUES(109, 'Green Forest',
+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)))',
+101));
+# Bridges
+INSERT INTO bridges VALUES(110, 'Cam Bridge', PointFromText(
+'POINT( 44 31 )', 101));
+# Streams
+INSERT INTO streams VALUES(111, 'Cam Stream',
+LineFromText(
+'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101));
+INSERT INTO streams VALUES(112, NULL,
+LineFromText(
+'LINESTRING( 76 0, 78 4, 73 9 )', 101));
+# Buildings
+INSERT INTO buildings VALUES(113, '123 Main Street',
+PointFromText(
+'POINT( 52 30 )', 101),
+PolyFromText(
+'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 101));
+INSERT INTO buildings VALUES(114, '215 Main Street',
+PointFromText(
+'POINT( 64 33 )', 101),
+PolyFromText(
+'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 101));
+# Ponds
+INSERT INTO ponds VALUES(120, NULL, 'Stock Pond',
+MPolyFromText(
+'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ),
+ ( ( 26 44, 26 40, 28 42, 26 44) ) )', 101));
+# Named Places
+INSERT INTO named_places VALUES(117, 'Ashton',
+PolyFromText(
+'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101));
+INSERT INTO named_places VALUES(118, 'Goose Island',
+PolyFromText(
+'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101));
+# Map Neatlines
+INSERT INTO map_neatlines VALUES(115,
+PolyFromText(
+'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101));
+SELECT Dimension(shore)
+FROM lakes
+WHERE name = 'Blue Lake';
+Dimension(shore)
+2
+SELECT GeometryType(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+GeometryType(centerlines)
+MULTILINESTRING
+SELECT AsText(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(boundary)
+POLYGON((67 13,67 18,59 18,59 13,67 13))
+SELECT AsText(PolyFromWKB(AsBinary(boundary),101))
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(PolyFromWKB(AsBinary(boundary),101))
+POLYGON((67 13,67 18,59 18,59 13,67 13))
+SELECT SRID(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+SRID(boundary)
+101
+SELECT IsEmpty(centerline)
+FROM road_segments
+WHERE name = 'Route 5'
+AND aliases = 'Main Street';
+IsEmpty(centerline)
+0
+SELECT AsText(Envelope(boundary))
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(Envelope(boundary))
+POLYGON((59 13,67 13,67 18,59 18,59 13))
+SELECT X(position)
+FROM bridges
+WHERE name = 'Cam Bridge';
+X(position)
+44
+SELECT Y(position)
+FROM bridges
+WHERE name = 'Cam Bridge';
+Y(position)
+31
+SELECT AsText(StartPoint(centerline))
+FROM road_segments
+WHERE fid = 102;
+AsText(StartPoint(centerline))
+POINT(0 18)
+SELECT AsText(EndPoint(centerline))
+FROM road_segments
+WHERE fid = 102;
+AsText(EndPoint(centerline))
+POINT(44 31)
+SELECT GLength(centerline)
+FROM road_segments
+WHERE fid = 106;
+GLength(centerline)
+26
+SELECT NumPoints(centerline)
+FROM road_segments
+WHERE fid = 102;
+NumPoints(centerline)
+5
+SELECT AsText(PointN(centerline, 1))
+FROM road_segments
+WHERE fid = 102;
+AsText(PointN(centerline, 1))
+POINT(0 18)
+SELECT AsText(Centroid(boundary))
+FROM named_places
+WHERE name = 'Goose Island';
+AsText(Centroid(boundary))
+POINT(63 15.5)
+SELECT Area(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+Area(boundary)
+40
+SELECT AsText(ExteriorRing(shore))
+FROM lakes
+WHERE name = 'Blue Lake';
+AsText(ExteriorRing(shore))
+LINESTRING(52 18,66 23,73 9,48 6,52 18)
+SELECT NumInteriorRings(shore)
+FROM lakes
+WHERE name = 'Blue Lake';
+NumInteriorRings(shore)
+1
+SELECT AsText(InteriorRingN(shore, 1))
+FROM lakes
+WHERE name = 'Blue Lake';
+AsText(InteriorRingN(shore, 1))
+LINESTRING(59 18,67 18,67 13,59 13,59 18)
+SELECT NumGeometries(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+NumGeometries(centerlines)
+2
+SELECT AsText(GeometryN(centerlines, 2))
+FROM divided_routes
+WHERE name = 'Route 75';
+AsText(GeometryN(centerlines, 2))
+LINESTRING(16 0,16 23,16 48)
+SELECT IsClosed(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+IsClosed(centerlines)
+0
+SELECT GLength(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+GLength(centerlines)
+96
+SELECT AsText(Centroid(shores))
+FROM ponds
+WHERE fid = 120;
+AsText(Centroid(shores))
+POINT(25 42)
+SELECT Area(shores)
+FROM ponds
+WHERE fid = 120;
+Area(shores)
+8
+SELECT ST_Equals(boundary,
+PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1))
+FROM named_places
+WHERE name = 'Goose Island';
+ST_Equals(boundary,
+PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1))
+1
+SELECT ST_Disjoint(centerlines, boundary)
+FROM divided_routes, named_places
+WHERE divided_routes.name = 'Route 75'
+AND named_places.name = 'Ashton';
+ST_Disjoint(centerlines, boundary)
+1
+SELECT ST_Touches(centerline, shore)
+FROM streams, lakes
+WHERE streams.name = 'Cam Stream'
+AND lakes.name = 'Blue Lake';
+ST_Touches(centerline, shore)
+1
+SELECT Crosses(road_segments.centerline, divided_routes.centerlines)
+FROM road_segments, divided_routes
+WHERE road_segments.fid = 102
+AND divided_routes.name = 'Route 75';
+Crosses(road_segments.centerline, divided_routes.centerlines)
+1
+SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines)
+FROM road_segments, divided_routes
+WHERE road_segments.fid = 102
+AND divided_routes.name = 'Route 75';
+ST_Intersects(road_segments.centerline, divided_routes.centerlines)
+1
+SELECT ST_Contains(forests.boundary, named_places.boundary)
+FROM forests, named_places
+WHERE forests.name = 'Green Forest'
+AND named_places.name = 'Ashton';
+ST_Contains(forests.boundary, named_places.boundary)
+0
+SELECT ST_Distance(position, boundary)
+FROM bridges, named_places
+WHERE bridges.name = 'Cam Bridge'
+AND named_places.name = 'Ashton';
+ST_Distance(position, boundary)
+12
+SELECT AsText(ST_Difference(named_places.boundary, forests.boundary))
+FROM named_places, forests
+WHERE named_places.name = 'Ashton'
+AND forests.name = 'Green Forest';
+AsText(ST_Difference(named_places.boundary, forests.boundary))
+POLYGON((56 34,62 48,84 48,84 42,56 34))
+SELECT AsText(ST_Union(shore, boundary))
+FROM lakes, named_places
+WHERE lakes.name = 'Blue Lake'
+AND named_places.name = 'Goose Island';
+AsText(ST_Union(shore, boundary))
+POLYGON((48 6,52 18,66 23,73 9,48 6))
+SELECT AsText(ST_SymDifference(shore, boundary))
+FROM lakes, named_places
+WHERE lakes.name = 'Blue Lake'
+AND named_places.name = 'Ashton';
+AsText(ST_SymDifference(shore, boundary))
+MULTIPOLYGON(((48 6,52 18,66 23,73 9,48 6),(59 13,59 18,67 18,67 13,59 13)),((56 30,56 34,62 48,84 48,84 30,56 30)))
+SELECT count(*)
+FROM buildings, bridges
+WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
+count(*)
+1
+DROP DATABASE gis_ogs;
+USE test;
View Lorry Controller Status