[R-sig-Geo] 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 <
basille.web at ase-research.org> 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 <
> > 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]]
> >
> > _______________________________________________
> > R-sig-Geo mailing list
> > R-sig-Geo at r-project.org
> > https://stat.ethz.ch/mailman/listinfo/r-sig-geo
> >
>
> --
>
> 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
>
>

	[[alternative HTML version deleted]]