Yerler Analizleri sorgusu yazma

Places Insights verilerine erişmek için BigQuery'de SQL sorguları yazarsınız. Bu sorgular, yerlerle ilgili toplu analizler döndürür. Sonuçlar, sorguda belirtilen arama ölçütleri için veri kümesinden döndürülür.

Not: Toplama sayıları yalnızca sayı 5 veya daha yüksekse döndürülür. Örneğin, arama ölçütleriniz 1, 2, 3 veya 4 sayısıyla sonuçlanırsa sonuç toplama, sonuçtan çıkarılır. Yer sayısı işlevleri 0 dahil olmak üzere herhangi bir sayıyı döndürebilir ancak minimum arama alanı olarak 40, 0 metreye 40, 0 metre (1.600 m²) zorunlu tutulur.

Sorgularla ilgili temel bilgiler

Aşağıdaki resimde bir sorgunun temel biçimi gösterilmektedir:

Sorgunun her bir bölümü aşağıda daha ayrıntılı olarak açıklanmıştır.

Başarı: BigQuery, coğrafi veriler üzerinde sorgu çalıştırma ile ilgili dokümanlar ve örnekler de sunar. Daha fazla bilgi için Coğrafi analizlere giriş başlıklı makaleyi inceleyin.

Sorgu koşulları

Sorgudaki SELECT ifadesi WITH AGGREGATION_THRESHOLD içermeli ve veri kümesini belirtmelidir. Örneğin:

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

Bu örnekte, ABD için places_insights___us.places veri kümesini belirtmek üzere FROM kodunu kullanıyorsunuz.

Proje adı belirtin (isteğe bağlı)

İsteğe bağlı olarak sorguya proje adınızı ekleyebilirsiniz. Proje adı belirtmezseniz sorgunuz varsayılan olarak etkin projeye yönlendirilir.

Farklı projelerde aynı ada sahip veri kümelerini bağladıysanız veya etkin proje dışındaki bir tabloya sorgu gönderiyorsanız proje adınızı eklemek isteyebilirsiniz.

Örneğin, [project name].[dataset name].places.

Toplama işlevi belirtme

Aşağıdaki örnekte, desteklenen BigQuery toplama işlevleri gösterilmektedir. Bu sorgu, New York'taki Empire State Binası'nın 1.000 metre yarıçapındaki tüm yerlerin puanlarını toplayarak puan istatistikleri oluşturur:

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"

Konum kısıtlaması belirtme

Konum kısıtlaması belirtmezseniz veri toplama işlemi tüm veri kümesine uygulanır. Genellikle aşağıda gösterildiği gibi belirli bir alanı aramak için bir konum kısıtlaması belirtirsiniz:

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)

Bu örnekte sorgunuz, New York'taki Empire State Binası'nı merkez alan ve 1.000 metre yarıçaplı bir hedef kısıtlama belirtiyor.

Not: Bir yerin sonuçlara dahil edilebilmesi için yerin merkez noktasının konum kısıtlaması içinde olması gerekir. Merkez noktası konum kısıtlamasının dışında olan bir yer (ör. park) sonuçlara dahil edilmez.

Arama alanını belirtmek için bir çokgen kullanabilirsiniz. Çokgen kullanırken çokgenin noktaları, çokgendeki ilk noktanın son noktayla aynı olduğu kapalı bir döngü tanımlamalıdır:

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)

Bir sonraki örnekte, arama alanını bağlı noktalarla oluşturulan bir çizgi kullanarak tanımlıyorsunuz. Çizgi, Routes API tarafından hesaplanan bir seyahat rotasına benzer. Rota bir araç, bir bisiklet veya bir yayaya ait olabilir:

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)

Bu örnekte, arama yarıçapını hat etrafında 100 metre olarak ayarlıyorsunuz.

Yer veri kümesi alanlarına göre filtreleme

Veri kümesi şeması tarafından tanımlanan alanlara göre aramanızı daraltın. Sonuçları yer regular_opening_hours, price_level ve müşteri rating gibi veri kümesi alanlarına göre filtreleyin.

İlgilendiğiniz ülke için veri kümesi şemasıyla tanımlanan veri kümesindeki tüm alanlara referans verin. Her ülkenin veri kümesi şeması iki bölümden oluşur:

• Tüm ülkelerin veri kümelerinde ortak olan çekirdek şema.
• Ülkeye özgü şema bileşenlerini tanımlayan ülkeye özgü şema.

Örneğin, sorgunuzda sorgu için filtreleme ölçütlerini tanımlayan bir WHERE koşulu olabilir. Aşağıdaki örnekte, business_status değeri OPERATIONAL olan, rating değeri 4, 0'dan büyük veya ona eşit olan ve allows_dogs değeri true olarak ayarlanmış tourist_attraction türündeki yerler için toplama verileri döndürüyorsunuz:

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

