Adding a trigger to get missing latitude and longitude fields filled.

SimonGoring · SimonGoring · commit 4ded3bcdcd14 · 2020-11-12T15:34:53.000-08:00
If Tilia adds a site with missing latitude and longitude, then this 
should get the fields added.
diff --git a/function/triggers/sitegeoms.sql b/function/triggers/sitegeoms.sql
@@ -0,0 +1,20 @@
+CREATE OR REPLACE FUNCTION ti.site_bounding() RETURNS TRIGGER
+AS $$
+BEGIN
+   IF NEW.latitudenorth IS NULL AND NEW.geom IS NOT NULL THEN
+   UPDATE ndb.sites
+   SET (longitudeeast, latitudenorth, longitudewest, latitudesouth) =
+     (ST_XMax(geog::geometry),
+      ST_YMax(geog::geometry),
+      ST_XMin(geog::geometry),
+      ST_YMin(geog::geometry));
+   END IF;
+   RETURN NEW;
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER updatelocations
+AFTER INSERT ON ndb.sites
+FOR EACH ROW
+EXECUTE PROCEDURE ti.site_bounding()
