Viết truy vấn Thông tin chi tiết về địa điểm

Để truy cập vào dữ liệu Places Insights, bạn viết các truy vấn SQL trong BigQuery để trả về thông tin chi tiết tổng hợp về các địa điểm. Kết quả được trả về từ tập dữ liệu cho tiêu chí tìm kiếm được chỉ định trong truy vấn.

Lưu ý: Số lượng tổng hợp chỉ được trả về nếu số lượng từ 5 trở lên. Ví dụ: nếu tiêu chí tìm kiếm của bạn cho ra kết quả là 1, 2, 3 hoặc 4 thì kết quả tổng hợp sẽ bị loại khỏi kết quả. Các hàm đếm địa điểm có thể trả về mọi số lượng, kể cả 0, nhưng phải có diện tích tìm kiếm tối thiểu là 40 m x 40 m (1.600 m²).

Thông tin cơ bản về truy vấn

Hình ảnh sau đây cho thấy định dạng cơ bản của một truy vấn:

Mỗi phần của truy vấn được mô tả chi tiết hơn ở bên dưới.

Thành công: BigQuery cũng cung cấp tài liệu và ví dụ về cách chạy truy vấn trên dữ liệu không gian địa lý. Để biết thêm thông tin, hãy xem phần Giới thiệu về phân tích không gian địa lý.

Yêu cầu về truy vấn

Câu lệnh SELECT trong truy vấn phải có WITH AGGREGATION_THRESHOLD và chỉ định tập dữ liệu. Ví dụ:

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

Trong ví dụ này, bạn sử dụng FROM để chỉ định tập dữ liệu places_insights___us.places cho Hoa Kỳ.

Chỉ định tên dự án (không bắt buộc)

Bạn có thể thêm tên dự án vào truy vấn (không bắt buộc). Nếu bạn không chỉ định tên dự án, truy vấn của bạn sẽ mặc định là dự án đang hoạt động.

Bạn nên thêm tên dự án nếu đã liên kết các tập dữ liệu có cùng tên trong các dự án khác nhau hoặc nếu đang truy vấn một bảng bên ngoài dự án đang hoạt động.

Ví dụ: [project name].[dataset name].places.

Chỉ định hàm tổng hợp

Ví dụ bên dưới cho thấy các hàm tổng hợp được BigQuery hỗ trợ. Truy vấn này tổng hợp điểm xếp hạng của tất cả những địa điểm nằm trong bán kính 1.000 mét tính từ Toà nhà Empire State ở Thành phố New York để tạo ra số liệu thống kê về điểm xếp hạng:

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"

Chỉ định hạn chế về vị trí

Nếu bạn không chỉ định quy định hạn chế về vị trí, thì quá trình tổng hợp dữ liệu sẽ được áp dụng cho toàn bộ tập dữ liệu. Thông thường, bạn chỉ định một hạn chế về vị trí để tìm kiếm một khu vực cụ thể, chẳng hạn như minh hoạ dưới đây:

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)

Trong ví dụ này, truy vấn của bạn chỉ định một hạn chế về mục tiêu tập trung vào Toà nhà Empire State ở Thành phố New York, với bán kính là 1.000 mét.

Lưu ý: Một địa điểm chỉ được đưa vào kết quả nếu điểm trung tâm của địa điểm đó nằm trong giới hạn vị trí. Một địa điểm, chẳng hạn như công viên, có điểm trung tâm nằm bên ngoài giới hạn vị trí sẽ không xuất hiện trong kết quả.

Bạn có thể dùng một đa giác để chỉ định khu vực tìm kiếm. Khi sử dụng một đa giác, các điểm của đa giác phải xác định một vòng khép kín, trong đó điểm đầu tiên trong đa giác giống với điểm cuối cùng:

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)

Trong ví dụ tiếp theo, bạn sẽ xác định vùng tìm kiếm bằng một đường thẳng gồm các điểm được kết nối. Đường này tương tự như tuyến đường di chuyển do Routes API tính toán. Tuyến đường có thể dành cho xe, xe đạp hoặc người đi bộ:

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)

Trong ví dụ này, bạn đặt bán kính tìm kiếm là 100 mét xung quanh đường kẻ.

Lọc theo các trường tập dữ liệu địa điểm

Tinh chỉnh nội dung tìm kiếm dựa trên các trường do sơ đồ tập dữ liệu xác định. Lọc kết quả dựa trên các trường dữ liệu như địa điểm regular_opening_hours, price_level và khách hàng rating.

Tham chiếu mọi trường trong tập dữ liệu do giản đồ tập dữ liệu xác định cho quốc gia mà bạn quan tâm. Lược đồ tập dữ liệu cho mỗi quốc gia bao gồm 2 phần:

• Sơ đồ cốt lõi thường thấy trong các tập dữ liệu của tất cả các quốc gia.
• Một giản đồ theo quốc gia cụ thể xác định các thành phần giản đồ dành riêng cho quốc gia đó.

Ví dụ: truy vấn của bạn có thể bao gồm một mệnh đề WHERE xác định tiêu chí lọc cho truy vấn. Trong ví dụ sau, bạn sẽ trả về dữ liệu tổng hợp cho những địa điểm thuộc loại tourist_attraction có business_status là OPERATIONAL, có rating lớn hơn hoặc bằng 4.0 và có allows_dogs được đặt thành 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

