[postgis-users] plpgsql - problem using variable schema and table names
Birgit Laggner
birgit.laggner at vti.bund.de
Wed Jan 6 00:54:06 PST 2010
Hi Kevin,
thank you for your detailled answer! You're right: I don't really need
the loops in this function. I just hoped to have a bit more of control
in case of errors and regarding the progress of the running function.
But with my test data sets your function with the simple sql-statement
is almost twice as fast as the (non-dynamic, but working) function with
the loops. Therefore, I did choose for the version you suggested.
Unfortunately, I have 2 other functions (which, together with the
discussed and some other functions, should be executed by a calling
function one after another), where I can't do it without these loops. I
managed that they are running, but I had to use so many workarounds,
that I am quite sure, the functions are very ineffective.
The reason, why I need the loops in these functions, is that I want to
compute the Difference of 2 data sets. That means, one row of the result
data set should consist of the remaining polygon of table_a resulting
from cutting away all overlapping areas with any polygons of table_b.
And because st_difference only compares one polygon at a time, I
normally get 3 different result polygons when there are 3 overlappings
for one polygon. To solve this, I let the st_difference loop until every
overlapping polygon is considered, and take the result of each loop as
an input for the next loop.
If you (or anybody else!!!) are interested: I copied the
difference-function below. Perhaps you can find some optimization trick
to improve performance...
Thanks again and a happy new year!
Birgit.
Here comes the script:
CREATE OR REPLACE FUNCTION _laggner_b_pgdifference_auto_a(schemaname
varchar(20), table_a varchar(50), a_id varchar(20), table_b varchar(50),
b_id varchar(20), diff_a varchar(60)) RETURNS void AS
$BODY$
DECLARE
recordset_object1 RECORD;
recordset_object2 RECORD;
i integer;
n integer;
j integer;
m integer;
tablename1 character(4);
sql_1 text;
sql_2 text;
sql_3 text;
sql_5 text;
r_1 record;
r_2 record;
r_aid record;
BEGIN
--4. Difference a:
i := 0;
n := 0;
sql_1 := 'select gid from
'||quote_ident(schemaname)||'.'||quote_ident(table_a);
for r_1 in execute sql_1 loop
n := n+1;
end loop;
tablename1 := 'tmp1';
execute
'create table
'||quote_ident(schemaname)||'.'||quote_ident(tablename1)||' (gid serial,
'||quote_ident(b_id)||' integer, the_geom geometry);';
execute
'create table tmp2 (aid integer, the_geom geometry);';
FOR i in 1..n LOOP --LOOP 1
RAISE NOTICE 'Beginn Difference für Tablle 1, Polygon % ', i;
sql_5 := 'SELECT '||quote_ident(a_id)||' as a_id from
'||quote_ident(schemaname)||'.'||quote_ident(table_a)||' as a where
a.gid='||i;
FOR r_aid in execute sql_5 loop end loop;
raise notice 'r_aid nach erster Zuweisung: % ', r_aid;
sql_3 := 'SELECT the_geom as the_geom from
'||quote_ident(schemaname)||'.'||quote_ident(table_a)||' as a where
a.gid='||i;
FOR recordset_object1 in execute sql_3 loop end loop;
raise notice 'recordset_object1 nach erster Zuweisung: % ',
recordset_object1;
execute
'truncate table '||quote_ident(schemaname)||'.tmp1;';
execute
'ALTER SEQUENCE '||quote_ident(schemaname)||'.tmp1_gid_seq RESTART WITH
1;';
EXECUTE
'INSERT INTO '||quote_ident(schemaname)||'.tmp1
('||quote_ident(b_id)||', the_geom)
SELECT b.'||quote_ident(b_id)||', b.the_geom
from '||quote_ident(schemaname)||'.'||quote_ident(table_a)||' a,
'||quote_ident(schemaname)||'.'||quote_ident(table_b)||' b
where a.gid='||i||' and
st_relate(a.the_geom, b.the_geom, ''2********'');';
m := 0;
sql_2 := 'select '||quote_ident(b_id)||' from
'||quote_ident(schemaname)||'.tmp1';
FOR r_2 in execute sql_2 loop
m := m+1;
end loop;
RAISE NOTICE 'Anzahl Intersection-Polygone: % ', m;
IF m > 0 THEN
j := 0;
FOR j in 1..m LOOP --LOOP 2
SELECT st_difference(recordset_object1.the_geom, tmp1.the_geom) as the_geom
FROM birgit.tmp1
INTO recordset_object2
WHERE tmp1.gid=j;
SELECT recordset_object2.the_geom INTO recordset_object1.the_geom;
RAISE NOTICE 'Intersection-Polygon % verarbeitet. ', j;
EXCEPTION
WHEN sqlstate 'XX000'
THEN --do nothing
END LOOP; --END LOOP 2
IF st_isempty(recordset_object1.the_geom)='f' then
INSERT INTO tmp2 (aid, the_geom)
VALUES (
r_aid.a_id,
recordset_object1.the_geom);
EXECUTE
'INSERT INTO '||quote_ident(schemaname)||'.'||quote_ident(diff_a)||'
('||quote_ident(a_id)||',
the_geom)
SELECT
aid,
the_geom
from tmp2;';
execute
'truncate table tmp2;';
END IF;
RAISE NOTICE 'Tabelle 1, Difference-Polygon % ist fertig. ', i ;
ELSE RAISE NOTICE 'Kein Difference berechnet. ';
END IF;
END LOOP; --END LOOP 1
execute
'drop table '||schemaname||'.tmp1;';
execute
'drop table tmp2;';
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION _laggner_b_pgdifference_auto_a(
schemaname varchar(20),
table_a varchar(50),
a_id varchar(20),
table_b varchar(50),
b_id varchar(20),
diff_a varchar(60))
OWNER TO postgres;
On 22.12.2009 19:46, Kevin Neufeld wrote:
> Hi Birgit,
>
> Hmmm, it looks like you have a few errors in your script.
> Comments inline.
>
>
> Birgit Laggner wrote:
>> Hi Kevin,
>>
>> for the defining of n, your suggestions works perfectly well. But in the
>> next step, I try to assign a record as an intersection of 2 tables. I
>> tried to solve this with a nested loop. But the problem is, after
>> assigning the records for table a and b, I can't address certain fields
>> within the records anymore, which would be necessary for the
>> intersection loop.
>>
>> By now, the part in question of my function looks like this (r_a, r_b
>> are declared as record, sql_a, sql_b are declared as text):
>>
>> sql_a := 'select gid, '||a_id||' as '||a_id||', the_geom from
>> '||quote_ident(schemaname)||'.'||quote_ident(table_a);
>
>
> I'm assuming, based on your original post, that 'a_id' is a parameter
> you wish to pass as a parameter to the function. You may want to
> quote_ident it as well to properly handle special characters, etc that
> may occur in the column name.
>
>>
>> raise debug '%', sql_a;
>> raise notice '%', sql_a;
>>
>> sql_b := 'select gid, '||b_id||' as '||b_id||', the_geom from
>> '||quote_ident(schemaname)||'.'||quote_ident(table_b);
>
> Same as for sql_a.
>
>>
>> raise debug '%', sql_b;
>>
>> FOR r_a in execute sql_a LOOP
>>
>> FOR r_b in execute sql_b LOOP
>>
>> FOR recordset_object IN execute 'select
>> '||quote_ident(r_a.a_id) ||',
>> '||quote_ident(r_b.b_id) ||',
>> ST_intersection('||quote_ident(r_a.the_geom)||',
>> '||quote_ident(r_b.the_geom)||') AS the_geom
>> WHERE st_intersects('||quote_ident(r_a.the_geom)||',
>> '||quote_ident(r_b.the_geom)||') and
>> '||quote_ident(r_a.the_geom)||' && '||quote_ident(r_b.the_geom)
>>
>
> Ok. Here r_a is referencing a field called a_id. So unless you've
> declared the text variable a_id to be 'a_id' (or passed it as a
> parameter with the same name), you'll get the error you see below. In
> the sql_a declaration above, change the select clause to 'select gid,
> ' || quote_ident(a_id) || ' AS a_id, the_geom from '... Similar for
> sql_b.
>
> Also, you are dereferencing the geometry fields which would be very
> slow. Your call to quote_ident(r_a.the_geom) is taking the geometry
> field in record r_a and casting the whole thing to text so it can be
> used in the textual execute statement.
>
>
>> LOOP
>>
>> execute
>> 'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',
>> ''||b_id||'', the_geom) '||
>> 'VALUES ( '||
>> ''||recordset_object||'.''||a_id||'', '||
>> ''||recordset_object||'.''||b_id||'', '||
>> ''||recordset_object||'.the_geom);';
>>
>> END LOOP;
>> END LOOP;
>> END LOOP;
>>
>> And the error message, I get, is:
>>
>> ERROR: Record »r_a« hat no field »a_id«
>>
>> Any ideas, how to handle this?
>>
>> Thanks a lot and merry christmas,
>>
>> Birgit.
>>
>
>
> This seems waaaay too complicated and I don't think passing the
> geometry around like that will work for you. If I may suggest, I
> think you could replace all three LOOPs with a single sql statement
> (logically, it looks like it should do the same thing):
>
> CREATE OR REPLACE FUNCTION _laggner_b_pgintersection(
> schemaname varchar(20),
> table_a varchar(50),
> a_id varchar(20),
> table_b varchar(50),
> b_id varchar(20),
> intersection varchar(60)) RETURNS void AS
> $BODY$
>
> BEGIN
> EXECUTE
> 'INSERT INTO ' ||
> quote_ident(schemaname) || '.' || quote_ident(intersection) ||
> '(a.' || quote_ident(a_id) || ', b.' || quote_ident(b_id)
> ||', ST_Intersection(a.the_geom, b.the_geom) ' ||
> 'FROM ' || quote_ident(schemaname) || '.' || quote_ident(table_a)
> || ' AS a, ' ||
> quote_ident(schemaname) || '.' || quote_ident(table_b)
> || ' AS b ' ||
> 'WHERE ST_Intersects(a.the_geom, b.the_geom)';
> END
> $BODY$
>
> The only thing that is quoted here are the schema, table, and column
> names. The query is parsed once by the query planner and then
> executed. We're not casting geometries to text and back into the
> database again. Is there a particular reason you broke out query into
> 3 main LOOPs?
>
> I hope this helps.
>
> You too, have a very Merry Christmas.
> Cheers,
> Kevin
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list