[mapserver-users] Postgres/Postgis additonal variable request

[Heiko Schroeter]

Hello,

i need to select data from an array inside a table. I cannot get mapserver to 
create the correct select request statement.
The problem i recognize is that the "unique" key is inserted in the Mapserver 
select request as ".... key from , ....".

Basically i need something like this:
select coords as geom, color from produkt_db using unique date using srid=-1

What do i have to do to request additional values in the mapserver DATA Line 
besides the "geom" and the "unique key" variables which are inserted 
automatically ?

Thanks and Regards
Heiko


The Mapserver Postgres Docs says this:
 ..... The form of DATA is “[geometry_column] from [table_name|sql_subquery] 
using unique [unique_key] using srid=[spatial_reference_id]”......

s/w versions:
Postgres 9.1
MapServer version 6.0.1
OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ SUPPORTS=AGG 
SUPPORTS=FREETYPE SUPPORTS=ICONV
SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER 
SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER 
SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI INPUT=POSTGIS INPUT=OGR INPUT=GDAL 
INPUT=SHAPEFILE


Requesting the database manually this select statement works fine:
select "datum", encode(ST_AsBinary(ST_Force_2D("rechteck"),'NDR'),'hex') as 
geom, HWFarbe[40] as farbe from produkt_20 where rechteck && 
GeomFromText('POLYGON((-269.711846318036 -138.025613660619,-269.711846318036 
138.025613660619,269.711846318036 138.025613660619,269.711846318036 
-138.025613660619,-269.711846318036 -138.025613660619))',-1) and 
((date_trunc('minute', datum) >= '2006-08-02 00:00:00' AND 
date_trunc('minute', datum) <= '2006-08-02 23:59:00'));



And this mapfile results in the query below (closest i could get to the 
working select statement above):
<snip>
 	LAYER 
 		EXTENT -180 -90 180 90
 		CONNECTIONTYPE POSTGIS
 		NAME "PRODLAYER"
 		PROCESSING "CLOSE_CONNECTION=DEFER"
 		OPACITY 100
 		STATUS ON
		VALIDATION
			"produkt" "^[a-zA-Z0-9_]+$"
			"hoehe" "^[0-9]+$"
		END
 		METADATA
 			"wms_timeextent" "1970-01-01 00:00/2030-12-31 24:00"
 			"wms_timeitem" "datum" #column in postgis table of type timestamp
 			"wms_timedefault" "2007-12-10 00:00/2007-12-11 24:00"
 			"wms_title" "%produkt%"
 			"wms_srs" "init=epsg:4326"
 		END
 		PROJECTION
 			"init=epsg:4326"
 		END
 		CONNECTION "user={97C559B77AE23F6F} password={53E7F71F40F9BD02} 
dbname={5A08CA2830DE1001} host=dw"

DATA "rechteck from , HWFarbe[%hoehe%] as farbe from %produkt% using unique 
datum using srid=-1"

 		TYPE POLYGON
 		SIZEUNITS meters
 		CLASS
 			NAME "produkt_total_class"
 			STYLE
 				OUTLINECOLOR 0 0 0
 				COLOR [farbe]
 			END
 		END
 	END # Layer
<snap>


Mapserver Error Log:
<snip>
[Wed May 23 14:10:15 2012].482261 msPostGISLayerWhichShapes(): Query error. 
Error (FEHLER:  Syntaxfehler bei ","
LINE 1: ...2D("rechteck"),'NDR'),'hex') as geom,"datum" from , HWFarbe[...
                                                             ^
select 
"datum","farbe",encode(ST_AsBinary(ST_Force_2D("rechteck"),'NDR'),'hex') as 
geom,"datum" from , HWFarbe[40] as farbe from produkt_20 where rechteck && 
GeomFromText('POLYGON((-269.711846318036 -138.025613660619,-269.711846318036 
138.025613660619,269.711846318036 138.025613660619,269.711846318036 
-138.025613660619,-269.711846318036 -138.025613660619))',-1) and 
((date_trunc('minute', datum) >= '2006-08-02 00:00:00' AND 
date_trunc('minute', datum) <= '2006-08-02 23:59:00'));
[Wed May 23 14:10:15 2012].482294 msDrawMap(): Image handling error. Failed to 
draw layer named 'PRODLAYER'.
<snap>