Skip to content

Instantly share code, notes, and snippets.

@walkerke

walkerke/duckdb-tiles.R

Last active October 25, 2025 03:07
Show Gist options
  • Save walkerke/c90ab6b8f403169e615eabeb0339b15b to your computer and use it in GitHub Desktop.
Save walkerke/c90ab6b8f403169e615eabeb0339b15b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
duckdb-tiles.R
# DuckDB Vector Tiles with mapgl Example
# This script demonstrates how to serve vector tiles from DuckDB using ST_AsMVT()
# and display them in a mapgl map using httpuv
library(mapgl)
library(duckdb) # Requires the latest DuckDB version (>= 1.4.0)
library(httpuv)
library(sf)
library(duckspatial)
library(tigris)
# 1. Setup DuckDB connection and load spatial extension
con <- dbConnect(duckdb::duckdb(), dbdir = "tiles.duckdb")
ddbs_install(con)
ddbs_load(con)
# 2. Load your spatial data into DuckDB
# Example: Load an sf object into DuckDB and transform to Web Mercator (EPSG:3857)
# All US block groups: 242,000 polygons
data <- block_groups(cb = TRUE)
# Transform to Web Mercator for tiling
data_mercator <- st_transform(data, 3857)
# Write to DuckDB using duckspatial
ddbs_write_vector(con, data_mercator, "features", overwrite = TRUE)
# Optional: Create a spatial index for better performance
dbExecute(con, "CREATE INDEX idx_geom ON features USING RTREE (geometry)")
# 3. Create a lightweight httpuv server for serving tiles
# Helper function to parse tile coordinates from URL path
parse_tile_path <- function(path) {
# Match pattern: /tiles/{z}/{x}/{y}.pbf
pattern <- "^/tiles/(\\d+)/(\\d+)/(\\d+)\\.pbf$"
matches <- regmatches(path, regexec(pattern, path))[[1]]
if (length(matches) == 4) {
list(
z = as.integer(matches[2]),
x = as.integer(matches[3]),
y = as.integer(matches[4])
)
} else {
NULL
}
}
# Create the httpuv application
tile_app <- list(
call = function(req) {
path <- req$PATH_INFO
# Handle CORS preflight requests
if (req$REQUEST_METHOD == "OPTIONS") {
return(list(
status = 200L,
headers = list(
'Access-Control-Allow-Origin' = '*',
'Access-Control-Allow-Methods' = 'GET, OPTIONS',
'Access-Control-Allow-Headers' = '*'
),
body = ""
))
}
# Parse tile coordinates from URL
tile_coords <- parse_tile_path(path)
if (!is.null(tile_coords)) {
# Query DuckDB for the tile
tile_query <- "
SELECT ST_AsMVT(mvt_geom, 'layer')
FROM (
SELECT
GEOID,
NAME,
ST_AsMVTGeom(
geometry,
(SELECT ST_Extent(ST_TileEnvelope(?, ?, ?)))
) AS geometry
FROM features
WHERE ST_Intersects(geometry, ST_TileEnvelope(?, ?, ?))
) AS mvt_geom
"
tryCatch(
{
result <- dbGetQuery(
con,
tile_query,
params = list(
tile_coords$z,
tile_coords$x,
tile_coords$y,
tile_coords$z,
tile_coords$x,
tile_coords$y
)
)
# Extract tile blob
tile_blob <- if (!is.null(result[[1]][[1]])) {
result[[1]][[1]]
} else {
raw(0)
}
# Return successful response
list(
status = 200L,
headers = list(
'Content-Type' = 'application/x-protobuf',
'Access-Control-Allow-Origin' = '*' # Enable CORS
),
body = tile_blob
)
},
error = function(e) {
# Return error response
list(
status = 500L,
headers = list(
'Content-Type' = 'text/plain',
'Access-Control-Allow-Origin' = '*'
),
body = paste("Error generating tile:", e$message)
)
}
)
} else {
# Return 404 for non-tile requests
list(
status = 404L,
headers = list(
'Content-Type' = 'text/plain',
'Access-Control-Allow-Origin' = '*'
),
body = "Not Found"
)
}
}
)
# 4. Start the tile server
# Find available port (starting from 8000)
find_available_port <- function(start_port = 8000, max_attempts = 10) {
for (i in 0:(max_attempts - 1)) {
port <- start_port + i
tryCatch(
{
# Try to start server on this port
test_server <- startServer(
"127.0.0.1",
port,
list(call = function(req) {
list(status = 200L, body = "test")
})
)
stopServer(test_server)
return(port)
},
error = function(e) {
# Port is in use, try next one
}
)
}
stop("Could not find available port")
}
port <- find_available_port()
cat("Starting tile server on port", port, "\n")
# Start the server in the background
server <- startDaemonizedServer("127.0.0.1", port, tile_app)
# Store server info for cleanup
tile_server_info <- list(
server = server,
port = port,
con = con
)
cat("Tile server running at http://127.0.0.1:", port, "/\n", sep = "")
cat(
"Tiles available at: http://127.0.0.1:",
port,
"/tiles/{z}/{x}/{y}.pbf\n",
sep = ""
)
# 5. Create the mapgl map with the DuckDB vector tile source
m <- maplibre(
center = c(-79.5, 35.5),
zoom = 6,
style = carto_style("positron")
) |>
add_vector_source(
id = "duckdb-tiles",
tiles = paste0("http://127.0.0.1:", port, "/tiles/{z}/{x}/{y}.pbf"),
minzoom = 0,
maxzoom = 14,
promote_id = "GEOID"
) |>
add_fill_layer(
id = "features-fill",
source = "duckdb-tiles",
source_layer = "layer", # ST_AsMVT default layer name
fill_color = "steelblue",
fill_opacity = 0.6,
tooltip = "GEOID",
hover_options = list(
fill_color = "yellow",
fill_opacity = 1
)
) |>
add_line_layer(
id = "features-line",
source = "duckdb-tiles",
source_layer = "layer",
line_color = "white",
line_width = 1
)
# Display the map
m
# Cleanup function (run this when done)
cleanup_tile_server <- function() {
if (exists("tile_server_info")) {
stopDaemonizedServer(tile_server_info$server)
dbDisconnect(tile_server_info$con)
cat("Tile server stopped and database disconnected\n")
}
}
# Note: To stop the server and disconnect, run:
# cleanup_tile_server()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment