Trigger on link length and filling attribute table

Question

I create linear geometry in QGIS 1.8.0 (geometry stored in PostGIS 2.0). Help create trigger functions:

1. 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"
2. 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();

Thanks!

Answer 1

I think CONSTRAINT would be better and faster way to do it

ALTER TABLE test.town
  ADD CONSTRAINT length
  CHECK (ST_Length_Spheroid(the_geom,'SPHEROID["WGS 84",6378137,298.257223563]') 

TRIGGER version

CREATE OR REPLACE FUNCTION check_geom_length() RETURNS trigger AS $$
BEGIN
IF (ST_Length_Spheroid(NEW.the_geom,'SPHEROID["WGS 84",6378137,298.257223563]') > 2) THEN
RAISE EXCEPTION 'feature % has wrong length', NEW.gid;
END IF;

RETURN NEW;
END; $$ LANGUAGE 'plpgsql';