Sf st_read postgis

-------- Doorgestuurd bericht --------
Onderwerp: 	Re: [R-sig-Geo] Sf st_read postgis
Datum: 	Thu, 14 Jun 2018 13:48:17 +0200
Van: 	Peter <peter at greenstat.nl>
Aan: 	Jan Willem van der Lichte <geodude at mail.com>

Hi Jan Willem,

reading from a pgdatabase seems extremly sensitive for upper/lowercase 
column titles and irregularities IN data (like '+'? and '&' signs)

I tested your query string on a db file in my local pgdatabase and? got 
the same error? on a data row with a text field with a '+' in the data.

My guess is there is a similair issue in your db.

An easy solution is :
library(sf)
library(tidyverse)

xx = st_read(connz, "vakantie") %>%? slice(,1:3)

(I know, ALL the data is read in first en then the row 1:3 are selected 
....)

# also? try this example

library(sf)
library(mapview)
library(tidyverse)
library(RPostgreSQL)

# read package file nc
nc <- st_read(system.file("shape/nc.shp", package="sf"))

# adjust to your local db parameters
ctest = dbConnect(PostgreSQL(), user="usr",dbname = "db")

# write nc file to db
st_write(nc,ctest)

# check if oke
st_read(ctest, "nc")

st_read(ctest,"nc") %>% slice(.,1:3)

st_read(ctest,"nc") %>% mapview(zcol="NAME")

## all oke !

st_read(ctest,? query="select * from nc limit 1") # oke

# this is the example given in 
https://r-spatial.github.io/sf/reference/st_read.html
st_read(ctest,? "nc",query="select * from nc limit 1") # not oke!

st_read(ctest,? query="select * from nc? where NAME = 'Surry'") # not oke

st_read(ctest,? query="select * from nc? where name = 'Ashe'") # not oke

st_read(ctest,? query="select * FROM nc? WHERE \"SID79\" = 0") # oke !
st_read(ctest,? query="select * FROM nc? WHERE \"NAME\" = 'Ashe' ") # oke !

# the query string is not R but sql :
#? UPPERCAST column names need to be double quoted
# and? a "\" is needed to protect the " in the string,
# spaces around the = sign are mandatory

dbDisconnect(ctest)

HTH
 ?Peter van Horssen

Op 14-6-2018 om 11:59 schreef Jan Willem van der Lichte: