db_get_query: Submit an arbitrary SQL query

In mbedward/CERMBlidarpostgis: Store and retrieve processed LIDAR data from a PostGIS database

Description

This is a short-cut function that retrieves the database connection object from a dbsettings list, submits an SQL query, and returns the results as a data frame. It is an alternative to composing queries as dplyr pipelines.

Usage

db_get_query(dbsettings, query)

Arguments

dbsettings	A named list of database connection settings returned by db_connect_postgis or db_create_postgis.
query	SQL query as a character string or similar (e.g. glue string).

Examples

## Not run: 
Sys.setenv(PGPASSWORD = "secret")
DBSettings <- db_connect_postgis("cermb_lidar")

# Simple query - count records in one of the tables
res <- db_get_query(DBSettings,
                    "select count(*) as n from mgazone56.point_counts")

# A more complex query: find overlapping LAS tiles in selected map sheets
library(glue)

mapnames <- c("Wallerawang", "StAlbans", "Gosford",
              "MountPomany", "HowesValley", "Cessnock")

values <- paste(mapnames, collapse = ", ")

# Using spatial self-join on the metadata table
#
query <- glue("select a.id as id1, b.id as id2, mapname, purpose, capture_start
              from mgazone56.las_metadata as a, mgazone56.las_metadata as b,
              where mapname in ({values}) and
              ST_Intersects(ST_Centroid(a.bounds), b.bounds) and
              a.id < b.id;")

res <- db_get_query(DBSettings, query)

## End(Not run)

mbedward/CERMBlidarpostgis documentation built on Nov. 14, 2021, 7:15 p.m.