walkerke · October 25, 2025 14:35 · Oct 24, 2025 · Oct 24, 2025
diff --git a/duckdb-tiles.R b/duckdb-tiles.R
@@ -1,6 +1,6 @@
 # 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 (lightweight alternative to plumber)
+# and display them in a mapgl map using httpuv
 
 library(mapgl)
 library(duckdb) # Requires the latest DuckDB version (>= 1.4.0)

diff --git a/duckdb-tiles.R b/duckdb-tiles.R
@@ -0,0 +1,239 @@
+# 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 (lightweight alternative to plumber)
+
+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()