Place count functions overview

Place count functions are another way to query the Places data available in Places Insights. These functions are predefined SQL queries that run in BigQuery and are complementary to places data queries. The main difference is that functions don't enforce a minimum count threshold but instead enforce a minimum search area:

• Place datasets queries can only return counts above 5 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²).

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. For example, being able to return low counts is important for site selection use cases.

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.

Similar to the places datasets, places count functions are available for a top city for each of the supported countries. For example, for Australia, you can access the place count functions for Sydney, Australia.

For the complete list of supported cities and countries, see Reference place count functions in BigQuery.

Writing queries with functions

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].[dataset name].places_sample.PLACES_COUNT.

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 `places_insights___us___sample.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:

In this example the SELECT statement references the PLACES_COUNT function for the United States using the syntax:

places_insights___us___sample.PLACES_COUNT

For the complete list of supported cities and countries, see Reference place count functions in BigQuery.

Use a JSON_OBJECT to pass arguments to the function. 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.

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 `places_insights___us___sample.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.

Apply filters to the function

Apply filters to narrow your search criteria. The place count functions support many filters to refine your search. 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 `places_insights___us___sample.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;

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 or address component.

Compare place count functions and place dataset queries

The following table lists the key differences:

Reference place count functions in BigQuery

Place count functions support the following cities and countries. You must subscribe to each city and country separately.