Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql-spatial.sql @ 5291:66d9389c80cb
added tables for jetties in spatial schema
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 14 Mar 2013 12:16:58 +0100 |
| parents | 170f514a4f29 |
| children | 3dc4c2798212 |
comparison
equal
deleted
inserted
replaced
| 5285:05eaecd2be42 | 5291:66d9389c80cb |
|---|---|
| 110 CREATE TABLE dem ( | 110 CREATE TABLE dem ( |
| 111 id int PRIMARY KEY NOT NULL, | 111 id int PRIMARY KEY NOT NULL, |
| 112 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 112 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
| 113 -- XXX Should we use the ranges table instead? | 113 -- XXX Should we use the ranges table instead? |
| 114 name VARCHAR(64), | 114 name VARCHAR(64), |
| 115 range_id INT REFERENCES ranges(id); | 115 range_id INT REFERENCES ranges(id), |
| 116 time_interval_id INT REFERENCES time_intervals(id); | 116 time_interval_id INT REFERENCES time_intervals(id), |
| 117 projection VARCHAR(32), | 117 projection VARCHAR(32), |
| 118 srid int NOT NULL, | 118 srid int NOT NULL, |
| 119 elevation_state VARCHAR(32), | 119 elevation_state VARCHAR(32), |
| 120 format VARCHAR(32), | 120 format VARCHAR(32), |
| 121 border_break BOOLEAN NOT NULL DEFAULT FALSE, | 121 border_break BOOLEAN NOT NULL DEFAULT FALSE, |
| 298 path VARCHAR(256) | 298 path VARCHAR(256) |
| 299 ); | 299 ); |
| 300 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); | 300 SELECT AddGeometryColumn('gauge_location','geom',31467,'POINT',2); |
| 301 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); | 301 ALTER TABLE gauge_location ALTER COLUMN id SET DEFAULT NEXTVAL('GAUGE_LOCATION_ID_SEQ'); |
| 302 | 302 |
| 303 | |
| 304 CREATE TABLE jetty_kinds( | |
| 305 id int PRIMARY KEY NOT NULL, | |
| 306 name VARCHAR(64) | |
| 307 ); | |
| 308 INSERT INTO jetty_kinds VALUES (0, 'Buhnenkopf'); | |
| 309 INSERT INTO jetty_kinds VALUES (1, 'Buhnenfuß'); | |
| 310 INSERT INTO jetty_kinds VALUES (2, 'Buhnenwurzel'); | |
| 311 | |
| 312 CREATE SEQUENCE JETTIES_ID_SEQ; | |
| 313 CREATE TABLE jetties ( | |
| 314 id int PRIMARY KEY NOT NULL, | |
| 315 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | |
| 316 path VARCHAR(256), | |
| 317 kind_id int REFERENCES jetty_kinds(id), | |
| 318 km FLOAT8, | |
| 319 z FLOAT8 | |
| 320 ); | |
| 321 SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); | |
| 322 ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ'); | |
| 323 | |
| 324 | |
| 303 COMMIT; | 325 COMMIT; |