search

r-spatial / sf

Simple Features for R
https://r-spatial.github.io/sf/
Other
1.34k stars 298 forks source link

st_read reads null column as geometry #866

Closed dpprdan closed 6 years ago

dpprdan commented 6 years ago

st_read() reads a text column with all null values as an empty geometry column (sfc_GEOMETRYCOLLECTION) when dsn is a {RPostgreSQL} connection.

suppressPackageStartupMessages(library(RPostgreSQL))
suppressPackageStartupMessages(library(sf))
suppressPackageStartupMessages(library(dplyr))

nc_name <- 
  st_read(
    system.file("gpkg/nc.gpkg", package="sf"), 
    stringsAsFactors = FALSE, 
    quiet = TRUE
  )[, c("NAME", "geom")] %>% 
  mutate(nc_na = NA_character_)

rpostgresql_con <- dbConnect(RPostgreSQL::PostgreSQL(),
                 host = "localhost", 
                 dbname = "postgis", 
                 user = Sys.getenv("pg_localhost_user"),
                 password = Sys.getenv("pg_localhost_password"))

dbWriteTable(
  conn = rpostgresql_con,
  name = "nc_rpostgresql",
  value = nc_name,
  overwrite = TRUE
)
#> [1] TRUE

nc_read <- st_read(rpostgresql_con, layer = "nc_rpostgresql")

str(nc_read)
#> Classes 'sf' and 'data.frame':   100 obs. of  3 variables:
#>  $ NAME : chr  "Ashe" "Alleghany" "Surry" "Currituck" ...
#>  $ nc_na:sfc_GEOMETRYCOLLECTION of length 100; first list element:  list()
#>   ..- attr(*, "class")= chr  "XY" "GEOMETRYCOLLECTION" "sfg"
#>  $ geom :sfc_MULTIPOLYGON of length 100; first list element: List of 1
#>   ..$ :List of 1
#>   .. ..$ : num [1:27, 1:2] -81.5 -81.5 -81.6 -81.6 -81.7 ...
#>   ..- attr(*, "class")= chr  "XY" "MULTIPOLYGON" "sfg"
#>  - attr(*, "sf_column")= chr "nc_na"
#>  - attr(*, "agr")= Factor w/ 3 levels "constant","aggregate",..: NA NA
#>   ..- attr(*, "names")= chr  "NAME" "geom"

dbDisconnect(rpostgresql_con)
#> [1] TRUE

This problem apparently does not appear with a {RPostgres} connection.

suppressPackageStartupMessages(library(RPostgres))

rpostgres_con <- dbConnect(RPostgres::Postgres(),
                             host = "localhost", 
                             dbname = "postgis", 
                             user = Sys.getenv("pg_localhost_user"),
                             password = Sys.getenv("pg_localhost_password"))

dbWriteTable(
  conn = rpostgres_con,
  name = "nc_rpostgres",
  value = nc_name,
  overwrite = TRUE
)
#> Note: method with signature 'DBIObject#sf' chosen for function 'dbDataType',
#>  target signature 'PqConnection#sf'.
#>  "PqConnection#ANY" would also be valid

nc_read <- st_read(rpostgres_con, layer = "nc_rpostgres")

str(nc_read)
#> Classes 'sf' and 'data.frame':   100 obs. of  3 variables:
#>  $ NAME : chr  "Ashe" "Alleghany" "Surry" "Currituck" ...
#>  $ nc_na: chr  NA NA NA NA ...
#>  $ geom :sfc_MULTIPOLYGON of length 100; first list element: List of 1
#>   ..$ :List of 1
#>   .. ..$ : num [1:27, 1:2] -81.5 -81.5 -81.6 -81.6 -81.7 ...
#>   ..- attr(*, "class")= chr  "XY" "MULTIPOLYGON" "sfg"
#>  - attr(*, "sf_column")= chr "geom"
#>  - attr(*, "agr")= Factor w/ 3 levels "constant","aggregate",..: NA NA
#>   ..- attr(*, "names")= chr  "NAME" "nc_na"

