Mercurial > gemma
diff schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql @ 5030:737d7859dd86
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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql Wed Mar 18 18:42:30 2020 +0100 @@ -0,0 +1,42 @@ +-- Cannot alter type of a column used in a policy definition +DROP POLICY responsibility_area_insert ON waterway.fairway_dimensions; +DROP POLICY responsibility_area_select ON waterway.fairway_dimensions; +DROP POLICY responsibility_area_update ON waterway.fairway_dimensions; +DROP POLICY responsibility_area_delete ON waterway.fairway_dimensions; + +ALTER TABLE waterway.fairway_dimensions + ALTER area TYPE geography(MULTIPOLYGON, 4326) + USING ST_Multi(CAST(area AS geometry)); + +-- Re-create policies +DO LANGUAGE plpgsql +$do$ +DECLARE + the_table CONSTANT varchar = 'fairway_dimensions'; + condition CONSTANT text = $$ + (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a))) + FROM users.current_user_area_utm() AS a (a)) + $$; +BEGIN + EXECUTE format($$ + CREATE POLICY responsibility_area_insert ON waterway.%I + FOR INSERT TO waterway_admin + WITH CHECK (%s) + $$, the_table, condition); + EXECUTE format($$ + CREATE POLICY responsibility_area_select ON waterway.%I + FOR SELECT TO waterway_admin + USING (staging_done OR %s) + $$, the_table, condition); + EXECUTE format($$ + CREATE POLICY responsibility_area_update ON waterway.%I + FOR UPDATE TO waterway_admin + USING (%s) + $$, the_table, condition); + EXECUTE format($$ + CREATE POLICY responsibility_area_delete ON waterway.%I + FOR DELETE TO waterway_admin + USING (%s) + $$, the_table, condition); +END; +$do$;