connection with DBI

[idshklein]

I work with sql files in rstudio, where you need to specify the type of connection in the first row as a comment, like so:
-- !preview conn=DBI::dbConnect(RSQLite::SQLite(),"some_file_name.gpkg")
using RSQLite doesn't allow for spatial function as gpkg allows.
I can't figure out what is the right SQLiteConnection to pass to that row.
Anyhow this could be a nice feature as a standalone function.

[brownag]

Hi @idshklein

Sorry for delay in responding to this issue

Inspired by this topic, I implemented a DBI compatible interface for the OGR SQL dialect used by GDAL (https://github.com/brownag/ROGRSQL), which should make possible what you want to do.

I still need to think through a few things.. and decide if it is something I would want in {gpkg}, or to put out there as a separate package (and then have {gpkg} suggest it). I am leaning towards the latter.

If I do release as a separate package, I want to make sure what I have done is as generalized as possible, and compatible with both the latest patterns used in DBI and as many data sources as possible (not necessarily just GeoPackage)

I have not had a lot of time to revisit this matter in the last month or so. I would welcome further comments or issues on the topic, perhaps you could try out swapping ROGRSQL for RSQLite and see if it does what you want, and we can go from there?

Thanks for raising this issue, it is thought provoking, and I hope to return to it and be able to deliver what you are looking for.

[brownag]

I also note that there is an alternate possible pattern which would involve providing a compiled version of Spatialite extension for SQLite via an R package instead of using GDAL OGR SQL.

At this time I have not explored this route extensively, but it is theoretically possible.

There is an implementation of this type on GitHub which shows generally what needs to be done to load the Spatialite extension in R (https://github.com/pschmied/RSQLite.spatialite)... But note the bundled version of Spatialite in this package is about 12 years old now, so I would need to come up with a scheme for using the latest version(s) of sqlite and spatialite, and then ideally automate the update process for the source code so the R package can be periodically updated