I create linear geometry in QGIS 1.8.0 (geometry stored in PostGIS 2.0). Help create trigger functions:
- in database should not be a lines less than 2 meters (linear geometry with length <2 meters should be corrected in the process of creating the geometry) - if I created linear feature with length <2 meters I receive message "feature "gid" have wrong length"
- auto fill Length column in attribute table, when new linear objects added
For calculating length I use
ST_Length_Spheroid(r.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]') function.
So, example trigger function for intersecting polygons below:
CREATE OR REPLACE FUNCTION check_geom_overlap() RETURNS trigger AS $$ DECLARE gid integer; BEGIN SELECT INTO gid c.gid FROM town c WHERE c.gid <> NEW.gid AND ST_Overlaps(c.the_geom, NEW.the_geom); IF gid IS NOT NULL THEN RAISE EXCEPTION 'Modified feature % overlaps with feature %!', NEW.gid, gid; END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER check_geom_overlap_trigger BEFORE INSERT OR UPDATE ON town FOR EACH ROW EXECUTE PROCEDURE check_geom_overlap();