Truy vấn tiếp theo sẽ trả về kết quả cho những địa điểm có ít nhất 8 trạm sạc xe điện:

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

Lọc theo loại chính của địa điểm và loại địa điểm

Mỗi vị trí trong tập dữ liệu có thể có:

• Một loại chính duy nhất được liên kết với loại này trong số các loại do Loại địa điểm xác định. Ví dụ: loại chính có thể là mexican_restaurant hoặc steak_house. Sử dụng primary_type trong một cụm từ tìm kiếm để lọc kết quả theo loại chính của một địa điểm.

• Nhiều giá trị kiểu được liên kết với nó từ các kiểu được xác định bởi Kiểu địa điểm. Ví dụ: một nhà hàng có thể có các loại sau: seafood_restaurant, restaurant, food, point_of_interest, establishment. Sử dụng types trong truy vấn để lọc kết quả trong danh sách các loại liên quan đến địa điểm.

Truy vấn sau đây trả về kết quả cho tất cả những địa điểm có loại chính là skin_care_clinic và cũng hoạt động như một 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

Lọc theo mã địa điểm

Ví dụ dưới đây tính toán xếp hạng trung bình cho 5 địa điểm. Các địa điểm được xác định bằng 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;

Lọc bỏ một số mã địa điểm

Bạn cũng có thể loại trừ một mảng Place IDs khỏi một truy vấn.

Bạn có thể tìm thấy mã địa điểm mà mình đang tìm bằng cách sử dụng công cụ tìm mã địa điểm hoặc sử dụng Places API theo phương thức lập trình để thực hiện yêu cầu Tìm kiếm văn bản (Mới).

Trong ví dụ bên dưới, truy vấn tìm số lượng quán cà phê trong mã bưu chính 2000 của Sydney, Úc, không xuất hiện trong mảng excluded_cafes. Truy vấn như vậy có thể hữu ích cho chủ doanh nghiệp muốn loại trừ doanh nghiệp của mình khỏi số liệu.

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;

Lọc theo các giá trị dữ liệu được xác định trước

Nhiều trường dữ liệu có giá trị được xác định trước. Ví dụ

• Trường price_level hỗ trợ các giá trị được xác định trước sau:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• Trường business_status hỗ trợ các giá trị được xác định trước sau:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Lưu ý: Thông thường, bạn nên bao gồm bộ lọc business_status để đảm bảo có được số lượng doanh nghiệp OPERATIONAL. Nếu không, số lượt truy cập sẽ bao gồm cả những doanh nghiệp đã đóng cửa.

Trong ví dụ này, truy vấn trả về số lượng tất cả các cửa hàng hoa có business_status là OPERATIONAL trong bán kính 1.000 mét tính từ Toà nhà Empire State ở Thành phố New York:

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)

Lọc theo giờ hoạt động

Trong ví dụ này, hãy trả về số lượng tất cả các địa điểm trong một khu vực địa lý có giờ khuyến mãi vào thứ Sáu:

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

Lọc theo Khu vực (thành phần địa chỉ)

Bộ dữ liệu địa điểm của chúng tôi cũng chứa một tập hợp các thành phần địa chỉ hữu ích cho việc lọc kết quả dựa trên ranh giới chính trị. Mỗi thành phần địa chỉ được xác định bằng tên mã văn bản (10002 đối với mã bưu chính ở NYC) hoặc ID địa điểm (ChIJm5NfgIBZwokR6jLqucW0ipg) đối với ID mã bưu chính tương đương.

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

Lọc theo sạc EV

Ví dụ này cung cấp số lượng địa điểm có ít nhất 8 bộ sạc xe điện:

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

Ví dụ này thống kê số địa điểm có ít nhất 10 bộ sạc Tesla hỗ trợ sạc nhanh:

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

Trả về nhóm kết quả

Các truy vấn được hiển thị cho đến nay trả về một hàng duy nhất trong kết quả có chứa số lượng tổng hợp cho truy vấn. Bạn cũng có thể sử dụng toán tử GROUP BY để trả về nhiều hàng trong phản hồi dựa trên tiêu chí nhóm.

Lưu ý: Không được phép thực hiện thao tác GROUP BY trên postal_code_names ở GB.

Ví dụ, truy vấn sau trả về kết quả được nhóm theo loại chính của từng địa điểm trong khu vực tìm kiếm:

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

Hình ảnh sau đây hiển thị ví dụ đầu ra cho truy vấn này:

Trong ví dụ này, bạn định nghĩa một bảng vị trí. Đối với mỗi địa điểm, bạn tính toán số nhà hàng gần đó, nghĩa là những nhà hàng trong phạm vi 1000 mét:

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`
JOIN
   my_locations l
ON
  ST_DWITHIN(l.location, p.point, 1000)
WHERE
  primary_type = "restaurant"
  AND business_status = "OPERATIONAL"
GROUP BY
  l.name

Hình ảnh sau đây hiển thị ví dụ đầu ra cho truy vấn này: