---
title: "countryatlas and ggsql"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{countryatlas and ggsql}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE, comment = "#>", message = FALSE, warning = FALSE
)
library(countryatlas)
# Rendering in the database needs ggsql >= 0.5.0, sf, and a network connection
# the first time in a session, for DuckDB's spatial extension. So the chunks
# that render run when the website is built, and are shown as code elsewhere,
# including on CRAN.
render <- identical(Sys.getenv("IN_PKGDOWN"), "true") &&
  requireNamespace("ggsql", quietly = TRUE) &&
  utils::packageVersion("ggsql") >= "0.5.0" &&
  requireNamespace("sf", quietly = TRUE) &&
  requireNamespace("rnaturalearth", quietly = TRUE)
# And only when DuckDB's spatial extension can actually be fetched.
if (render) {
  render <- tryCatch({
    as_ggsql_source(attach_geometry(world_snapshot$countries[1, ],
                                    geometry = "sf"))
    TRUE
  }, warning = function(w) FALSE)
}
```

[ggsql](https://ggsql.org) is a grammar of graphics for SQL: you describe a plot
*inside a SQL query* and it renders in the database (DuckDB), returning a plot
that draws as SVG or as an interactive widget, with no ggplot2 runtime required.
Its spatial layer, `DRAW spatial`, reached CRAN with ggsql 0.5.2 on
2026-10-08, and countryatlas needs ggsql >= 0.5.0, the version
[interactive_map()] checks for.

countryatlas and ggsql fit together cleanly:

* **countryatlas** does the part ggsql's static bundled world can't: reconcile
  messy country names to the ISO spine, repair the entities map backends get
  wrong (`country_overrides()`), and join World Bank indicators onto geometry.
* **ggsql** does the part countryatlas doesn't: push the rendering down into the
  database, so the geometry never has to come back into R.

So countryatlas becomes the *data layer* and ggsql the *renderer*.

## Emit a query (no dependencies)

`world_query()` is a pure string builder (it needs nothing installed), so you
can see exactly what will be sent to ggsql:

```{r}
world_query(
  gdp_per_capita,
  projection = "equal_earth",
  palette    = "magma",
  transform  = "log10",
  title      = "GDP per capita"
)
```

Every projection the package knows has a translation, and the other layers are
a word away:

```{r}
world_query(gdp_per_capita, projection = "north_polar", layer = "binned")
world_query(gdp_per_capita, layer = "bubble", size = "population")
```

## Render in the database

`as_ggsql_source()` hands a curated countryatlas table to ggsql: by default as
a ggsql reader with the table registered, `sf` geometry encoded as WKB in
longitude and latitude, and DuckDB's `spatial` extension loaded. ggsql's DuckDB
downloads that extension once per session, into the session's temporary
directory. The one-call path is `interactive_map(engine = "ggsql")`:

```{r, eval = render}
d <- attach_geometry(world_snapshot$countries, geometry = "sf")
interactive_map(d, gdp_per_capita, engine = "ggsql", transform = "log10")
```

Under the hood that is just the two building blocks, which you can also drive
yourself for full control over the query and the output:

```{r, eval = render}
reader <- as_ggsql_source(d)
spec <- ggsql::ggsql_execute(
  reader,
  world_query(gdp_per_capita, projection = "orthographic", palette = "viridis")
)
ggsql::ggsql_widget(spec)
```

`ggsql::ggsql_render(ggsql::svg_writer(), spec)` returns the same map as SVG
markup, and `ggsql::ggsql_save(spec, "map.png")` writes it to a file.

## In a Quarto / R Markdown document

Loading `ggsql` registers a chunk engine, and a ` ```{ggsql} ` block reads R
data as `r:<object>`. `as_ggsql_source(format = "frame")` returns the prepared
frame for that; load DuckDB's `spatial` extension once, then chart it:

````{verbatim}
```{r}
library(countryatlas)
library(ggsql)

world <- world_data(2020, geometry = "sf") |>
  as_ggsql_source(format = "frame")
```

```{ggsql}
SET extension_directory = 'duckdb-extensions';
INSTALL spatial;
LOAD spatial;
```

```{ggsql}
VISUALISE gdp_per_capita AS fill, geometry FROM r:world
DRAW spatial
PROJECT TO equal_earth
SCALE fill TO magma VIA log10
LABEL title => 'GDP per capita, 2020'
```
````

## Why bother?

The win is the same as ggsql's everywhere else: only the rendered result leaves
the database. For a single world map that is minor, but the moment your country
panel lives in a warehouse (millions of rows, many years), pushing the
aggregation and rendering down to where the data already is, rather than pulling
it into R, is the whole point. countryatlas makes sure what you push down is
keyed on an honest, reconciled ISO spine.