Bir sonraki sorgu, en az sekiz EV şarj istasyonuna sahip yerlere ilişkin sonuçları döndürür:

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

Yer birincil türüne ve yer türüne göre filtreleme

Veri kümesindeki her yer şunlara sahip olabilir:

• Yer türleri tarafından tanımlanan türler arasından bununla ilişkilendirilmiş tek bir birincil tür. Örneğin, birincil tür mexican_restaurant veya steak_house olabilir. Bir yerin birincil türüne göre sonuçları filtrelemek için sorguda primary_type kullanın.

• Yer türleri tarafından tanımlanan türlerden birden fazla tür değeri ile ilişkilendirilmiş olmalıdır. Örneğin, bir restoranın şu türleri olabilir: seafood_restaurant, restaurant, food, point_of_interest, establishment. Bir sorguda types kullanarak yerle ilişkili türler listesindeki sonuçları filtreleyin.

Aşağıdaki sorgu, birincil türü skin_care_clinic olan ve aynı zamanda spa olarak işlev gören tüm yerlerle ilgili sonuçlar döndürür:

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

Yer kimliğine göre filtreleme

Aşağıdaki örnekte 5 yerin ortalama puanı hesaplanmaktadır. Yerlerin place_id ile tanımlandığını belirtin.

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;

Belirli yer kimliklerini filtreleme

Ayrıca, bir sorgudan bir dizi yer kimliğini hariç tutabilirsiniz.

Aradığınız yer kimliklerini Yer Kimliği Bulucu'yu kullanarak veya Places API'yi kullanarak programatik olarak Metin Arama (Yeni) isteği göndererek bulabilirsiniz.

Aşağıdaki örnekte, sorgu Avustralya'nın Sidney şehrindeki 2000 posta kodunda bulunan ve excluded_cafes dizisinde görünmeyen kafelerin sayısını bulur. Bu tür bir sorgu, kendi işletmelerini bir sayımdan hariç tutmak isteyen bir işletme sahibi için yararlı olabilir.

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;

Önceden tanımlanmış veri değerlerine göre filtreleme

Birçok veri kümesi alanında önceden tanımlanmış değerler bulunur. Örneğin:

• price_level alanı aşağıdaki önceden tanımlanmış değerleri destekler:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• business_status alanı aşağıdaki önceden tanımlanmış değerleri destekler:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Not: Genellikle OPERATIONAL işletmelerin sayısını aldığınızdan emin olmak için business_status filtresini eklersiniz. Aksi takdirde, sayımlara kapalı işletmeler de dahil edilir.

Bu örnekte, sorgu New York City'deki Empire State Binası'nın 1.000 metre yarıçapındaki OPERATIONAL business_status olan tüm çiçekçilerin sayısını döndürüyor:

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)

Çalışma saatlerine göre filtreleme

Bu örnekte, cuma günleri happy hour'ın olduğu bir coğrafi bölgedeki tüm yerlerin sayısını döndürün:

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

Bölgeye göre filtreleme (adres bileşenleri)

Yerler veri setimizde, sonuçları siyasi sınırlara göre filtrelemek için yararlı olan bir dizi adres bileşeni de bulunur. Her adres bileşeni, metin kodu adıyla (NYC'deki posta kodu için 10002) veya eşdeğer posta kodu kimliği için yer kimliğiyle (ChIJm5NfgIBZwokR6jLqucW0ipg) tanımlanır.

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

EV şarjına göre filtreleme

Bu örnekte, en az 8 elektrikli araç şarj cihazı olan yerlerin sayısı verilmektedir:

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

Bu örnekte, hızlı şarjı destekleyen en az 10 Tesla şarj cihazının bulunduğu yerlerin sayısı hesaplanmaktadır:

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

Sonuç gruplarını döndürme

Şu ana kadar gösterilen sorgular, sonuçta sorgunun toplama sayısını içeren tek bir satır döndürür. Gruplandırma ölçütlerine göre yanıtta birden fazla satır döndürmek için GROUP BY operatörünü de kullanabilirsiniz.

Not: GROUP BY işlemi, GB'deki postal_code_names üzerinde yapılamaz.

Örneğin, aşağıdaki sorgu, arama alanındaki her yerin birincil türüne göre gruplandırılmış sonuçlar döndürür:

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

Aşağıdaki resimde bu sorguya ilişkin örnek bir çıkış gösterilmektedir:

Bu örnekte, konumların bulunduğu bir tablo tanımlıyorsunuz. Ardından, her konum için yakındaki restoranların sayısını (1.000 metre içindeki restoranlar) hesaplarsınız:

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

Aşağıdaki resimde bu sorguya ilişkin örnek bir çıkış gösterilmektedir: