Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question
    
why? my own experience from utility networks is that you really want to have long as possible linestrings (until you get to intersection or some attribute changes). –  simplexio Dec 11 '12 at 10:18
    
@simplexio, for my data exclusion linear features less 2 meters is necessary –  HasT Dec 11 '12 at 10:54
add comment

1 Answer 1

up vote 2 down vote accepted
+50

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';
share|improve this answer
    
Thanks! only "> 2" change on "< 2" –  HasT Dec 12 '12 at 16:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.