[R-sig-Geo] Adding SQL "where" to readOGR

Roger Bivand Roger.Bivand at nhh.no
Wed Jul 3 11:46:40 CEST 2013

On Wed, 3 Jul 2013, Barry Rowlingson wrote:

> This pops up on stackoverflow and r-sig-geo occasionally - how can I
> add SQL to my readOGR call?
> There was a discussion here about 2.5 years ago, which ended in some
> comment from Roger about some refactoring of rgdal before trying to
> implement setAttributeFilter. Since then, nothing, and no sign of it
> in the current codebase.

Nobody volunteered ...

> Is it as simple as it seems it could be?
> 1. Add an optional 'where' argument to readOGR.
> 2. Pass that string to C++ and call setAttributeFilter on the OGRLayer object.
> 3. Check for errors...
> 4. Get back a filtered data set.
> spdf = readOGR("PG:dbname=maps","countries",where="population > 1000")
> I don't think it needs the full hammer of using OGR's ExecuteSQL
> functionality whch looks a bit different. The attribute filter
> mechanism is smart enough to pass the SQL to the database for database
> drivers, or do the hard work itself for reading from Shapefiles. I
> think the implementation of this in gdal is widespread enough now for
> it to be accepted (1.8?).

It does vary from version to version, and may imply geometric where=, 
which may also drag GEOS into its build train. If we stay with 
bounding-box/envelope geometric conditions and attribute conditions, it 
might be OK, but will meet design questions about how much of the where= 
to parse in R before despatch.

readOGR() does still need rewriting anyway, even though it is a lot faster 
now that lists of sp polygon class objects are created in C.

> Now, if only I wasn't teaching 100 ecologists next week I might attack
> this. Actually I would probably be going to UseR!... But anyway....
> Has anyone looked at this in more detail?

All welcome!


> Barry
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo

Roger Bivand
Department of Economics, NHH Norwegian School of Economics,
Helleveien 30, N-5045 Bergen, Norway.
voice: +47 55 95 93 55; fax +47 55 95 95 43
e-mail: Roger.Bivand at nhh.no

More information about the R-sig-Geo mailing list