As mentioned in #592, st_read does not work with an odbc dsn.
suppressPackageStartupMessages(library(sf))
library(DBI)
nc <-
st_read(
system.file("shape/nc.shp", package = "sf"),
stringsAsFactors = FALSE,
quiet = TRUE
)
odbc_con <-
dbConnect(
odbc::odbc(),
driver = "PostgreSQL Unicode(x64)",
host = "localhost",
database = "postgis"
)
st_write(obj = nc, dsn = odbc_con, layer = "nc_tbl", overwrite = TRUE)
#> Note: method with signature 'DBIObject#sf' chosen for function 'dbDataType',
#> target signature 'PostgreSQL#sf'.
#> "OdbcConnection#ANY" would also be valid
st_read(dsn = odbc_con, layer = "nc_tbl")
#> Error in CPL_read_wkb(x, EWKB, spatialite): range check error: WKB buffer too small. Input file corrupt?
UPDATE: Now with traceback()
```{r}
traceback()
> 8: .Call("_sf_CPL_read_wkb", PACKAGE = "sf", wkb_list, EWKB, spatialite)
> 7: CPL_read_wkb(x, EWKB, spatialite)
> 6: st_as_sfc.WKB(as_wkb(x), EWKB = EWKB)
> 5: st_as_sfc(as_wkb(x), EWKB = EWKB)
> 4: FUN(X[[i]], ...)
> 3: lapply(tbl[geom_column], postgis_as_sfc, EWKB = EWKB, conn = dsn)
> 2: st_read.DBIObject(dsn = odbc_con, layer = "nc_tbl")
> 1: st_read(dsn = odbc_con, layer = "nc_tbl")
<details>
<summary>Session info</summary>
``` r
dplyr::glimpse(dbGetInfo(odbc_con))
#> List of 13
#> $ dbname : chr "postgis"
#> $ dbms.name : chr "PostgreSQL"
#> $ db.version : chr "9.6.8"
#> $ username : chr "Daniel"
#> $ host : chr ""
#> $ port : chr ""
#> $ sourcename : chr ""
#> $ servername : chr ""
#> $ drivername : chr "PSQLODBC35W.DLL"
#> $ odbc.version : chr "03.80.0000"
#> $ driver.version : chr "09.06.0500"
#> $ odbcdriver.version : chr "03.51"
#> $ supports.transactions: logi TRUE
#> - attr(*, "class")= chr [1:3] "PostgreSQL" "driver_info" "list"
devtools::session_info()
#> Session info -------------------------------------------------------------
#> setting value
#> version R version 3.4.4 (2018-03-15)
#> system x86_64, mingw32
#> ui RTerm
#> language en
#> collate German_Germany.1252
#> tz Europe/Berlin
#> date 2018-04-24
#> Packages -----------------------------------------------------------------
#> package * version date source
#> assertthat 0.2.0 2017-04-11 CRAN (R 3.4.2)
#> backports 1.1.2 2017-12-13 CRAN (R 3.4.3)
#> base * 3.4.4 2018-03-15 local
#> bindr 0.1.1 2018-03-13 CRAN (R 3.4.4)
#> bindrcpp 0.2.2 2018-03-29 CRAN (R 3.4.4)
#> bit 1.1-12 2014-04-09 CRAN (R 3.4.0)
#> bit64 0.9-7 2017-05-08 CRAN (R 3.4.0)
#> blob 1.1.1 2018-03-25 CRAN (R 3.4.4)
#> class 7.3-14 2015-08-30 CRAN (R 3.4.4)
#> classInt 0.2-3 2018-04-16 CRAN (R 3.4.4)
#> compiler 3.4.4 2018-03-15 local
#> datasets * 3.4.4 2018-03-15 local
#> DBI * 0.8 2018-03-02 CRAN (R 3.4.3)
#> devtools 1.13.5 2018-02-18 CRAN (R 3.4.3)
#> digest 0.6.15 2018-01-28 CRAN (R 3.4.3)
#> dplyr 0.7.4 2017-09-28 CRAN (R 3.4.2)
#> e1071 1.6-8 2017-02-02 CRAN (R 3.4.2)
#> evaluate 0.10.1 2017-06-24 CRAN (R 3.4.0)
#> glue 1.2.0.9000 2018-04-13 Github (tidyverse/glue@99e0171)
#> graphics * 3.4.4 2018-03-15 local
#> grDevices * 3.4.4 2018-03-15 local
#> grid 3.4.4 2018-03-15 local
#> hms 0.4.2 2018-03-10 CRAN (R 3.4.4)
#> htmltools 0.3.6 2017-04-28 CRAN (R 3.4.0)
#> knitr 1.20 2018-02-20 CRAN (R 3.4.3)
#> magrittr 1.5 2014-11-22 CRAN (R 3.4.2)
#> memoise 1.1.0 2018-01-08 Github (hadley/memoise@611cfad)
#> methods * 3.4.4 2018-03-15 local
#> odbc 1.1.5 2018-01-23 CRAN (R 3.4.3)
#> pillar 1.2.1 2018-02-27 CRAN (R 3.4.3)
#> pkgconfig 2.0.1 2017-03-21 CRAN (R 3.4.0)
#> R6 2.2.2 2017-06-17 CRAN (R 3.4.0)
#> Rcpp 0.12.16 2018-03-13 CRAN (R 3.4.4)
#> rlang 0.2.0.9001 2018-04-13 Github (tidyverse/rlang@82b2727)
#> rmarkdown 1.9.8 2018-04-05 Github (rstudio/rmarkdown@8cfc878)
#> RPostgreSQL 0.6-3 2018-01-24 Github (tomoakin/RPostgreSQL@f93cb17)
#> rprojroot 1.3-2 2018-01-03 CRAN (R 3.4.3)
#> sf * 0.6-2 2018-04-23 Github (r-spatial/sf@5a708f0)
#> spData 0.2.8.3 2018-03-25 CRAN (R 3.4.4)
#> stats * 3.4.4 2018-03-15 local
#> stringi 1.1.7 2018-03-12 CRAN (R 3.4.4)
#> stringr 1.3.0 2018-02-19 CRAN (R 3.4.3)
#> tibble 1.4.2 2018-01-22 CRAN (R 3.4.3)
#> tools 3.4.4 2018-03-15 local
#> udunits2 0.13 2016-11-17 CRAN (R 3.4.1)
#> units 0.5-1 2018-01-08 CRAN (R 3.4.3)
#> utils * 3.4.4 2018-03-15 local
#> withr 2.1.2 2018-03-15 CRAN (R 3.4.4)
#> yaml 2.1.18 2018-03-08 CRAN (R 3.4.4)
dbDisconnect(odbc_con)
dpprdan
All 4 comments
Thanks @dpprdan. After running your example, here's what I see.
library(sf)
#> Linking to GEOS 3.5.0, GDAL 2.2.2, proj.4 4.8.0
library(DBI)
odbc_con <- pg <- dbConnect(odbc::odbc(), "PostgreSQL")
pg_con <- dbConnect(RPostgres::Postgres(), host = "localhost",
dbname = "postgis")
dbGetQuery(odbc_con, "SELECT * from nc_bool limit 1;")$geometry %>% nchar()
#> [1] 255
dbGetQuery(pg_con, "SELECT * from nc_bool limit 1;")$geometry %>% nchar()
#> [1] 916
My intuition is that the geometry type is coerced to a VARCHAR(255), but I don't know where it happens in odbc (most likely not in the database). Maybe @krlmlr or @jimhester have an idea?
etiennebr
on 25 Apr 2018
I'm not familiar with the internals of _odbc_.
krlmlr
on 9 May 2018
Just to be explicit if someone stumbles upon this. The issue isn't fixed, but is an issue with odbc rather than sf, see https://github.com/r-dbi/odbc/issues/202.
dpprdan
on 16 Nov 2018
👍1
The final solution is to use maxvarcharsize = 0 in the connection. This fully closes the issue. The solution is from @ellisvalentiner (in r-dbi/odbc#202 and further detailed on his blog).
odbc_con <- dbConnect(odbc::odbc(), "postgres", maxvarcharsize = 0)
dbGetQuery(odbc_con, "SELECT * from nc_tbl limit 1;")$geometry %>% nchar()
#> [1] 916
st_read(dsn = odbc_con, layer = "nc_tbl")
#> Simple feature collection with 100 features and 14 fields
#> geometry type: MULTIPOLYGON
#> dimension: XY
#> bbox: xmin: -84.32385 ymin: 33.88199 xmax: -75.45698 ymax: 36.58965
#> epsg (SRID): 4267
#> proj4string: +proj=longlat +datum=NAD27 +no_defs
#> First 10 features:
#> AREA PERIMETER CNTY_ CNTY_ID NAME FIPS FIPSNO CRESS_ID BIR74 SID74
#> 1 0.114 1.442 1825 1825 Ashe 37009 37009 5 1091 1
#> 2 0.061 1.231 1827 1827 Alleghany 37005 37005 3 487 0
#> 3 0.143 1.630 1828 1828 Surry 37171 37171 86 3188 5
#> 4 0.070 2.968 1831 1831 Currituck 37053 37053 27 508 1
#> 5 0.153 2.206 1832 1832 Northampton 37131 37131 66 1421 9
#> 6 0.097 1.670 1833 1833 Hertford 37091 37091 46 1452 7
#> 7 0.062 1.547 1834 1834 Camden 37029 37029 15 286 0
#> 8 0.091 1.284 1835 1835 Gates 37073 37073 37 420 0
#> 9 0.118 1.421 1836 1836 Warren 37185 37185 93 968 4
#> 10 0.124 1.428 1837 1837 Stokes 37169 37169 85 1612 1
#> NWBIR74 BIR79 SID79 NWBIR79 geometry
#> 1 10 1364 0 19 MULTIPOLYGON (((-81.47276 3...
#> 2 10 542 3 12 MULTIPOLYGON (((-81.23989 3...
#> 3 208 3616 6 260 MULTIPOLYGON (((-80.45634 3...
#> 4 123 830 2 145 MULTIPOLYGON (((-76.00897 3...
#> 5 1066 1606 3 1197 MULTIPOLYGON (((-77.21767 3...
#> 6 954 1838 5 1237 MULTIPOLYGON (((-76.74506 3...
#> 7 115 350 2 139 MULTIPOLYGON (((-76.00897 3...
#> 8 254 594 2 371 MULTIPOLYGON (((-76.56251 3...
#> 9 748 1190 2 844 MULTIPOLYGON (((-78.30876 3...
#> 10 160 2038 5 176 MULTIPOLYGON (((-80.02567 3...
Created on 2019-11-23 by the reprex package (v0.3.0)
etiennebr
on 23 Nov 2019
👍2
Was this page helpful?
0 / 5 - 0 ratings
Related issues
duleise
路
3Comments
angela-li
路
4Comments
jsta
路
4Comments
faridcher
路
4Comments
thiagoveloso
路
3Comments
Most helpful comment
The final solution is to use
maxvarcharsize = 0in the connection. This fully closes the issue. The solution is from @ellisvalentiner (in r-dbi/odbc#202 and further detailed on his blog).Created on 2019-11-23 by the reprex package (v0.3.0)