Mercurial > gemma
changeset 2542:fc7d828695c9
Schema: Added caching schema containing the sounding differences.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Thu, 07 Mar 2019 15:55:25 +0100 |
| parents | 468c8dc796cf |
| children | a542045f28a6 |
| files | schema/auth.sql schema/gemma.sql |
| diffstat | 2 files changed, 36 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Thu Mar 07 15:53:12 2019 +0100 +++ b/schema/auth.sql Thu Mar 07 15:55:25 2019 +0100 @@ -25,8 +25,9 @@ -- -- Privileges for waterway_user -- -GRANT USAGE ON SCHEMA public, users, waterway, systemconf TO waterway_user; +GRANT USAGE ON SCHEMA public, users, waterway, systemconf, caching TO waterway_user; GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user; +GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA caching TO waterway_user; GRANT SELECT ON systemconf.feature_colours TO waterway_user; GRANT UPDATE (pw, map_extent, email_address) ON users.list_users TO waterway_user;
--- a/schema/gemma.sql Thu Mar 07 15:53:12 2019 +0100 +++ b/schema/gemma.sql Thu Mar 07 15:55:25 2019 +0100 @@ -755,4 +755,38 @@ $$ LANGUAGE plpgsql; +CREATE SCHEMA caching + + CREATE TABLE sounding_differences ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + minuend int NOT NULL REFERENCES waterway.sounding_results(id) + ON DELETE CASCADE, + subtrahend int NOT NULL REFERENCES waterway.sounding_results(id) + ON DELETE CASCADE, + UNIQUE (minuend, subtrahend) + ) + + CREATE TABLE sounding_differences_contour_lines ( + sounding_differences_id int NOT NULL REFERENCES sounding_differences(id) + ON DELETE CASCADE, + height numeric NOT NULL, + lines geography(multilinestring, 4326) NOT NULL, + PRIMARY KEY (sounding_differences_id, height) + ) +; + +CREATE VIEW waterway.sounding_differences AS SELECT + sd.id AS id, + bn.objnam AS objnam, + srm.date_info AS minuend, + srs.date_info AS subtrahend, + sdcl.height AS height, + sdcl.lines::geometry AS lines +FROM + caching.sounding_differences sd JOIN + caching.sounding_differences_contour_lines sdcl ON sd.id = sdcl.sounding_differences_id JOIN + waterway.sounding_results srm ON sd.minuend = srm.id JOIN + waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN + waterway.bottlenecks bn ON srm.bottleneck_id = bn.id; + COMMIT;