pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdate column based on postgis query on anther table
5+ messages / 5 participants
[nested] [flat]
* update column based on postgis query on anther table
@ 2013-07-16 02:53 Stefan Sylla <stefansylla@gmx.de>
0 siblings, 1 reply; 5+ messages in thread
From: Stefan Sylla @ 2013-07-16 02:53 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: update column based on postgis query on anther table
@ 2013-07-16 05:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stefan Sylla <stefansylla@gmx.de>
0 siblings, 2 replies; 5+ messages in thread
From: Tom Lane @ 2013-07-16 05:24 UTC (permalink / raw)
To: Stefan Sylla <stefansylla@gmx.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: update column based on postgis query on anther table
@ 2013-07-16 07:57 ssylla <stefansylla@gmx.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: ssylla @ 2013-07-16 07:57 UTC (permalink / raw)
To: pgsql-sql
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-tp57638...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: update column based on postgis query on anther table
@ 2013-07-16 13:45 Igor Neyman <ineyman@perceptron.com>
parent: ssylla <stefansylla@gmx.de>
0 siblings, 0 replies; 5+ messages in thread
From: Igor Neyman @ 2013-07-16 13:45 UTC (permalink / raw)
To: ssylla <stefansylla@gmx.de>; pgsql-sql
> -----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 values in 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: update column based on postgis query on anther table
@ 2013-07-17 09:58 Gulcin Yildirim <yildirim.gulcin88@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Gulcin Yildirim @ 2013-07-17 09:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stefan Sylla <stefansylla@gmx.de>; pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-07-17 09:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-16 02:53 update column based on postgis query on anther table Stefan Sylla <stefansylla@gmx.de>
2013-07-16 05:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-07-16 07:57 ` ssylla <stefansylla@gmx.de>
2013-07-16 13:45 ` Igor Neyman <ineyman@perceptron.com>
2013-07-17 09:58 ` Gulcin Yildirim <yildirim.gulcin88@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox