Query the dataset using place count functions

Place count functions are predefined SQL queries that run in BigQuery and are complementary to querying the dataset directly. The main difference between querying the data directly and using a function is that functions don't enforce a minimum count threshold but instead enforce a minimum search area:

• Place datasets queries can only return counts 5 and above, but enforce no limitations on the size of the search area.
• Place count functions can return any counts, including 0, but enforce a minimum search area of 40.0 meters by 40.0 meters (1600 m²). Functions can also return Place IDs, which can be used to look up information about individual Places.

You might want to use Place count functions if it's important to know when a query returns no results or if you need to know low counts of places below 5. It is also useful if you need to get individual place information to spot check your results.

Places Count functions only provide counts, so if you need to perform more complex queries such as data joins, or get additional insights such as the average rating of a group of places, then query the dataset directly.

Supported place count functions and countries

Places Insights supports the following functions:

• PLACES_COUNT: Returns a single row containing a count of places.
• PLACES_COUNT_PER_TYPE: Returns a BigQuery table of places counts per places type.
• PLACES_COUNT_PER_GEO: Returns a BigQuery table of places counts per geographies.
• PLACES_COUNT_PER_H3: Returns a BigQuery table of places counts per H3 cell.

Along with the place counts, the PLACES_COUNT_PER_TYPE, PLACES_COUNT_PER_GEO, and PLACES_COUNT_PER_H3 functions also return up to 250 place IDs per element of the response. See all filter parameters.

Place IDs can be used with:

• Places API (New)
• Geocoding API
• Places UI Kit

Note: You can access the places count functions for the same cities and countries for which you have

Write queries with functions

Use the following format to call the functions: [project name (optional)].[table name].[function name].

If you changed the linked dataset name when setting up Places Insights, then use your custom name instead of the default table names listed here. You can also optionally include your project name. If one is not included, the query will default to the active project.

For example:

PROJECT_NAME.places_insights___us.PLACES_COUNT

Use a JSON_OBJECT to pass arguments to the function.

Note: When you use a place count function, the function is specified in the FROM clause and WITH_AGGREGATION_THRESHOLD is omitted.

Filter your results

The place count functions support many filters to refine your search. These parameters (for example, price_level or types) are case-sensitive and must match the parameter names exactly. See the filter parameters reference for a full list of options.

In the next example you apply filters to limit the search by minimum user rating, price level, business status, and whether the restaurant allows dogs:

SELECT `PROJECT_NAME.places_insights___us.PLACES_COUNT`(
  JSON_OBJECT(
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'business_status', ['OPERATIONAL'],
      'types', ["restaurant"],
      'min_rating', 1.3,
      'price_level', ['PRICE_LEVEL_INEXPENSIVE', 'PRICE_LEVEL_MODERATE'],
      'allows_dogs', TRUE
      )
) as count;

Note: Include the business_status filter to make sure that you get the count of OPERATIONAL businesses. Otherwise, the count includes closed businesses.

Place count function example

The following example uses the PLACES_COUNT function to return the number of operational restaurants within 1000 meters of the Empire State Building in New York City:

SELECT `PROJECT_NAME.places_insights___us.PLACES_COUNT`(
  JSON_OBJECT(
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'business_status', ['OPERATIONAL'],
      'types', ["restaurant"]
      )
) as count;

The response contains a single count:

This example uses the BigQuery ST_GEOGPOINT function to return a GEOGRAPHY value from a point and then pass that value to the geography parameter. It also passes the search radius around the point and the place type, "restaurant", to search for.

Note: The required and optional parameters differ for each place count function as described in filters.

Place count per type, geo, or H3 example

Along with the place counts, the PLACES_COUNT_PER_TYPE, PLACES_COUNT_PER_GEO, and PLACES_COUNT_PER_H3 functions also return up to 250 place IDs for the places included in the response.

For example, the PLACES_COUNT_PER_TYPE function returns a table of place counts per place type. Included in the response is an array of place IDs for the places that match each type. You can use the returned place IDs to look up information about each place.

The following function call returns a count of places with the types: restaurant, cafe, and bar:

SELECT * FROM `PROJECT_NAME.places_insights___us.PLACES_COUNT_PER_TYPE`(
  JSON_OBJECT(
      'geography', ST_GEOGPOINT(-73.9857, 40.7484), -- Empire State Building
      'geography_radius', 1000, -- Radius in meters
      'types', ["restaurant", "cafe", "bar"],
      'business_status', ['OPERATIONAL']
      )
);

This function returns a table with three columns: type, count, and sample_place_ids. The count columns shows the place count for each type, and the sample_place_ids column shows up to 250 place IDs for each type.

Note: To get information about a place, use the Place ID to call the Places API or Places UI kit. Validate Places Insights data using sample Place IDs provides an example.

Visualize results

Analysis and business intelligence tools are crucial to helping you discover insights from your BigQuery data. BigQuery supports several Google and third-party data visualization tools that you can use to analyze the results of your functions on Places Insights data.

For an example of visualizing the results of a function, see Visualize results. For more information and example on visualizing Places Insights results, see Visualize query results.

Limitations and requirements

Place count functions have the following limitations and requirements:

• Only COUNT insights are supported.
• A minimum search area of 40.0 meters by 40.0 meters (1600 m²) is required.
• Place function count parameter input size is limit to 1 MB.
• No support for filtering by place ID, brands, EV charge options, or address component.
• You can only access the places count functions for the cities and countries you have subscribed to. See Set up Places Insights for dataset access.
• Filter parameters (for example, geography or types) are case-sensitive and must match the parameter names exactly, or query will fail.