Mercurial > gemma

comparison schema/manage_users.sql @ 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 5b03f420957d
children 3f803d64a6ee
comparison
equal deleted inserted replaced
369:a43495c91856 370:fe87457a05d7
59 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger 59 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
60 AS $$ 60 AS $$
61 BEGIN 61 BEGIN
62 IF NEW.map_extent IS NULL 62 IF NEW.map_extent IS NULL
63 THEN 63 THEN
64 NEW.map_extent = ST_Extent(area) FROM users.responsibility_areas ra 64 NEW.map_extent = ST_Extent(CAST(area AS geometry))
65 FROM users.responsibility_areas ra
65 WHERE ra.country = NEW.country; 66 WHERE ra.country = NEW.country;
66 END IF; 67 END IF;
67 INSERT INTO internal.user_profiles ( 68 INSERT INTO internal.user_profiles (
68 username, country, map_extent, email_address) 69 username, country, map_extent, email_address)
69 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address); 70 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);