Mercurial > gemma

changeset 370:fe87457a05d7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Store spatial data as geography The main coordinate system should be lat/lon. Just using geometry with SRS 4326 will nonetheless let PostGIS consider coordinates as Cartesian, leading to nonesense results in some calculations. As there is no ST_Within for geography, replace with ST_CoveredBy, which should more correct anyhow, because we now accept also data on the boundary of the responsibility area.
author Tom Gottfried <tom@intevation.de>
date Thu, 09 Aug 2018 15:45:16 +0200
parents a43495c91856
children d5b707bbd1d5
files schema/auth.sql schema/gemma.sql schema/manage_users.sql
diffstat 3 files changed, 13 insertions(+), 13 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth.sql	Thu Aug 09 15:50:46 2018 +0200
+++ b/schema/auth.sql	Thu Aug 09 15:45:16 2018 +0200
@@ -91,11 +91,11 @@
 -- TODO: add all relevant tables here
 CREATE POLICY responsibility_area ON waterway.bottlenecks
     FOR ALL TO waterway_admin
-    USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
+    USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas
         WHERE country = users.current_user_country())));
 CREATE POLICY responsibility_area ON waterway.sounding_results
     FOR ALL TO waterway_admin
-    USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
+    USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas
         WHERE country = users.current_user_country())));
 
 COMMIT;
--- a/schema/gemma.sql	Thu Aug 09 15:50:46 2018 +0200
+++ b/schema/gemma.sql	Thu Aug 09 15:45:16 2018 +0200
@@ -143,8 +143,7 @@
 CREATE SCHEMA users
     CREATE TABLE responsibility_areas (
         country char(2) PRIMARY KEY REFERENCES countries,
-        area geometry(MULTIPOLYGON, 4326)
-        --XXX: Should be geography (elsewhere too)
+        area geography(MULTIPOLYGON, 4326)
     )
 
     CREATE TABLE templates (
@@ -198,7 +197,7 @@
     --)
 
     CREATE TABLE waterway_area (
-        area geometry(POLYGON, 4326) PRIMARY KEY,
+        area geography(POLYGON, 4326) PRIMARY KEY,
         catccl smallint REFERENCES catccls,
         dirimp smallint REFERENCES dirimps
     )
@@ -208,7 +207,7 @@
         function_code varchar(10) NOT NULL, -- XXX: What is this really for?
         objname varchar NOT NULL,
         is_left boolean, -- XXX: Or reference position_codes?
-        geom geometry(POINT, 4326) NOT NULL,
+        geom geography(POINT, 4326) NOT NULL,
         applicability isrsrange,
         validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy
         -- pasted text from a more general specification is given
@@ -259,7 +258,7 @@
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE waterway_axis (
-        wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
+        wtwaxs geography(LINESTRING, 4326) PRIMARY KEY,
         -- TODO: Do we need to check data set quality (DRC 2.1.6)?
         objnam varchar NOT NULL,
         nobjnam varchar
@@ -270,7 +269,7 @@
     -- distance marks along waterway axis (see SUC7).
     CREATE TABLE distance_marks (
         location_code isrs PRIMARY KEY,
-        geom geometry(POINT, 4326) NOT NULL,
+        geom geography(POINT, 4326) NOT NULL,
         distance_mark_function varchar(8)
             NOT NULL REFERENCES distance_mark_functions,
         -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
@@ -314,7 +313,7 @@
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE fairway_dimensions (
-        area geometry(POLYGON, 4326) PRIMARY KEY,
+        area geography(POLYGON, 4326) PRIMARY KEY,
         level_of_service smallint NOT NULL REFERENCES levels_of_service,
         min_width smallint NOT NULL,
         max_width smallint NOT NULL,
@@ -340,7 +339,7 @@
         objnam varchar,
         nobjnm varchar,
         stretch isrsrange NOT NULL,
-        area geometry(POLYGON, 4326) NOT NULL,
+        area geography(POLYGON, 4326) NOT NULL,
         rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
         lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
         responsible_country char(2) NOT NULL REFERENCES countries,
@@ -373,7 +372,7 @@
         bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
         date_info date NOT NULL,
         PRIMARY KEY (bottleneck_id, date_info),
-        area geometry(POLYGON, 4326) NOT NULL,
+        area geography(POLYGON, 4326) NOT NULL,
         surtyp varchar NOT NULL REFERENCES survey_types,
         coverage varchar REFERENCES coverage_types,
         depth_reference char(3) NOT NULL REFERENCES depth_references,
@@ -408,7 +407,7 @@
         fairway_radius int,
         CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius)
             IS NOT NULL),
-        shallowest_spot geometry(POINT, 4326)
+        shallowest_spot geography(POINT, 4326)
     )
 
     CREATE TABLE bottleneck_pdfs (
--- a/schema/manage_users.sql	Thu Aug 09 15:50:46 2018 +0200
+++ b/schema/manage_users.sql	Thu Aug 09 15:45:16 2018 +0200
@@ -61,7 +61,8 @@
 BEGIN
     IF NEW.map_extent IS NULL
     THEN
-        NEW.map_extent = ST_Extent(area) FROM users.responsibility_areas ra
+        NEW.map_extent = ST_Extent(CAST(area AS geometry))
+            FROM users.responsibility_areas ra
             WHERE ra.country = NEW.country;
     END IF;
     INSERT INTO internal.user_profiles (