編寫 Places Insights 查詢

如要存取 Places Insights 資料，請在 BigQuery 中編寫 SQL 查詢，傳回地點的匯總洞察資訊。系統會根據查詢中指定的搜尋條件，從資料集傳回結果。

注意： 只有在計數達到 5 以上時，系統才會傳回匯總計數。舉例來說，如果搜尋條件的結果數量為 1、2、3 或 4，結果中就不會顯示結果彙整。地點計數函式可以傳回任何計數 (包括 0)，但會強制執行 40.0 公尺 x 40.0 公尺 (1600 公尺²) 的最小搜尋範圍。

成功： BigQuery 也提供文件和範例，說明如何對地理空間資料執行查詢。詳情請參閱地理空間分析簡介。

查詢規定

查詢中的 SELECT 陳述式必須包含 WITH AGGREGATION_THRESHOLD，並指定資料集。例如：

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

在本範例中，您會使用 FROM 指定美國的 places_insights___us___sample.places_sample 資料集。

指定地點限制

請注意，先前的查詢未指定任何位置限制，這表示資料匯總會套用至整個資料集。通常您會指定地點限制，如下所示：

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)

在本例中，查詢會指定以紐約市帝國大廈為中心，半徑 1000 公尺的目標限制。

注意： 只有地點的中心點落在位置限制內，該地點才會納入結果。如果地點 (例如公園) 的中心點超出位置限制，就不會納入結果。

您可以使用多邊形指定搜尋區域。使用多邊形時，多邊形的點必須定義封閉迴圈，也就是多邊形的第一個點與最後一個點相同：

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)

在下一個範例中，您會使用一連串相連的點定義搜尋區域。這條線類似於 Routes API 計算出的路線。路線可能適用於車輛、自行車或行人：

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)

在本例中，您將搜尋半徑設為線條周圍 100 公尺。

依地點資料集欄位篩選

根據資料集結構定義的欄位，縮小搜尋範圍。根據資料集欄位 (例如地點 regular_opening_hours、price_level 和顧客 rating) 篩選結果。

參照感興趣國家/地區的資料集結構定義所定義的任何欄位。每個國家/地區的資料集結構定義都包含兩部分：

• 所有國家/地區資料集通用的核心結構定義。
• 國家/地區專屬結構定義，定義該國家/地區專屬的結構定義元件。

舉例來說，查詢可以包含 WHERE 子句，定義查詢的篩選條件。在下列範例中，您會傳回類型為 restaurant、business_status 為 OPERATIONAL、rating 大於或等於 4.0，且 allows_dogs 設為 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

下一個查詢會傳回至少有八個電動車充電站的地點結果：

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

依地點主要類型和地點類型篩選

資料集中的每個地點可以有：

• 與資料表 A 定義的類型相關聯的單一主要類型。舉例來說，主要類型可能是 mexican_restaurant 或 steak_house。在查詢中使用 primary_type，即可依地點的主要類型篩選結果。

• 多個類型值，這些值與 表 A 定義的類型相關聯。舉例來說，餐廳可能會有下列類型：seafood_restaurant、restaurant、food、point_of_interest、establishment。在查詢中使用 types，即可篩選與地點相關聯的類型清單結果。

下列查詢會傳回主要類型為 bar 的所有地點結果，但也會傳回 restaurant：

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

根據預先定義的資料值篩選

許多資料集欄位都有預先定義的值。例如

• price_level 欄位支援下列預先定義的值：

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• business_status 欄位支援下列預先定義的值：

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

注意： 通常您會加入 business_status 篩選器，確保取得 OPERATIONAL 商家數量。否則計數會包含已關閉的商家。

在本例中，查詢會傳回紐約市帝國大廈方圓 1000 公尺內，business_status 為 OPERATIONAL 的所有餐廳數量：

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)

依營業時間篩選

在這個範例中，傳回地理區域內所有提供週五歡樂時光的地點數量：

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);

使用匯總函式

以下範例顯示支援的 BigQuery 匯總函式。 這項查詢會匯總紐約市帝國大廈方圓 1000 公尺內所有地點的評分，產生評分統計資料：

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"

傳回結果群組

目前為止，顯示的查詢會在結果中傳回單一資料列，其中包含查詢的匯總計數。您也可以使用 GROUP BY 運算子，根據分組條件在回應中傳回多個資料列。

注意： 不允許在英國的 postal_code 或美國的 postal_code_suffix 上執行 GROUP BY 作業。

舉例來說，下列查詢會傳回搜尋區域中每個地點的結果，並依主要類型分組：

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

下圖顯示這項查詢的輸出內容範例：

在本範例中，您將定義位置資料表。接著，針對每個位置計算附近餐廳的數量，也就是 1000 公尺內的餐廳：

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

下圖顯示這項查詢的輸出內容範例：