try this form: DATA "the_geom from (select id,coords as the_geom, *all_other_attributes_used_in_layer* from %farbe% join table2 on foo=bar where blabla) as foo using unique id using srid=xxx"
On Wed, May 23, 2012 at 2:34 PM, Heiko Schroeter <[email protected]> wrote: > 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> > > _______________________________________________ > mapserver-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/mapserver-users _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
