[R-sig-Geo] Fwd: Fwd: Re: Sf st_read postgis

[Peter van Horssen]

-------- Doorgestuurd bericht --------
Onderwerp: 	Re: [R-sig-Geo] Sf st_read postgis
Datum: 	Thu, 14 Jun 2018 13:48:17 +0200
Van: 	Peter <peter using greenstat.nl>
Aan: 	Jan Willem van der Lichte <geodude using 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:
> Hi, thx for answering.
> If I follow your suggestion, I get an error. see below.
> > xx = st_read(connz, query = "SELECT * FROM vakantie LIMIT 3;") Error 
> in CPL_hex_to_raw(vapply(x, skip0x, USE.NAMES = FALSE, "")) : 
> char2int: false character in hex string
> Any other ideas?
> Thanks Jan Willem
> *Sent:* Tuesday, June 12, 2018 at 2:29 PM
> *From:* "Michael DELORME" <michael.delorme using agriculture.gouv.fr>
> *To:* r-sig-geo using r-project.org
> *Subject:* Re: [R-sig-Geo] Sf st_read postgis
> Hi
>
> If you use a query you don't have to set a table name, so
>
> xx <- st_read(connz, query="SELECT * FROM vakantie LIMIT 3;")
>
> should work...
>
>
> Le 12/06/2018 12:12, Jan Willem van der Lichte a �crit :
> > Hi list,
> >
> > Can someone help me with a basic sf question.
> >
> > I try to use the sf::st_read function to get some data out of postgis.
> >
> > I set up a connection (connz) to postgis.
> > I checked if the connection was working with
> >
> > dbListTables(connz)
> >
> > The result was ok, I saw all the tables in the database.
> >
> > Next I tried to get some data with the st_read() function, from the 
> sf package.
> >
> > I tried to follow the example in the help.
> >
> > xx<- st_read(connz, "vakantie", query="SELECT * FROM vakantie LIMIT 3;")
> >
> > So i replaced the table meuse with vakantie.
> >
> > When I execute this statement, I get a warning
> >
> > Warning message: In st_read.DBIObject(...) : Ignoring query 
> argument, only using table.
> >
> > So what's happening, am I doing something wrong? Which is possible, 
> cause I'm new to R. :-)
> >
> > Thanks
> > Jan Willem
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > Sent with my iPad
> >
> > _______________________________________________
> > R-sig-Geo mailing list
> > R-sig-Geo using r-project.org
> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >
> >
>
> --
> *Micha�l DELORME*
> Administrateur de donn�es, charg� de projet en g�omatique
> Service r�gional de l'information statistique, �conomique et territoriale
> Direction r�gionale de l'alimentation, de l'agriculture et de la for�t
> Auvergne-Rh�ne-Alpes
> Site de Lyon
> 165 rue Garibaldi � BP 3202 � 69401 LYON cedex 03
> 04 78 63 34 21
>
> Pour �crire au service :
> DRAAF Auvergne-Rh�ne-Alpes � SRISET
> 16B rue Aim� Rudel � BP 45 � 63370 LEMPDES
>
> http://www.draaf.auvergne-rhone-alpes.agriculture.gouv.fr/
>
> [[alternative HTML version deleted]]
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo


-- 
*GreenStat* - Statistiek en GIS diensten voor ecologisch onderzoek
*GreenStat* - /Statistical and GIS services for ecological research/

Peter van Horssen | M: +31(0)6 34011089 | W: www.greenstat.nl 
<http://www.greenstat.nl>

	[[alternative HTML version deleted]]