Get PHS Open Data using SQL

Source: R/get_resource_sql.R

Downloads data from the NHS Open Data platform using a SQL query. Similar to get_resource(), but allows more flexible server-side querying. This function has a lower maximum row number (32,000 vs 99,999) for returned results.

get_resource_sql(sql)

Arguments

sql

A single PostgreSQL SELECT query (character). Must include a resource ID, which must be double-quoted (e.g., SELECT * from "58527343-a930-4058-bf9e-3c6e5cb04010").

Value

A tibble with the query results. Only 32,000 rows can be returned from a single SQL query.

See also

get_resource() for downloading a resource without using a SQL query.

Examples

sql <- "
   SELECT
     \"TotalCancelled\",\"TotalOperations\",\"Hospital\",\"Month\"
   FROM
     \"bcc860a4-49f4-4232-a76b-f559cf6eb885\"
   WHERE
     \"Hospital\" = 'D102H'
"
df <- get_resource_sql(sql)

# This is equivalent to:
cols <- c("TotalCancelled", "TotalOperations", "Hospital", "Month")
row_filter <- c(Hospital = "D102H")

df2 <- get_resource(
  "bcc860a4-49f4-4232-a76b-f559cf6eb885",
  col_select = cols,
  row_filters = row_filter
)