Mercurial > gemma

comparison pkg/imports/fd.go @ 2923:9bd98daf0b0f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Let sys_admin import fairway dimensions without restrictions
author Tom Gottfried <tom@intevation.de>
date Wed, 03 Apr 2019 17:19:26 +0200
parents 372dc8382389
children 4acbee65275d
comparison
equal deleted inserted replaced
2922:372dc8382389 2923:9bd98daf0b0f
140 deleteFairwayDimensionSQL = ` 140 deleteFairwayDimensionSQL = `
141 WITH resp AS ( 141 WITH resp AS (
142 SELECT users.current_user_area_utm() AS a 142 SELECT users.current_user_area_utm() AS a
143 ) 143 )
144 DELETE FROM waterway.fairway_dimensions 144 DELETE FROM waterway.fairway_dimensions
145 WHERE ST_Covers( 145 WHERE (pg_has_role('sys_admin', 'MEMBER')
146 (SELECT a FROM resp), 146 OR ST_Covers((SELECT a FROM resp),
147 ST_Transform(area::geometry, (SELECT ST_SRID(a) FROM resp))) 147 ST_Transform(area::geometry, (SELECT ST_SRID(a) FROM resp))))
148 AND staging_done 148 AND staging_done
149 AND level_of_service = $1::smallint` 149 AND level_of_service = $1::smallint`
150 150
151 // The ST_MakeValid and ST_Buffer below are a workarround to 151 // The ST_MakeValid and ST_Buffer below are a workarround to
152 // avoid errors due to reprojection. 152 // avoid errors due to reprojection.
153 insertFairwayDimensionSQL = ` 153 insertFairwayDimensionSQL = `
154 WITH resp AS ( 154 WITH resp AS (
155 SELECT users.current_user_area_utm() AS a 155 SELECT users.current_user_area_utm() AS a
156 ) 156 )
157 INSERT INTO waterway.fairway_dimensions (area, level_of_service, min_width, max_width, min_depth, date_info, source_organization) 157 INSERT INTO waterway.fairway_dimensions (
158 SELECT ST_Transform(clipped.geom, 4326)::geography, $3, $4, $5, $6, $7, $8 FROM ( 158 area,
159 SELECT (ST_Dump( 159 level_of_service,
160 ST_Intersection( 160 min_width,
161 (SELECT ST_Buffer(a, -0.0001) FROM resp), 161 max_width,
162 ST_MakeValid(ST_Transform( 162 min_depth,
163 ST_GeomFromWKB($1, $2::integer), 163 date_info,
164 (SELECT ST_SRID(a) FROM resp) 164 source_organization)
165 )) 165 SELECT ST_Transform(dmp.geom, 4326), $3, $4, $5, $6, $7, $8
166 ) 166 FROM ST_GeomFromWKB($1, $2::integer) AS new_fd (new_fd),
167 )).geom AS geom 167 ST_Dump(
168 ) AS clipped 168 CASE WHEN pg_has_role('sys_admin', 'MEMBER')
169 WHERE clipped.geom IS NOT NULL 169 THEN ST_MakeValid(ST_Transform(
170 new_fd, best_utm(ST_Transform(new_fd, 4326))))
171 ELSE ST_CollectionExtract(ST_Intersection(
172 (SELECT ST_Buffer(a, -0.0001) FROM resp),
173 ST_MakeValid(ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp)))),
174 3)
175 END) AS dmp
170 RETURNING id, 176 RETURNING id,
171 ST_X(ST_Centroid(area::geometry)), 177 ST_X(ST_Centroid(area::geometry)),
172 ST_Y(ST_Centroid(area::geometry)) 178 ST_Y(ST_Centroid(area::geometry))
173 ` 179 `
174 ) 180 )