-
-
Save anuzcan/9cfd6c702d9e4f3048b992907cfd4cb0 to your computer and use it in GitHub Desktop.
postgis trigger automatic calc area
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- CREATE NEW SCHEMA | |
| CREATE SCHEMA fields; | |
| -- ADD POSTGIS TABLE | |
| CREATE TABLE fields.parcelas ( | |
| id serial primary key, | |
| geom geometry(polygon, 4326), | |
| --geom geometry(polygon, 32616), | |
| centre_northing decimal, | |
| centre_easting decimal, | |
| centre_latitude decimal, | |
| centre_longitude decimal, | |
| latlong_dms varchar, | |
| area_ha decimal, | |
| date_created timestamp with time zone DEFAULT now(), | |
| created_by character varying DEFAULT "current_user"(), | |
| date_modified timestamp with time zone, | |
| modified_by character varying | |
| ); | |
| -- ADD FUNCTION TRIGGER | |
| CREATE OR REPLACE FUNCTION fields.trigger_function() RETURNS trigger AS $$ | |
| BEGIN | |
| IF (TG_OP = 'INSERT') THEN | |
| NEW.centre_northing = st_y(st_centroid(NEW.geom)); | |
| NEW.centre_easting =st_x(st_centroid(NEW.geom)); | |
| NEW.centre_latitude = st_y(st_centroid(st_transform(NEW.geom,4326))); | |
| NEW.centre_longitude = st_x(st_centroid(st_transform(NEW.geom,4326))); | |
| NEW.latlong_dms = ST_AsLatLonText(st_centroid(st_transform(NEW.geom,4326))); | |
| NEW.area_ha = ROUND ((st_area(NEW.geom::geography)/10000)::numeric,2) ; | |
| --NEW.area_ha = ROUND ((st_area(NEW.geom)/10000)::numeric,2) ; | |
| NEW.date_modified = now(); | |
| NEW.modified_by = "current_user"(); | |
| RETURN NEW; | |
| ELSIF (TG_OP = 'UPDATE') THEN | |
| IF NOT (NEW.geom ~= OLD.geom) THEN | |
| NEW.centre_northing = st_y(st_centroid(NEW.geom)); | |
| NEW.centre_easting =st_x(st_centroid(NEW.geom)); | |
| NEW.centre_latitude = st_y(st_centroid(st_transform(NEW.geom,4326))); | |
| NEW.centre_longitude = st_x(st_centroid(st_transform(NEW.geom,4326))); | |
| NEW.latlong_dms = ST_AsLatLonText(st_centroid(st_transform(NEW.geom,4326))); | |
| NEW.area_ha = ROUND ((st_area(NEW.geom::geography)/10000)::numeric,2) ; | |
| --NEW.area_ha = ROUND ((st_area(NEW.geom)/10000)::numeric,2) ; | |
| NEW.date_modified = now(); | |
| END IF; | |
| RETURN NEW; | |
| END IF; | |
| END; | |
| $$ language plpgsql; | |
| -- ADD TRIGGER | |
| CREATE TRIGGER trigger_funcions | |
| BEFORE INSERT OR UPDATE | |
| ON fields.parcelas | |
| FOR EACH ROW | |
| EXECUTE PROCEDURE fields.trigger_function(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment