Mercurial > gemma
changeset 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 | 0fedd50dbf52 |
| children | 8c590ef35280 |
| files | pkg/imports/fd.go schema/gemma.sql schema/updates/1428/01.fairway_dimensions_as_multipolygon.sql schema/version.sql |
| diffstat | 4 files changed, 47 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- 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))
--- 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,
--- /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$;