Thread: update column based on postgis query on anther table
Dear list,
This might be a postgis-specific question, but I could not get access to
the postgis mailing list so I will have a try here as my problem might
be related to SQL:
I need to update a column of a table based on a postgis-query function
that involves another table as follows:
Assuming I have the following two tables:
/* 1) point layer */
CREATE TABLE test1_point ( id serial PRIMARY KEY, id_test1_poly integer);
SELECT AddGeometryColumn('test1_point','the_geom',32648,'POINT',2);
INSERT INTO test1_point values ( 1,1,GeomFromText('POINT(91770.4424465354 2296241.06858129)',32648));
/* 2) polygon layer*/
CREATE TABLE test1_poly ( id serial PRIMARY KEY);
SELECT AddGeometryColumn('test1_poly','the_geom',32648,'POLYGON',2);
INSERT INTO test1_poly values ( 22,GeomFromText('POLYGON((91755.2765951597
2296254.99925063,91787.7961588885 2296240.64800429,91757.7034700958
2296227.19771158,91755.2765951597 2296254.99925063))',32648));
/**/
And I create the following function to get the value 'id' from
'test1_poly' table:
/**/
create function test1_point_get_id_test1_poly(integer) returns integer as 'select test1_poly.id from
test1_poly,test1_point where ST_Within( test1_point.the_geom,
test1_poly.the_geom) and test1_point.id=$1;'
language SQL returns null on null input;
/**/
This function works perfectly when I am using it manually like
/**/
select test1_point_get_id_test1_poly(1)
/**/
(returns '22', as the point from test1_point lies within the polygon of
test1_poly)
Now I want to use a trigger function to automatically update the column
'id_test1_poly' in tabel 'test1_point':
/**/
create or replace function test1_point_get_id_test1_poly() returns
trigger as $$ begin new.id_test1_poly=test1_point_get_id_test1_poly(new.id); return new; end;
$$
language plpgsql volatile;
-- create trigger for function:
create trigger test1_point_get_id_test1_poly after insert or update on test1_point for each row execute procedure
test1_point_get_id_test1_poly();
/**/
However, if I insert a new row into 'test1_point', the column
'id_test1_poly' remains empty, i.e. the function seems to return a null
value:
/**/
INSERT INTO test1_point (id,the_geom) values ( 2,GeomFromText('POINT(91759.2587143761 2296245.93565599)',32648));
select * from test1_point where id=2
--(returns: 2;;"0101000020887F000086AFB123F466F6405393C3F7DA844141")
Any ideas what is going wrong here? Thanks in advance for any help!
Stefan
Stefan Sylla <stefansylla@gmx.de> writes:
> Now I want to use a trigger function to automatically update the column
> 'id_test1_poly' in tabel 'test1_point':
> /**/
> create or replace function test1_point_get_id_test1_poly() returns
> trigger as $$
> begin
> new.id_test1_poly=test1_point_get_id_test1_poly(new.id);
> return new;
> end;
> $$
> language plpgsql volatile;
> -- create trigger for function:
> create trigger test1_point_get_id_test1_poly
> after insert or update on test1_point for each row execute procedure
> test1_point_get_id_test1_poly();
I think you need that to be a BEFORE insert or update trigger. In
an AFTER trigger, it's too late to affect the stored row.
regards, tom lane
Hi Tom, I tried changing the trigger to be BEFORE instead of AFTER: create trigger test1_point_get_id_test1_poly before insert or update on test1_point for each row execute procedure test1_point_get_id_test1_poly(); But the problem persits, the column id_test1_poly remains empty. Stefan -- View this message in context: http://postgresql.1045698.n5.nabble.com/update-column-based-on-postgis-query-on-anther-table-tp5763886p5763904.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
> -----Original Message----- > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] On Behalf Of ssylla > Sent: Tuesday, July 16, 2013 3:58 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] update column based on postgis query on anther table > > Hi Tom, > > I tried changing the trigger to be BEFORE instead of AFTER: > > create trigger test1_point_get_id_test1_poly > before insert or update on test1_point for each row execute procedure > test1_point_get_id_test1_poly(); > > But the problem persits, the column id_test1_poly remains empty. > > Stefan > > Stefan, Probably, you need to change something in your trigger logic, because like Tom stated it's too late to change NEW valuesin AFTER record, and in BEFORE trigger the record with NEW value doesn't exist yet, so: select test1_poly.id from test1_poly,test1_point where ST_Within( test1_point.the_geom, test1_poly.the_geom) and test1_point.id=$1; with $1 being NEW.id returns NULL (record with test1_point.id = NEW.id isn't written yet into test1_point table). May be this trigger function is what you need: create or replace function test1_point_get_id_test1_poly() returns trigger as $$ begin select test1_poly.id INTO new.id_test1_poly from test1_poly where ST_Within( NEW.the_geom, test1_poly.the_geom); return new; end; $$ language plpgsql volatile; Still there is an issue. What if your point falls within multiple polygons (multiple records in test1_poly satisfy your criteria)? In this case, select from test1_poly should return multiple records. This will break trigger function code. Regards, Igor Neyman
Hi Igor, thank you so much, the trigger function that you provided is exactly what I was looking for. I already read/heard about the SELECT INTO statement but I never actually understood what it is needed for. Here I go ;-) Stefan
Sent from my iPhone İ On 16 Tem 2013, îat 08:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Stefan Sylla <stefansylla@gmx.de> writes: >> Now I want to use a trigger function to automatically update the column >> 'id_test1_poly' in tabel 'test1_point': > >> /**/ >> create or replace function test1_point_get_id_test1_poly() returns >> trigger as $$ >> begin >> new.id_test1_poly=test1_point_get_id_test1_poly(new.id); >> return new; >> end; >> $$ >> language plpgsql volatile; >> -- create trigger for function: >> create trigger test1_point_get_id_test1_poly >> after insert or update on test1_point for each row execute procedure >> test1_point_get_id_test1_poly(); > > I think you need that to be a BEFORE insert or update trigger. In > an AFTER trigger, it's too late to affect the stored row. > > regards, tom lane > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql