[R-sig-Geo] Specifying Schema for PostGIS Layer with Simple Features

[Michael Treglia]

Thanks so much, everybody.

Edzer - your primary fix works - tested on Windows and on an Ubuntu VM. (I
installed from GitHub on both - my Ubuntu VM was behind on things, so it
was a good excuse to upgrade gdal, etc. there :-)) Thanks for the quick fix!

just FYI, Your alternative suggestion threw an error though:

>   test = st_read_db(conn, table = "ipis",
+    query = "select * from 'staging.ipis'")
Error in postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at
or near "'staging.ipis'"
LINE 1: select * from 'staging.ipis'
                      ^
)
Error in CPL_hex_to_raw(sapply(x, skip0x, USE.NAMES = FALSE)) :
  not compatible with STRSXP




On Fri, Dec 9, 2016 at 4:04 PM, Edzer Pebesma <edzer.pebesma at uni-muenster.de
> wrote:

>
>
> On 09/12/16 21:02, Michael Treglia wrote:
> > Hi All,
> >
> > Getting going with Simple Features - awesome job Developers, and thanks
> for
> > your time!
> >
> > Quick question - I'm working on bringing in PostGIS layers to work with
> in
> > R - I've got the db connection made using the RPostgreSQL connection and
> > have successfully imported data from the public schema. However, I'm not
> > figuring out how to bring in layers from other schema. Sorry if I'm just
> > missing something obvious.
> >
> > I've tried a few different iterations, as detailed below, for table
> 'ipis'
> > in schema 'staging'
> >
> > Try 1: specify table without naming schema
> >> test <- st_read_db(conn, 'ipis')
> > Error in postgresqlExecStatement(conn, statement, ...) :
> >   RS-DBI driver: (could not Retrieve the result : ERROR:  relation "ipis"
> > does not exist
> > LINE 1: select * from  ipis ;
> >                        ^
> > )
> > Error in .subset2(x, i, exact = exact) : subscript out of bounds
> >
> >
> > Try 2: specify schema with table as i would in postgres directly
> >> test <- st_read_db(conn, 'staging.ipis')
> > Error in .subset2(x, i, exact = exact) : subscript out of bounds
>
> thanks, this doesn't work indeed; the bug is fixed, but it may take a
> while until this works on windows. A work-around for now may be:
>
> test = st_read_db(conn, table = "ipis",
>    query = "select * from 'staging.ipis'")


> >
> >
> > Try 3: Specify schema as I would with RPostgreSQL operations (e.g.,
> > dbExistsTable)
> >> test <- st_read_db(conn, c('staging','ipis') )
> > Error in postgresqlExecStatement(conn, statement, ...) :
> >   RS-DBI driver: (could not Retrieve the result : ERROR:  relation
> > "staging" does not exist
> > LINE 1: select * from  staging ;
> >                        ^
> > )
> > Error in .subset2(x, i, exact = exact) : subscript out of bounds
> >
> > I've verified R can see the table using:
> >> dbExistsTable(conn, c('staging','ipis'))
> > [1] TRUE
> >
> >
> > Thanks in advance for any suggestions!
> > Best,
> > Mike T
> >
> >
> > PS - I was originally trying to use rgdal to read these layers in, but
> > found rgdal did not have the PostgreSQL/PostGIS driver with it on
> Windows -
> > if that's a simple fix too, I'm all ears.
>
> Switch to linux? Small step, these days.
>

Definitely - I often use a Linux VM as mentioned above, but like to have
things running across envs when I can. (with sf working for me, given your
fix, I'm all set for now!)

Best,
mike

>
> >
> >       [[alternative HTML version deleted]]
> >
> > _______________________________________________
> > R-sig-Geo mailing list
> > R-sig-Geo at r-project.org
> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >
>
> --
> Edzer Pebesma
> Institute for Geoinformatics  (ifgi),  University of Münster
> Heisenbergstraße 2, 48149 Münster, Germany; +49 251 83 33081
> Journal of Statistical Software:   http://www.jstatsoft.org/
> Computers & Geosciences:   http://elsevier.com/locate/cageo/
>
>
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

	[[alternative HTML version deleted]]