[Mapserver-users] complex postgis queries in mapserver

[andreas.mueller at bfua.de]

Hi,

although there has been a lot of mails about this problem I still don't figure it out.

When I enter a simple query:
 DATA "the_geom FROM ruhr"
I get a nice map.
But if I do a join on two tables in the same way as the documentation (and about 5 
million mails from this list) says, I get the well known error message:
------------------------------
msDrawMap(): Image handling error. Failed to draw layer named 'ruhr'.
prep_DB(): Query error. Error executing POSTGIS DECLARE (the actual query) 
statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT 
hp1::text,asbinary(force_collection(force_2d(x3x3)),'NDR'),oid::text from 
x3x3 WHERE x3x3 && setSRID('BOX3D(2510000 
5608433.33333333,2680000 5778566.66666667)'::BOX3D, -1 )'
and so on
-----------------------------
The subquery is fine for PostgreSQL. We have an alias for the result set. We use "oid" 
as UNIQUE and "SRID" as well.

The layer definition is as follows:
-----------------
LAYER
  NAME ruhr
  TYPE POLYGON
  CONNECTIONTYPE postgis
  CONNECTION "user=andy dbname=mygisdb host=localhost password=harry" 
  DATA "the_geom FROM (SELECT ruhr.oid AS oid, ruhr.the_geom AS the_geom, 
ruhr_small.hp1 AS hp1 FROM ruhr LEFT JOIN ruhr_small ON ruhr.gewkz = 
ruhr_small.gewkz AND ruhr.stat = ruhr_small.abschnitt ) AS test USING UNIQUE oid 
USING SRID=-1"
  STATUS DEFAULT
	CLASSITEM hp1
	CLASS
  		EXPRESSION "1"
...
-------------------------------------

My Mapserver version is running on my Win 2k desktop:
MapServer version 4.0.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP 
OUTPUT=PDF OUTPUT=SWF SUPPORTS=PROJ SUPPORTS=FREETYPE 
SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT 
SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT INPUT=EPPL7 
INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE

I'm running PostGIS 0.81 and PostgreSQL 7.4.1.3 within Cygwin.

Where is the mistake?

Thanks

Andreas


-- 
Dr. Andreas Müller
Medien und Umwelt
Breite Straße 21 			Rüttenscheider Straße 61 
53111 Bonn 				45130 Essen 
Tel.: 02 28.9 73 83 90 			Tel.: 02 01.79 49 41 
Fax: 02 28.9 73 83 91 			Fax: 02 01.79 49 42 
info at m-u-u.de				www.m-u-u.de