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

[Stefan Schantz]

Hello Even,
that's it. It is really strictly string based...
Thanks!
Stefan


Am 20.12.2011 16:58, schrieb 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
>>>
>
>