Skip to main content
The following content is a read-only preview of an executable Jupyter notebook.To run this notebook interactively:
  1. Go to Wherobots Cloud.
  2. Start a runtime.
  3. Open the notebook.
  4. In the Jupyter Launcher:
    1. Click File > Open Path.
    2. Paste the following path to access this notebook: examples/Open_Data_Connections/Foursquare_Places.ipynb
    3. Click Enter.
This notebook introduces the Foursquare Places table in the Wherobots Open Data catalog. We will create a live, interactive map like that shows the count of coffee shops in San Francisco aggregated by neighborhoods. drawing

​
Connecting

First, let’s list the tables in the wherobots_open_data.foursquare namespace.
We can use printSchema to see the columns in both these tables, and you can find the full documentation of the schema on the Foursquare site.

​
Counting records

Let’s see how many records exist total and with non-null entries for some core columns.
There are more than 100 million places in this dataset. About two-thirds of them have addresses associated with them, and most of them (over 90 million) are assigned to a category.

​
Selecting specific columns and places that are not closed

We can select the colummns we could use to find coffee shops that are currently open: Foursquare place ID, name, geometry, category labels, country, date refreshed, and date created. This query excludes places that are closed or have a null value for name, geom, or country.

​
Geographical subsets

We can use regular SQL to limit our dataset to the United States.
We can also filter to a region or polygon that is not part of the dataset by defining an area of interest and doing a spatial join. We’ll start with a WKT (well-known text) representation of the boundaries of San Francisco.

​
Mapping the data

​
Searching by business name

Let’s search for Starbucks locations within San Francisco.

​
Foursquare Categories

The Foursquare data assigns categories to most places, but not all. You can find more detail about the categories in the Foursquare docs.

​
Subset by category

​
Aggregation: Counting coffee shops in each neighborhood

To do this, we need a dataset of polygons for the SF neighborhoods. We have one from the city’s open data portal that we have loaded into an S3 bucket as a CSV file.
We can aggregate the count of coffee shops from Foursquare into these neighborhoods using a Spatial SQL query. ST_GeomFromWKT converts the text in the CSV file to a native geometry value.
We’ll use Sedona Kepler again, this time to visualize the data as a choropleth map. The darker the color, the more coffee shops there are in the neighborhood. We can see the downtown business district stands out in the eastern half of the city. The code cell below embeds the choropleth map configuration so the notebook can run without any local config files.

​
How is the Foursquare Places dataset different than other POI datasets?

The Foursquare Places dataset is continually checked and updated by the community though the Foursquare Placemaker Tools. Foursquare’s Placemaker Tools are a set of web-based tools that let individuals contribute to the Foursquare Places dataset and validate the accuracy of places in the dataset. These tools, currently in beta, allow users to add new venues, update existing information, and validate place data. Key features include:
  • Simple process for adding or editing places.
  • A collaborative system for verifying edits to ensure accuracy and transparency.
  • Integrates verified community insights immediately into the global Places dataset, ensuring it remains current and reflective of real-world changes.
By leveraging community knowledge, Foursquare’s Placemaker Tools enhance the quality and accuracy of Foursquare’s Places data. foursquare placemaker tools

​
Snapshots and versions

The Wherobots Open Data catalog keeps mutliple versions of the Foursquare data, snapshots taken of each release. Havasu, the Wherobots optimization of Iceberg GEO, supports built-in tags that are used for versioning. We can query what versions exist like this:
By default, queries will return data from the latest snapshot. If you want to query a specific snapshot, you can use the VERSION AS OF clause with a Foursquare release name. For example, we could look at the February 2025 snapshot.