cidree/rpostgis

rpostgis: R Interface to a 'PostGIS' Database

rpostgis's Issues

Please remove dependencies on **rgdal**, **rgeos**, and/or **maptools**

This package depends on (depends, imports or suggests) raster and one or more of the retiring packages rgdal, rgeos or maptools (https://r-spatial.org/r/2022/04/12/evolution.html). Since raster 3.6.3, all use of external FOSS library functionality has been transferred to terra, making the retiring packages very likely redundant. It would help greatly if you could remove dependencies on the retiring packages as soon as possible.

pgGetRast() error

Context

• I have added several rasters to a pgsql DB using pgWriteRast(..., constraints = FALSE). After all my rasters have been added to the db, I then manually run:

SELECT AddRasterConstraints('my_rasters'::name, 'rast'::name);

• I add constraints after loading all rasters because it takes too much time to add constraints while loading each individual raster.

• Everything is added successfully:
  

• Retrieving any of the rasters using pgGetRast does not work...

Error

Error in pgSRID(conn, rout@crs) : 
  No SRID matches found. Re-run with 'create.srid = TRUE' to create new SRID entry in spatial_ref_sys.

Question

• Do I really need to re-run with create.srid = TRUE? Or is this a bug?
• Shouldn't running the AddRasterConstraints manually be enough?

Feature request: Allow multiple columns in dbAddKey

At the moment, dbAddKey seems to only be able to set a primary key on a single column, but not on multiple columns (e.g. longitudinal table with id and year). Would it be possible to add this feature?

Feature Request: add clauses functionality to pgGetRast

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!

terra support

Hey Mathieu!

I really liked playing around with rpostgis recently.

Is there any chance that the package will get a minor upgrade any time soon that incorporates the new terra package? Currently, pgWriteRast fails with a SpatRast object because it doesn't know how to handle the CRS.

Cheers
Marco

pgInsert hang with SPDF

I have RStudio running R v3.5.0 with rpostgis 1.4.1 running under CentOS 7.5 connecting to a postgres DB:

PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

Problem: pgInsert() hangs with no feedback when using spatial data.

What works:
test <- data.frame(x=0, y=0, a=1)
pgInsert(conn, name=c("public", "test_table1"), data.obj=test)

What doesn't work (hangs or crashes R):
coordinates(test) <- ~x + y
pgInsert(conn, name=c("public", "test_table2"), data.obj=test)

The database has the postgis extension
What would be the best way to diagnose the problem?

Error: PostGIS extension not available. (PostGIS is installed)

I'm running R 4.0.01 with the latest rpostgis. I've verified that I have postgis installed on my PG server. SELECT PostGIS_Version() returns 3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1.

When I run the following:

con <- dbConnect("PostgreSQL", dbname="ross_projects", host="192.168.3.21", user="rwardrup", password="REDACTED")
pgInsert(con, name=c("arlington_restaurants", "restaurant_locations"), data.obj = data, overwrite = TRUE)

I get Error in pgPostGIS(conn) : PostGIS extension not available. I'm able to run postgis queries straight on the DB, so I'm guessing that the postgis installation is fine. Is there anything I can check on the R side?

SSL mode with RPostgreSQL driver

I try to connect to a SSL PostgreSQL instance using RPostgreSQL , but apparently it isn't supported.
It works fine when the library used is 'RPostgres' , however rpostgis only works with RPostgreSQL driver connections . 'RPostgres' library has better maintenance lately , would be useful allow connections from this library .

ERROR: PostGIS is already installed in schema 'public', uninstall it first

I tried to run the examples of pgInsert, after opening a connection with

conn = dbConnect(PostgreSQL(), dbname = "postgis")

but get the following errors:

...
>      pgInsert(conn, name = c("public", "meuse_data"), data.obj = spdf)
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  PostGIS is already installed in schema 'public', uninstall it first

does this suggest that you assume the database is not a PostGIS database?

pgGetRast() SSL SYSCALL error: EOF detected

I have a raster from here: ftp://ftp.fgdl.org/pub/state/usgsdem.zip

After converting to geotiff I loaded to PostGIS as raster2pgsql -s 4269 -I -t auto -M ./usgsdem/florida_dem.tif | psql -d rpostgisLT

When trying to get it into R from PostGIS with pgGetRast(conn, c("public", "florida_dem")), I get

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : SSL SYSCALL error: EOF detected
)

Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: select 
              st_xmax(st_envelope(rast)) as xmx,
              st_xmin(st_envelope(rast)) as xmn,
              st_ymax(st_envelope(rast)) as ymx,
              st_ymin(st_envelope(rast)) as ymn,
              st_width(rast) as cols,
              st_height(rast) as rows
              from
              (select st_union("rast",1) rast from "public"."florida_dem") as a;

