[postgis-users] PostGIS functions inside triggers

[Eduin Yezid Carrillo Vega]

Thanks for your suggestions Ralf. Tried BEFORE and new. prefix with no
luck. Any other clue?

Eduin Yesid Carrillo Vega
Ingeniero Civil, Esp. Sistemas de Información Geográfica
Bogotá D.C., Colombia



On Sun, Feb 15, 2009 at 3:21 PM, Ralf Suhr <gisbox at web.de> wrote:
> Am Sonntag, 15. Februar 2009 21:01:36 schrieb Eduin Yezid Carrillo Vega
> <yecarrillo at gmail.com>
>
> I hope the following will help you, but I think the main problem is the design
> of your table.
> At first all postgis functions work in a trigger/function. Call the trigger
> BEFORE insert/update. Use the NEW. prefix for the values, that you wont to
> set.
>
> CREATE OR REPLACE FUNCTION table1_change()
> RETURNS trigger AS
> $BODY$
> DECLARE
>   a_row table2%ROWTYPE;
> BEGIN
>   IF tg_op = 'INSERT' THEN
>
>      NEW.the_geom := ST_GeomFromText(''POINT(new.lat new.lon)'',4326);
>
>    SELECT * FROM table2
>    INTO a_row
>    WHERE ST_Contains(table2.the_geom,  NEW.the_geom);
>
>    UPDATE table1 SET
>      table2_id = a_row.table2_id,
>      the_geom = NEW.the_geom
>      WHERE table1_id=new.table1_id;
>
>   END IF;
>   RETURN new;
>  END
>  $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>> Maybe I miss something, but ST_Contains isn't available inside this
>> trigger. It returns empty values trying to get which polygon in table2
>> contains a recently inserted point on table1. table1.the_geom value is
>> filled correctly but table1.table2_id value is null. Tried ST_Within
>> and ST_Intersects also.
>>
>>
>> CREATE TABLE table1
>> (
>>   table1_id serial NOT NULL,
>>   lon character varying(30),
>>   lat character varying(30),
>>   table2_id character varying(200),
>>   the_geom geometry,
>>   CONSTRAINT table1_pkey PRIMARY KEY (table1_id),
>>   CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
>>   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
>> 'POINT'::text OR the_geom IS NULL),
>>   CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326)
>> )
>> WITH (OIDS=FALSE);
>>
>> CREATE TRIGGER table1_change
>>   AFTER INSERT OR UPDATE OR DELETE
>>   ON table1
>>   FOR EACH ROW
>>   EXECUTE PROCEDURE table1_change();
>>
>> CREATE TABLE table2
>> (
>>   gid serial NOT NULL,
>>   table2_id character varying,
>>   the_geom geometry,
>>   CONSTRAINT table1_pkey PRIMARY KEY (table2_id),
>>   CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
>>   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
>> 'MULTIPOLYGON'::text OR the_geom IS NULL),
>>   CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326)
>> )
>> WITH (OIDS=FALSE);
>>
>>
>> CREATE OR REPLACE FUNCTION table1_change()
>>   RETURNS trigger AS
>> $BODY$
>> DECLARE
>>   the_point geometry;
>>   a_row table2%ROWTYPE;
>> BEGIN
>>   IF tg_op = 'INSERT' THEN
>>
>>      SELECT ST_GeomFromText('POINT(' || new.lat || ' ' || new.lon ||
>> ')',4326) INTO the_point;
>>
>>      SELECT * FROM table2 WHERE ST_Contains(table2.the_geom,
>> the_point) INTO a_row;
>>
>>      UPDATE table1 SET
>>         table2_id = a_row.table2_id,
>>         the_geom = the_point
>>         WHERE table1_id=new.table1_id;
>>
>>   END IF;
>>   RETURN new;
>> END
>> $BODY$
>>   LANGUAGE 'plpgsql' VOLATILE
>>   COST 100;
>> ALTER FUNCTION table1_change() OWNER TO postgres;
>>
>>
>> Triggered with:
>> INSERT INTO table1(table1_id, lon, lat)  VALUES
>> (999,-73.138583333333,7.0854783333333);
>>
>>
>> Eduin Yesid Carrillo Vega
>> Ingeniero Civil, Esp. Sistemas de Información Geográfica
>> Bogotá D.C., Colombia
>
> --
>
> Ralf
>
>
>