Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- > require(rgdal)
- > dsn="PG:dbname='gis'"
- > ogrListLayers(dsn)
- [1] "ccsm_polygons" "nongp" "WrldTZA"
- [4] "nongpritalin" "ritalinmerge" "metforminmergev"
- > polys = readOGR(dsn="PG:dbname='gis'","ccsm_polygons")
- OGR data source with driver: PostgreSQL
- Source: "PG:dbname='gis'", layer: "ccsm_polygons"
- with 32768 features and 4 fields
- Feature type: wkbMultiPolygon with 2 dimensions
- > summary(polys)
- Object of class SpatialPolygonsDataFrame
- Coordinates:
- min max
- x -179.2969 180.7031
- y -90.0000 90.0000
- Is projected: NA
- proj4string : [NA]
- Data attributes:
- area perimeter ccsm_polys ccsm_pol_1
- Min. :1.000 Min. :5.000 Min. : 2 Min. : 1
- 1st Qu.:1.000 1st Qu.:5.000 1st Qu.: 8194 1st Qu.: 8193
- Median :1.000 Median :5.000 Median :16386 Median :16384
- Mean :1.016 Mean :5.016 Mean :16386 Mean :16384
- 3rd Qu.:1.000 3rd Qu.:5.000 3rd Qu.:24577 3rd Qu.:24576
- Max. :2.000 Max. :6.000 Max. :32769 Max. :32768
- > require(RPostgreSQL)
- Loading required package: RPostgreSQL
- Loading required package: DBI
- > m <- dbDriver("PostgreSQL")
- > con <- dbConnect(m, dbname="gis")
- > q="SELECT ST_AsText(the_geom) AS geom from ccsm_polygons LIMIT 10;"
- > rs = dbSendQuery(con,q)
- > df = fetch(rs,n=-1)
- dbGetSp <- function(dbInfo,query) {
- if(!require('rgdal')|!require(RPostgreSQL))stop('missing rgdal or RPostgreSQL')
- d <- dbInfo
- tmpTbl <- sprintf('tmp_table_%s',round(runif(1)*1e5))
- dsn <- sprintf("PG:dbname='%s' host='%s' port='%s' user='%s' password='%s'",
- d$dbname,d$host,d$port,d$user,d$password
- )
- drv <- dbDriver("PostgreSQL")
- con <- dbConnect(drv, dbname=d$dbname, host=d$host, port=d$port,user=d$user, password=d$password)
- tryCatch({
- sql <- sprintf("CREATE UNLOGGED TABLE %s AS %s",tmpTbl,query)
- res <- dbSendQuery(con,sql)
- nr <- dbGetInfo(res)$rowsAffected
- if(nr<1){
- warning('There is no feature returned.');
- return()
- }
- sql <- sprintf("SELECT f_geometry_column from geometry_columns WHERE f_table_name='%s'",tmpTbl)
- geo <- dbGetQuery(con,sql)
- if(length(geo)>1){
- tname <- sprintf("%s(%s)",tmpTbl,geo$f_geometry_column[1])
- }else{
- tname <- tmpTbl;
- }
- out <- readOGR(dsn,tname)
- return(out)
- },finally={
- sql <- sprintf("DROP TABLE %s",tmpTbl)
- dbSendQuery(con,sql)
- dbClearResult(dbListResults(con)[[1]])
- dbDisconnect(con)
- })
- }
- d=list(host='localhost', dbname='spatial_db', port='5432', user='myusername', password='mypassword')
- spatialObj<-dbGetSp(dbInfo=d,"SELECT * FROM spatial_table")
- user system elapsed
- 0.001 0.000 0.008
- user system elapsed
- 0.313 0.021 1.436
- RPostGIS <- function(coninfo,query) {
- dsn=paste("PG:dbname='",coninfo$dbname,"' host='",coninfo$host,"' port='",coninfo$port,"' user='",coninfo$user,"' password='",coninfo$password,"'", sep='')
- drv <- dbDriver("PostgreSQL")
- con <- dbConnect(drv, user=coninfo$user, password=coninfo$password, dbname=coninfo$dbname)
- res <- dbSendQuery(con,paste('CREATE TABLE tmp1209341251dva1 AS ',query,sep=''))
- geo <- dbGetQuery(con,"SELECT f_geometry_column from geometry_columns WHERE f_table_name='tmp1209341251dva1'")
- if(length(geo)>1){
- tname=paste("tmp1209341251dva1(",geo$f_geometry_column[1],")")
- }else{
- tname="tmp1209341251dva1";
- }
- out <- tryCatch(readOGR(dsn,tname), finally=dbSendQuery(con,'DROP TABLE tmp1209341251dva1'))
- dbDisconnect(con)
- return(out)
- }
- > require('rgdal')
- > require('RPostgreSQL')
- > coninfo=list(host='localhost',dbname='spatial_db',port='5432',user='myusername',password='mypassword')
- > spatial_obj<-RPostGIS(coninfo,"SELECT * FROM spatial_table")
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement