Read Postgis geom available as string variable with R

Question

I have a Postgis Database from which I query data. I know that I can use the rgdal::readOGR() function with the PG driver to get geoms from the database into R.

However, I am also querying lots of non-spatial data from the same database using DBI. From a programming point of view it seems not clean to have two different methods to connect to the same database in the same script.

What I do now is to query the column with SELECT ST_AsBinary(the_geom) ... using DBI and then read the result with wkb::readWKB(). This works (only uses DBI for connection) but is quite slow.

Is there any way to read a geom string as returned from Postgis with rgdal when the string is already available as variable?

• Spacedman: Can you show some example code with timings to demonstrate how slow using DBI and converting is compared to reading with the OGR PG driver?

Answer 1

I always find it difficult, but now there is existing solutions, and even an R package: rpostgis (https://journal.r-project.org/archive/2018/RJ-2018-025/RJ-2018-025.pdf)

There is functions to read/write from/to postgis and even to store and read R data.frame (dbWriteDataFrame)

The connection its uses is RPostGreSQL, so you can use it simply for normal tables.

• AEF: That looks like a really useful package, thank you!