# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1584553350 -3600
# Node ID 737d7859dd865676e5adc626c6e3773b623f9a38
# Parent  0fedd50dbf52c42fb32a793f75bcf40c277f470c
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.

diff -r 0fedd50dbf52 -r 737d7859dd86 pkg/imports/fd.go
--- a/pkg/imports/fd.go	Wed Mar 18 18:10:19 2020 +0100
+++ b/pkg/imports/fd.go	Wed Mar 18 18:42:30 2020 +0100
@@ -160,14 +160,15 @@
   source_organization)
 SELECT dmp.geom, $3, $4, $5, $6, $7, $8
   FROM ST_GeomFromWKB($1, $2::integer) AS new_fd (new_fd),
-    ST_Dump(ST_CollectionExtract(ST_MakeValid(ST_Transform(
+    ST_Multi(ST_CollectionExtract(ST_MakeValid(ST_Transform(
       CASE WHEN pg_has_role('sys_admin', 'MEMBER')
         THEN new_fd
         ELSE ST_Intersection(
             (SELECT ST_Buffer(a, -0.0001) FROM resp),
             ST_MakeValid(ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp))))
         END,
-      4326)), 3)) AS dmp
+      4326)), 3)) AS dmp (geom)
+  WHERE NOT ST_IsEmpty(dmp.geom)
 RETURNING id,
   ST_X(ST_Centroid(area::geometry)),
   ST_Y(ST_Centroid(area::geometry))
diff -r 0fedd50dbf52 -r 737d7859dd86 schema/gemma.sql
--- a/schema/gemma.sql	Wed Mar 18 18:10:19 2020 +0100
+++ b/schema/gemma.sql	Wed Mar 18 18:42:30 2020 +0100
@@ -707,7 +707,7 @@
 
     CREATE TABLE fairway_dimensions (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        area geography(POLYGON, 4326) NOT NULL
+        area geography(MULTIPOLYGON, 4326) NOT NULL
             CHECK(ST_IsValid(CAST(area AS geometry))),
         level_of_service smallint NOT NULL REFERENCES levels_of_service,
         min_width smallint NOT NULL,
diff -r 0fedd50dbf52 -r 737d7859dd86 schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql
--- /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$;
diff -r 0fedd50dbf52 -r 737d7859dd86 schema/version.sql
--- a/schema/version.sql	Wed Mar 18 18:10:19 2020 +0100
+++ b/schema/version.sql	Wed Mar 18 18:42:30 2020 +0100
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1427);
+INSERT INTO gemma_schema_version(version) VALUES (1428);