[postgis-users] PostGIS functions inside triggers

[Eduin Yezid Carrillo Vega]

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