進階：直接查詢資料集

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

瞭解匯總門檻

直接對原始資料表執行的標準 SQL 查詢會強制執行嚴格的匯總門檻。只有在計算出的匯總計數為 5 以上時，系統才會產生輸出列。

如果查詢篩選器產生的計數為 0、1、2、3 或 4，系統會從回應中省略整個結果列。如果應用程式邏輯需要精確的低計數 (包括 0)，或需要擷取個別地點 ID，則必須改用地點計數函式查詢資料集。

查詢基本資訊

下圖顯示查詢的基本格式：

以下將詳細說明查詢的各個部分。

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

查詢規定

直接對資料集執行的 SQL 查詢必須指定資料集，並在 SELECT 子句中加入 WITH AGGREGATION_THRESHOLD。否則查詢會失敗。

這個範例指定 places_insights___us.places，查詢美國的資料集。

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`

指定專案名稱 (選用)

您可以在查詢中加入專案名稱，如未指定專案名稱，查詢會預設為使用中的專案。

如果您在不同專案中連結了同名的資料集，或要查詢使用中專案以外的資料表，建議您加入專案名稱。

例如 [project name].[dataset name].places。

指定匯總函式

以下範例顯示支援的 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
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

使用地理多邊形和線條篩選結果

如未指定位置限制，資料匯總會套用至「整個」資料集。您通常會指定位置限制，以便搜尋特定區域。

1. 使用半徑搜尋緩衝區 (ST_DWITHIN) 篩選

以下範例查詢指定了目標限制，以紐約市帝國大廈為中心，半徑 1000 公尺。

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

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

2. 使用多邊形邊界篩選 (ST_CONTAINS)

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

SELECT WITH AGGREGATION_THRESHOLD
COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`
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)

3. 沿路線行程緩衝區篩選 (LINESTRING)

在下一個範例中，您將使用一條連接點線定義搜尋區域，並將搜尋半徑設為線條周圍 100 公尺。這條線類似於 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 `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(route, point, 100)

依地點資料集欄位篩選

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

請參考資料集架構為您感興趣的國家/地區定義的資料集欄位。每個國家/地區的資料集結構定義都包含兩個部分：

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

例如，查詢可以包含 WHERE 子句，定義查詢的篩選條件。

提示： 請加入 business_status 篩選條件，確保取得 OPERATIONAL 商家數量。否則，計數會包含已停業的商家。

在以下範例中，您會傳回 tourist_attraction 類型地點的匯總資料，其中 business_status 為 OPERATIONAL、rating 大於或等於 4.0，且 allows_dogs 設為 true：

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

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ev_charge_options.connector_count > 8;

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

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

• 單一主要類型：與地點相關聯，從地點類型定義的類型中選取。舉例來說，主要類型可能是 mexican_restaurant 或 steak_house。在查詢中使用 primary_type，即可依地點的主要類型篩選結果。
• 與其相關聯的多個類型值，這些值來自地點類型定義的類型。舉例來說，餐廳可能會有下列類型：seafood_restaurant、restaurant、food、point_of_interest、establishment。在查詢中使用 types，即可篩選與地點相關聯的類型清單結果。

下列查詢會傳回主要類型為 skin_care_clinic，且同時做為 spa 的所有地點結果：

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  'spa' IN UNNEST(types)
  AND 'skin_care_clinic' = primary_type

依地點 ID 篩選

以下範例會計算 5 個地點的平均評分。地點是以 place_id 識別。

DECLARE place_ids ARRAY<STRING>;
SET place_ids = ['ChIJPQOh8YVZwokRE2WsbZI4tOk', 'ChIJibtT3ohZwokR7tX0gp0nG8U',
                 'ChIJdfD8moVZwokRO6vxjXAtoWs', 'ChIJsdNONuFbwokRLM-yuifjb8k',
                 'ChIJp0gKoClawokR0txqrcaEkFc'];
SELECT WITH AGGREGATION_THRESHOLD
 AVG(rating) as avg_rating,
FROM
  `PROJECT_NAME.places_insights___us.places`,
  UNNEST(place_ids) place_id
WHERE
  id = place_id;

從查詢結果中排除特定地點 ID

您也可以從查詢中排除地點 ID 陣列。

您可以使用地點 ID 搜尋工具，或使用 Places API 以程式輔助方式執行 Text Search (新版) 要求，找出所需的地點 ID。

在以下範例中，查詢會找出澳洲雪梨郵遞區號 2000 中，未出現在 excluded_cafes 陣列中的咖啡館數量。如果業主想在計數時排除自己的商家，這類查詢可能很有用。

WITH excluded_cafes AS (
  -- List the specific place IDs to exclude from the final count
  SELECT * FROM UNNEST([
    'ChIJLTcYGz-uEmsRmazk9oMnP5w', 'ChIJeWDDDNOvEmsRF8SMPUwPbhw',
    'ChIJKdaKHbmvEmsRSdxq_1O05bU'
  ]) AS place_id
)

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `places_insights___au.places` AS places
-- Perform a LEFT JOIN to identify which places are in the exclusion list
LEFT JOIN
  excluded_cafes ON places.id = excluded_cafes.place_id
WHERE
  -- Filter for specific place type and postal code
  places.primary_type = 'cafe'
  AND '2000' IN UNNEST(places.postal_code_names)
  -- Keep only the rows where the join failed (meaning the ID was NOT in the list)
  AND excluded_cafes.place_id IS NULL;

篩選預先定義的資料值

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

• 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
  • FUTURE_OPENING

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

在這個範例中，查詢會傳回紐約市帝國大廈半徑 1000 公尺範圍內，所有 business_status 為 OPERATIONAL 的花店數量：

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

依營業時間篩選

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

SELECT WITH AGGREGATION_THRESHOLD COUNT(*) AS count
FROM `PROJECT_NAME.places_insights___us.places`,
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);

依區域 (地址元件) 篩選

我們的地點資料集也包含一組地址元件，可用於根據政治界線篩選結果。每個地址元件都會以文字代碼名稱 (紐約市郵遞區號為 10002) 或地點 ID (ChIJm5NfgIBZwokR6jLqucW0ipg) 識別，代表對應的郵遞區號 ID。

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  '10002' IN UNNEST(postal_code_names)
  -- 'ChIJm5NfgIBZwokR6jLqucW0ipg' IN UNNEST(postal_code_ids) -- same filter as above using postal code ID

依電動車充電設定篩選

以下範例會計算至少有 8 個電動車充電樁的地點數量：

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ev_charge_options.connector_count > 8;

這個範例會計算至少有 10 個 Tesla 充電器支援快速充電的地點數量：

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`, UNNEST(ev_charge_options.connector_aggregation) as connectors
WHERE
  connectors.type ='EV_CONNECTOR_TYPE_TESLA'
  AND connectors.max_charge_rate_kw >= 50
  AND connectors.count >= 10

依資料快照日期篩選

地點資料集包含 2024 年 1 月起每個月的地點數量資料。如要查詢特定月份的地點資料快照，請使用 snapshot_date 欄位篩選。

注意： snapshot_date 欄位的格式為 YYYY-MM-01 (例如「2024-01-01」)。雖然日期字串中必須包含日期值 (01)，但每個快照代表的是整個月份的匯總資料，而非個別日期。

舉例來說，下列查詢會傳回 2024 年 1 月搜尋區域中，各個地點的主要類型分組結果：

SELECT WITH AGGREGATION_THRESHOLD
  primary_type,
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places_historical`
WHERE
  snapshot_date = '2024-01-01'
  AND ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
GROUP BY
  primary_type;

傳回分組結果列

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

注意： 在英國，不得對postal_code_names執行GROUP BY作業。

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

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

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

在本例中，您會定義位置資料表。接著，您要計算每個地點附近的餐廳數量，也就是 1, 000 公尺內的餐廳：

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
  `PROJECT_NAME.places_insights___us.places` AS 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

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