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

[jds-linux]

Dear Michael.

To add on Avipsa answer. You may use this query to grant your user 
(maybe not full secure)

GRANT ALL ON ALL TABLES IN SCHEMA public to MICHAEL with grant option;

To my knowledge Rpostgresql does not support postgis geometry. To 
retreive geometry in R, you should take a look at Rpostgis.

https://cran.r-project.org/web/packages/rpostgis/index.html

Regards.

Jean-Daniel

On 12/09/2016 03:34 PM, Avipsa Roy wrote:
> Hi Michael,
>
> You can check if the execute permission is granted to your user id on the schema you are trying to access?
>
> Thanks & Regards,
> Avipsa Roy
>
> Michael Treglia wrote on 2016-12-09:
>> 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
>
>> 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.
>> 	[[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
> _______________________________________________
> R-sig-Geo mailing list
> R-sig-Geo at r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo