ExecuteSQL SpatialFilter about vapour HOT 14 CLOSED

All done now. Upcoming NEWS item:

Vector read functions gain new `extent` argument to apply a spatial filter in conjunction with 
 the `sql` argument, per discussion #34. The extent can be of type sp, sf, raster, or generic 
 vector `c(xmin, xmax, ymin, ymax)`.  The extent is ignored if the `sql` argument is not specified, 
 with a warning.  Applies to `vapour_geom_summary`,  `vapour_read_attributes`, `vapour_read_extent`, 
 `vapour_read_geometry`, `vapour_read_geometry_text`, and `vapour_read_names`. 

Please check, but create new issues for specific problems.

from vapour.

Heh, that was pretty easy - I just tested on extent, so it has to be copied throughout for each instance of ExecuteSQL, and I need to think about the interface. I'm inclined to let it be user-beware, it must be a numeric vector of four, assuming xmin, xmax, ymin, ymax, no NAs. I haven't exposed it to R yet, and not cleaning up the geometry yet.

It seems to work, the filter is inclusive - which I wasn't sure about - so the filter boundary is red, and the four heavy black regions are the return from 1) input sql and 2) xmin, xmax, ymin, ymax of the filter

from vapour.

Yeah that's really easy to implement. I'll give it a go next week.

from vapour.

Given 'vapour_read_extent', is this really needed? I'm not saying we shouldn't expose more GDAL (we should!). But with the extents (bboxes) I think this is simple in R only.

from vapour.

I did it, let me know

file <- "list_locality_postcode_meander_valley.tab"
mvfile <- system.file(file.path("extdata/tab", file), package="vapour")

## doesn't need names, they aren't used
## order is assumed
e <- c(xmin = 469750, xmax = 500840, 
       ymin = 5381892, ymax = 5393322)

library(vapour)
## no change
length(vapour_read_geometry(mvfile, extent = e))
#> [1] 58

## needs sql to be run as well
g <- vapour_read_geometry(mvfile, extent = e, sql = "SELECT * FROM list_locality_postcode_meander_valley")
length(g)  ## 9, they intersect the bounding box / extent
#> [1] 9


## compare with sf plot
library(sf)
#> Linking to GEOS 3.6.2, GDAL 2.3.2, PROJ 4.9.3
x <- sf::read_sf(mvfile)
plot(x[1], reset = F)
plot(sf::st_cast(sf::st_as_sfc(g)), add = TRUE, lwd =5)
rect(e[1], e[3], e[2], e[4])

^{Created on 2018-11-06 by the reprex package (v0.2.1)}

from vapour.

Good question! I'm happy to try to find out, probably @mpadge will know more easily than I? I'm interested to find ou about these other extended options too, just hadn't gotten to it - any exploration welcome :)

from vapour.

This is the equivalent gdalUtils command:

library(gdalUtils)
tfile <- tempfile(fileext = ".gpkg")
ogr2ogr(gdb_path, tfile, layer = "some_layer", spat = st_bbox(some_sf), f = "GPKG")

from vapour.

Sorry for dropping this one, but yeah, agreed @mdsumner that it's likely easier in R. Moreover, remember I did a timing comparison somewhere using the SQL filters in GDAL, and it was actually slower than post-filtering with sf? [... more organised me inserts relevant link here ...]

from vapour.

I will probably do it, just so we can!

It's been discussed over at sf, and it inspired me to build on Barry's PR for ExecuteSQL and do the DBI thing:

https://github.com/mdsumner/RGDALDB

(Not the first time I tried to do exactly that ... but this time I really got somewhere).

from vapour.

I'm having trouble telling if this functionality has been implemented yet here or in a different package. Should I look for it in RGDALDB?

from vapour.

Er, no - it's here but only in C++:

https://github.com/hypertidy/vapour/blob/master/src/vapour.cpp#L251

ex is a numeric vector xmin, xmax, ymin, ymax,

I can't expose this to R right now, only because it's late at night and I won't be able to check it carefully - I just haven't gotten back to this.

from vapour.

didn't mean to close it ...

from vapour.

My read of the Rcpp code is that the extent argument could be ported over from vapour_read_geometry_cpp to vapour_read_attributes_cpp. Is this correct?

from vapour.

Yes, for sure - I should have done it at the time

from vapour.