Creating a Database in MotherDuck

Instead of downloading the full 11.05 GB locally (aws s3 cp --no-sign s3://fsq-os-places-us-east-1/release/dt=2025-02-06/places/parquet . --recursive ), we can simply create a database over the network using the power of MotherDuck with:

Copy code
CREATE TABLE fsq_os_places AS
select * from read_parquet('hf://datasets/foursquare/fsq-os-places/release/dt=2025-01-10/places/parquet/*.parquet')

Now we can simply use the data from everywhere with MotherDuck in a shared Notebook or locally with:

Copy code
❯ duckdb 
v1.2.0 5f5512b827
Enter ".help" for usage hints.
D ATTACH 'md:_share/foursquare/0cbf467d-03b0-449e-863a-ce17975d2c0b';
D show all databases;
┌─────────────┬─────────────┬──────────────────┬────────────────────────────────────────────────────────────┐
│    alias    │ is_attached │       type       │                    fully_qualified_name                    │
│   varchar   │   boolean   │     varchar      │                          varchar                           │
├─────────────┼─────────────┼──────────────────┼────────────────────────────────────────────────────────────┤
│ bsky        │ true        │ motherduck       │ md:bsky                                                    │
│ foursquare  │ true        │ motherduck       │ md:foursquare                                              │
└─────────────┴─────────────┴──────────────────┴────────────────────────────────────────────────────────────┘
D use foursquare;
D show tables;
┌───────────────────┐
│       name        │
│      varchar      │
├───────────────────┤
│ fsq_os_categories │
│ fsq_os_places     │
└───────────────────┘

We can check the data types with describe fsq_os_places; or check on Places OS Data Schemas. If we check locally, we see that we have some geometric data:

Copy code
D describe fsq_os_places;
┌─────────────────────┬────────────────────────────────────────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│     column_name     │                        column_type                         │  null   │   key   │ default │  extra  │
│       varchar       │                          varchar                           │ varchar │ varchar │ varchar │ varchar │
├─────────────────────┼────────────────────────────────────────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ fsq_place_id        │ VARCHAR                                                    │ YES     │         │         │         │
│ name                │ VARCHAR                                                    │ YES     │         │         │         │
│ latitude            │ DOUBLE                                                     │ YES     │         │         │         │
│ longitude           │ DOUBLE                                                     │ YES     │         │         │         │
│ address             │ VARCHAR                                                    │ YES     │         │         │         │
│ locality            │ VARCHAR                                                    │ YES     │         │         │         │
│ region              │ VARCHAR                                                    │ YES     │         │         │         │
...
│ geom                │ GEOMETRY                                                   │ YES     │         │         │         │
│ bbox                │ STRUCT(xmin DOUBLE, ymin DOUBLE, xmax DOUBLE, ymax DOUBLE) │ YES     │         │         │         │

Highest Chocolate Store Density in Swiss Cities

As the data contains Chocolate stores and I'm from Switzerland, the land of chocolate 😉, I was interested in the city with the most significant store density. We can do that with this data and DuckDB.

Let's first check the data and query all the cities with the 20 most entries:

Copy code
D select locality, count(*) from fsq_os_places where country = 'CH' group by locality order by 2 desc limit 20;
┌──────────────┬──────────────┐
│   locality   │ count_star() │
│   varchar    │    int64     │
├──────────────┼──────────────┤
│              │        84228 │
│ Zürich       │        32488 │
│ Basel        │        11975 │
│ Bern         │        11256 │
│ Genève       │        11083 │
│ Lausanne     │         9161 │
│ Luzern       │         6343 │
│ Winterthur   │         6058 │
│ St. Gallen   │         4807 │
│ Zurich       │         4497 │
│ Lugano       │         4027 │
│ Zug          │         3849 │
│ Geneva       │         2938 │
│ Chur         │         2611 │
│ Fribourg     │         2426 │
│ Thun         │         2383 │
│ Schaffhausen │         2288 │
│ Sion         │         2280 │
│ Aarau        │         2234 │
│ Carouge      │         2188 │
├──────────────┴──────────────┤
│ 20 rows           2 columns │
└─────────────────────────────┘

Secondly, we need the category_id for chocolate stores. We find these in the metadata table that comes with this dataset fsq_os_categories:

Copy code
D select distinct category_label, category_name, category_id  from fsq_os_categories  where lower(category_label) like '%chocolate%';
┌─────────────────────────────────────────────────────┬─────────────────┬──────────────────────────┐
│                   category_label                    │  category_name  │       category_id        │
│                       varchar                       │     varchar     │         varchar          │
├─────────────────────────────────────────────────────┼─────────────────┼──────────────────────────┤
│ Retail > Food and Beverage Retail > Chocolate Store │ Chocolate Store │ 52f2ab2ebcbc57f1066b8b31 │
└─────────────────────────────────────────────────────┴─────────────────┴──────────────────────────┘

Next, let's install and activate the spatial extensions:

Copy code
INSTALL spatial; 
LOAD spatial; 

As with the above city data, I chose the biggest cities in Switzerland—Zurich, Geneva, Bern, Basel, and Luzern—and checked the highest density of chocolate stores.

The query has three major queries: it defines city centers and their bounding boxes to speed up spatial queries by pre-filtering coordinates (not needed); second, it identifies chocolate stores within a 5km radius of each city center using spatial functions and category filtering; and third, it calculates store density per square kilometer and lists the three closest chocolate stores to each city center:

Copy code
WITH city_centers AS (  
  SELECT * FROM (
    VALUES 
      ('Zurich', ST_Point(8.5417, 47.3769), 8.5417-0.05, 8.5417+0.05, 47.3769-0.05, 47.3769+0.05),
      ('Geneva', ST_Point(6.1432, 46.2044), 6.1432-0.05, 6.1432+0.05, 46.2044-0.05, 46.2044+0.05),
      ('Bern', ST_Point(7.4474, 46.9480), 7.4474-0.05, 7.4474+0.05, 46.9480-0.05, 46.9480+0.05),
      ('Basel', ST_Point(7.5886, 47.5596), 7.5886-0.05, 7.5886+0.05, 47.5596-0.05, 47.5596+0.05),
      ('Luzern', ST_Point(8.3093, 47.0505), 8.3093-0.05, 8.3093+0.05, 47.0505-0.05, 47.0505+0.05),
  ) AS cities(city_name, center, lon_min, lon_max, lat_min, lat_max)
),
stores_by_city AS (
  SELECT 
    c.city_name,
    p.name as store_name,
    ROUND(ST_Distance_Spheroid(
      ST_Point(p.longitude, p.latitude),
      c.center
    )::numeric, 2) as distance_from_center
  FROM fsq_os_places p
  CROSS JOIN city_centers c
  WHERE
	  ---unnest and filter by chocolate category
     array_contains(fsq_category_ids, '52f2ab2ebcbc57f1066b8b31')
    AND country = 'CH' --filter by metadata too to speed up
    AND p.longitude BETWEEN c.lon_min AND c.lon_max
    AND p.latitude BETWEEN c.lat_min AND c.lat_max
    AND ST_Distance_Spheroid(ST_Point(p.longitude, p.latitude), c.center) <= 5000
)
SELECT 
  s.city_name,
  COUNT(*) as total_stores,
  -- Calculate stores per km² (area of 5km radius circle is π*5² ≈ 78.54 km²)
  ROUND(COUNT(*)::numeric / 78.54, 2) as stores_per_km2,
  (
    SELECT STRING_AGG(store_name, ', ')
    FROM (
      SELECT store_name
      FROM stores_by_city s2
      WHERE s2.city_name = s.city_name
      ORDER BY distance_from_center
      LIMIT 3
    )
  ) as closest_stores
FROM stores_by_city s
GROUP BY city_name
ORDER BY total_stores DESC;

The result looks like this:

It is interesting to know that Geneva has 42 chocolate stores, which is 0.53 stores per km. If the data quality is correct, this is quite impressive. Geneva has a higher density of chocolate stores than Zurich and Basel. Unfortunately, my favorite city, Bern, is last in this measurement 🙂.

In the next step, you could visualize this on a map, but more on it and its libraries later in "Data Visualization".

Finding Store Clusters around Switzerland

Another common example is to build clusters. For example, store clusters allow us to identify retail hotspots where multiple businesses are located extremely close to each other. This analysis is particularly valuable for urban planners studying commercial density, real estate investors looking for high-traffic locations, or businesses seeking to understand competitive proximity. These micro-clusters often indicate shopping arcades, malls, or historic commercial districts where businesses benefit from shared foot traffic.

First, we install the spatial extension again (in case you haven't run it above):

Copy code
INSTALL spatial; 
LOAD spatial; 

The below query selects clusters of shops within a 2km radius of Biel, Switzerland, where neighboring businesses are located within 2 meters of each other, identifying extremely close commercial pairings that likely share walls or entrances:

Copy code
WITH base_location AS (
  SELECT 
    ST_Point(7.2474174805428335, 47.13673837848461) as center  -- Biel, Switzerland
),
nearby_stores AS (
SELECT 
    fsq_place_id,
    name, 
    longitude, 
    latitude,
    ST_Point(longitude, latitude) as location,
    -- Calculate distance in meters
    ROUND(ST_Distance_Spheroid(
        ST_Point(longitude, latitude), 
        base_location.center
    )::numeric, 2) as distance_meters
FROM fsq_os_places, base_location
WHERE date_closed IS NULL
    -- Use bounding box for initial filtering
    AND longitude BETWEEN 7.0 AND 7.5
    AND latitude BETWEEN 46.9 AND 47.3
    -- Then apply precise distance filter
    AND ST_Distance_Spheroid(
        ST_Point(longitude, latitude), 
        base_location.center
    ) <= 2000  -- 2km radius
)
 SELECT 
  a.name as store1, CAST(a.latitude AS VARCHAR) || ', ' || CAST(a.longitude AS VARCHAR) as location,
  b.name as store2, CAST(b.latitude AS VARCHAR) || ', ' || CAST(b.longitude AS VARCHAR) as location,
  ROUND(ST_Distance(a.location, b.location), 2) as distance_meters
FROM nearby_stores a
JOIN nearby_stores b 
  ON a.fsq_place_id < b.fsq_place_id
  AND ST_DWithin(a.location, b.location, 2)  -- Looking for stores within Xm of each other
ORDER BY distance_meters
LIMIT 20000; 

The query employs a two-step spatial filtering process for efficiency: first using simple bounding box coordinates (longitude BETWEEN 7.0 AND 7.5) as a coarse filter, then applying the more computationally expensive ST_Distance_Spheroid function only on that filtered subset.

This approach significantly reduces processing time. The self-join with a.fsq_place_id < b.fsq_place_id ensures each pair is counted only once, while ST_DWithin efficiently identifies stores within the 2-meter proximity threshold without calculating exact distances until the final display.

This data lets you do many more use cases. I encourage you to play around with it yourself. We have shared the database on MotherDuck, so you can easily query it with DuckDB via duckdb and attach all databases with ATTACH 'md:' , or use MotherDuck UI and attach from there.