Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql.sql @ 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 | 8076f6a689d0 |
| children | 2b4de678e29a |
comparison
equal
deleted
inserted
replaced
| 1191:1557cccb88f6 | 1192:5f8444df19e4 |
|---|---|
| 258 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id | 258 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id |
| 259 JOIN ranges r ON wqr.range_id = r.id | 259 JOIN ranges r ON wqr.range_id = r.id |
| 260 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id | 260 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id |
| 261 ORDER BY wc.position, wcqr.wst_column_id, r.a; | 261 ORDER BY wc.position, wcqr.wst_column_id, r.a; |
| 262 | 262 |
| 263 -- data for the cross-sections | |
| 264 | |
| 265 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; | |
| 266 | |
| 267 CREATE TABLE cross_sections ( | |
| 268 id int PRIMARY KEY NOT NULL, | |
| 269 km NUMERIC NOT NULL, | |
| 270 river_id int NOT NULL REFERENCES rivers(id), | |
| 271 time_interval_id int REFERENCES time_intervals(id), | |
| 272 UNIQUE (km, river_id) -- XXX: Maybe too hard? | |
| 273 ); | |
| 274 | |
| 275 -- TODO: Use a geometry column in cross_section_points | |
| 276 | |
| 277 CREATE SEQUENCE POINTS3D_ID_SEQ; | |
| 278 | |
| 279 CREATE TABLE points3d ( | |
| 280 id int PRIMARY KEY NOT NULL, | |
| 281 x NUMERIC NOT NULL, | |
| 282 y NUMERIC NOT NULL, | |
| 283 z NUMERIC NOT NULL | |
| 284 ); | |
| 285 | |
| 286 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; | |
| 287 | |
| 288 CREATE TABLE cross_section_points ( | |
| 289 id int PRIMARY KEY NOT NULL, | |
| 290 cross_section_id int NOT NULL REFERENCES cross_sections(id), | |
| 291 point3d_id int NOT NULL REFERENCES points3d(id), | |
| 292 col_pos int NOT NULL, | |
| 293 UNIQUE (cross_section_id, point3d_id, col_pos), | |
| 294 UNIQUE (cross_section_id, col_pos) | |
| 295 ); | |
| 296 | |
| 263 COMMIT; | 297 COMMIT; |