Mercurial > dive4elements > river
changeset 1192:5f8444df19e4
Add relations for cross sections.
flys-backend/trunk@2287 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Wed, 06 Jul 2011 10:23:45 +0000 |
| parents | 1557cccb88f6 |
| children | 1e3979e07b9e |
| files | flys-backend/ChangeLog flys-backend/doc/schema/postgresql.sql |
| diffstat | 2 files changed, 69 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/ChangeLog Tue Jun 28 07:57:43 2011 +0000 +++ b/flys-backend/ChangeLog Wed Jul 06 10:23:45 2011 +0000 @@ -1,3 +1,38 @@ +2011-07-06 Sascha L. Teichmann <sascha.teichmann@intevation.de> + + * doc/schema/postgresql.sql: Add relations for cross sections. + To update existing databases: + BEGIN; + + CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; + CREATE TABLE cross_sections ( + id int PRIMARY KEY NOT NULL, + km NUMERIC NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + time_interval_id int REFERENCES time_intervals(id), + UNIQUE (km, river_id) + ); + + CREATE SEQUENCE POINTS3D_ID_SEQ; + CREATE TABLE points3d ( + id int PRIMARY KEY NOT NULL, + x NUMERIC NOT NULL, + y NUMERIC NOT NULL, + z NUMERIC NOT NULL + ); + + CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; + CREATE TABLE cross_section_points ( + id int PRIMARY KEY NOT NULL, + cross_section_id int NOT NULL REFERENCES cross_sections(id), + point3d_id int NOT NULL REFERENCES points3d(id), + col_pos int NOT NULL, + UNIQUE (cross_section_id, point3d_id, col_pos), + UNIQUE (cross_section_id, col_pos) + ); + + COMMIT; + 2011-06-28 Ingo Weinzierl <ingo@intevation.de> Tagged RELEASE 2.4
--- a/flys-backend/doc/schema/postgresql.sql Tue Jun 28 07:57:43 2011 +0000 +++ b/flys-backend/doc/schema/postgresql.sql Wed Jul 06 10:23:45 2011 +0000 @@ -260,4 +260,38 @@ JOIN wst_columns wc ON wcqr.wst_column_id = wc.id ORDER BY wc.position, wcqr.wst_column_id, r.a; +-- data for the cross-sections + +CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; + +CREATE TABLE cross_sections ( + id int PRIMARY KEY NOT NULL, + km NUMERIC NOT NULL, + river_id int NOT NULL REFERENCES rivers(id), + time_interval_id int REFERENCES time_intervals(id), + UNIQUE (km, river_id) -- XXX: Maybe too hard? +); + +-- TODO: Use a geometry column in cross_section_points + +CREATE SEQUENCE POINTS3D_ID_SEQ; + +CREATE TABLE points3d ( + id int PRIMARY KEY NOT NULL, + x NUMERIC NOT NULL, + y NUMERIC NOT NULL, + z NUMERIC NOT NULL +); + +CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; + +CREATE TABLE cross_section_points ( + id int PRIMARY KEY NOT NULL, + cross_section_id int NOT NULL REFERENCES cross_sections(id), + point3d_id int NOT NULL REFERENCES points3d(id), + col_pos int NOT NULL, + UNIQUE (cross_section_id, point3d_id, col_pos), + UNIQUE (cross_section_id, col_pos) +); + COMMIT;