Write a query using brands data

Places Insights provides brand information for many categories of places. For example:

• For the category of "ATMs, Banks, and Credit Unions", the brands data contains an entry for each of the brands PNC, UBS, and Chase banks.
• For the category "Automotive Rentals", the data contains an entry for each of the brands Budget, Hertz, and Thrifty.

A typical use case for querying the brands dataset is to join it with a query on the place data to answer questions such as:

• What is the count of all stores by brand in an area?
• What is the count of my top three competitor brands in the area?
• What is the count of brands of a specific category, such as "Fitness" or "Gas Station", in the area?

About the brands dataset

The brands dataset for the US is named places_insights___us.brands .

Brands dataset schema

The schema for the brands dataset defines three fields:

• id : The brand ID.
• name : The brand name, such as "Hertz" or "Chase".
• category : The brand type, such as "Gas Station", "Food and Drink", or "Lodging". For a list of possible values, see Category values .

Use brands dataset in a query

The places datasetschema defines the brand_ids field. If a place in the places dataset is associated with a brand, then the brand_ids field for the place contains the corresponding brand ID.

A typical query that references the brands datasetperforms a JOIN with the places datasetbased on the brand_ids field.

For example, to find the count of the number of McDonald's restaurants within 2000 meters of the Empire State Building in New York City:

 SELECT 
  
 WITH 
  
 AGGREGATION_THRESHOLD 
  
 COUNT 
 ( 
 * 
 ) 
 FROM 
  
  PROJECT_NAME 
 
 . 
 places_insights___us 
 . 
 places 
  
 places 
 , 
  
 UNNEST 
 ( 
 brand_ids 
 ) 
  
 AS 
  
 brand_id 
 LEFT 
  
 JOIN 
  
  PROJECT_NAME 
 
 . 
 places_insights___us 
 . 
 brands 
  
 ON 
  
 brand_id 
  
 = 
  
 brands 
 . 
 id 
 WHERE 
 ST_DWITHIN 
 ( 
 ST_GEOGPOINT 
 ( 
 - 
 73.9857 
 , 
  
 40.7484 
 ), 
  
 point 
 , 
  
 2000 
 ) 
 AND 
  
 brands 
 . 
 name 
  
 = 
  
 "McDonald's" 
 AND 
  
 business_status 
  
 = 
  
 "OPERATIONAL" 

The next query returns the count of the number of banks in New York City that belong to a brand, grouped by brand name:

 SELECT 
  
 WITH 
  
 AGGREGATION_THRESHOLD 
  
 brands 
 . 
 name 
 , 
  
 COUNT 
 ( 
 * 
 ) 
  
 AS 
  
 store_count 
 FROM 
  
  PROJECT_NAME 
 
 . 
 places_insights___us 
 . 
 places 
  
 places 
 , 
  
 UNNEST 
 ( 
 brand_ids 
 ) 
  
 AS 
  
 brand_id 
 LEFT 
  
 JOIN 
  
  PROJECT_NAME 
 
 . 
 places_insights___us 
 . 
 brands 
  
 ON 
  
 brand_id 
  
 = 
  
 brands 
 . 
 id 
 WHERE 
  
 brands 
 . 
 category 
  
 = 
  
 "ATMs, Banks and Credit Unions" 
 AND 
  
 "bank" 
  
 IN 
  
 UNNEST 
 ( 
 places 
 . 
 types 
 ) 
 AND 
  
 business_status 
  
 = 
  
 "OPERATIONAL" 
 GROUP 
  
 BY 
  
 brands 
 . 
 name 
 ORDER 
  
 BY 
  
 store_count 
  
 DESC 
 ; 

The following image shows the counts by brand:

Category values

The category field for a brand can contain the following values: