[mapserver-users] Postgres/Postgis additonal variable request

[thomas bonfort]

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