Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql-spatial.sql @ 5385:2da74705c29d
Scheme change: Building table is extended and got a lookup table for kind
| author | Andre Heinecke <aheinecke@intevation.de> |
|---|---|
| date | Fri, 22 Mar 2013 17:30:10 +0100 |
| parents | 7b35f2c070c3 |
| children | e65fee5ea98a |
comparison
equal
deleted
inserted
replaced
| 5384:5a42ca06b93e | 5385:2da74705c29d |
|---|---|
| 54 path VARCHAR(256) | 54 path VARCHAR(256) |
| 55 ); | 55 ); |
| 56 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2); | 56 SELECT AddGeometryColumn('cross_section_tracks', 'geom', 31467, 'LINESTRING', 2); |
| 57 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); | 57 ALTER TABLE cross_section_tracks ALTER COLUMN id SET DEFAULT NEXTVAL('CROSS_SECTION_TRACKS_ID_SEQ'); |
| 58 | 58 |
| 59 | 59 CREATE TABLE building_kinds( |
| 60 -- Geodaesie/Bauwerke/Wehre.shp | 60 id int PRIMARY KEY NOT NULL, |
| 61 name VARCHAR(64) | |
| 62 ); | |
| 63 INSERT INTO building_kinds(id, name) VALUES (0, 'Sonstige'); | |
| 64 INSERT INTO building_kinds(id, name) VALUES (1, 'Brücken'); | |
| 65 INSERT INTO building_kinds(id, name) VALUES (2, 'Wehre'); | |
| 66 INSERT INTO building_kinds(id, name) VALUES (3, 'Pegel'); | |
| 67 | |
| 68 -- Geodaesie/Bauwerke | |
| 61 CREATE SEQUENCE BUILDINGS_ID_SEQ; | 69 CREATE SEQUENCE BUILDINGS_ID_SEQ; |
| 62 CREATE TABLE buildings ( | 70 CREATE TABLE buildings ( |
| 63 id int PRIMARY KEY NOT NULL, | 71 id int PRIMARY KEY NOT NULL, |
| 64 river_id int REFERENCES rivers(id) ON DELETE CASCADE, | 72 river_id int REFERENCES rivers(id) ON DELETE CASCADE, |
| 65 name VARCHAR(256), | 73 description VARCHAR(256) -- Name taken from attributes, |
| 74 name VARCHAR(256), -- The layername | |
| 75 station FLOAT8, | |
| 76 kind_id int REFERENCES building_kinds(id) NOT NULL DEFAULT 0, | |
| 66 path VARCHAR(256) | 77 path VARCHAR(256) |
| 67 ); | 78 ); |
| 68 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); | 79 SELECT AddGeometryColumn('buildings', 'geom', 31467, 'LINESTRING', 2); |
| 69 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); | 80 ALTER TABLE buildings ALTER COLUMN id SET DEFAULT NEXTVAL('BUILDINGS_ID_SEQ'); |
| 70 | 81 |