R/MapSave-methods.R

Defines functions rangeMap.save

Documented in rangeMap.save

setGeneric("rangeMapSave", function(object,FUN,formula,cl, ...)  standardGeneric("rangeMapSave") )

# method for species richness,
setMethod("rangeMapSave",
	signature = c(object = "rangeMapSave", FUN = "missing", formula = "missing"),
	definition = function(object, FUN, formula, ...){

		if(length(object@tableName) == 0) object@tableName = "species_richness"

		#build tableName
		tableName = paste(object@MAP, object@tableName, sep = "")

		# build sql subset
		sset = subsetSQLstring(object@CON, object@subset)

		# build sql string
		richnessSQL = paste("SELECT id, count(r.id) as", object@tableName ,"from ranges as r",
						if(!is.null(sset)) paste("WHERE", sset), "group by r.id")

		# build table and index
		dbExecute(object@CON, paste("CREATE TABLE" ,tableName, "(", object@ID, "INTEGER,",object@tableName, "NUMERIC) -- DDL:", richnessSQL))
		dbExecute(object@CON,paste("CREATE  INDEX", paste(object@tableName, object@ID, sep = "_") ,"ON", tableName, "(id)") )
		dbExecute(object@CON, paste("INSERT INTO" ,tableName, richnessSQL) )

	 	return(dbtable.exists(object@CON, tableName))


		})

# aggregate method using sqlite
setMethod("rangeMapSave",
	signature  = c(object = "rangeMapSave", FUN = "character", formula = "missing"),
		definition = function(object, FUN, formula, ...) {

		# CHECKS
		biotab = paste(object@BIO, object@biotab, sep = "")
			if(!dbtable.exists(object@CON,biotab) )
			stop( paste(sQuote(object@biotab), "does not exist") )
		# object@biotrait should exist as a field in biotab
		if(!dbfield.exists(object@CON,biotab, object@biotrait) )
			stop(paste(sQuote(object@biotrait), "is not a field of", sQuote(object@biotab)))

		initExtension(object@CON)

		# fun should  be known by sqlite
		sqlAggregate(FUN)

		# BIO_tab name
		biotab = paste(object@BIO, object@biotab, sep = "")

		#build MAP_ tableName
		tableName = paste(object@MAP, object@tableName, sep = "")

		# build sql subset
		sset = subsetSQLstring(object@CON, object@subset)
		# build sql string
		sql = paste("SELECT r.id, b.",object@biotrait,"FROM ranges r left join ",
				biotab, " b WHERE r.bioid = b.", extract.indexed(object@CON, biotab),
				  if(!is.null(sset)) paste("AND", sset) )
		sql = paste("SELECT id,", FUN ,"(", object@biotrait, ") as", object@biotrait, "from (",sql,") group by id")

		# build table and index
		dbExecute(object@CON, paste("CREATE TABLE",tableName, "(", object@ID, "INTEGER,",object@biotrait, "NUMERIC)"))
		dbExecute(object@CON, paste("CREATE INDEX", paste(tableName, "id", sep = "_") , "ON", tableName, "(id)") )
		dbExecute(object@CON, paste("INSERT INTO" ,tableName, sql) )


		return(dbtable.exists(object@CON, tableName))


		cat(strwrap(sql, width = 100))
		}
	)

# aggregate method using R functions called directly on the data
setMethod("rangeMapSave",
	signature  = c(object = "rangeMapSave", FUN = "function", formula = "missing"),
	definition = function(object, FUN, formula, ...) {

		# tableName
		tableName = paste(object@MAP, object@tableName, sep = "")

		# get data after checking
		dl = .rangeMapSaveData (object)
		
		# apply R function
		X = future.apply::future_sapply(dl, FUN = function(x) FUN(x[, object@biotrait], ...) )


		X = data.frame(id = names(X), X)
		names(X) = c(object@ID, object@biotrait)
		row.names(X) = NULL

		# build table and index
		dbExecute(object@CON, paste("CREATE TABLE" ,tableName, "(", object@ID, "INTEGER,",object@biotrait, "NUMERIC)"))
		dbExecute(object@CON, paste("CREATE INDEX", paste(tableName, "id", sep = "_") , "ON", tableName, "(id)") )
		dbWriteTable(object@CON, tableName, X, row.names = FALSE, append = TRUE)

		return(dbtable.exists(object@CON, tableName))

		})

