[postgis-users] View PostGIS layer in MapServer. (Was: Load of [....])

[William C. Rowden]

On 2003-05-07T11:55:55 +0000, William C. Rowden wrote:
> (<http://postgis.refractions.net/windows/WindowsHowTo.html>).
> 
> I couldn't, however, find the "great_lakes.shp" sample, nor was
> modifying the itasca demo successful:
> 
> 	msPOSTGISLayerWhichShapes(): Query error. prep_DB:Error
> 	executing POSTGIS DECLARE statement (0.6 failed - retried 0.5
> 	and it failed too). DECLARE mycursor BINARY CURSOR FOR SELECT
> 	asbinary(force_collection(force_2d()),'NDR'),OID::text from
> 	WHERE && setSRID('BOX3D(388107.634400379
> 	5200301.16644403,500896.339019834 5313063.02375446)'::BOX3D,
> 	find_srid('','','') )

Again I can answer my own question.  I had an error in the MapFile.  I
ran the query in PostgreSQL directly:

	SELECT asbinary(force_collection(force_2d()),'NDR'),OID::text
	from WHERE && setSRID('BOX3D(388107.634400379
	5200301.16644403,500896.339019834 5313063.02375446)'::BOX3D,
	find_srid('','','') );

Obviously, this is missing the table name after the "from" and before
the "WHERE":

	ERROR:  parser: parse error at or near "WHERE" at character 84

The answer is RTFM (in this case,
<http://mapserver.gis.umn.edu/doc36/mapfile-reference.html#layer>).
The DATA parameter for PostGIS should be in the form of "<columnname>
from <tablename>".

Those wishing to modify the Itasca demonstration to display a layer
from a PostgreSQL database via PostGIS could use the steps in
"WindowsHowTo.html".  The following illustrates the necessary changes:

	shp2pgsql lakespy2.shp lakespy2 myGISdb > lakespy2.sql
	psql -h localhost -U postgres -d myGISdb -f lakespy2.sql

	diff demo.map demo.map.orig
	187,188d186
	<   CONNECTIONTYPE postgis
	<   CONNECTION "user=postgres dbname=myGISdb host=localhost"
	191c189
	<   DATA "the_geom from lakespy2"
	---
	>   DATA lakespy2	
-- 
     -William