plus a huge memory leak.

However, this works:

dsn="PG:dbname='rpostgisLT' host=localhost user='user' password='pw' port=5432 schema='public' table='florida_dem' mode=2"
ras <- readGDAL(dsn) # Get your file as SpatialGridDataFrame
ras2 <- raster(ras,1)

Lines in raster after importing from Database

Hi,

I have a large dem stored on a remote DB and when importing with pgGetRast, lines appear on seamingly random locations :

When using QGIS or the readGDAL function in R, there are no issues. Any idea where this could be originating from? When importing smaller dems, there are no such artifacts.

Thanks!

pgWriteRast SnapToGrid causes Raster resizing and mismatch

I have been trying to get pgWriteRast to work for some large raster files.
Example raster file I'm having issues with https://ufile.io/25trv

I am getting warnings / error "NOTICE: Cannot set value for pixel( x, y) outside raster bounds: X' x Y' ". I dug around a bit and it seems like Line 175 through 180 ST_SnapToGrid is changing the Raster width / height and causing mismatch once you get to Line 189 through 194 where you update pixel values. Could you fix this issue perhaps by adding band within b==1 loop but SnapToGrid after pixel value update?

dbSchema() returns NULL instead of FALSE when schema does not exist in database

I am using dbSchema() to check for the existence of a schema name before attempting to pgInsert() to it. The documentation says that dbSchema() will return FALSE if the schema does not exist but it is returning NULL instead. I am running PostgreSQL 15 on a iMac (late 2015) that is running macOS Monterey. The client end is calling dbSchema() from a MacBook Pro running R 3.5.1 and rpostgis version 1.4.3.

pgInsert: No column name matches found in database table.

I received the following error when trying to import an ESRI shapefile using pgInsert into a table in my database:

Error in pgInsertize(data.obj, create.table, force.match, conn, new.id, :
No column name matches found in database table.
Error in pgInsert(connection, name = , c("master", "bounding_box"), geom = "geom", :
Table preparation failed. No changes made to database.

My table has the following fields: id, description and geom, whereas my shapefile has the following: FID, shape and id.

Am I missing an argument or a step that triggers the error message?

My code is as follows:

library(rgdal)
library(rpostgis)
location <- "D:/workings" 
shape <- readOGR(dsn=location,layer="input_shapefile")
shape.df <- as(shape,"data.frame") # convert to dataframe
pgi <- pgInsert(con,name=c("master","bounding_box"),data.obj=shape.df,new.id="gid")

Feature request: upload dataframes as spatial tables

Hi - and thanks for the great work in this project!

I often work with database tables both with and without spatial data/geometries. Most often, I download them as dataframe using dbReadTable and then process them via dplyr (because I mostly work with the data features, not the geometries)). Downloading using dbReadTable results in a column with the ST geometry value as chr. It is my understanding that one can not upload a spatial table using pgInsert by pointing it at this column for the geometry - but that would be very handy for a lot of people I think.

Your opinions? Or am I missing an easier alternative? (I found out that one can't use dplyr on SpatialPointDataFrames)

Thanks!

Using "out db" cloud rasters for Cloud Optimize GeoTiff

Hello team if rpostgis
I was wandering if its possible to connect a bucket and trigger the query through rpostgis for single zone how is explain in the documentation of postgis.
If it so, please could guide me in how would be the step to do it?
Thanks you

pgGetGeom() other.cols = TRUE by default restarts session

Hi,

R version 4.1.2 (2021-11-01) -- "Bird Hippie"
rpostgis_1.4.3 RPostgreSQL_0.7-3 DBI_1.1.2

pgGetGeom has other.cols = T by default and when you use the fucntion without the argument other.cols it restarts my R session instead of throwing an error.

`
#this works
pgGetGeom(con, query=paste0("QUERY HERE "), other.cols=c("col1","col2", "col3"))
pgGetGeom(con, query=paste0("QUERY HERE "), other.cols=F)

#this doesn't work and terminates R session
pgGetGeom(con, query=paste0("QUERY HERE "))
`
This didn't happen with previous versions

Thanks!

Running any SQL query that returns a spatial object

It would be great to have a function in RPostGIS that could be used to run any more complex SQL functions that involves multiple layers. For example, as far as I understand, it is not possible to run a ST_Within() between layers to return a spatial object with pgGetGeom() at the moment. Why not just create a function that sends any SQL statement to PostGIS and returns the result as a spatial data frame? @basille

ERROR: Geometry type (Polygon) does not match column type (MultiPolygon)

Hi,

Using the command pgInsert I easily create (the table did not exist previously) a table with geom type MultiPolygons (pgi data can be found here) on the database. However, when I perform the same command to insert new polygons (pgi data can be found here), from same source but different extension, it gives me this error:

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  Geometry type (Polygon) does not match column type (MultiPolygon)
)

There must be a solution using sf package or RPostgreSQL package, but I would appreciate if it can be solved with this excellent interface, directly from sp Spatial Data.

Command I used to perform both inserts is:

pgi = pgInsert(connection, name = c("Tree","tree_valencia"),
               data.obj = polygonTrees, geom = "geom", encoding = c("latin1", "UTF-8"), return.pgi = T)

The sources (shape format) can be found here: Valencia_Trees.zip

Thanks in advance!

Error in pgCheckGeom(conn, name, geom)

Hi, Thanks for creating this package which looks very useful. Unfortunately I have got this error trying to use this package,

VOA_ValsLond <- pgGetGeom(con, "VOA_Vals_Lsoa_Lond_g",geom = "geom")
Error in pgCheckGeom(conn, name, geom) : 
  Table/view 'public.VOA_Vals_Lsoa_Lond_g' is not listed in geometry_columns or geography_columns.

I am sure the geom column is fine because I have linked the postgis to QGIS and it maps the table without any issues. Any suggestions to correct this?

Read geometries from materialized views

I think this is more of a feature request to add support for materialized views, unless I'm missing something about the pgGetGeom() or dbReadDataFrame() functions, which fail when I try to read a materialized view with this sort of error:

Error in dbReadDataFrame(con, c("public", "properties")) : 
Table public.properties not found.

Geometry columns from materialized views do appear in pgListGeom().

Thanks!

pgInsert using `sf` objects

Hi,

Is there any plan to allow uploading sf vectors to a PostGIS database using pgInsert?
If not, that would be a wonderful new feature.

pgWriteGeom() fails when writing a data frame with a timestamp column

When attempting to write a data frame that contains a timestamp with the pgWriteGeom() function, the function throws an error when it attempts to write the .R_df_defs table.

Creating new table...
New R data frame definitions table created ("owm".".R_df_defs").
Error in pgWriteGeom(conn, c("owm", "test1234"), df, df.mode = FALSE) : 
  Table preparation failed. No changes made to database.

It should be noted that this occurs even when the user tries to set df.mode to FALSE as pgWriteGeom() seems to have this hardcoded as TRUE for data frames.

Example:

library(rpostgis)

df <- data.frame(dt = Sys.time(),
           x = 1:3,
           y = letters[1:3])

pgWriteGeom(conn, "test_table"), df, df.mode = FALSE)

Ubuntu 20.04.6 LTS, R version 4.3.1, rpostgis_1.5.1

