[Mapserver-users] complex postgis queries in mapserver

[Pericles S. Nacionales]

Do you have a postgresql log that you can check for error messages?

Check that your user account have SELECT privileges on both tables. 
This is an oft-forgotten hint.

-Perry

On Tue, 2004-04-27 at 13:29, andreas.mueller at bfua.de wrote:
> 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
-- 
"He's no geek.His tan's too good." -Benjamin Choate