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

[Lee Hachadoorian]

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