dbDisconnect(rpostgres_con)
Session info ``` r devtools::session_info() #> - Session info ---------------------------------------------------------- #> setting value #> version R version 3.5.1 (2018-07-02) #> os Windows 10 x64 #> system x86_64, mingw32 #> ui RTerm #> language en #> collate German_Germany.1252 #> ctype German_Germany.1252 #> tz Europe/Berlin #> date 2018-10-17 #> #> - Packages -------------------------------------------------------------- #> package * version date lib #> assertthat 0.2.0 2017-04-11 [1] #> backports 1.1.2 2017-12-13 [1] #> base64enc 0.1-3 2015-07-28 [1] #> bindr 0.1.1 2018-03-13 [1] #> bindrcpp 0.2.2 2018-03-29 [1] #> bit 1.1-14 2018-05-29 [1] #> bit64 0.9-7 2017-05-08 [1] #> blob 1.1.1 2018-03-25 [1] #> callr 3.0.0 2018-08-24 [1] #> class 7.3-14 2015-08-30 [2] #> classInt 0.2-3 2018-04-16 [1] #> cli 1.0.1 2018-09-25 [1] #> crayon 1.3.4 2017-09-16 [1] #> DBI * 1.0.0 2018-05-02 [1] #> debugme 1.1.0 2017-10-22 [1] #> desc 1.2.0 2018-05-01 [1] #> devtools 1.13.6.9000 2018-10-09 [1] #> digest 0.6.18 2018-10-10 [1] #> dplyr * 0.7.6 2018-06-29 [1] #> e1071 1.7-0 2018-07-28 [1] #> evaluate 0.12 2018-10-09 [1] #> fs 1.2.6 2018-08-23 [1] #> glue 1.3.0 2018-07-17 [1] #> hms 0.4.2.9001 2018-08-16 [1] #> htmldeps 0.1.1 2018-07-30 [1] #> htmltools 0.3.6 2017-04-28 [1] #> knitr 1.20 2018-02-20 [1] #> magrittr 1.5 2014-11-22 [1] #> memoise 1.1.0 2017-04-21 [1] #> pillar 1.3.0.9000 2018-08-16 [1] #> pkgbuild 1.0.2 2018-10-16 [1] #> pkgconfig 2.0.2 2018-08-16 [1] #> pkgload 1.0.1 2018-10-11 [1] #> prettyunits 1.0.2 2015-07-13 [1] #> processx 3.2.0 2018-08-16 [1] #> ps 1.2.0 2018-10-16 [1] #> purrr 0.2.5 2018-05-29 [1] #> R6 2.3.0 2018-10-04 [1] #> Rcpp 0.12.19 2018-10-01 [1] #> remotes 2.0.0 2018-10-11 [1] #> rlang 0.2.2 2018-08-16 [1] #> rmarkdown 1.10.13 2018-09-04 [1] #> RPostgres * 1.1.1 2018-05-06 [1] #> RPostgreSQL * 0.6-3 2018-08-20 [1] #> rprojroot 1.3-2 2018-01-03 [1] #> sessioninfo 1.1.0 2018-09-25 [1] #> sf * 0.6-4 2018-09-05 [1] #> spData 0.2.9.4 2018-09-15 [1] #> stringi 1.2.4 2018-07-20 [1] #> stringr 1.3.1 2018-05-10 [1] #> testthat 2.0.1 2018-10-13 [1] #> tibble 1.4.2 2018-01-22 [1] #> tidyselect 0.2.5 2018-10-11 [1] #> units 0.6-1 2018-09-21 [1] #> usethis 1.4.0 2018-08-14 [1] #> withr 2.1.2 2018-03-15 [1] #> yaml 2.2.0 2018-07-25 [1] #> source #> CRAN (R 3.5.1) #> CRAN (R 3.5.0) #> CRAN (R 3.5.0) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.0) #> CRAN (R 3.5.0) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> Github (r-lib/devtools@c24b146) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> Github (tidyverse/hms@979286f) #> Github (rstudio/htmldeps@c1023e0) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> Github (hadley/colformat@3fabb4e) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> Github (rstudio/rmarkdown@19008bf) #> CRAN (R 3.5.1) #> Github (tomoakin/RPostgreSQL@ac45baf) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> Github (r-spatial/sf@9a49d03) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> CRAN (R 3.5.1) #> #> [1] C:/Users/daniel/Documents/.R/win-library #> [2] C:/Program Files/R/R-3.5.1/library ```
ateucher commented 6 years ago

This should be fixed in sf v0.7.0 (which I believe should be on CRAN shortly) via this PR. If you install from github with devtools::install_github("r-spatial/sf") it should work...

dpprdan commented 6 years ago

It sure is! And I should have searched not only the open issues before opening this... 🤦‍♂️ Thanks a lot and sorry for the noise. BTW v0.7-0 is on CRAN now.