Mercurial > gemma

annotate schema/updates/1204/01.create-iso-areas.sql @ 5030:737d7859dd86

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Store fairway dimensions as MultiPolygon This avoids storing a single invalid geometry from the data source as multiple valid geometries with duplicate attribute sets. The previous behaviour was not correctly handled in import tracking, because only the ID of the first item in a set of multiple geometries generated from a single entry from the data source was tracked.
author Tom Gottfried <tom@intevation.de>
date Wed, 18 Mar 2020 18:42:30 +0100
parents 26e9846ed69f
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4566
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 CREATE TABLE waterway.sounding_results_iso_areas (
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 sounding_result_id int NOT NULL REFERENCES waterway.sounding_results
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 ON DELETE CASCADE,
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 height numeric NOT NULL,
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 areas geography(MULTIPOLYGON, 4326) NOT NULL,
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 -- TODO: generate valid simple features and add constraint:
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 -- CHECK(ST_IsSimple(CAST(areas AS geometry))),
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 PRIMARY KEY (sounding_result_id, height)
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 );
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10
4573
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
11 CREATE TABLE caching.sounding_differences_iso_areas (
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
12 sounding_differences_id int NOT NULL REFERENCES caching.sounding_differences(id)
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
13 ON DELETE CASCADE,
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
14 height numeric NOT NULL,
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
15 areas geography(MULTIPOLYGON, 4326) NOT NULL,
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
16 PRIMARY KEY (sounding_differences_id, height)
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
17 );
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
18
4566
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
19 GRANT INSERT, UPDATE, DELETE ON waterway.sounding_results_iso_areas
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 TO waterway_admin;
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 GRANT SELECT ON waterway.sounding_results_iso_areas
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23 TO waterway_user;
2c49a293f275 Fixed access problem for waterway_admins and sys_admins.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24
4573
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
25 GRANT SELECT, UPDATE, DELETE, INSERT ON caching.sounding_differences_iso_areas
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
26 TO waterway_user;
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4566
diff changeset
27