[R-sig-Geo] 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 <
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]]