[postgis-users] Joining tables in PostGis/MapServer

[Jan Hartmann]

Hello,

I posted this question on the MapServer list; sorry to repost it here, but I
really don't know if this is a bug or if I am doing something stupid.

A few months ago Dave Blasby described how to create maps in MapServer based
on SQL-queries (instead of regular tables).
See http://mapserver.gis.umn.edu/wilma/mapserver-users/0110/msg00283.html ).

I have MapServer 3.6 (April 30) running on both Windows 2000 and Linux.
PostGreSQL 7.2.1/Postgis 0.7.1 runs on the Linux machine. In both Windows
and Linux I can retrieve a map from a PostGis table (e.g. DATA "the_geom
from mymap") However, when I try to create a map with an SQL join query
(e.g. DATA "the_geom from (select the_geom, g97.oid, d97.sted from
gemeenten97 as g97,gemeenten97_data as d97 where g97.id = d97.id) as foo" I
get the following error message on both systems:

Warning: MapServer Error in msPOSTGISLayerWhichShapes(): Error executing
POSTGIS SQL statement (in FETCH ALL): DECLARE mycursor BINARY CURSOR FOR
SELECT
sted::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text
from (select the_geom,g97.oid,d97.sted from gemeenten97 as
g97,gemeenten97_data as d97 where g97.id=d97.id) as foo WHERE the_geom &&
setSRID('BOX3D(-12400 300000,307400 620000)'::BOX3D, find_srid('','(select
the_geom,g97.oid,d97.sted from gemeenten97 as g97,gemeenten97_data as d97
where g97.id=d97.id) as foo','the_geom') )

This looks as if the program fails when trying to get spatial reference
(SRID)-information from the query. I had a look in mappostgis.c in the
MapServer source-tree, and saw that queries could be done in both 0.5 and
0.6 format, i.e. with or without spatial reference system. Accordingly, I
removed the SRID-information from the PostGis table, and forced the
query-format in mappostgis.c to 0.5 (line 262). After that my maps turned
out fine.

Am I doing something wrong? I cannot put all my maps and data into one flat
table, so I need dynamic queries. Moreover, I would like to create maps
based on GIS-functions  (overlap, inside etc), and I don't see how to do
this without dynamic queries. Is it possible to do so without giving up all
spatial reference information?

Thanks in advance


Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl