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

[Mathieu Basille]

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