« back to all changes in this revision
Viewing changes to examples/ogc_test_suite/1_schema.sql
- browse files at revision 1559
- compare with another revision
- download diff
- download tarball
- view history from revision 1559
- Committer: strk
- Date: 2005-04-15 14:09:20 UTC
- Revision ID: svn-v4:b70326c6-7e19-0410-871a-916f4a2858ee:trunk:1623
Renamed examples/ to extras/. Added WFS_locks.
- files added:
- extras
- extras/WFS_locks
- extras/ogc_test_suite
- extras/wkb_reader
- files removed:
- examples
- examples/ogc_test_suite
- examples/wkb_reader
added
removed
examples/ogc_test_suite/1_schema.sql
1
--
2
--345678901234567890123456789012345678901234567890123456789012345678901234567890
3
--//////////////////////////////////////////////////////////////////////////////
4
--
5
--
6
--
7
--//////////////////////////////////////////////////////////////////////////////
8
--
9
--
10
--
11
--
12
--
13
--
14
--
15
--
16
--//////////////////////////////////////////////////////////////////////////////
17
--
18
--
19
--//////////////////////////////////////////////////////////////////////////////
20
--
21
--
22
--
23
--//////////////////////////////////////////////////////////////////////////////
24
--
25
--
26
--
27
--//////////////////////////////////////////////////////////////////////////////
28
--
29
--
30
--//////////////////////////////////////////////////////////////////////////////
31
--
32
--
33
--
34
--CREATE TABLE spatial_ref_sys (
35
--);
36
--
37
INSERT INTO spatial_ref_sys (SRID,AUTH_NAME,AUTH_SRID,SRTEXT)
38
VALUES (101, 'POSC', 32214,
39
'PROJCS["UTM_ZONE_14N", GEOGCS["World Geodetic System 72",
40
DATUM["WGS_72", SPHEROID["NWL_10D", 6378135, 298.26]],
41
PRIMEM["Greenwich", 0], UNIT["Meter", 1.0]],
42
PROJECTION["Transverse_Mercator"],
43
PARAMETER["False_Easting", 500000.0],
44
PARAMETER["False_Northing", 0.0],
45
PARAMETER["Central_Meridian", -99.0],
46
PARAMETER["Scale_Factor", 0.9996],
47
PARAMETER["Latitude_of_origin", 0.0],
48
UNIT["Meter", 1.0]]'
49
);
50
--
51
--
52
--
53
--//////////////////////////////////////////////////////////////////////////////
54
--
55
--
56
--
57
--//////////////////////////////////////////////////////////////////////////////
58
--
59
--------------------------------------------------------------------------------
60
--
61
--
62
--------------------------------------------------------------------------------
63
--
64
--
65
--
66
--
67
CREATE TABLE lakes (
68
fid INTEGER NOT NULL PRIMARY KEY,
69
name VARCHAR(64)
70
);
71
SELECT AddGeometryColumn('lakes','shore','101','POLYGON','2');
72
--
73
--
74
--
75
--
76
--
77
CREATE TABLE road_segments (
78
fid INTEGER NOT NULL PRIMARY KEY,
79
name VARCHAR(64),
80
aliases VARCHAR(64),
81
num_lanes INTEGER
82
);
83
SELECT AddGeometryColumn('road_segments','centerline','101','LINESTRING','2');
84
--
85
--
86
--
87
--
88
--
89
CREATE TABLE divided_routes (
90
fid INTEGER NOT NULL PRIMARY KEY,
91
name VARCHAR(64),
92
num_lanes INTEGER
93
);
94
SELECT AddGeometryColumn('divided_routes','centerlines','101','MULTILINESTRING','2');
95
--
96
--
97
--
98
--
99
--
100
CREATE TABLE forests (
101
fid INTEGER NOT NULL PRIMARY KEY,
102
name VARCHAR(64)
103
);
104
SELECT AddGeometryColumn('forests','boundary','101','MULTIPOLYGON','2');
105
--
106
--
107
--
108
--
109
--
110
CREATE TABLE bridges (
111
fid INTEGER NOT NULL PRIMARY KEY,
112
name VARCHAR(64)
113
);
114
SELECT AddGeometryColumn('bridges','position','101','POINT','2');
115
--
116
--
117
--
118
--
119
--
120
CREATE TABLE streams (
121
fid INTEGER NOT NULL PRIMARY KEY,
122
name VARCHAR(64)
123
);
124
SELECT AddGeometryColumn('streams','centerline','101','LINESTRING','2');
125
--
126
--
127
--*** ADAPTATION ALERT ***
128
--
129
--
130
--
131
CREATE TABLE buildings (
132
fid INTEGER NOT NULL PRIMARY KEY,
133
address VARCHAR(64)
134
);
135
SELECT AddGeometryColumn('buildings','position','101','POINT','2');
136
SELECT AddGeometryColumn('buildings','footprint','101','POLYGON','2');
137
--
138
--
139
--
140
--
141
--
142
CREATE TABLE ponds (
143
fid INTEGER NOT NULL PRIMARY KEY,
144
name VARCHAR(64),
145
type VARCHAR(64)
146
);
147
SELECT AddGeometryColumn('ponds','shores','101','MULTIPOLYGON','2');
148
--
149
--
150
--
151
--
152
--
153
CREATE TABLE named_places (
154
fid INTEGER NOT NULL PRIMARY KEY,
155
name VARCHAR(64)
156
);
157
SELECT AddGeometryColumn('named_places','boundary','101','POLYGON','2');
158
--
159
--
160
--
161
--
162
--
163
CREATE TABLE map_neatlines (
164
fid INTEGER NOT NULL PRIMARY KEY
165
);
166
SELECT AddGeometryColumn('map_neatlines','neatline','101','POLYGON','2');
167
--
168
--
169
--
170
--//////////////////////////////////////////////////////////////////////////////
171
--
172
--
173
--
174
--//////////////////////////////////////////////////////////////////////////////
175
--
176
--==============================================================================
177
--
178
--==============================================================================
179
--
180
--
181
INSERT INTO lakes VALUES (101, 'Blue Lake',
182
PolygonFromText('POLYGON((52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18))', 101)
183
);
184
--
185
--==================
186
--
187
--
188
--==================
189
--
190
--
191
INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2,
192
LineStringFromText('LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101)
193
);
194
INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4,
195
LineStringFromText('LINESTRING( 44 31, 56 34, 70 38 )' ,101)
196
);
197
INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2,
198
LineStringFromText('LINESTRING( 70 38, 72 48 )' ,101)
199
);
200
INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4,
201
LineStringFromText('LINESTRING( 70 38, 84 42 )' ,101)
202
);
203
INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL, 1,
204
LineStringFromText('LINESTRING( 28 26, 28 0 )',101)
205
);
206
--
207
--==================
208
--
209
--
210
--==================
211
--
212
INSERT INTO divided_routes VALUES(119, 'Route 75', 4,
213
MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))', 101)
214
);
215
--
216
--==================
217
--
218
--
219
--==================
220
--
221
INSERT INTO forests VALUES(109, 'Green Forest',
222
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)))', 101)
223
);
224
--
225
--==================
226
--
227
--
228
--==================
229
--
230
INSERT INTO bridges VALUES(110, 'Cam Bridge',
231
PointFromText('POINT( 44 31 )', 101)
232
);
233
--
234
--==================
235
--
236
--
237
--==================
238
--
239
INSERT INTO streams VALUES(111, 'Cam Stream',
240
LineStringFromText('LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101)
241
);
242
INSERT INTO streams VALUES(112, NULL,
243
LineStringFromText('LINESTRING( 76 0, 78 4, 73 9 )', 101)
244
);
245
--
246
--==================
247
--
248
--
249
--==================
250
--
251
INSERT INTO buildings VALUES(113, '123 Main Street',
252
PointFromText('POINT( 52 30 )', 101),
253
PolygonFromText('POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 101)
254
);
255
INSERT INTO buildings VALUES(114, '215 Main Street',
256
PointFromText('POINT( 64 33 )', 101),
257
PolygonFromText('POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 101)
258
);
259
--
260
--==================
261
--
262
--
263
--==================
264
--
265
INSERT INTO ponds VALUES(120, NULL, 'Stock Pond',
266
MultiPolygonFromText('MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ), ( ( 26 44, 26 40, 28 42, 26 44) ) )', 101)
267
);
268
--
269
--==================
270
--
271
--
272
--==================
273
--
274
INSERT INTO named_places VALUES(117, 'Ashton',
275
PolygonFromText('POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101)
276
);
277
INSERT INTO named_places VALUES(118, 'Goose Island',
278
PolygonFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101)
279
);
280
--
281
--==================
282
--
283
--
284
--==================
285
--
286
INSERT INTO map_neatlines VALUES(115,
287
PolygonFromText('POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101)
288
);
289
--
290
--
291
--
Loggerhead is a web-based interface for Breezy
Version: 2.0.1