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

[Avipsa]

Hi Michael,
Ideally if you are the superuser you should have access to all tables and schemas. Sometimes it happens that when you make an odbc connection from R you still need to explicitly grant access to non-public/user-specific tables. I would suggest to use the GRANT ALL ON <SCHEMA> TO <USER> , as Edzer mentioned in the previous email.
Thanks & Regards,
Avipsa Roy
Institute für Geoinformatics 
Westfälische Wilhelms- Universität Münster

		_____________________________
From: Michael Treglia <mtreglia at gmail.com>
Sent: Friday, December 9, 2016 10:20 PM
Subject: Re: [R-sig-Geo] Specifying Schema for PostGIS Layer with Simple Features
To: Avipsa Roy <a_roy001 at uni-muenster.de>
Cc:  <r-sig-geo at r-project.org>


Hi Avipsa,
Thanks for the quick response! The role of the username is Superuser, so I should have all permissions, right? Or is there something else I will need to check on?
Best,Mike

On Fri, Dec 9, 2016 at 3:34 PM, Avipsa Roy <a_roy001 at uni-muenster.de> 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




	
	[[alternative HTML version deleted]]