Feature Request: add clauses functionality to pgGetRast

[crabel99]

pgGetRast only allows the option of specifying a bounding box with the boundary option. This limits program functionality. Adding a clauses option would allow returning data based on a where clause, e.g. clauses="WHERE rid = 2" similar to the approach taken in pgGetBoundary. This would greatly simplify the workflow for dealing with tiled rasters, when raster aggregation causes problems due to memory constraints.

As a note when I use pgGetBoundary to supply pgGetRast, pgGetRast behaves like it should but it complains about something that makes no sense...

test<-pgGetBoundary(conn,c("public","ned_1m"),geom="rast",clauses="where rid = 14301")
pgGetRast(conn, c("public","ned_1m"),rast="rast",boundary=c(test@bbox[2,2],test@bbox[2,1],test@bbox[1,2],test@bbox[1,1]))

I get a return that looks like:

NOTICE:  The rasters (pixel corner coordinates) are not aligned
class      : RasterLayer 
dimensions : 100, 100, 10000  (nrow, ncol, ncell)
resolution : 1, 1  (x, y)
extent     : 510000, 510100, 3735600, 3735700  (xmin, xmax, ymin, ymax)
crs        : +proj=utm +zone=16 +datum=NAD83 +units=m +no_defs +ellps=GRS80 +towgs84=0,0,0 
source     : memory
names      : layer 
values     : 120.9662, 150.9203  (min, max)

Adding clauses to pgGetRast would be super convenient!

[dnbucklin]

Hi @crabel99 , see the dev branch, which already has this functionality - install with devtools:

devtools::install_github("mablab/rpostgis", ref = "dev")

This update should get pushed to CRAN, hopefully in the next month or so. I'll leave this issue open until then.

[crabel99]

Thank you. I will play around with it. As it stands I have a viable workaround (clunky but effective), so will likely wait for the CRAN release. It is nice to know that it is done and waiting on distribution.

As a random and unrelated question, is rpostgis thread safe?

[crabel99]

Thank you. I will play around with it. As it stands I have a viable workaround (clunky but effective), so will likely wait for the CRAN release. It is nice to know that it is done and waiting on distribution.

Now the new problem that I have is that pgWriteRast won't append the rasters that I create to the temporary table. I have a large number of raster files I'm processing, on the order of tens of thousands. I can execute a db query to move the file from the one created by rpostgis into a temporary table and recycle the names, but this increases the overhead on my database as each raster is written twice and read one extra time. An append option would sure be helpful!

As a random and unrelated question, is rpostgis thread safe?

[dnbucklin]

I should have mentioned- append for pgWriteRast was also added to dev at the same time as clauses for pgGetRast. If you are able to test, any usage at this point would be helpful to identify bugs prior to release.

I haven't tested multi-threading, and I would not assume rpostgis is thread-safe. It's possible that multi-threading pgInsert or pgWriteRast with append=TRUE calls on a single table could cause issues.

[dnbucklin]

eae1f09 Features have been added, now on CRAN (v1.4.3).

[crabel99]

Thank you! I’ve been using the pre release version with no issue since your original response. Thank you for your work on this project!