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

[Even Rouault]

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
>