transpordiTsoonid/R/queryWithGeom.R

#' Get query string with geom column representations to Spatial objects.
#' @details Päringu stringi koostamine geomeetria veeruga Spatial objektina.
#' @param con An PostgreSQLConnection object as produced by dbConnect.
#' @param tbl The database table name.
#' @param geom The array with postgresql geom column names.
#' @param where Other conditions in request.
#' @return query string.
#' @seealso \code{\link{queryDropColumnsFromPostgresDbAccordingDF}},
#' \code{\link{queryAddColumnsToPostgresDbAccordingDF}},
#' \code{\link{queryWithoutGeom}},
#' \code{\link{queryWithGeom}}
#' @examples \dontrun{
#' # Connect with database
#' con <- myPostgresConnect(host = conf$host, port = conf$port, user = conf$user,
#'         password = conf$password, dbname = conf$dbname, type = "RPostgreSQL")
#' tblName = "transport_tsoonid_tallinn"
#' q <- queryWithGeom(con = con, tbl = tblName, geom = 'geom', where = "LIMIT 100")
#' df <- dbGetQuery(con, q)
#' df
#' DBI::dbClearResult(res)
#' DBI::dbDisconnect(con)
#'
#' }
#'
#' @export
#'

queryWithGeom <- function(con, tbl, geom = 'geom', where = NULL) {
  ## All columns without geom as shp@data
  # geom = paste(paste0("'", geom, "'"), collapse = ", ")
  q <- paste0("SELECT 'SELECT ' ||
            ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50)
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME='", tbl, "' AND
            COLUMN_NAME NOT IN ('", geom,"')
            ORDER BY ORDINAL_POSITION
            ), ', ') || ', ST_AsText(", geom,") AS ", geom," FROM ", tbl, "'")
  q <- DBI::dbGetQuery(con,q)[1,1]
  q <- paste(q, where)
  q
}