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

Even Rouault even.rouault at mines-paris.org
Fri Mar 25 15:40:48 EDT 2011


Le vendredi 25 mars 2011 18:35:28, Christy Nieman a écrit :
> 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.

Thanks. I've commited a fix that will go in 1.8.1.

I confirm the workaround I suggested with the -sql works with 1.8.0, except 
that I got it wrong in my previous mail because I forgot the "from 
${tablename}" in the -sql expression.

So the correct syntax is :

ogr2ogr -sql "select ano from Prodes2009_23367 where ano = '2009' and 
class_name = 'DESFLORESTAMENTO'" filter_select_where10.shp Prodes2009_23367.shp

Note I've removed the  -f "ESRI Shapefile". It's useless. I've just checked 
that Shapefile has been the default output format of ogr2ogr since its first 
version :-) At that time, ogr2ogr.cpp was a nice simple code of only 280 line 
long, now it has reached 2224 lines ... So I definitely recommand the reading 
of http://trac.osgeo.org/gdal/browser/trunk/ogr/ogr2ogr.cpp?rev=974 to people 
who want to understand the gist of ogr2ogr ;-)

> 
> 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


More information about the gdal-dev mailing list