[Mapserver-users] PostGIS and Mapscript subselects

[Paul Smith]

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