[gdal-dev] OGR2OGR options -select and -where together

Fri Mar 25 13:35:28 EDT 2011

Even,
Thanks for the explanations.  I've created ticket #4015 
(http://trac.osgeo.org/gdal/ticket/4015) with regards to the necessity 
to include all fields used in the -where clause in -select.

Luiz,
Thanks for noticing this!  It'd have caused me many problems had I 
switched to using 1.8.0 before this was noticed.

Christy

On 03/25/2011 01:17 PM, Even Rouault wrote:
> Selon Christy Nieman<cnieman at dmsolutions.ca>:
>
>> Aha.  And sorry for kind of spamming the list.  I've been looking into
>> this between working on something else.
>>
>> with -sql in 1.8.0:
>> ogr2ogr -f "ESRI Shapefile" -sql "select ano from Prodes2009_23367 where
>> ano = '2009' and class_name = 'DESFLORESTAMENTO'"
>> filter_select_where7.shp Prodes2009_23367.shp
>>
>> the ' around 2009 wasn't required when I tried in 1.7.x
> This part is expected. In OGR 1.8.0 the SQL engine has been rewritten and is a
> bit more strict regarding to SQL compliance, so quoting around literal values is
> now required.
>
>> In 1.7.x you could have just the ano field in -select, but in 1.8.0 it
>> seems you need both fields included in the -where, so:
>> ogr2ogr -f "ESRI Shapefile" -select ano,class_name -where "ano = '2009'
>> and class_name = 'DESFLORESTAMENTO'" filter_select_where10.shp
>> Prodes2009_23367.shp
>>
>> Maybe one of the developers could explain this change?
> This one sounds like a bug due to the use of the new IgnoreFields capability,
> that seems to be used a bit too agressively. Could you file a GDAL Track ticket
> for that issue ? Thanks
>
> I didn't try but could you try the following ?
>
> ogr2ogr -f "ESRI Shapefile" -sql "select ano where ano = '2009' and class_name =
> 'DESFLORESTAMENTO'" filter_select_where10.shp Prodes2009_23367.shp
>
> I *think* this would work.
>
> Best regards,
>
> Even
>
>> Christy
>>
>> On 03/25/2011 10:50 AM, Christy Nieman wrote:
>>> Sorry, I wanted to provide more info, but I wanted to compile 1.8.0
>>> before expanding on this (I was running a trunk version from before
>>> 1.8.0's release).  I was going to suggest that you could use the -sql
>>> parameter with an sql query, however I was unable to get this to work
>>> with 1.8.0 either, though it worked in 1.7.x.  I also wanted to read
>>> through the release notes to see if there was something obvious I
>>> missed, but didn't see anything.  Anyone else encountered this problem?
>>>
>>> On 03/25/2011 10:17 AM, Luiz Motta wrote:
>>>> Thank's  Christy.
>>>>
>>>> The idea is report this problem, or verify , if i am using correct
>>>> form the OGR2OGR.
>>>>
>>>> Luiz
>>>>
>>>>
>>>>
>>>> 2011/3/25 Christy Nieman<cnieman at dmsolutions.ca>:
>>>>> And apparently I'm half asleep this morning.  Didn't see your
>>>>> examples of
>>>>> what you've tried.  Sorry.  I tried your 3.1 ogr2ogr with your data
>>>>> and got
>>>>> 754 features returned with an older version of GDAL (1.7 I believe),
>>>>> but 0
>>>>> with 1.8 also.
>>>>>
>>>>> On 03/25/2011 09:46 AM, Christy Nieman wrote:
>>>>>> Sorry, wrong order for the source and destination files.  Should be
>>>>>> output.shp then input.shp
>>>>>>
>>>>>> C
>>>>>>
>>>>>> On 03/25/2011 09:45 AM, Christy Nieman wrote:
>>>>>>> Hello,
>>>>>>>
>>>>>>> You can indeed use both -select and -where together together.  For
>>>>>>> example
>>>>>>>
>>>>>>> ogr2ogr -select field1,field2,field3 -where "field1 = value OR
>>>>>>> field2 =
>>>>>>> anotherValue" input.shp output.shp
>>>>>>>
>>>>>>> Best regards,
>>>>>>> Christy
>>>>>>>
>>>>>>> On 03/25/2011 09:38 AM, Luiz Motta wrote:
>>>>>>>> Devs,
>>>>>>>>
>>>>>>>> I would like have the subset of data, by filters of field (-select)
>>>>>>>> and attributes(-where), using OGR2OGR.
>>>>>>>>
>>>>>>>> My enviroment is:
>>>>>>>> - Windows XP
>>>>>>>> - GDAL from OSGEO4W
>>>>>>>> - Version: GDAL 1.8.0, released 2011/01/12
>>>>>>>>
>>>>>>>> At the moment, i can only make subset(field and attribute) by call
>>>>>>>> separate of OGR2OGR.
>>>>>>>>
>>>>>>>> Has one manner to use options  "select" and "where" in the same call
>>>>>>>> OGR2OGR  ?
>>>>>>>>
>>>>>>>> Next, my tests  to make subsets:
>>>>>>>>
>>>>>>>> 1) Source data:
>>>>>>>>
>>>>>>>>
>> http://www.dpi.inpe.br/prodesdigital/dadosn/2009/PDigital2009_23367_shp.zip
>>>>>>>>
>>>>>>>> 2) Show info:
>>>>>>>> 2.1) Summary
>>>>>>>> ogrinfo -so Prodes2009_23367.shp Prodes2009_23367
>>>>>>>> 2.2) Subset features (see -where)
>>>>>>>> ogrinfo -q -geom=NO -where "ano = '2009' and class_name =
>>>>>>>> 'DESFLORESTAMENTO'"  Prodes2009_23367.shp Prodes2009_23367 | more
>>>>>>>>
>>>>>>>> 3) Subset data
>>>>>>>>
>>>>>>>> 3.1) Filter by field(-select) and attribute(-where) ->     Empty data
>>>>>>>> output
>>>>>>>> ogr2ogr -f "ESRI Shapefile" -select "ano" -where "ano = '2009' and
>>>>>>>> class_name = 'DESFLORESTAMENTO'" filter_select_where.shp
>>>>>>>> Prodes2009_23367.shp
>>>>>>>> ogrinfo -so filter_select_where.shp filter_select_where
>>>>>>>> * Feature Count = 0
>>>>>>>>
>>>>>>>> 3.2) Filter by field(-select)
>>>>>>>> ogr2ogr -f "ESRI Shapefile" -select "ano" filter_select.shp
>>>>>>>> Prodes2009_23367.shp
>>>>>>>> ogrinfo -so filter_select.shp filter_select
>>>>>>>>
>>>>>>>> 3.3) Filter by attribute(-where)
>>>>>>>> ogr2ogr -f "ESRI Shapefile" -where "ano = '2009' and class_name =
>>>>>>>> 'DESFLORESTAMENTO'" filter_where.shp Prodes2009_23367.shp
>>>>>>>> ogrinfo -so filter_where.shp filter_where
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Luiz Motta
>>>>>>>> _______________________________________________
>>>>>>>> gdal-dev mailing list
>>>>>>>> gdal-dev at lists.osgeo.org
>>>>>>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>>>>> _______________________________________________
>>>>>> gdal-dev mailing list
>>>>>> gdal-dev at lists.osgeo.org
>>>>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>>>> _______________________________________________
>>>>> gdal-dev mailing list
>>>>> gdal-dev at lists.osgeo.org
>>>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>>>>
>>> _______________________________________________
>>> gdal-dev mailing list
>>> gdal-dev at lists.osgeo.org
>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>> _______________________________________________
>> gdal-dev mailing list
>> gdal-dev at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>