[Mapserver-users] PostGIS and Mapscript subselects

[Jan Hartmann]

Paul,

I don't think it's a bug. The following works with me:

$map = ms_newMapObj("mapfile");
$layer = ms_newLayerobj($map);
$layer->set("type",MS_LAYER_POLYGON);
$layer->set("status",MS_ON);
$layer->set("connectiontype",MS_POSTGIS);
$layer->set("connection","host= port= user= password= dbname=);
$layer->set("data","the_geom from (select oid,the_geom from gem) as foo");
$img = $map->draw();
$img->saveImage("",MS_PNG,1,0,0);

Don't know what is wrong on you side though; everything looks correct. 
You could try using an existing layer in the mapfile with only the DATA 
statement set by PHP. This will give you almost as much flexibility as 
creating the whole layer on the fly.

Jan Hartmann

Paul Smith wrote:
> Hi,
> 
> FreeBSD 4.6, PHP 4.2.3, PostGIS 0.7.3, Mapserver 3.6.3 w/ latest CVS 
> mappostgis.c
> 
> It seems as though PostGIS subselects only work for me if the Layer 
> definition is in the .map file, which I'd rather not use, instead of in 
> PHP. I've combed through the lists and Google, and I can't seem to find 
> a previous mention of this bug.
> 
> Here's my DATA statement:
> 
>     the_geom from (select the_geom, oid from grid_streets)
>     as foo using unique oid using SRID=-1
> 
> (By the way, a plain, non-subselect DATA statement like:
> 
>     the_geom from grid_streets
> 
> works fine)
> 
> If this is in the .map file, ala:
> 
>     LAYER
>         [ ... ]
>            DATA "the_geom from (select the_geom, oid from grid_streets)
>                 as foo using unique oid using SRID=-1"
>           [ ... ]
>     END
> 
> it's fine. If it's done in PHP, ala:
> 
>     $grid_streets = ms_newLayerObj($map);
>     [ ... ]
>     $dataSql = "the_geom from (select the_geom, oid from grid_streets)
>                 as foo using using oid using SRID=-1";
>     $grid_streets->set('data', $dataSql);
>     [ ... ]
> 
> then I get the following error:
> 
> Warning: MapServer Error in prep_DB(): Error executing POSTGIS DECLARE 
> (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT 
> street::text,asbinary(force_collection(force_2d()),'NDR'),oid::text 
> from #3¸ WHERE && setSRID('BOX3D(-87.6720208305802 
> 41.8238094568678,-87.5728089028288 41.9230213846192)'::BOX3D, -1 )'
> 
> Postgresql reports the error 'ERROR: parser: parse error at or near "#" '
> 
> More Help:
> 
> Error with POSTGIS data variable. You specified '<check your .map file>'.
> Standard ways of specifiying are :
> (1) 'geometry_column from geometry_table'
> (2) 'geometry_column from (<sub query>) as foo using unique <column 
> name> using SRID=<srid#>'
> 
> Make sure you put in the 'using unique <column name>' and 'using SRID=#' 
> clauses in.
> 
> For more help, please see http://postgis.refractions.net/documentation.php
> 
> Mappostgis.c - version of Nov 15/2002. in 
> /usr/local/www/sites/mapserver.cnt.org/docs/index-3-6-3.php on line 547
> 
> Fatal error: Call to a member function on a non-object in 
> /usr/local/www/sites/mapserver.cnt.org/docs/index-3-6-3.php on line 548
> 
> ------------
> 
> Thanks for any help.
> 
> Paul
> 
> -- 
> Paul Smith <paul at cnt.org>
> Center for Neighborhood Technology
> Chicago, IL USA
> 
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> 

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