[postgis-users] UPDATE a value using trigger and ST_Contains

[Bruce Rindahl]

I'm sorry - I only speak/read english but I think I know what you are
trying to do.

My table "tiles" is the same as your table t1 - a table of polygons.
My table "temp" is the same as your table t2 - a table of points.
Both tables have a field "rec" where the tile table has the unique rec i
want in the point table.

The following will first generate a geometry from the temp  X and Y
fields.  Then it will populate the correct rec value from the tile table.

------------------------------------------------------------------------------------------------------------
-- Trigger: make_geom_trigger on public.temp

-- DROP TRIGGER make_geom_trigger ON public.temp;

CREATE TRIGGER make_geom_trigger
  BEFORE INSERT
  ON public.temp
  FOR EACH ROW
  EXECUTE PROCEDURE public.make_geom();

------------------------------------------------------------------------------------------------------------

-- Function: public.make_geom()

-- DROP FUNCTION public.make_geom();

CREATE OR REPLACE FUNCTION public.make_geom()
  RETURNS trigger AS
$BODY$
BEGIN

 NEW.the_geom =  ST_SetSRID(ST_Point( NEW.x, NEW.y),2229);
 NEW.rec =  (select rec from tiles t where
st_intersects(NEW.the_geom,t.the_geom) limit 1);

return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.make_geom()
  OWNER TO postgres;
------------------------------------------------------------------------------------------------------------

Sorry if this doesn't follow the thread.  Not sure I am doing this right.
Bruce
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130222/4e6fe6bd/attachment.html>