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.

The trigger below don't works and I need that when a new pipeline (adutoras) is inserted or updated, the procedure check what are the municipalities (municipios) that intersects the pipes and put the names of the municipalities inside the field "municipios_abastecidos".

CREATE OR REPLACE FUNCTION topology_fields() 
RETURNS "trigger" AS
$$
BEGIN
NEW.municipios_abastecidos:=(
                      SELECT string_agg(municipios.nome,', ')
                      FROM municipios, adutoras
                      WHERE ST_Intersects(adutoras.geom, municipios.geom)
                      AND adutoras.gid = NEW.gid
                    );
RETURN NEW;
END; 
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER fill_topology_fields
BEFORE INSERT OR UPDATE ON adutoras
FOR EACH ROW EXECUTE PROCEDURE topology_fields();

Thanks in Advance

share|improve this question

1 Answer 1

Solved,

CREATE OR REPLACE FUNCTION topology_fields()
RETURNS trigger AS
$$
BEGIN

SELECT string_agg(municipios.nome,', ')
INTO NEW.municipios_abastecidos
FROM municipios
WHERE ST_Intersects(NEW.geom, municipios.geom);

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

CREATE TRIGGER fill_topology_fields
BEFORE INSERT OR UPDATE ON adutoras
FOR EACH ROW EXECUTE PROCEDURE topology_fields();

Appreciate the help from PostGIS-User List, especially Ralf Suhr and Tom Glancy, thank you so much!

share|improve this answer

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.