ggsql 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:
country_overrides()), and
join World Bank indicators onto geometry.So countryatlas becomes the data layer and ggsql the renderer.
world_query() is a pure string builder (it needs nothing
installed), so you can see exactly what will be sent to ggsql:
world_query(
gdp_per_capita,
projection = "equal_earth",
palette = "magma",
transform = "log10",
title = "GDP per capita"
)
#> VISUALISE gdp_per_capita AS fill, geometry FROM countryatlas_world
#> DRAW spatial
#> PROJECT TO equal_earth
#> SCALE fill TO magma VIA log10
#> LABEL title => 'GDP per capita'Every projection the package knows has a translation, and the other layers are a word away:
world_query(gdp_per_capita, projection = "north_polar", layer = "binned")
#> VISUALISE gdp_per_capita AS fill, geometry FROM countryatlas_world
#> DRAW spatial
#> PROJECT TO lambert SETTING origin => (0, 90)
#> SCALE BINNED fill TO viridis SETTING breaks => 5
world_query(gdp_per_capita, layer = "bubble", size = "population")
#> VISUALISE FROM countryatlas_world
#> DRAW spatial
#> MAPPING geometry
#> DRAW point
#> MAPPING centroid_lon AS lon, centroid_lat AS lat, population AS size, gdp_per_capita AS fill
#> PROJECT TO equal_earth
#> SCALE fill TO viridisas_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"):
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:
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.
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:
```{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'
```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.