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 sebesar 40,0 meter x 40,0 meter (1.600 m²).

Dasar-dasar kueri

Gambar berikut menunjukkan format dasar kueri:

Setiap bagian kueri dijelaskan secara lebih mendetail di bawah.

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

Dalam contoh ini, Anda menggunakan FROM untuk menentukan set data places_insights___us.places untuk Amerika Serikat.

Tentukan nama project (opsional)

Anda dapat menyertakan nama project dalam kueri. Jika Anda tidak menentukan nama project, kueri Anda akan ditetapkan secara default ke project aktif.

Anda mungkin ingin menyertakan nama project jika telah menautkan set data dengan nama yang sama di project yang berbeda, atau jika Anda membuat kueri tabel di luar project aktif.

Misalnya, [project name].[dataset name].places.

Menentukan 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
  `PROJECT_NAME.places_insights___us.places`
WHERE
  ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 1000)
  AND business_status = "OPERATIONAL"

Menentukan pembatasan lokasi

Jika Anda tidak menentukan batasan lokasi, agregasi data diterapkan ke seluruh set data. Biasanya Anda menentukan batasan lokasi untuk menelusuri area tertentu, seperti yang ditunjukkan di bawah:

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)

Dalam contoh ini, kueri Anda menentukan pembatasan 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. Suatu tempat, seperti taman, yang titik pusatnya berada di luar batasan 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 `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)

Pada contoh berikutnya, Anda akan menentukan area penelusuran menggunakan garis titik yang terhubung. 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 `PROJECT_NAME.places_insights___us.places`
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.

Referensikan bidang mana pun dalam kumpulan data yang ditentukan oleh skema kumpulan data untuk negara yang Anda minati. Skema set data untuk setiap negara terdiri dari dua bagian:

• Skema inti yang umum untuk set data 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 tourist_attraction 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 `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

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

SELECT WITH AGGREGATION_THRESHOLD
  COUNT(*) AS count
FROM
  `PROJECT_NAME.places_insights___us.places`
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 Jenis tempat. 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 Jenis tempat. 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 skin_care_clinic yang juga berfungsi sebagai 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

Memfilter menurut ID tempat

Contoh di bawah ini menghitung rating rata-rata untuk 5 tempat. Tempat diidentifikasi berdasarkan place_id-nya.

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;

Mengecualikan ID tempat tertentu

Anda juga dapat mengecualikan array ID Tempat dari kueri.

Anda dapat menemukan ID Tempat yang Anda cari dengan menggunakan Pencari ID Tempat, atau secara terprogram menggunakan Places API untuk melakukan permintaan Text Search (Baru).

Dalam contoh di bawah, kueri menemukan jumlah kafe di kode pos 2000 di Sydney, Australia, yang tidak muncul dalam array excluded_cafes. Kueri tersebut mungkin berguna bagi pemilik bisnis yang ingin mengecualikan bisnisnya sendiri dari jumlah.

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;

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 toko bunga 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 `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)

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

Filter menurut Wilayah (komponen alamat)

Kumpulan data tempat kami juga berisi serangkaian komponen alamat yang berguna untuk memfilter hasil berdasarkan batas politik. Setiap komponen alamat diidentifikasi berdasarkan nama kode teksnya (10002 untuk kode pos di NYC) atau ID tempat (ChIJm5NfgIBZwokR6jLqucW0ipg) untuk ID kode pos yang setara.

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

Memfilter menurut pengisian daya EV

Contoh ini memberikan jumlah tempat dengan minimal 8 pengisi daya EV:

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

Contoh ini menghitung jumlah tempat yang memiliki minimal 10 pengisi daya Tesla yang mendukung pengisian daya cepat:

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

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_names di GB.

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
  `PROJECT_NAME.places_insights___us.places`
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 di sekitar, 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
  `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

Gambar berikut menunjukkan contoh output untuk kueri ini: