Specifying Schema for PostGIS Layer with Simple Features

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]]

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]]

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]]

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]]

  test = st_read_db(conn, table = "ipis",

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.

      [[alternative HTML version deleted]]

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

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!)

On Fri, Dec 9, 2016 at 6:06 PM, Michael Treglia <mtreglia at gmail.com>
wrote:

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!)

A couple of years ago I wrote a function that can load PostGIS geometries
using RPostgreSQL instead of rgdal. It converts to WKT in SQL and then
converts the WKT to R spatial objects using the rgeos library.

Code below. Blogged here: https://geospatial.commons.gc.cuny.edu/2014/01/
14/load-postgis-geometries-in-r-without-rgdal/

```
library(RPostgreSQL)
library(rgeos)
library(sp)

# Load data from the PostGIS server
conn = dbConnect(
  dbDriver("PostgreSQL"), dbname=dbname, host=host, port=5432,
  user=user, password=password
  )

strSQL = "
  SELECT gid, ST_AsText(geom) AS wkt_geometry, attr1, attr2[, ...]
  FROM geo_layer"
dfTemp = dbGetQuery(conn, strSQL)
row.names(dfTemp) = dfTemp$gid

# Create spatial polygons
# To set the PROJ4 string, enter the EPSG SRID and uncomment the
# following two lines:
# EPSG = make_EPSG()
# p4s = EPSG[which(EPSG$code == SRID), "prj4"]
for (i in seq(nrow(dfTemp))) {
  if (i == 1) {
    spTemp = readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i])
    # If the PROJ4 string has been set, use the following instead
    # spTemp = readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i], p4s)
  }
  else {
    spTemp = rbind(
      spTemp, readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i])
      # If the PROJ4 string has been set, use the following instead
      # spTemp, readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i], p4s)
    )
  }
}

# Create SpatialPolygonsDataFrame, drop WKT field from attributes
spdfFinal = SpatialPolygonsDataFrame(spTemp, dfTemp[-2])
```

--
Lee Hachadoorian
Assistant Professor of Instruction, Geography and Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University

Oh, cool Lee! That's great to have around!

Thanks,
Mike

On Fri, Dec 9, 2016 at 7:47 PM, Lee Hachadoorian <
Lee.Hachadoorian+L at gmail.com> wrote:

On Fri, Dec 9, 2016 at 6:06 PM, Michael Treglia <mtreglia at gmail.com>
wrote:

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!)

A couple of years ago I wrote a function that can load PostGIS geometries
using RPostgreSQL instead of rgdal. It converts to WKT in SQL and then
converts the WKT to R spatial objects using the rgeos library.

Code below. Blogged here: https://geospatial.commons.gc.cuny.edu/2014/01/
14/load-postgis-geometries-in-r-without-rgdal/

```
library(RPostgreSQL)
library(rgeos)
library(sp)

# Load data from the PostGIS server
conn = dbConnect(
  dbDriver("PostgreSQL"), dbname=dbname, host=host, port=5432,
  user=user, password=password
  )

strSQL = "
  SELECT gid, ST_AsText(geom) AS wkt_geometry, attr1, attr2[, ...]
  FROM geo_layer"
dfTemp = dbGetQuery(conn, strSQL)
row.names(dfTemp) = dfTemp$gid

# Create spatial polygons
# To set the PROJ4 string, enter the EPSG SRID and uncomment the
# following two lines:
# EPSG = make_EPSG()
# p4s = EPSG[which(EPSG$code == SRID), "prj4"]
for (i in seq(nrow(dfTemp))) {
  if (i == 1) {
    spTemp = readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i])
    # If the PROJ4 string has been set, use the following instead
    # spTemp = readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i], p4s)
  }
  else {
    spTemp = rbind(
      spTemp, readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i])
      # If the PROJ4 string has been set, use the following instead
      # spTemp, readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i], p4s)
    )
  }
}

# Create SpatialPolygonsDataFrame, drop WKT field from attributes
spdfFinal = SpatialPolygonsDataFrame(spTemp, dfTemp[-2])
```

--
Lee Hachadoorian
Assistant Professor of Instruction, Geography and Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University

	[[alternative HTML version deleted]]

Hi Michael,

As pointed out earlier in that thread by Jean-Daniel, if you're only
interested in loading PostGIS geometries in R, and not in Simple Features
themselves, you may want to give a look at rpostgis, which present a
standard and flexible solution (using rgeos and RPostgreSQL in the
background):

https://cran.r-project.org/package=rpostgis

Mathieu.


On 12/10/2016 06:02 PM, Michael Treglia wrote:

Oh, cool Lee! That's great to have around!

Thanks,
Mike

On Fri, Dec 9, 2016 at 7:47 PM, Lee Hachadoorian <
Lee.Hachadoorian+L at gmail.com> wrote:

On Fri, Dec 9, 2016 at 6:06 PM, Michael Treglia <mtreglia at gmail.com>
wrote:

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!)

A couple of years ago I wrote a function that can load PostGIS

geometries

using RPostgreSQL instead of rgdal. It converts to WKT in SQL and then
converts the WKT to R spatial objects using the rgeos library.

Code below. Blogged here: https://geospatial.commons.gc.

cuny.edu/2014/01/

14/load-postgis-geometries-in-r-without-rgdal/

```
library(RPostgreSQL)
library(rgeos)
library(sp)

# Load data from the PostGIS server
conn = dbConnect(
  dbDriver("PostgreSQL"), dbname=dbname, host=host, port=5432,
  user=user, password=password
  )

strSQL = "
  SELECT gid, ST_AsText(geom) AS wkt_geometry, attr1, attr2[, ...]
  FROM geo_layer"
dfTemp = dbGetQuery(conn, strSQL)
row.names(dfTemp) = dfTemp$gid

# Create spatial polygons
# To set the PROJ4 string, enter the EPSG SRID and uncomment the
# following two lines:
# EPSG = make_EPSG()
# p4s = EPSG[which(EPSG$code == SRID), "prj4"]
for (i in seq(nrow(dfTemp))) {
  if (i == 1) {
    spTemp = readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i])
    # If the PROJ4 string has been set, use the following instead
    # spTemp = readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i], p4s)
  }
  else {
    spTemp = rbind(
      spTemp, readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i])
      # If the PROJ4 string has been set, use the following instead
      # spTemp, readWKT(dfTemp$wkt_geometry[i], dfTemp$gid[i], p4s)
    )
  }
}

# Create SpatialPolygonsDataFrame, drop WKT field from attributes
spdfFinal = SpatialPolygonsDataFrame(spTemp, dfTemp[-2])
```

--
Lee Hachadoorian
Assistant Professor of Instruction, Geography and Urban Studies
Assistant Director, Professional Science Master's in GIS
Temple University

      [[alternative HTML version deleted]]

--

Mathieu Basille

basille at ufl.edu | http://ase-research.org/basille
+1 954-577-6314 | University of Florida FLREC

  ? Le tout est de tout dire, et je manque de mots
  Et je manque de temps, et je manque d'audace. ?
  ? Paul ?luard

This message is signed to guarantee its authenticity.
For a true private correspondence, use my public key
to encrypt your messages:

  http://mathieu.basille.net/pub.asc

Learn more: http://mzl.la/1BsOGiZ