[mapserver-users] [Fwd: mapserver - postgis bug]
Peter Freimuth
pfreimuth at arcor.de
Tue Oct 13 16:01:58 EDT 2009
P Kishor schrieb:
> On Mon, Oct 12, 2009 at 3:00 PM, Peter Freimuth <pfreimuth at arcor.de> wrote:
>
>>
My Mapserver Configuration
>> owsuser at map2:~> /srv/www/cgi-bin/mapserv -v
>> MapServer version 5.4.0 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP OUTPUT=SVG 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=THREADS SUPPORTS=GEOS INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=MYGIS INPUT=SHAPEFILE
>>
>>
The Mapfile with the 4 WMS-Time layer
>> MAP
>> ...
>> #-----------------------------------------------------------------------------------------------------------------------------------
>> # Index and Raster Layer for all requested "service_products"
>> # duplicate the template below and modify the Product Shortname in the sql query and rename the layer itself
>> #-----------------------------------------------------------------------------------------------------------------------------------
>> LAYER
>> NAME "service_product_gc_idx"
>> STATUS OFF
>> #DEBUG ON
>> TYPE POLYGON
>> PROCESSING "CLOSE_CONNECTION=DEFER"
>> CONNECTIONTYPE POSTGIS
>> CONNECTION "user=ows_user password=xxx dbname=ows_customer_nnn host=db2 port=5432"
>> DATA "wkb_geom FROM (SELECT * FROM ows_customer.tileindex_service_products tsp JOIN ows_customer.analysis_units au ON (au.unit_id=tsp.unit_id) JOIN ows_customer.service_product_types spt ON (tsp.product_type_id=spt.product_type_id) WHERE spt.name = 'GC' ) foo USING UNIQUE service_product_id USING SRID=3785"
>> PROJECTION
>> "init=epsg:3785"
>> END
>> METADATA
>> "ows_title" "service_product_gc_idx"
>> "wms_srs" "EPSG:3785 EPSG:4269 EPSG:4326 EPSG:32610 EPSG:32611 EPSG:32612 EPSG:32613 EPSG:32614 EPSG:26710 EPSG:26711 EPSG:26712 EPSG:26713 EPSG:26714 EPSG:900913" #output
>> #"ows_extent" "380000 5230000 530000 5390000"
>> END
>> END
>> # Raster Layer
>> LAYER
>> NAME "serv_prod_gc20090415"
>> #GROUP "service_products"
>> TYPE RASTER
>> STATUS ON
>> #DEBUG ON
>> #TRANSPARENCY 70
>> OFFSITE 255 255 255
>> TILEITEM "location_path" #location_path is a column in above referenced POSTGIS table "tileindex_service_products" that stores the filepath to each image
>> TILEINDEX "service_product_gc_idx"
>> PROJECTION
>> "init=epsg:3785"
>> END
>> METADATA
>> "ows_title" "Groundcover Map - April 2009"
>> #"ows_group_title" "Service Products WS 2009"
>> "wms_srs" "EPSG:3785 EPSG:4269 EPSG:4326 EPSG:32610 EPSG:32611 EPSG:32612 EPSG:32613 EPSG:32614 EPSG:26710 EPSG:26711 EPSG:26712 EPSG:26713 EPSG:26714 EPSG:900913" #output
>> #"ows_extent" "380000 5230000 530000 5390000"
>> "ows_timeextent" "2009-04-15/2009-12-15"
>> "ows_timeitem" "product_date" #yearmoda is our date column in shape of type date
>> "ows_timedefault" "2009-04-15"
>> END
>> CLASS
>> NAME "GCMap"
>> KEYIMAGE "/srv/www/htdocs/nnn/GroundCover.png"
>> END
>> END
>> LAYER
>> NAME "serv_prod_gc20090515"
>> #GROUP "service_products"
>> TYPE RASTER
>> STATUS ON
>> #DEBUG ON
>> #TRANSPARENCY 70
>> OFFSITE 255 255 255
>> TILEITEM "location_path" #location_path is a column in above referenced POSTGIS table "tileindex_service_products" that stores the filepath to each image
>> TILEINDEX "service_product_gc_idx"
>> PROJECTION
>> "init=epsg:3785"
>> END
>> METADATA
>> "ows_title" "Groundcover Map - May 2009"
>> "wms_srs" "EPSG:3785 EPSG:4269 EPSG:4326 EPSG:32610 EPSG:32611 EPSG:32612 EPSG:32613 EPSG:32614 EPSG:26710 EPSG:26711 EPSG:26712 EPSG:26713 EPSG:26714 EPSG:900913" #output
>> #"ows_extent" "380000 5230000 530000 5390000"
>> "ows_timeextent" "2009-04-15/2009-12-15"
>> "ows_timeitem" "product_date" #yearmoda is our date column in shape of type date
>> "ows_timedefault" "2009-05-15"
>> END
>> CLASS
>> NAME "GCMap"
>> KEYIMAGE "/srv/www/htdocs/nnn/GroundCover.png"
>> END
>> END
>> LAYER
>> NAME "serv_prod_gc20090615"
>> #GROUP "service_products"
>> TYPE RASTER
>> STATUS ON
>> #DEBUG ON
>> #TRANSPARENCY 70
>> OFFSITE 255 255 255
>> TILEITEM "location_path" #location_path is a column in above referenced POSTGIS table "tileindex_service_products" that stores the filepath to each image
>> TILEINDEX "service_product_gc_idx"
>> PROJECTION
>> "init=epsg:3785"
>> END
>> METADATA
>> "ows_title" "Groundcover Map - June 2009"
>> "wms_srs" "EPSG:3785 EPSG:4269 EPSG:4326 EPSG:32610 EPSG:32611 EPSG:32612 EPSG:32613 EPSG:32614 EPSG:26710 EPSG:26711 EPSG:26712 EPSG:26713 EPSG:26714 EPSG:900913" #output
>> #"ows_extent" "380000 5230000 530000 5390000"
>> "ows_timeextent" "2009-04-15/2009-12-15"
>> "ows_timeitem" "product_date" #yearmoda is our date column in shape of type date
>> "ows_timedefault" "2009-06-15"
>> END
>> CLASS
>> NAME "GCMap"
>> KEYIMAGE "/srv/www/htdocs/nnn/GroundCover.png"
>> END
>> END
>> LAYER
>> NAME "serv_prod_gc20090715"
>> #GROUP "service_products"
>> TYPE RASTER
>> STATUS ON
>> #DEBUG ON
>> #TRANSPARENCY 70
>> OFFSITE 255 255 255
>> TILEITEM "location_path" #location_path is a column in above referenced POSTGIS table "tileindex_service_products" that stores the filepath to each image
>> TILEINDEX "service_product_gc_idx"
>> PROJECTION
>> "init=epsg:3785"
>> END
>> METADATA
>> "ows_title" "Groundcover Map - July 2009"
>> "wms_srs" "EPSG:3785 EPSG:4269 EPSG:4326 EPSG:32610 EPSG:32611 EPSG:32612 EPSG:32613 EPSG:32614 EPSG:26710 EPSG:26711 EPSG:26712 EPSG:26713 EPSG:26714 EPSG:900913" #output
>> #"ows_extent" "380000 5230000 530000 5390000"
>> "ows_timeextent" "2009-04-15/2009-12-15"
>> "ows_timeitem" "product_date" #yearmoda is our date column in shape of type date
>> "ows_timedefault" "2009-07-15"
>> END
>> CLASS
>> NAME "GCMap"
>> KEYIMAGE "/srv/www/htdocs/nnn/GroundCover.png"
>> END
>> END
>> ...
>>
>>
The request which is send by the Mapbender 2.5 Client
>> https://10.20.2.45/map2/ows/allianz_brazil?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&WIDTH=1000&HEIGHT=1000&BBOX=-52,-23.8,-50,-13.55&SRS=EPSG:4326&FORMAT=image/jpeg&STYLES=&LAYERS=serv_prod_gc20090415,service_product_gc_idx
>>
>>
>>
The regarding umn mapserver error log section which shows the sql which
was created by mapserver and the Syntax error message of the database
>> [Mon Oct 12 10:45:12 2009].604172 msWMSLoadGetMapParams(): WMS server error. Invalid layer(s) given in the LAYERS parameter.
>> [Mon Oct 12 10:45:45 2009].724205 msPostGISLayerWhichShapes(): Query error. Error (ERROR: syntax error at or near ")"
>> LINE 1: ...and date_trunc('day', product_date) = '2009-04-15') and (dat...
>> ^
>> ) executing query: select "product_date","location_path",encode(AsBinary(force_collection(force_2d("wkb_geom")),'NDR'),'base64') as geom,"service_product_id" from (SELECT * FROM ows_customer.tileindex_service_products tsp JOIN ows_customer.analysis_units au ON (au.unit_id=tsp.unit_id) JOIN ows_customer.service_product_types spt ON (tsp.product_type_id=spt.product_type_id) WHERE spt.name = 'GC' ) foo where wkb_geom && GeomFromText('POLYGON((-5788613.52125023 -2729056.10763308,-5788613.52125023 -1522639.17855836,-5565974.53966368 -1522639.17855836,-5565974.53966368 -2729056.10763308,-5788613.52125023 -2729056.10763308))',3785) and date_trunc('day', product_date) = '2009-04-15') and (date_trunc('day', product_date) = '2009-05-15') and (date_trunc('day', product_date) = '2009-06-15') and (date_trunc('day', product_date) = '2009-07-15'
>> [Mon Oct 12 10:45:45 2009].724297 msDrawMap(): Image handling error. Failed to draw layer named 'serv_prod_gc20090415'.
>> What is the bug? Why not just tell us instead of reproducing a boat
>> load of inconsequential text.
>>
>> As far as I can see, you have unbalanced parentheses in your query. I
>> leave it as an exercise for you to find out where.
>>
>>
OK,
maybe it needs more explanation. The problem is the following: I have 4
WMS-Time layer (see the layer definitions above) in a mapfile and in
case i send a getmap request asking for more than one (see the above
GetMap request) i get a postgis error in the query which is
automatically created by mapserver (as you can see in the text above)
which ends in an empty image instead of drawing all 4 layers.
The problem is that mapserver seems to set incorrect brackets when it
constructs the sql with more than one WMS-Time layer which cause the
'ERROR: syntax error at or near ")""'
So thanks Puneet Kishor that you remind me on explaining the problem in
more detail even if i thougth that the details which i send explain the
problem quite good and there is nothing "inconsequential".
Peter
More information about the mapserver-users
mailing list