One SQL statement to create a web map of Pizza places

On Christmas 2021, I received one of my favorite gifts in recent years: a pizza oven!¹ It's a family favorite too, as we all enjoy our weekend pizza nights. Among mapping enthusiasts, beer and pizza seem to have a special place—my latest evidence is that Overturemaps uses pizza places as an example for downloading their data.

After writing my blog post about cloud native geo formats last week, it was time to prepare some pizza dough, and I pondered an equivalent of a one-liner program to create a complete map: Is there a way to extract geodata from public sources, process it, and create a web map in one go?

Without further ado, here is a one-liner you can copy and paste into your favorite shell, assuming you have DuckDB installed:

duckdb -c "load spatial; COPY(WITH switzerland AS (SELECT ST_Transform(ST_FlipCoordinates(geometry), 'epsg:4326', 'epsg:3857') AS geom FROM read_parquet('s3://overturemaps-us-west-2/release/2025-02-19.0/theme=divisions/type=division_area/*') WHERE country='CH' AND subtype='country'), pizza_places_bbox AS (SELECT id, names.primary AS name, addresses, confidence, ST_Transform(ST_FlipCoordinates(geometry), 'epsg:4326', 'epsg:3857') AS geom, bbox FROM read_parquet('s3://overturemaps-us-west-2/release/2025-02-19.0/theme=places/type=place/*', filename=true, hive_partitioning=1) WHERE categories.primary='pizza_restaurant' AND bbox.xmin BETWEEN 5.9559 AND 10.4921 AND bbox.ymin BETWEEN 45.818 AND 47.8084) SELECT p.id, p.name, p.confidence, p.addresses[1].freeform AS street, p.addresses[1].locality AS locality, p.geom AS geometry FROM pizza_places_bbox p, switzerland s WHERE ST_Contains(s.geom, p.geom)) TO 'swiss_pizza.pmtiles' WITH (FORMAT GDAL, DRIVER 'PMTiles');"

It takes about 20 seconds to run—1 second if the data is on your local disk. The one-liner is essentially an SQL statement executed in DuckDB that creates a map of all the Swiss pizza restaurants, "end-to-end." You can seamlessly drop it into Brandon Lui's PMTileViewer (credit where credit is due: Brandon is the inventor of PMTiles).

Here is the map of Swiss Pizza places, generated by one SQL statement (make sure to check the "show attributes" flag on the top right to see name and address of each place).

Nicely formatted, the SQL statement looks like this:

COPY(
  WITH switzerland AS (
    SELECT
      ST_Transform(ST_FlipCoordinates(geometry), 'epsg:4326', 'epsg:3857') AS geom
    FROM read_parquet('s3://overturemaps-us-west-2/release/2025-02-19.0/theme=divisions/type=division_area/*')
    WHERE country = 'CH'
      AND subtype = 'country'
  ),
  pizza_places_bbox AS (
    SELECT
      id,
      names.primary AS name,
      addresses,
      confidence,
      ST_Transform(ST_FlipCoordinates(geometry), 'epsg:4326', 'epsg:3857') AS geom,
      bbox
    FROM read_parquet('s3://overturemaps-us-west-2/release/2025-02-19.0/theme=places/type=place/*', filename=true, hive_partitioning=1)
    WHERE categories.primary = 'pizza_restaurant'
      AND bbox.xmin BETWEEN 5.9559 AND 10.4921
      AND bbox.ymin BETWEEN 45.818 AND 47.8084
  )
  SELECT
    p.id,
    p.name,
    p.confidence,
    p.addresses[1].freeform as street,
    p.addresses[1].locality as locality,
    p.geom AS geometry
  FROM pizza_places_bbox p,
       switzerland s
  WHERE ST_Contains(s.geom, p.geom)
) TO 'swiss_pizza.pmtiles' WITH (FORMAT GDAL, DRIVER 'PMTiles');

A quick explainer:

1. First, we obtain a polygon of Switzerland from Overturemaps. Since we want to show web maps, we need to convert the WGS84 coordinates to World Mercator. I'm not sure why we need to flip the coordinates, but it seems to me that flipping coordinate pairs is like the off-by-one errors in the spatial world ¯\_(ツ)_/¯. We label this table switzerland.
2. Second, we download our points of interest - pizza places! Again, we use Overturemaps and employ a bounding box to speed up the query, label it as pizza_places_bbox.
3. Let's perform some simple spatial filtering with the two datasets: we select only those pizza places that are inside the switzerland polygon (Yes, we could have also filtered for p.addresses[1].country, but where is the fun?!
4. Finally, we export them directly to PMTiles. DuckDB's spatial extension integrates GDAL, which makes it possible to export in any supported format (my installation supports 35 formats; you can check yours with select * FROM ST_Drivers() WHERE can_create = true;).

There is one caveat: This process does work for the entire planet, but the final results may have missing overview tiles. The PMTiles implementation in GDAL works reliably only for small datasets; tippecanoe is a better choice for large datasets, although the tile creation is no longer part of the SQL statement (which is admittedly only important in the context of the title of this blog).

Yes, you could also use OpenStreetMap instead of Overture. You just need to download the PBF file of your choice and use ST_ReadOSM in DuckDB. However, this involves two steps, and as far as I know, PBF files are not yet cloud-native.

Final thoughts 🔗

This is all fun, but the key ingredient for this one-liner is, once again, the recent cloud native geo movement, which I discussed in the blog post from last week. Returning to the pizza theme, cloud-native geo is like owning your private pizza oven that not only reaches 450°C but also offers precise temperature control and perfect heat distribution. Just as my oven can turn raw dough and toppings into a perfect Neapolitan pizza in 60 seconds for my guests, cloud-native geo transforms raw spatial data into refined web maps with remarkable speed, delivering them directly to clients without involving third parties. Just as I can serve dozens of pizzas in an evening with my oven, cloud-native geo allows you to deliver massive amounts of spatial data at scale.

Okay, I admit the metaphor in the last paragraph is somewhat forced and strained in places (no pun intended)-but the key benefits in both cases are simplicity, speed, control and scalability.

Do you have feedback? A good recipe for pizza dough? Don't hesitate to get in touch with me!

image credits: Vienia Napoli