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ả. 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ành công: BigQuery cũng cung cấp tài liệu và ví dụ về cách chạy các 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 bao gồm WITH AGGREGATION_THRESHOLD và chỉ định tập dữ liệu. Ví dụ:

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

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

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

Xin lưu ý rằng truy vấn trước đó không chỉ định bất kỳ quy định hạn chế nào về vị trí, tức là việc tổng hợp dữ liệu đượ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í như minh hoạ dưới đây:

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)

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ó tâm nằm ngoài giới hạn vị trí sẽ không được đưa vào 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 `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)

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 được tính bằng Routes API. 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 `places_insights___us___sample.places_sample`
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.

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 trong tập dữ liệu, chẳng hạn 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 restaurant 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 `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

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
  `places_insights___us___sample.places_sample`
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 Bảng A 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ị loại được liên kết với giá trị đó từ các loại do Bảng A xác định. 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 một cụm từ tìm kiếm để lọc kết quả trong danh sách các loại được liên kết với đị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à bar nhưng cũng hoạt động như một restaurant:

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

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

Nhiều trường tập 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 đây:

  • 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 đây:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Lưu ý: Thông thường, bạn sẽ thêm bộ lọc business_status để đảm bảo rằng bạn nhận đượ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ả nhà hàng 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 `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)

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ó ưu đãi giờ vàng vào thứ Sáu:

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

Sử dụng 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ả các đị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
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

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

Các truy vấn đã hiển thị cho đến nay trả về một hàng duy nhất trong kết quả 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 ý: Bạn không được thực hiện thao tác GROUP BY trên các trường thành phần địa chỉ (chẳng hạn như administrative_area_level, locality và postal_code) trong tập dữ liệu Thông tin chi tiết về địa điểm.

Ví dụ: truy vấn sau đây 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
  `places_insights___us___sample.places_sample`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.99992071622756, 40.71818785986936), point, 1000)
GROUP BY primary_type

Hình ảnh sau đây cho thấy một ví dụ về kết quả của truy vấn này:

Trong ví dụ này, bạn sẽ xác định một bảng vị trí. Sau đó, đối với mỗi vị trí, bạn sẽ tính số lượng nhà hàng ở gần, tức là những nhà hàng trong vòng 1.000 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
  `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

Hình ảnh sau đây cho thấy một ví dụ về kết quả của truy vấn này: