Mercurial > gemma

annotate schema/updates/1428/01.fairway_dimensions_as_multipolygon.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
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5030
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- Cannot alter type of a column used in a policy definition
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 DROP POLICY responsibility_area_insert ON waterway.fairway_dimensions;
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 DROP POLICY responsibility_area_select ON waterway.fairway_dimensions;
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 DROP POLICY responsibility_area_update ON waterway.fairway_dimensions;
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 DROP POLICY responsibility_area_delete ON waterway.fairway_dimensions;
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 ALTER TABLE waterway.fairway_dimensions
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 ALTER area TYPE geography(MULTIPOLYGON, 4326)
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 USING ST_Multi(CAST(area AS geometry));
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- Re-create policies
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 DO LANGUAGE plpgsql
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 $do$
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 DECLARE
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 the_table CONSTANT varchar = 'fairway_dimensions';
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 condition CONSTANT text = $$
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a)))
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 FROM users.current_user_area_utm() AS a (a))
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 $$;
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 BEGIN
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 EXECUTE format($$
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 CREATE POLICY responsibility_area_insert ON waterway.%I
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 FOR INSERT TO waterway_admin
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 WITH CHECK (%s)
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 $$, the_table, condition);
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 EXECUTE format($$
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 CREATE POLICY responsibility_area_select ON waterway.%I
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 FOR SELECT TO waterway_admin
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 USING (staging_done OR %s)
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 $$, the_table, condition);
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 EXECUTE format($$
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 CREATE POLICY responsibility_area_update ON waterway.%I
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 FOR UPDATE TO waterway_admin
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 USING (%s)
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 $$, the_table, condition);
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 EXECUTE format($$
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 CREATE POLICY responsibility_area_delete ON waterway.%I
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 FOR DELETE TO waterway_admin
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 USING (%s)
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 $$, the_table, condition);
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 END;
737d7859dd86 Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 $do$;