Specifying Schema for PostGIS Layer with Simple Features

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Specifying Schema for PostGIS Layer with Simple Features

Michael Treglia
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
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

Avipsa Roy
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
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

juejack
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
>> [hidden email]
>> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> _______________________________________________
> R-sig-Geo mailing list
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Jean-Daniel
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

edzer
In reply to this post by Michael Treglia


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
> [hidden email]
> 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
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

Michael Treglia
In reply to this post by Avipsa Roy
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 <[hidden email]> 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
> > [hidden email]
> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

Avipsa Roy
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 <[hidden email]>
Sent: Friday, December 9, 2016 10:20 PM
Subject: Re: [R-sig-Geo] Specifying Schema for PostGIS Layer with Simple Features
To: Avipsa Roy <[hidden email]>
Cc:  <[hidden email]>


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 <[hidden email]> 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
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo




       
        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

Michael Treglia
In reply to this post by edzer
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 <[hidden email]
> 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
> > [hidden email]
> > 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
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

Lee Hachadoorian
On Fri, Dec 9, 2016 at 6:06 PM, Michael Treglia <[hidden email]> 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]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

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

Thanks,
Mike

On Fri, Dec 9, 2016 at 7:47 PM, Lee Hachadoorian <
[hidden email]> wrote:

>
>
> On Fri, Dec 9, 2016 at 6:06 PM, Michael Treglia <[hidden email]>
> 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]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

Mathieu Basille-3
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 <
> [hidden email]> wrote:
>
>>
>>
>> On Fri, Dec 9, 2016 at 6:06 PM, Michael Treglia <[hidden email]>
>> 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]]
>
> _______________________________________________
> R-sig-Geo mailing list
> [hidden email]
> https://stat.ethz.ch/mailman/listinfo/r-sig-geo
>

--

Mathieu Basille

[hidden email] | 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

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo
Reply | Threaded
Open this post in threaded view
|

Re: Specifying Schema for PostGIS Layer with Simple Features

Michael Treglia
Thanks so much Matthieu! This looks like a great package!

Best,
Mike

On Sun, Dec 11, 2016 at 1:25 AM, Mathieu Basille <
[hidden email]> wrote:

> 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 <
> > [hidden email]> wrote:
> >
> >>
> >>
> >> On Fri, Dec 9, 2016 at 6:06 PM, Michael Treglia <[hidden email]>
> >> 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]]
> >
> > _______________________________________________
> > R-sig-Geo mailing list
> > [hidden email]
> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >
>
> --
>
> Mathieu Basille
>
> [hidden email] | 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
>
>

        [[alternative HTML version deleted]]

_______________________________________________
R-sig-Geo mailing list
[hidden email]
https://stat.ethz.ch/mailman/listinfo/r-sig-geo