st_write() to postgis returns "Error: Could not create execute: select * from spatial_ref_sys where srtext="

[SimonCoulombe]

Describe the bug I am running a postgis database on unRaid using what I understand to be the postgis/postgis docker image located at https://registry.hub.docker.com/r/postgis/postgis/

When I try to write to postgis I get an "Error: could not create execute".

additional info: I tried writing an alternate set of polygons and got "I get the "Error in nchar(sm[1L], type = "w") : invalid multibyte string, element 1" error." instead.

To Reproduce

library(DBI)
library(sf)
library(RPostgreSQL) 
library(dplyr)
library(dbplyr)
library(rnaturalearth)


con <- DBI::dbConnect(
  RPostgreSQL::PostgreSQL(),
  dbname = "dbname",
  host = "192.168.1.x",
  user = "user",
  password="password"
)

ne_world <- rnaturalearth::ne_countries(scale = 50, returnclass = "sf")

st_write(ne_world, dsn = con, layer = "ne_world",
         delete_layer = TRUE, append = FALSE)

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: ()
Error: Could not create execute: select * from spatial_ref_sys where srtext = 'GEOGCS["unknown",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AXIS["Longitude",EAST],AXIS["Latitude",NORTH]]'


osm_polygon <- osmdata::getbb(place_name ="Monowi, Boyd County, Nebraska,United states", format_out = "sf_polygon")


st_write(osm_polygon, dsn = con, layer = "osm_polygon",
         delete_layer = TRUE, append = FALSE)
Error in nchar(sm[1L], type = "w") : invalid multibyte string, element 1

Paste the output of your `sessionInfo()` and `sf::sf_extSoftVersion()` ```

sessionInfo() R version 4.1.0 (2021-05-18) Platform: x86_64-pc-linux-gnu (64-bit) Running under: Ubuntu 20.04.2 LTS

Matrix products: default BLAS: /usr/lib/x86_64-linux-gnu/atlas/libblas.so.3.10.3 LAPACK: /usr/lib/x86_64-linux-gnu/atlas/liblapack.so.3.10.3

locale: [1] LC_CTYPE=en_CA.UTF-8 LC_NUMERIC=C LC_TIME=en_CA.UTF-8 LC_COLLATE=en_CA.UTF-8
[5] LC_MONETARY=en_CA.UTF-8 LC_MESSAGES=en_CA.UTF-8 LC_PAPER=en_CA.UTF-8 LC_NAME=C
[9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=en_CA.UTF-8 LC_IDENTIFICATION=C

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] rnaturalearth_0.1.0 dbplyr_2.1.1 dplyr_1.0.7 RPostgreSQL_0.7-3 sf_1.0-4
[6] DBI_1.1.1 testthat_3.1.1 devtools_2.4.2 usethis_2.1.5

loaded via a namespace (and not attached): [1] Rcpp_1.0.7 lubridate_1.8.0 lattice_0.20-44 prettyunits_1.1.1
[5] class_7.3-19 ps_1.6.0 assertthat_0.2.1 rprojroot_2.0.2
[9] digest_0.6.29 utf8_1.2.2 R6_2.5.1 rnaturalearthdata_0.1.0 [13] evaluate_0.14 e1071_1.7-9 pillar_1.6.4 rlang_0.4.12
[17] rstudioapi_0.13 callr_3.7.0 rmarkdown_2.11.3 desc_1.4.0
[21] stringr_1.4.0 cancensus_0.4.3 proxy_0.4-26 compiler_4.1.0
[25] janitor_2.1.0 xfun_0.29 pkgconfig_2.0.3 pkgbuild_1.3.0
[29] htmltools_0.5.2 tidyselect_1.1.1 tibble_3.1.6 fansi_0.5.0
[33] crayon_1.4.2 withr_2.4.3 wk_0.5.0 grid_4.1.0
[37] jsonlite_1.7.2 lifecycle_1.0.1 magrittr_2.0.1 units_0.7-2
[41] KernSmooth_2.23-20 cli_3.1.0 stringi_1.7.6 cachem_1.0.6
[45] fs_1.5.2 remotes_2.4.2 sp_1.4-6 snakecase_0.11.0
[49] ellipsis_0.3.2 generics_0.1.1 vctrs_0.3.8 s2_1.0.7
[53] geojsonsf_2.0.1 tools_4.1.0 glue_1.5.1 purrr_0.3.4
[57] processx_3.5.2 pkgload_1.2.4 fastmap_1.1.0 yaml_2.2.1
[61] sessioninfo_1.1.1 classInt_0.4-3 memoise_2.0.0 knitr_1.36

sf::sf_extSoftVersion() GEOS GDAL proj.4 GDAL_with_GEOS USE_PROJ_H PROJ "3.8.0" "3.0.4" "6.3.1" "true" "true" "6.3.1"

[edzer]

@etiennebr any idea?

[etiennebr]

Bonjour Simon! This looks like permission issues to me. Make sure user can select on spatial_ref_sys.

[etiennebr]

I tried to reproduce your issue, but it seems to work on my side no matter what. I'm linking against proj 7, which might make a difference (because an unmatched crs will attempt other strategies on spatial_ref_sys).

As a quick fix, I think it could work by forcing the srid with st_set_crs.

st_write(st_set_crs(ne_world, 4326), dsn = con, layer = "ne_world", delete_layer = TRUE, append = FALSE)

I'm still curious to investigate further, or if the workaround I suggested fails. Would you mind also sharing the exact docker command you're using?

[SimonCoulombe]

Salut Étienne!

The line with the forced srid returns this error:

> st_write(st_set_crs(ne_world, 4326), dsn = con, layer = "ne_world", delete_layer = TRUE, append = FALSE)
Error in nchar(sm[1L], type = "w") : invalid multibyte string, element 1

I'm.. unsure what docker image I am using.

This is on my unRAID server and I'm a newbie on that platform. I'm running a "community app" that claims to be based on postgis/postgis, but I'm not sure how to know exactly what is the difference. The community app is here : https://unraid.net/community/apps?notTheDroidYoureLookingFor=a3R1bGd3Z2pha3ZidmVrbmF5bWx1bGJvcXFlanJlamI%3D&q=postgis#r

I was hoping it might be a known issue with a quick fix. I really don't want you to waste your time on what might be an issue specific to this unRAID image.

I'll do some googling to make sure user can select on spatial_ref_sys. I'll try with the normal postgis/postgis on my ubuntu box and report back. I'll report back after I'Ve done that.

thanks.