[postgis-users] temporary geomtry table

[Jan Hartmann]

David Bitner wrote:

> Is it possible to have the output of a plpgsql function be a table?  I would
> like to create a table that contained a regular grid of polygons.
> 
> What I eventually want is to be able to have a dataset that could be used in
> mapserver that could be created by giving a function an extent and a grid
> size (ie each cell is 100 acres in area).  This temporary dataset would then
> be summarized with a line dataset. (ie crossings column in grid dataset had
> number of times the cell had been crossed by a line from the line dataset).
> 
> Thanks
> 

David,

A plpgsql function can return a table by setting its return type to 
"setof <some data type>". You can even generate computed maps! In the 
simplest case this is done by defining a user defined type consisting of 
an oid and a geometry:

--------------------------------------------------------------------
create type mapline as (
   oid oid,
   the_geom geometry,
);
--------------------------------------------------------------------

and then writing a function that returns a computed set of this type (in 
this case simple lines) :

---------------------------------------------------------------------
create or replace function mknet () returns setof mapline as '
declare
   ...
   x1 float;y1 float;x2 float;y2 float;
   numlines integer;
   oidcounter int;
   rc mapline;
begin
   oidcounter := 0;
   for i in 0..numlines loop
         oidcounter := oidcounter + 1;
      -- compute begin and end of line:
         x1 := ...
         y1 := ...
         x2 := ...
         y2 := ...
      -- and assign it to the return-variable
         rc.the_geom := geometryFromText
	(''LINESTRING(''
            || x1 || '' '' || y1 || '','' || x2 || '' '' || y2
            || '')'',
	28992);   -- 28992 is the SRID
         rc.oid = oidcounter;
         return next rc;
   end loop;
   return;
end
'
language plpgsql;

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

This function can be used as the DATA source in a MapFile layer just 
like a regular table:

DATA "the_geom from mknet() using SRID=28992

OF course this is not a real table, not even a temporary one, but 
MapServer does not see the difference. Note the parentheses and the fact 
that you need to specify the SRID explicitly

You can add more variables to the user defined type, and these are 
handled by MapServer just like regular table columns, e.g. for use in a 
CLASS expression

HTH,

Jan