PLACES_COUNT_PER_H3 function

The PLACES_COUNT_PER_H3 function takes a geographic area to search and returns a table of places counts per H3 cell in the search area.

Because the PLACES_COUNT_PER_GEO function returns a table, call it using a FROM clause.

• Input parameters:

  • Required: The geography filter parameter that specifies the search area. The geography parameter a value defined by the BigQuery GEOGRAPHY data type, which supports points, linestrings, and polygons.

    For examples of using different types of search geographies, such as viewports and lines, see PLACES_COUNT function.

  • Required: The h3_resolution filter parameter that specifies the H3 resolution used to aggregate the count of places in each H3 cell. Supported value are from 0 to 8.

  • Optional: Additional filter parameters to refine your search.

• Returns:

• A table with one row per H3 cell. The table contains the columns h3_cell_index (STRING), geography (GEOGRAPHY) which defines the polygon representing the H3 cell, count (INT64), and sample_place_ids (ARRAY<STRING>) where sample_place_ids contains up to 250 place IDs for each H3 cell.

Example: Count wheelchair accessible convenience and grocery stores per H3 cell

The example below computes the number of operational, wheelchair accessible convenience and grocery stores per H3 cell in New York City. The H3 cells use a resolution level of 8.

This example uses the Overture Maps Data BigQuery public dataset to get the geography for New York City.

Note: Because the response table contains a geography field, you can visualize it using tools that support BigQuery GEOGRAPHY types. 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.

DECLARE geo GEOGRAPHY;

-- Get the geography for New York City.
SET geo = (SELECT geometry FROM `bigquery-public-data.overture_maps.division_area`
  WHERE country = 'US' AND names.primary = 'City of New York'  LIMIT 1);

SELECT * FROM `maps-platform-analytics-hub.sample_places_insights_us.PLACES_COUNT_PER_H3`(
  JSON_OBJECT(
      'geography', geo,
      'types', ["convenience_store","grocery_store"],
      'wheelchair_accessible_entrance', true,
      'business_status', ['OPERATIONAL'],
      'h3_resolution', 8
    )
);

The response for the function:

Visualize results

The following images show this data displayed in Looker Studio as a filled map. The darker the H3 cell the higher the concentration of results:

To import your data into Looker Studio:

1. Run the function above to generate the results.

2. In the BigQuery results, click Open in -> Looker Studio. Your results are automatically imported into Looker Studio.

3. Looker Studio creates a default report page and initializes it with a title, table, and bar graph of the results.

   

4. Select everything on the page and delete it.

5. Click Insert -> Filled map to add a filled map to your report.

6. Under Chart types -> Setup configure the fields as shown below::

   

7. The filled map appears as above. You can optionally select Chart types -> Styles to further configure the appearance of the map.

For more information and example on visualizing Places Insights results, see Visualize query results.