Thanks!

About the future of `rpostgis`

This issue is the central place to report news about the situation of rpostgis.

The package rpostgis is at a crossroad. The main purpose of rpostgis is to provide an interface between R and PostGIS to transparently transfer spatial data (both vectors and rasters) — secondarily, rpostgis also provides convenience functions to execute common procedures in PostGIS.

rpostgis was however developed (by @dnbucklin and myself) at a time when both sp and raster were the de facto reference packages for spatial data (first stable release of rpostgis in August 2016). Since then, R as seen an incredibly active development of the spatial ecosystem, most notably the packages sf, terra and stars. To stay relevant, rpostgis would need to switch to these modern classes of spatial objects, and thus support sf, terra and stars. In addition, packages rgdal, rgeos and maptools will retire by the end of 2023, which also means that rpostgis not only need to support the modern packages mentioned above, but also remove dependencies to rgeos (see this issue on rpostgis repository).

Altogether, this would require a major overhaul of rpostgis. Unfortunately, as our positions have evolved, neither @dnbucklin or myself have the time and resources to take care of this. If nothing happens, rpostgis will thus simply retire by the end of 2023 as well. We can however oversee a transition to a new model, maintainer or community for rpostgis.

Lastly, there is a single package that relies on rpostgis: lucas (on CRAN) (package to download and create the DB of LUCAS Data Harmonized), in the form of an import.

Steps to be taken as of now are:

• Contact lucas' maintainer (Momchil Yordanov) to see if they have enough interest in rpostgis to take it over and assure future development and maintenance, or if their use of the package justifies it. → by email
• Raise an issue in the r-spatial repository dedicated to discussion about the status and relevance of rpostgis. To start with, we need to establish whether the purpose of rpostgis is still relevant in the current R spatial ecosystem (in particular, sf has a specific DB architecture and should be able to handle the vector part; it is not so clear for terra and stars for the raster counterpart). If rpostgis is deemed relevant, see if anyone is interested in taking over development and maintenance of the package. → r-spatial/discuss#58
• Contact ROpenSci and ask pretty much the same question about rpostgis. In the end, see if ROpenSci team and community would like to take over development and maintenance of the package as part of their Geospatial category (access, manipulate, convert geospatial data). → https://discuss.ropensci.org/t/rpostgis-retiring-soon-call-for-maintainers/3483

Of course, @dnbucklin and myself remain available (as much as possible) to support a smooth transition.

Error when using a single colum apart from geometry to get spatial object back

Hi,
First thank you for building this package which is exactly what I was looking for as most database packages don't handle spatial objects.

However, when I want to extract a geometry with a single other column, there's problem because the returned data object is not a data.frame and I get this error:

 Error in validObject(.Object) : 
  invalid class “SpatialPointsDataFrame” object: invalid object for slot "data" in class "SpatialPointsDataFrame": got class "integer", should be or extend class "data.frame" 

I couldn't make a reproducible example but here is a conceptual one.

my_table

x (numeric)	y (numeric)	coord (ST_Point)
1	10.1	ST_POINT(x1, y1)
2	11.1	ST_POINT(x2, y2)
3	12.1	ST_POINT(x3, y3)

If i want to retrieve all coords it works with:

spatial_table = pgGetGeom(local_db, "my_table", "coord")
# or
spatial_table = pgGetGeom(local_db, "my_table", "coord", other.cols = c("x", "y"))

but with a single column it breaks

# Error above
spatial_table = pgGetGeom(local_db, "my_table", "coord", other.cols = c("x"))
# Error in validObject(.Object) : 
#   invalid class “SpatialPointsDataFrame” object: invalid object for slot "data" in class "SpatialPointsDataFrame": got class "integer", should be or extend class "data.frame" 

Is there a way to fix that ? (for the moment I query two columns and get rid of one)

Support for RPostgres connection

Hi,

Are there any plans to add support for a RPostgres connection as the package is more actively maintained and supports SSL connections from Windows? SSL connections from RPostgresql only work from Linux it seems.