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

[Edzer Pebesma]

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.

> 
> 	[[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/

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-sig-geo/attachments/20161209/e24e29ca/attachment.bin>