Can't connect to PostGIS using st_read()

[jguelat]

I can't connect to my PostGIS database using the following command (the same dsn works with ogrinfo):

pol <- st_read("PG:dbname='gis' host='localhost' port=5432 user='mylogin' password='mypassword'", "p1")

I get the following error: Error in eval(substitute(expr), envir, enclos) : Open failed

However the following query is OK with st_read_db():

dbcon <- dbConnect(dbDriver("PostgreSQL"), dbname = "gis", host = "localhost", user = "mylogin", password = "mypassword")
pol <- st_read_db(dbcon, query = "select * from p1")

[edzer]

What does

ogrinfo "PG:dbname='gis' host='localhost' port=5432 user='mylogin' password='mypassword'"

gives you?

[jguelat]

I get a list of all the available tables in the database (including table p1)

[edzer]

 "PostgreSQL" %in% st_drivers()$name

?

[jguelat]

That's the problem, thanks! I always forget that the PostgreSQL driver is not included in the built-in OGR/GDAL which is available on CRAN.

Sorry for the noise (and thanks for the great package)...

[edzer]

I thought it was available, but you're right -- maybe raise an issue there and collect many upvotes?

[jguelat]

Good idea! I'll try...

Done: rwinlib/gdal2#1

[seanangio]

I seem to be facing the same problem trying to connect to a PostGIS database from R. Loading the sf library links to GEOS 3.6.1, GDAL 2.1.3, PROJ 4.9.3. I'm using mac OS 10.14.

"PostgreSQL" %in% st_drivers()$name

returns FALSE. I'm using mac OS 10.14. Does the answer here only apply to Windows users?

Many thanks in advance for any direction! And apologies for re-opening.

[bwcastillo]

Hi @jguelat ,

I now that this post is old, currently I am facing the same problem in windows. Do you remember how is possible resolve this issue?

"PostgreSQL" %in% st_drivers()$name
[1] FALSE

Thanks in advance.

[rsbivand]

Please provide sessionInfo() output with output of sf_extSoftVersion().

[bwcastillo]

@rsbivand Thank you in advance for the fast response. Here is the asked output.

>sessionInfo() 
R version 4.2.2 (2022-10-31 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 22000)

Matrix products: default

locale:
[1] LC_COLLATE=English_Canada.utf8  LC_CTYPE=English_Canada.utf8    LC_MONETARY=English_Canada.utf8 LC_NUMERIC=C                    LC_TIME=English_Canada.utf8    

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

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.9         compiler_4.2.2     pillar_1.8.1       RPostgres_1.4.4    class_7.3-20       tools_4.2.2        bit_4.0.4          tibble_3.1.8      
 [9] jsonlite_1.8.3     lifecycle_1.0.3    lattice_0.20-45    pkgconfig_2.0.3    rlang_1.0.6        DBI_1.1.3          cli_3.4.1          rstudioapi_0.14   
[17] crul_1.3           curl_4.3.3         geojson_0.3.4      geojsonio_0.10.0   e1071_1.7-12       jqr_1.2.3          dplyr_1.0.10       hms_1.1.2         
[25] rgeos_0.5-9        generics_0.1.3     vctrs_0.5.0        bit64_4.0.5        tidyselect_1.2.0   classInt_0.4-8     grid_4.2.2         glue_1.6.2        
[33] httpcode_0.3.0     geojsonsf_2.0.3    sf_1.0-9           R6_2.5.1           fansi_1.0.3        foreign_0.8-83     sp_1.5-0           blob_1.2.3        
[41] tzdb_0.3.0         readr_2.1.3        magrittr_2.0.3     ellipsis_0.3.2     maptools_1.1-5     units_0.8-0        assertthat_0.2.1   V8_4.2.1          
[49] KernSmooth_2.23-20 utf8_1.2.2         proxy_0.4-27       lazyeval_0.2.2 
There were 16 warnings (use warnings() to see them)

> library(sf)
Linking to GEOS 3.9.3, GDAL 3.5.2, PROJ 8.2.1; sf_use_s2() is TRUE

> sf_extSoftVersion()
          GEOS           GDAL         proj.4 GDAL_with_GEOS     USE_PROJ_H           PROJ 
       "3.9.3"        "3.5.2"        "8.2.1"         "true"         "true"        "8.2.1" 

[rsbivand]

I'm away from any Windows machine for several days more, nor do I use PG myself. @etiennebr could you please take a look? A report on the Windows R-devel version: install https://cran.r-project.org/bin/windows/base/R-devel-win.exe, then install sf for R-devel, and see whether a PG driver is present.

[bwcastillo]

@rsbivand you solution worked! I am very glad to received a response from you, thank you so much !!

[rsbivand]

Thanks for trying it out. The new version of Rtools for R 4.3 has a broader det of upstream external libraries. However, R-devel is still being worked on, so may be less stable.

[etiennebr]

My understanding is that the issue was resolved with a reinstall, but happy to have a look if needed.