Menulis kueri Places Insights

Untuk mengakses data Places Insights, Anda menulis kueri SQL di BigQuery yang menampilkan insight gabungan tentang tempat. Hasil ditampilkan dari set data untuk kriteria penelusuran yang ditentukan dalam kueri.

Catatan: Jumlah agregasi hanya ditampilkan jika jumlahnya 5 atau lebih tinggi. Misalnya, jika kriteria penelusuran Anda menghasilkan jumlah 1, 2, 3, atau 4, maka penggabungan hasil akan dihilangkan dari hasil. Fungsi jumlah tempat dapat menampilkan jumlah apa pun, termasuk 0, tetapi menerapkan area penelusuran minimum 40,0 meter x 40,0 meter (1.600 m²).

Berhasil: BigQuery juga menyediakan dokumentasi dan contoh untuk menjalankan kueri pada data geospasial. Untuk mengetahui informasi selengkapnya, lihat Pengantar analisis geospasial.

Persyaratan kueri

Pernyataan SELECT dalam kueri harus menyertakan WITH AGGREGATION_THRESHOLD dan menentukan set data. Contoh:

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

Dalam contoh ini, Anda menggunakan FROM untuk menentukan set data places_insights___us___sample.places_sample untuk Amerika Serikat.

Menentukan pembatasan lokasi

Perhatikan bahwa kueri sebelumnya tidak menentukan batasan lokasi apa pun, yang berarti agregasi data diterapkan ke set data seluruhnya. Biasanya Anda menentukan batasan lokasi seperti yang ditunjukkan di bawah:

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)

Dalam contoh ini, kueri Anda menentukan batasan target yang berpusat pada Empire State Building di New York City, dengan radius 1.000 meter.

Catatan: Tempat hanya disertakan dalam hasil jika titik tengah tempat tersebut berada dalam batasan lokasi. Tempat, seperti taman, yang titik tengahnya berada di luar pembatasan lokasi tidak disertakan dalam hasil.

Anda dapat menggunakan poligon untuk menentukan area penelusuran. Saat menggunakan poligon, titik-titik poligon harus menentukan loop tertutup di mana titik pertama dalam poligon sama dengan titik terakhir:

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)

Pada contoh berikutnya, Anda menentukan area penelusuran menggunakan garis yang menghubungkan titik-titik. Jalur ini mirip dengan rute perjalanan yang dihitung oleh Routes API. Rute dapat berupa rute untuk kendaraan, sepeda, atau pejalan kaki:

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)

Dalam contoh ini, Anda menetapkan radius penelusuran ke 100 meter di sekitar garis.

Memfilter menurut kolom set data tempat

Persempit penelusuran Anda berdasarkan kolom yang ditentukan oleh skema set data. Memfilter hasil berdasarkan kolom set data seperti tempat regular_opening_hours, price_level, dan pelanggan rating.

Merujuk kolom apa pun dalam set data yang ditentukan oleh skema set data untuk negara yang Anda minati. Skema set data untuk setiap negara terdiri dari dua bagian:

• Skema inti yang umum untuk set data di semua negara.
• Skema khusus negara yang menentukan komponen skema khusus untuk negara tersebut.

Misalnya, kueri Anda dapat menyertakan klausa WHERE yang menentukan kriteria pemfilteran untuk kueri. Dalam contoh berikut, Anda menampilkan data agregasi untuk tempat berjenis restaurant dengan business_status OPERATIONAL, yang memiliki rating lebih besar dari atau sama dengan 4.0, dan dengan allows_dogs yang ditetapkan ke 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

Kueri berikutnya menampilkan hasil untuk tempat yang memiliki setidaknya delapan SPKLU:

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

Memfilter berdasarkan jenis utama tempat dan jenis tempat

Setiap tempat dalam set data dapat memiliki:

• Satu jenis utama yang terkait dengannya dari jenis yang ditentukan oleh Tabel A. Misalnya, jenis utama dapat berupa mexican_restaurant atau steak_house. Gunakan primary_type dalam kueri untuk memfilter hasil berdasarkan jenis utama suatu tempat.

• Beberapa nilai jenis yang terkait dengannya dari jenis yang ditentukan oleh Tabel A. Misalnya, restoran dapat memiliki jenis berikut: seafood_restaurant, restaurant, food, point_of_interest, establishment. Gunakan types dalam kueri untuk memfilter hasil pada daftar jenis yang terkait dengan tempat.

Kueri berikut menampilkan hasil untuk semua tempat dengan jenis utama bar tetapi juga berfungsi sebagai restaurant:

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

Memfilter nilai data standar

Banyak kolom set data memiliki nilai yang telah ditentukan sebelumnya. Contoh:

• Kolom price_level mendukung nilai standar berikut:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• Kolom business_status mendukung nilai standar berikut:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Catatan: Biasanya Anda menyertakan filter business_status untuk memastikan bahwa Anda mendapatkan jumlah bisnis OPERATIONAL. Jika tidak, jumlahnya mencakup bisnis yang tutup.

Dalam contoh ini, kueri menampilkan jumlah semua restoran dengan business_status OPERATIONAL dalam radius 1.000 meter dari Empire State Building di New York City:

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)

Memfilter menurut jam operasional

Dalam contoh ini, tampilkan jumlah semua tempat di area geografis dengan happy hour pada hari Jumat:

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

Menggunakan fungsi agregasi

Contoh di bawah menunjukkan fungsi agregasi BigQuery yang didukung. Kueri ini menggabungkan rating semua tempat yang berada dalam radius 1.000 meter dari Empire State Building di New York City untuk menghasilkan statistik rating:

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"

Menampilkan grup hasil

Kueri yang ditampilkan sejauh ini menampilkan satu baris dalam hasil yang berisi jumlah agregasi untuk kueri. Anda juga dapat menggunakan operator GROUP BY untuk menampilkan beberapa baris dalam respons berdasarkan kriteria pengelompokan.

Catatan: Operasi GROUP BY tidak diizinkan di postal_code di Inggris Raya atau postal_code_suffix di Amerika Serikat.

Misalnya, kueri berikut menampilkan hasil yang dikelompokkan menurut jenis utama setiap tempat di area penelusuran:

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

Gambar berikut menunjukkan contoh output untuk kueri ini:

Dalam contoh ini, Anda menentukan tabel lokasi. Untuk setiap lokasi, Anda kemudian menghitung jumlah restoran terdekat, yaitu yang berada dalam jarak 1.000 meter:

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

Gambar berikut menunjukkan contoh output untuk kueri ini: