Create PostGIS polygon

David LeBauer

7/30/2015

Code used to insert a polygon for Champaign County into a PostGIS geometry column.

Note that the sites.geometry column has three dimensions. Latitude and longitude are queried using ggplot2::map_data('counties') and elevation data is queried using rgbif::elevation(latidutde, longitude).

This query is used to define the boundary of site 1254, Champaign County, which will be used for testing and development of regional runs of PEcAn

Load Libraries

library(ggplot2)
library(data.table)
library(rgbif)
library(knitr)

Select lat and lon points for Champaign County Boundary

d <- data.table(map_data("county"))
champaign <- d[region == "illinois" & subregion == "champaign"]

kable(champaign)

Query Elevation points

### for PostGIS, need polygon to return to start point
champaign <- rbind(champaign, champaign[1])

## need to get key from Google Maps API
## I've put mine in a ~/.googlemapskey
key <- readLines(con = "~/.googlemapskey")
champaign_pts <- data.table(champaign[,elevation(latitude = lat, longitude = long, key = key)])

kable(champaign_pts, caption = "Table with elevations")

Define boundary in postGIS

boundary <- champaign_pts[,paste(longitude, latitude, elevation, collapse = ",")]

writeLines(boundary)

Define PostGIS geom

Use srid = 4326

geometry <- paste0("ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(", boundary , ")')), 4326)")

writeLines(geometry)

insert statement

insert_polygon <- paste0("INSERT into sites (sitename, geometry) VALUES ('Champaign County', ", geometry, ");")
writeLines(insert_polygon)

Update geom statement

update_polygon <- paste0("update sites set geometry = ", geometry , " where sitename = 'Champaign County';")
writeLines(update_polygon)

Insert into BETYdb

library(PEcAn.DB)
library(RPostgreSQL)
#settings <- read.settings('settings.xml')
settings <-list(database = list(bety = list(driver = "PostgreSQL", user = "bety", dbname = "bety", password = "bety")))

db.query(insert_polygon, con = db.open(settings$database$bety))