# aggregate method using R functions called directly using formula, data interface
setMethod("rangeMapSave",
	signature  = c(object = "rangeMapSave", FUN = "function", formula = "formula"),
	definition = function(object, FUN, formula, ...) {

		# tableName
		tableName = paste(object@MAP, object@tableName, sep = "")

		# get data after checking
		dl = .rangeMapSaveData (object)


		X = future.apply::future_sapply(dl, FUN = function(x) FUN(formula = formula, data = x, ...) )
	
		X = data.frame(id = names(X), X)
		names(X) = c(object@ID, object@biotrait)
		row.names(X) = NULL

		# build table and index
		dbExecute(object@CON, paste("CREATE TABLE" ,tableName, "(", object@ID, "INTEGER,",object@biotrait, "NUMERIC)"))
		dbExecute(object@CON, paste("CREATE INDEX", paste(tableName, "id", sep = "_") , "ON", tableName, "(id)") )
		dbWriteTable(object@CON, tableName, X, row.names = FALSE, append = TRUE)

		dbtable.exists(object@CON, tableName)

		})

# IMPORT RASTER
setGeneric("rangeMapImport", function(object,FUN, ...)  	  standardGeneric("rangeMapImport") )

# method for  importing external files
setMethod("rangeMapImport",
	signature  = c(object = "MapImport", FUN = "function"),
		definition = function(object,FUN, ...) {

	filenam = basename(object@path)

	if(length(object@tableName)== 0) tableName = make.db.names(filenam)

	tableName = paste(object@MAP, object@tableName, sep = "")

	cnv = canvas.fetch(object@CON)
	message("Converting canvas to polygons...")
	cnv = raster::rasterToPolygons(raster(cnv))

	message("Loading external MAP data...")
	rst = raster::raster(object@path)

	p4s_is_ok = proj4string_is_identical(CRSargs(CRS(proj4string(cnv), doCheckCRSArgs = FALSE)), CRSargs(raster::projection(rst, FALSE)))
	if(! p4s_is_ok )
		warning(sQuote(filenam), " may have a different PROJ4 string;\n", "canvas:", CRSargs(CRS(proj4string(cnv))), "\n", filenam, ":", CRSargs(projection(rst, FALSE)) )

	rstp = as(as(rst, "SpatialGridDataFrame"), "SpatialPointsDataFrame")
	message("Extracting Layer 1...")
	rstp = rstp[which(!is.na(rstp@data[,1])), ]

	rstp@data$ptid = as.numeric(rownames(rstp@data)) # add point id

	message(paste("Performing overlay: canvas polygons over", filenam, "...") )
	o = over(rstp, cnv)
	o$ptid = as.numeric(rownames(o))

	o = merge(o, rstp@data, by = "ptid")
	o$ptid = NULL

	message("Agregating data...")
	o = aggregate(o[, 2], list(o[,1]), FUN = FUN, na.rm = TRUE, ...)

	names(o) = c(object@ID, object@tableName)

	# build table and index
	message("Creating table and indexes...")
	dbExecute(object@CON, paste("CREATE TABLE" ,tableName, "(", object@ID, "INTEGER,",object@tableName, "FLOAT)"))
	dbExecute(object@CON, paste("CREATE INDEX", paste(tableName, "id", sep = "_") , "ON", tableName, "(id)") )
	dbWriteTable(object@CON, tableName, o, row.names = FALSE, append = TRUE)


	res = dbtable.exists(object@CON, tableName)

	if(res) message(paste(sQuote(basename(object@path)), "imported"))

	return(res)


		}
	)

#' Save, retrieve and export maps.
#'
#' Apply a chosen \code{SQL} or function at each grid cell, allowing for
#' complex subsetting at both ID (e.g. species) and pixel (e.g. assemblage)
#' levels.
#'
#' The subset argument accepts a named list. Names refers to \sQuote{BIO},
#' \sQuote{MAP} and \sQuote{metadata_rages} table names while the strings in
#' the list are character strings containing the SQL \code{WHERE} clause. The
#' subset can point to either one table type (e.g.
#' \code{list(MAP_species_richness = "species_richness > 500")} ) or can point
#' to several table types (e.g. \code{list(BIO_lifeHistory = "clutch_size > 4",
#' MAP_meanAltitude = "meanAltitude < 1000", metadata_ranges = "Area < 1000")})
#'
#' Any valid SQL expression can be used to build up a subset. See
#' \url{http://www.sqlite.org/lang_expr.html}
#'
#' When using \code{cl} parameter you must load the given packages used in
#' \code{FUN} by loading the packages inside the function, using '::' or initializing the
#' cluster before calling rangeMap.save (e.g. \code{clusterEvalQ(cl=cl, library(caper))})).
#'
#' @param CON       an sqlite connection pointing to a valid \code{rangeMapper}
#'                  project.
#' @param tableName name of the table (quoted) to be added to the sqlite database.
#'                  the prefix \sQuote{MAP} will be appended to \code{tableName}
#'                  prior to saving.
#' @param FUN       the function to be applied to each pixel. If \code{FUN} is
#'                  missing then species richness (species count) is computed.
#' @param biotab    character string identifying the \sQuote{BIO} table to use.
#' @param biotrait  character string identifying the ID of the \sQuote{BIO}
#'                  table. see \code{\link{bio.save}}
#' @param subset    a named \code{\link{list}}. See details
#' @param path      path to the raster file(quoted) to be imported to the existing
#'                  project. \code{raster package} is required at this step.
#' @param overwrite if \code{TRUE} then the table is removed
#' @param \dots     when \code{FUN} is a function, \dots{} denotes any extra
#'                  arguments to be passed to it.
#' @return          \code{TRUE} when the MAP was created successfully.
#' @note            \code{SQL} aggregate functions are more efficient then their R
#'                  counterparts. For simple aggregate functions like mean, median, sd, count
#'                  it is advisable to use \code{SQL} functions rather then R functions.
#' @seealso         \code{\link{metadata.update}}.
#' @export
#' @examples
#' require(rangeMapper)
#' require(rgdal)
#' breding_ranges = readOGR(system.file(package = "rangeMapper",
#'      "extdata", "wrens", "vector_combined"), "wrens", verbose = FALSE)[1:50, ]
#' breding_ranges = spTransform(breding_ranges,
#'     CRS("+proj=cea +lon_0=0 +lat_ts=30 +x_0=0 +y_0=0
#'         +ellps=WGS84 +units=m +no_defs") )
#' data(wrens)
#' d = subset(wrens, select = c('sci_name', 'body_size', 'body_mass', 'clutch_size') )
#'
#' con = ramp("wrens.sqlite", gridSize = 500000, spdf = breding_ranges, biotab = d,
#'             ID = "sci_name", metadata = rangeTraits(),
#'             FUN = "median", overwrite = TRUE)
#'
#'
#' lmSlope = function(formula, data) {
#'     fm = try(lm(formula, data = data), silent = TRUE)
#'     if (inherits(fm, "try-error"))
#'         res = NA else res = coef(fm)[2]
#'     as.numeric(res)
#' }
#'
#' # Subsetting by Species and Assembladge
#' rangeMap.save(con, FUN = lmSlope, biotab = "biotab", biotrait = "body_mass",
#'     tableName = "slope_bodyMass_clutchSize", formula = log(body_mass) ~ clutch_size,
#'     list(MAP_species_richness = "species_richness >= 5",
#'         BIO_biotab = "body_size > 15"
#'         ), overwrite = TRUE)
#'
#' \dontrun{
#' # Import raster maps to the current project
#' r = system.file(package = "rangeMapper", "extdata", "etopo1", "etopo1_Americas.tif")
#' rangeMap.save(con, path = r, tableName = "meanAltitude", FUN = mean, overwrite = TRUE)
#' m = rangeMap.fetch(con, spatial = FALSE)
#' plot(m)
#' 
#' }

rangeMap.save  <- function(CON, tableName, FUN, biotab, biotrait, subset = list(), path , overwrite = FALSE, ...) {

	if(overwrite & !missing(tableName))
		dbExecute(CON, paste("DROP TABLE IF EXISTS", paste("MAP", tableName, sep = "_")))
	if(overwrite & missing(tableName))
        dbExecute(CON, "DROP TABLE IF EXISTS MAP_species_richness")



	o = FALSE

	if(!missing(path)) { #  external map
			if(missing(tableName))
				rmap = new("MapImport", CON = CON, path = path) else
				rmap = new("MapImport", CON = CON, path = path, tableName = tableName)

			 o = rangeMapImport(rmap, FUN = FUN)
			}

	if(missing(FUN) ) { #species richness
			if(missing(tableName))
				rmap = new("rangeMapSave", CON = CON, subset = subset) else
				rmap = new("rangeMapSave", CON = CON, tableName = tableName, subset = subset)
			 o = rangeMapSave(rmap)

			}

	if(!missing(FUN) & missing(path)) { # SQL or R function

	  
		rmap = new("rangeMapSave", CON = CON,  biotab = biotab, biotrait = biotrait, tableName = tableName, subset = subset)
		o = rangeMapSave(rmap, FUN = FUN, ...)

	}
	o
}
valcu/rangeMapper documentation built on Feb. 6, 2021, 8:20 p.m.