Write a Places Insights query

To access Places Insights data, you write SQL queries in BigQuery that return aggregated insights about places. Results are returned from the dataset for the search criteria specified in the query.

Note: Aggregation counts are returned only if the count is 5 or higher. For example, if your search criteria results in a count of 1, 2, 3, or 4 then the result aggregation is omitted from the result. 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²).

Success: BigQuery also provides documentation and examples for running queries on geospatial data. For more information, see Introduction to geospatial analytics.

Query requirements

The SELECT statement in the query must include WITH AGGREGATION_THRESHOLD and specify the dataset. For example:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`

In this example, you use FROM to specify the places_insights___us___sample.places_sample dataset for the United States.

Specify a location restriction

Notice that the previous query does not specify any location restriction, which means the data aggregation is applied to the entire dataset. Typically you specify a location restriction such as shown below:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)

In this example, your query specifies a target restriction centered on the Empire State Building in New York City, with a radius of 1000 meters.

Note: A place is only included in the results if the center point of the place falls within the location restriction. A place, such as a park, whose center point is outside the location restriction is not included in the results.

You can use a polygon to specify the search area. When using a polygon, the points of the polygon must define a closed loop where the first point in the polygon is the same as the last point:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
ST_CONTAINS(ST_GEOGFROMTEXT("""POLYGON((-73.985708 40.75773,-73.993324 40.750298,
                                      -73.9857 40.7484,-73.9785 40.7575,
                                      -73.985708 40.75773))"""), point)

In the next example, you define the search area using a line of connected points. The line is similar to a travel route calculated by the Routes API. The route might be for a vehicle, a bicycle, or for a pedestrian:

DECLARE route GEOGRAPHY;

SET route = ST_GEOGFROMTEXT("""LINESTRING(-73.98903537033028 40.73655649223003,
                                          -73.93580216278471 40.80955538843361)""");

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(route, point, 100)

In this example, you set the search radius to 100 meters around the line.

Filter by place dataset fields

Refine your search based on the fields defined by the dataset schema. Filter results based on dataset fields such as place regular_opening_hours, price_level, and customer rating.

Reference any fields in the dataset defined by the dataset schema for your country of interest. The dataset schema for each country is comprised of two parts:

• The core schema that is common to the datasets for all countries.
• A country-specific schema that defines schema components specific to that country.

For example, your query can include a WHERE clause that defines filtering criteria for the query. In the following example, you return aggregation data for places of type restaurant with a business_status of OPERATIONAL, that have a rating greater than or equal to 4.0, and with allows_dogs set to true:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND 'restaurant' IN UNNEST(types)
AND business_status = "OPERATIONAL"
AND rating >= 4.0
AND allows_dogs = true

The next query returns results for places that have at least eight EV charging stations:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  ev_charge_options.connector_count > 8;

Filter on place primary type and place type

Each place in the dataset can have:

• A single primary type associated with it from the types defined by Table A. For example, the primary type might be mexican_restaurant or steak_house. Use primary_type in a query to filter the results on a place's primary type.

• Multiple type values associated with it from the types defined by Table A. For example a restaurant might have the following types: seafood_restaurant, restaurant, food, point_of_interest, establishment. Use types in a query to filter the results on the list of types associated with the place.

The following query returns results for all places with a primary type of bar but also function as a restaurant:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  'restaurant' IN UNNEST(types)
  AND 'bar' = primary_type

Filter on predefined data values

Many dataset fields have predefined values. For example

• The price_level field supports the following predefined values:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• The business_status field supports the following predefined values:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Note: Typically you include the business_status filter to make sure that you get the count of OPERATIONAL businesses. Otherwise the counts includes closed businesses.

In this example, the query returns the count of all restaurants with a business_status of OPERATIONAL within a 1000 meter radius of the Empire State Building in New York City:

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
AND business_status = "OPERATIONAL"
AND 'restaurant' IN UNNEST(types)

Filter by hours of operation

In this example, return the count of all places in a geographic area with Friday happy hours:

SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count
FROM `places_insights___us___sample.places_sample`,
UNNEST(regular_opening_hours_happy_hour.friday) AS friday_hours
WHERE '17:00:00' BETWEEN friday_hours.start_time AND friday_hours.end_time
AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000);

Use an aggregation function

The example below shows the supported BigQuery aggregation functions. This query aggregates the ratings of all places situated within a 1000 meter radius of the Empire State Building in New York City to produce rating statistics:

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(id) AS place_count,
  APPROX_COUNT_DISTINCT(rating) as distinct_ratings,
  COUNTIF(rating > 4.0) as good_rating_count,
  LOGICAL_AND(rating <= 5) as all_ratings_equal_or_below_five,
  LOGICAL_OR(rating = 5) as any_rating_exactly_five,
  AVG(rating) as avg_rating,
  SUM(user_rating_count) as rating_count,
  COVAR_POP(rating, user_rating_count) as rating_covar_pop,
  COVAR_SAMP(rating, user_rating_count) as rating_covar_samp,
  STDDEV_POP(rating) as rating_stddev_pop,
  STDDEV_SAMP(rating) as rating_stddev_samp,
  VAR_POP(rating) as rating_var_pop,
  VAR_SAMP(rating) as rating_var_samp,
FROM
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

Return result groups

The queries shown so far return a single row in the result that contains the aggregation count for the query. You can also use the GROUP BY operator to return multiple rows in the response based on the grouping criteria.

Note: Performing a GROUP BY operation is not allowed on address component fields (such as administrative_area_level, locality and postal_code) in the Places Insights dataset.

For example, the following query returns results grouped by the primary type of each place in the search area:

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000)
GROUP BY primary_type

The following image shows an example output to this query:

In this example you define a table of locations. For each location you then compute the number of nearby restaurants, meaning those within 1000 meters:

WITH my_locations AS (
  SELECT 'Location 1' AS name, ST_GEOGPOINT(-74.00776440888504, 40.70932825380786) AS location
  UNION ALL
  SELECT 'Location 2' AS name, ST_GEOGPOINT(-73.98257192833559, 40.750738934863215) AS location
  UNION ALL
  SELECT 'Location 3' AS name, ST_GEOGPOINT(-73.94701794263223, 40.80792954838445)  AS location
)
SELECT WITH AGGREGATION_THRESHOLD
  l.name,
  COUNT(*) as count
FROM
  `places_insights___us___sample.places_sample` p
JOIN
   my_locations l
ON
  ST_DWITHIN(l.location, p.point, 1000)
WHERE
  primary_type = "restaurant"
  AND business_status = "OPERATIONAL"
GROUP BY
  l.name

The following image shows an example output to this query: