Explore the world with Foursquare Places on Snowflake

The Foursquare Places dataset covers more than 96 million places around the world — including interesting fields like the popularity of each one. In this post we’ll explore the dataset and highlight some of its possibilities.

Watch on youtube

This is a map that shows the most popular type of restaurant for each state around the US, and the pattern is clear — states around the East love pizza, states around the West love Mexican food:

Most popular restaurant type per US state. Image by author

But there are some exceptions:

• Idaho in the West prefers pizza.
• Louisiana in the South is all about cajun food.
• And Maine in the Northeast loves seafood.

To build this map I used the Foursquare Places premium dataset, through the Snowflake Data Marketplace. You can find my queries below.

This dataset includes more than 96 million places from all over the world — so we can expand our food map to the whole world. These are the results:

Most popular restaurant type per country. Image by author

If we zoom into Europe, we’ll find that countries like Italy, France, Spain, and Germany love their own food — but England? They would rather eat Indian food.

Most popular restaurant type per European country. Image by author

Same story around South America: Peru and Brazil love their own food, but Chileans? We love sushi.

Most popular restaurant type per South American country. Image by author

What is fascinating about Foursquare is that you can explore the whole world, and also focus on the hyper local. For example, let’s go to San Francisco, where you can find that each zip code has its own favorite type of food: Chinese, Mexican, Thai, Italian, we got it all.

Most popular restaurant type per SF zip code. Image by author

Now with Foursquare you can also go way beyond food. For example, this is the most popular type of business in each zip code:

Most popular business type per SF zip code. Image by author

Offices, shopping, boutiques, laundry services, and… bakeries, of course.

And we can go deeper, find for example the exact location of each beauty salon around San Francisco — and their popularity.

Beauty salons around SF. Image by author

The Foursquare Places Dataset can help you understand where your customers are, how to grow your business — and all with SQL and the power of the Data Cloud. Where would you open a new store?

Queries

Creating views to filter data and customize schema

Some rules I followed to produce these visualizations:

• I’m only looking at places with data updated after Jan 2020.
• For restaurants, I’m only counting those with at least 1 tip left by users on Foursquare.

create or replace view fsq
as (
    select *
    from fsq.public.global_latest_venues
    where date_refreshed > '2020-01-01'
)
;
create or replace view restaurants_us_state
as
select region
  , count(*) c
  , any_value(name) sample_name
  , any_value(locality||', '||region) sample_city
from fsq
where country='us'
and total_tips > 1
and category_labels[0][2]='Restaurants'
group by 1
;
create or replace view restaurants_us_zip
as
select postcode
  , count(*) c
  , any_value(name) sample_name
  , any_value(locality||', '||region) sample_city
from fsq
where country='us'
and total_tips > 1
and category_labels[0][2]='Restaurants'
group by 1
;
create or replace view restaurants_world
as
select country
  , count(*) c
  , any_value(name) sample_name
  , any_value(locality||', '||region) sample_city
from fsq
where total_tips > 1
and category_labels[0][2]='Restaurants'
group by 1
order by c desc
;

Top restaurant type per state

Pizza and Mexican are the most popular type of restaurants in each state in the US — only if you remove the also popular categories ‘American Restaurant’, ‘Fast Food Restaurant’, and ‘Sandwich Place’. I did so to get a more interesting map.

create table viz_pizza_mexican_other_state
as
select a.region, venue_primary_category
  , count(*) cc
  , count(*) / any_value(b.c) ratio
  , row_number() over(partition by a.region order by ratio desc) rn
  , any_value(name) sample_name
  , any_value(locality||', '||a.region) sample_locality
from fsq a
join restaurants_us_state b
on a.region=b.region
where country='us'
and total_tips > 1
and category_labels[0][2]='Restaurants'
and venue_primary_category not in ('American Restaurant', 'Fast Food Restaurant', 'Sandwich Place')
group by 1,2
having cc>10
qualify rn=1
;

World restaurants

create table viz_world_fav_restaurants
as
select a.country, venue_primary_category
  , count(*) cc
  , count(*) / any_value(b.c) ratio
  , row_number() over(partition by a.country order by ratio desc) rn
  , any_value(name) sample_name
  , any_value(locality||', '||a.region) sample_locality
from fsq a
join restaurants_world b
on a.country=b.country
where total_tips > 1
and category_labels[0][2]='Restaurants'
and venue_primary_category not in ('American Restaurant', 'Fast Food Restaurant', 'Sandwich Place', 'Restaurant')
group by 1,2
having cc>10
qualify rn=1;

San Francisco restaurants

create table viz_san_fco_restaurants
as
select a.postcode, venue_primary_category
  , count(*) cc
  , count(*) / any_value(b.c) ratio
  , row_number() over(partition by a.postcode order by ratio desc) rn
  , any_value(name) sample_name
  , any_value(locality||', '||a.region) sample_locality
from fsq a
join restaurants_us_zip b
on a.postcode=b.postcode
where total_tips > 1
and category_labels[0][2]='Restaurants'
and venue_primary_category not in ('American Restaurant', 'Fast Food Restaurant', 'Sandwich Place', 'Restaurant')
and locality='San Francisco'
group by 1,2
having cc>10
qualify rn=1;

San Francisco shops

select a.postcode, venue_primary_category
  , count(*) cc
  , row_number() over(partition by a.postcode order by cc desc) rn
  , any_value(name) sample_name
  , any_value(locality||', '||a.region) sample_locality
from fsq a
where total_tips > 1
and category_labels[0][2] not in ('Restaurants', 'Cafes, Coffee and Tea Houses', 'Beauty Salons and Barbers', 'Hotels and Motels')
and locality='San Francisco'
group by 1,2
having cc>10
qualify rn=1

San Francisco beauty salons

create or replace table viz_sf_beauty
as
select a.postcode, venue_primary_category
, name, latitude, longitude, popularity
from fsq a
where total_tips > 1
and category_labels[0][2] in ('Beauty Salons and Barbers')
;

Next steps

Now it’s your turn to explore the world — contact Foursquare through the Snowflake Data Marketplace to get your hands on this dataset.

Check out our webinars with Foursquare, to go even deeper:

How to Select Your Next Retail Location Using Third-Party Data

Want more?

I’m Felipe Hoffa, Data Cloud Advocate for Snowflake. Thanks for joining me on this adventure. You can follow me on Twitter, and check reddit.com/r/snowflake for the most interesting Snowflake news.

Snowflake-Labs/awesome-snowflake