Error in postgresqlExecStatement for spatial data

General
1

Since this year I work with R to clean data and load them to PostgreSQL.
The procedure worked until august this year. Since then the spatial data (only the spatial data, the other tables can be inserted with the same user) can't be inserted anymore. R gives this error message:

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : FEHLER: Berechtigung nur für Eigentümer der Relation pointdata )

The following versions are installed:

  • R version 3.5.1 (2018-07-02)
  • Platform: x86_64-w64-mingw32/x64 (64-bit)
  • Running under: Windows >= 8 x64 (build 9200)
  • PostgreSQL/postGIS: PostgreSQL 10.5 on Windows x86-64
  • Server hosting PostgreSQL: Windows Server 2012 R2

The following Packages are loaded:
other attached packages:

  • rgdal_1.3-3,
  • rpostgis_1.4.0
  • raster_2.6-7
  • sp_1.3-1
  • bindrcpp_0.2.2
  • rlang_0.2.1
  • stringr_1.3.1
  • reshape2_1.4.3
  • dplyr_0.7.6
  • xlsx_0.6.1
  • lubridate_1.7.4
  • RPostgreSQL_0.6-2
  • DBI_1.0.0
  • RevoUtils_11.0.1
  • RevoUtilsMath_11.0.0

Beside some non spatial tables there is one SpatialPointDataFrame I used to insert into PostgreSQL.

Code with example data

Punktdaten <- data.frame(ID = "xyz",
                         x_koord=2701598,
                         y_koord=1237470.03)

koord <- Punktdaten[c("x_koord", "y_koord")]
coordinates(koord) <- ~ x_koord + y_koord 
koord@proj4string <- CRS("+init=epsg:2056")
Punktdaten<- SpatialPointsDataFrame(coords = koord, data = Punktdaten,
                                    proj4string = CRS("+init=epsg:2056"))

pgInsert(con, name=c("public","pointdata"), 
         Punktdaten, 
         geom = "geom", df.mode = FALSE,
         overwrite=FALSE,
         df.geom = c("geom","(POINT,2056)"), geog = FALSE)

Error message:
Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : FEHLER: Berechtigung nur für Eigentümer der Relation pointdata )

Some experiments to solve the problem:
I tried to insert the spatial data using a different package:
library(RPostgreSQL)
library(postGIStools)
postgis_insert(con, Punktdaten, "sondierung_punktdaten",
geom_name="geom")

It does not work, giving this Error:
Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : FEHLER: Geometry SRID (0) does not match column SRID (2056) )

But inserting the table as DataFrame only (without Spatial Information), the procedure is successful.

This makes me wonder if the problem is because of autorithy as stated in the first Error message.

Somebody got the same problem and found a solution?
Some ideas how to find the solution?
Some ideas whrer to dig?

Thanks in adwance.

2

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.