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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) SELECT fid,g FROM gis_point;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_line;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_polygon;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_multi_point;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_multi_line;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_multi_polygon;
INSERT into gis_geometry (fid,g) SELECT fid,g 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(57.98031067576927 17.854754130800433)
118	POINT(57.98031067576927 17.854754130800433)
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	0	1	0
120	121	0	0	1	0	0	0	1	0
120	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
120	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
121	120	0	0	1	0	0	0	1	0
121	121	1	1	0	1	0	0	1	0
121	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
121	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	120	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	121	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	120	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	121	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
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 (fid,name,shore) 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 (fid,name,aliases,num_lanes,centerline) VALUES(102, 'Route 5', NULL, 2, 
LineFromText( 
'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(103, 'Route 5', 'Main Street', 4, 
LineFromText( 
'LINESTRING( 44 31, 56 34, 70 38 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(104, 'Route 5', NULL, 2, 
LineFromText( 
'LINESTRING( 70 38, 72 48 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(105, 'Main Street', NULL, 4, 
LineFromText( 
'LINESTRING( 70 38, 84 42 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(106, 'Dirt Road by Green Forest', NULL, 
1, 
LineFromText( 
'LINESTRING( 28 26, 28 0 )',101));
# DividedRoutes 
INSERT INTO divided_routes (fid,name,num_lanes,centerlines) VALUES(119, 'Route 75', 4, 
MLineFromText( 
'MULTILINESTRING((10 48,10 21,10 0), 
  (16 0,16 23,16 48))', 101));
# Forests 
INSERT INTO forests (fid,name,boundary) 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 (fid,name,position) VALUES(110, 'Cam Bridge', PointFromText( 
'POINT( 44 31 )', 101));
# Streams 
INSERT INTO streams (fid,name,centerline) VALUES(111, 'Cam Stream', 
LineFromText( 
'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101));
INSERT INTO streams (fid,name,centerline) VALUES(112, NULL, 
LineFromText( 
'LINESTRING( 76 0, 78 4, 73 9 )', 101));
# Buildings 
INSERT INTO buildings (fid,name,position,footprint) 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 (fid,name,position,footprint) 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 (fid,name,type,shores) 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 (fid,name,boundary) VALUES(117, 'Ashton', 
PolyFromText( 
'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101));
INSERT INTO named_places (fid,name,boundary) VALUES(118, 'Goose Island', 
PolyFromText( 
'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101));
# Map Neatlines 
INSERT INTO map_neatlines (fid,neatline) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) 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 (fid,g) SELECT fid,g FROM gis_point;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_line;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_polygon;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_multi_point;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_multi_line;
INSERT into gis_geometry (fid,g) SELECT fid,g FROM gis_multi_polygon;
INSERT into gis_geometry (fid,g) SELECT fid,g 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(57.98031067576927 17.854754130800433)
118	POINT(57.98031067576927 17.854754130800433)
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	0	1	0
120	121	0	0	1	0	0	0	1	0
120	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
120	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
121	120	0	0	1	0	0	0	1	0
121	121	1	1	0	1	0	0	1	0
121	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
121	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	120	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	121	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	120	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	121	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	122	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	123	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
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 (fid,name,shore) 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 (fid,name,aliases,num_lanes,centerline) VALUES(102, 'Route 5', NULL, 2, 
LineFromText( 
'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(103, 'Route 5', 'Main Street', 4, 
LineFromText( 
'LINESTRING( 44 31, 56 34, 70 38 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(104, 'Route 5', NULL, 2, 
LineFromText( 
'LINESTRING( 70 38, 72 48 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(105, 'Main Street', NULL, 4, 
LineFromText( 
'LINESTRING( 70 38, 84 42 )' ,101));
INSERT INTO road_segments (fid,name,aliases,num_lanes,centerline) VALUES(106, 'Dirt Road by Green Forest', NULL, 
1, 
LineFromText( 
'LINESTRING( 28 26, 28 0 )',101));
# DividedRoutes 
INSERT INTO divided_routes (fid,name,num_lanes,centerlines) VALUES(119, 'Route 75', 4, 
MLineFromText( 
'MULTILINESTRING((10 48,10 21,10 0), 
  (16 0,16 23,16 48))', 101));
# Forests 
INSERT INTO forests (fid,name,boundary) 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 (fid,name,position) VALUES(110, 'Cam Bridge', PointFromText( 
'POINT( 44 31 )', 101));
# Streams 
INSERT INTO streams (fid,name,centerline) VALUES(111, 'Cam Stream', 
LineFromText( 
'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101));
INSERT INTO streams (fid,name,centerline) VALUES(112, NULL, 
LineFromText( 
'LINESTRING( 76 0, 78 4, 73 9 )', 101));
# Buildings 
INSERT INTO buildings (fid,name,position,footprint) 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 (fid,name,position,footprint) 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 (fid,name,type,shores) 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 (fid,name,boundary) VALUES(117, 'Ashton', 
PolyFromText( 
'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101));
INSERT INTO named_places (fid,name,boundary) VALUES(118, 'Goose Island', 
PolyFromText( 
'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101));
# Map Neatlines 
INSERT INTO map_neatlines (fid,neatline) 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;