[mapserver-users] Postgres/Postgis additonal variable request

[Heiko Schroeter]

Am Mittwoch, 23. Mai 2012, 14:58:02 schrieben Sie:

thanks for the hint. I had to extend a bit (by probably complete 
misunderstanding and misinterpreting), but it works ! :


DATA "the_geom from  (select coords as the_geom, datum, HWFarbe[25] as farbe 
from %produkt%) as farbe using unique farbe using srid=-1"


Generates:
select "datum", "farbe", 
encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as geom ,
"farbe" from (select rechteck as the_geom, datum, HWFarbe[25] as farbe from 
produkt_20) as farbe
 where the_geom && 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'))


> 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
> 
> <schroete at iup.physik.uni-bremen.de> 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
> > mapserver-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users