[gdal-dev] how to use the filter option -where within an mysql request?

[Even Rouault]

Selon Stefan Schantz <SSchantz at BfS.de>:

> Hi again,
> I 've tried it now with
> ogrinfo --version
> GDAL 1.8.0, released 2011/01/12
>
> but with the same negative result:
>
> without any filter, I get results like
>
> OGRFeature(sql_statement):1677
>    value (Real) =             99.215000
>    end_measure (DateTime) = 2011/12/20  0:00:00
>    POINT (8.64381981 50.6018486)
>
> with the filter option "-where"
> ogrinfo -ro
>
MYSQL:onmon,host=10.130.63.11,user=user,password=pasword,port=3306,tables=result_current
> -SQL "SELECT value, end_measure,locality.geom from result_current JOIN
> locality on result_current.locality_code =locality.locality_code" -where
> "end_measure = '2011/12/20 0:00:00'"
>
> I didn't get any hits.
>
> Any idea?

Perhaps, there's 2 spaces between 2011/12/20 and 0:00:00 in the output of
ogrinfo, whereas there's only one in your where clause... Yes, the matching is
strictly string based...

>
> Best regards,
> Stefan
>
>
> Am 20.12.2011 13:50, schrieb Even Rouault:
> > Stefan,
> >
> > Please always mention the GDAL/OGR version you are using in your reports.
> It
> > will save the readers from figuring out from the error message that you
> must use
> > GDAL 1.7.x or an earlier version.
> >
> > The -where "end_measure = '2011-12-20'" clause doesn't work in GDAL 1.7.x
> or
> > earlier because the code didn't support comparison on DateTime fields (the
> error
> > message mentions list of binary fields, but DateTime fields too)
> >
> > I've done a quick test on GDAL 1.9.0beta1 and you can use comparisons on
> date,
> > but they seem to be string based. And you have to respect the format of the
> > string serialization used by OGR. In your case, it would be -where
> "end_measure
> > = '2011/12/20  0:00:00'"
> >
> > However, there's no advantage of using -where when you already use -sql.
> The
> > -sql is directly forwarded to the SQL engine of the RDBMS (when the OGR
> driver
> > is the one of a RDBMS of course), so it will run faster than -where which,
> > depending on situations, can be evaluated on OGR side.
> > - On a layer which is already the result of -sql, the -where clause is
> always
> > evaluated on OGR side, after the evaluation of -sql
> > - If the layer is directly a table name, then the -where clause is
> generally
> > forwarded to the server. (It might depend on the OGR driver however).
> >
> > So, as a rule of thumb, you can remember that there's no point in using
> both
> > -sql and -where.
> >
> > Best regards,
> >
> > Even
> >
>