[Mapserver-users] postgis + coordinates in differents rows

[Fabien Brachere]

Hello,

I would like to draw points with Mapserver and Postgis.
My problem is that the points are not in a geometry column but in 2 rows
of a table like this:

my_table:
CREATE TABLE "foo_31" (
    "id_da_exploitant" SERIAL,
    "id" text,
    "val" text,
);

"id" is a string like: "aaaaaa/bbbbb/GEO_X"
or  "aaaaaa/bbbbb/GEO_Y".
val is the corresponding value of geographic coordinate (for X and Y).

I wrote a function which returns a geometry type:
language: pgsql
arguments: character varying, character varying
returns: geometry
 
DECLARE
query varchar;
test geometry;
row RECORD;

BEGIN
query:='select xy_to_point(cast(x as double precision),cast(y as double precision)) as value from (select A.val AS x, B.val AS y FROM ' || $1 || '  A, ' || $1 || ' B WHERE A.id=''' || $2 || 'GEO_X'' AND B.id=''' || $2 || 'GEO_Y'') AS foo;';

FOR row IN EXECUTE query LOOP
    test = row.value;
END LOOP;

return test;
END;

The 2 arguments of the function are:
#1: a table name (like foo_31)
#2: the beginning of id ( aaaaaa/bbbbb ).

When I call the function:
SELECT my_function('foo_31','aaaaaa/bbbbb/');
I've got the right answer:
SRID=-1;POINT(558552.25 1975365.75)

And now my question is: how can I draw this point with mapserver. I
tried to do something like this (in phpMapscript), but with no results:
$couche_da_exploitant->set("data","* from (select
my_function('foo_31','aaaaaa/bbbbb/')) AS foo");

If someone has a suggestion, it will be welcome.
Thanks

Fabien Brachere