incorrect sql generated when requesting two or more time indexed layers

[relliott]

The following request to the latest version of mapserver (5.4) produces the badly formatted sql as detailed in the exception. This didn't happen in 5.2.

?BBOX=33.803742,-14.469536,63.568409,36.695549&BGCOLOR=0xFFFFFF&CRS=EPSG:4326&FORMAT=image/jpeg&HEIGHT=395&LAYERS=MER_RR__2P_ALGAL_1,MER_RR__2P_ALGAL_2&REQUEST=GetMap&SERVICE=WMS&STYLES=&time=2006-12-09&TRANSPARENT=TRUE&Version=1.3.0&WIDTH=679

<ServiceExceptionReport version="1.3.0" xsi:schemaLocation="http://www.opengis.net/ogc ​http://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd"> − <ServiceException>

msDrawMap(): Image handling error. Failed to draw layer named 'MER_RR2P_ALGAL_1'. msPostGISLayerWhichShapes(): Query error. Error (ERROR: syntax error at or near ")" LINE 1: ...) and date_trunc('day', start_date) = '2006-12-09') and (dat...

) executing query: select "start_date","algol_1_file",encode(AsBinary(force_collection(force_2d("geom")),'NDR'),'base64') as geom,"oid" from mer_rr2p where geom && GeomFromText('POLYGON((-14.4318596088608 33.8414187936709,-14.4318596088608 63.5307322063291,36.6578726088608 63.5307322063291,36.6578726088608 33.8414187936709,-14.4318596088608 33.8414187936709))',find_srid(,'mer_rr2p','geom')) and date_trunc('day', start_date) = '2006-12-09') and (date_trunc('day', start_date) = '2006-12-09' </ServiceException> </ServiceExceptionReport>

the various layers are: # Tile Index LAYER

STATUS ON NAME "MER_RR2P_IDX" TYPE POLYGON DATA "geom from mer_rr2p" METADATA

"wms_title" "MER_RR2P TIME INDEX" "wms_group_title" "MER_RR2P TIME INDEX" "wms_srs" "EPSG:4326" "wms_extent" "-180 -90 180 90" #"wms_timeextent" "1900-01-01/2032-12-31" "wms_timeextent" "2006-12-09" "wms_timeitem" "start_date" #column in postgis table of type timestamp "wms_timedefault" "2006-12-09"

END GROUP "TIME_INDEXES"

CONNECTION "dbname=xxxx host=xxxx user=xxxx password=xxxx" CONNECTIONTYPE postgis

END

LAYER

NAME "MER_RR2P_IMAGE" TYPE RASTER STATUS ON DEBUG ON DUMP TRUE PROJECTION

"init=epsg:4326"

END GROUP "MER_RR2P"

METADATA

"wms_title" "Image" "wms_group_title" "MER_RR2P" "wms_srs" "EPSG:4326" "wms_extent" "-180 -90 180 90" #wms_timeextent" "1900-01-01/2032-12-31" "wms_timeextent" "2006-12-09" "wms_timeitem" "start_date" #datetime is a column in postgis table of type timestamp "wms_timedefault" "2006-12-09"

END OFFSITE 0 0 0 TILEITEM "img_file" #filepath is a column in postgis table with varchar of the filepath to each image TILEINDEX "MER_RR2P_IDX"

END

LAYER

NAME "MER_RR2P_ALGAL_1" TYPE RASTER STATUS ON DEBUG ON DUMP TRUE PROJECTION

"init=epsg:4326"

END GROUP "MER_RR2P"

METADATA

"wms_title" "Algal 1" "wms_group_title" "MER_RR2P" "wms_srs" "EPSG:4326" "wms_extent" "-180 -90 180 90" "wms_timeextent" "1900-01-01/2032-12-31" "wms_timeitem" "start_date" #datetime is a column in postgis table of type timestamp #"wms_timedefault" "1900-01-01"

END OFFSITE 0 0 0 TILEITEM "algol_1_file" #filepath is a column in postgis table with varchar of the filepath to each image TILEINDEX "MER_RR2P_IDX"

CLASS

NAME "Algal 1" KEYIMAGE "/var/www/html/legends/algal_chl_legend.png" END

END

LAYER

NAME "MER_RR2P_ALGAL_2" TYPE RASTER STATUS ON DEBUG ON DUMP TRUE PROJECTION

"init=epsg:4326"

END GROUP "MER_RR2P"

METADATA

"wms_title" "Algal 2" "wms_group_title" "MER_RR2P" "wms_srs" "EPSG:4326" "wms_extent" "-180 -90 180 90" "wms_timeextent" "1900-01-01/2032-12-31" "wms_timeitem" "start_date" #datetime is a column in postgis table of type timestamp #"wms_timedefault" "1900-01-01"

END OFFSITE 0 0 0 TILEITEM "algol_2_file" #filepath is a column in postgis table with varchar of the filepath to each image TILEINDEX "MER_RR2P_IDX"

CLASS NAME "Algal 2" KEYIMAGE "/var/www/html/legends/algal_chl_legend.png" END

END

If someone can point me in the right direction I don't mind trying to pin down where it does wrong. With only one layer requested it works just fine, so it looks as if the time filter for both layers is being applied at the same time but the wrong way round.

Ross Elliott

[relliott]

I have now got to the bottom of this. Take two or more layers that share the same postgres time index layer, when a request is made for a layer the filter string of the time index layer is set, in my case it gets set to "(date_trunc('day', start_date) = '2006-12-09')", this works fine for one layer but when two layers are requested a new filter string is created that includes the old one, so I get "(date_trunc('day', start_date) = '2006-12-09') and (date_trunc('day', start_date) = '2006-12-09')", this would work fine except that the call to loadExpressionString strips the first and last bracket from the string and I end up with "date_trunc('day', start_date) = '2006-12-09') and (date_trunc('day', start_date) = '2006-12-09'" which is obviously wrong.

My quick fix is to change loadExpressionString in mapfile.c to: int loadExpressionString(expressionObj *exp, char *value) {

msyystate = MS_TOKENIZE_STRING; msyystring = value; msyylex(); /* sets things up but processes no tokens */

freeExpression(exp); /* we're totally replacing the old expression so free then init to start over */ /* initExpression(exp); */

if((exp->type = getSymbol(4, MS_EXPRESSION,MS_REGEX,MS_IREGEX,MS_ISTRING)) != -1) {

/* ross elliott - 7.5.2009, use the original value if it starts with a '(' */

if((strlen(value) - strlen(msyytext)) == 2 && *value!='(')

exp->string = strdup(msyytext); /* value was quoted */

else

exp->string = strdup(value); /* use the whole value */

//

if(exp->type == MS_ISTRING) {

exp->type = MS_STRING; exp->flags = exp->flags | MS_EXP_INSENSITIVE;

} else if(exp->type == MS_IREGEX) {

exp->type = MS_REGEX; exp->flags = exp->flags | MS_EXP_INSENSITIVE;

}

} else {

msResetErrorList(); /* failure above is not really an error since we'll consider anything not matching (like an unquoted number) as a STRING) */ exp->type = MS_STRING; if((strlen(value) - strlen(msyytext)) == 2)

exp->string = strdup(msyytext); /* value was quoted */

else

exp->string = strdup(value); /* use the whole value */

}

/* if(exp->type == MS_REGEX) { */ /* if(ms_regcomp(&(exp->regex), exp->string, MS_REG_EXTENDED|MS_REG_NOSUB) != 0) { compile the expression */ /* sprintf(ms_error.message, "Parsing error near (%s):(line %d)", exp->string, msyylineno); */ /* msSetError(MS_REGEXERR, ms_error.message, "loadExpression()"); */ /* return(-1); */ /* } */ /* } */

return(0);

}

Is the the right fix in general or is there a neater way to do it in the lexer?

Ross Elliott

[sdlime]

With changes to the PostGIS driver and new WMS support it's possible there were errors introduced there. It's hard for me to believe that changing the expression loader is the place to fix this.

Ross, could you post your patch and mapfile as attachements? Easier to read I think.

Steve

[relliott]

Ok, patch and map file attached. The problem isn't technically with the lexer as the real fix, as I see it, is to only process the index file once for each layer, but my patch provide a quick fix for me which as I have a project to deliver very soon is what I'm interested in most of all at the moment.