Tworzenie zapytania dotyczącego statystyk miejsc

Aby uzyskać dostęp do danych Statystyk miejsc, piszesz zapytania SQL w BigQuery, które zwracają zagregowane statystyki dotyczące miejsc. Wyniki są zwracane z zestawu danych dla kryteriów wyszukiwania określonych w zapytaniu.

Uwaga: zagregowane liczby są zwracane tylko wtedy, gdy liczba wynosi co najmniej 5. Jeśli na przykład kryteria wyszukiwania dają wynik 1, 2, 3 lub 4, agregacja wyników jest pomijana. Funkcje zliczania miejsc mogą zwracać dowolne liczby, w tym 0, ale wymuszają minimalny obszar wyszukiwania o wymiarach 40,0 m × 40,0 m (1600 m²).

Podstawowe informacje na temat zapytań

Obraz poniżej przedstawia podstawowy format zapytania:

Każda część zapytania została szczegółowo opisana poniżej.

Sukces: BigQuery udostępnia też dokumentację i przykłady wykonywania zapytań dotyczących danych geoprzestrzennych. Więcej informacji znajdziesz w artykule Wprowadzenie do analizy geoprzestrzennej.

Wymagania dotyczące zapytania

Instrukcja SELECT w zapytaniu musi zawierać WITH AGGREGATION_THRESHOLD i określać zbiór danych. Na przykład:

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

W tym przykładzie użyjesz FROM, aby określić places_insights___us.places zbiór danych dla Stanów Zjednoczonych.

Podaj nazwę projektu (opcjonalnie)

Opcjonalnie możesz podać w zapytaniu nazwę projektu. Jeśli nie podasz nazwy projektu, zapytanie domyślnie będzie dotyczyć aktywnego projektu.

Warto podać nazwę projektu, jeśli masz połączone zbiory danych o tej samej nazwie w różnych projektach lub jeśli wysyłasz zapytanie do tabeli spoza aktywnego projektu.

Na przykład: [project name].[dataset name].places.

Określanie funkcji agregującej

Poniższy przykład pokazuje obsługiwane funkcje agregacji BigQuery. To zapytanie agreguje oceny wszystkich miejsc znajdujących się w promieniu 1000 metrów od Empire State Building w Nowym Jorku, aby uzyskać statystyki ocen:

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"

Określanie ograniczenia lokalizacji

Jeśli nie określisz ograniczenia lokalizacji, agregacja danych zostanie zastosowana do całego zbioru danych. Zazwyczaj ograniczenie lokalizacji określa się, aby wyszukiwać w określonym obszarze, jak pokazano poniżej:

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)

W tym przykładzie zapytanie określa ograniczenie kierowania na obszar o promieniu 1000 metrów wokół Empire State Building w Nowym Jorku.

Uwaga: miejsce jest uwzględniane w wynikach tylko wtedy, gdy jego środek znajduje się w obszarze objętym ograniczeniem lokalizacji. Miejsce, np. park, którego środek znajduje się poza obszarem objętym ograniczeniami, nie jest uwzględniane w wynikach.

Obszar wyszukiwania możesz określić za pomocą wielokąta. W przypadku wielokąta punkty muszą tworzyć zamkniętą pętlę, w której pierwszy punkt jest taki sam jak ostatni:

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)

W następnym przykładzie obszar wyszukiwania jest definiowany za pomocą linii połączonych punktów. Linia jest podobna do trasy podróży obliczonej przez Routes API. Trasa może być przeznaczona dla pojazdu, roweru lub pieszego:

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)

W tym przykładzie promień wyszukiwania został ustawiony na 100 metrów od linii.

Filtrowanie według pól zbioru danych o miejscach

Zawęź wyszukiwanie na podstawie pól zdefiniowanych przez schemat zbioru danych. Filtruj wyniki na podstawie pól zbioru danych, takich jak miejsce regular_opening_hours, price_level i klient rating.

Odwołuj się do dowolnych pól w zbiorze danych zdefiniowanych przez schemat zbioru danych dla wybranego kraju. Schemat zbioru danych dla każdego kraju składa się z 2 części:

• Schemat podstawowy, który jest wspólny dla zbiorów danych ze wszystkich krajów.
• schemat specyficzny dla danego kraju, który określa komponenty schematu właściwe dla tego kraju;

Zapytanie może na przykład zawierać klauzulę WHERE, która określa kryteria filtrowania zapytania. W tym przykładzie zwracasz dane agregacji dotyczące miejsc typu tourist_attraction z wartością business_status równą OPERATIONAL, które mają wartość rating większą lub równą 4, 0, a wartość allows_dogs ustawioną na 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

Następne zapytanie zwraca wyniki dotyczące miejsc, w których znajduje się co najmniej 8 stacji ładowania EV:

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

Filtrowanie według głównego typu miejsca i typu miejsca

Każde miejsce w zestawie danych może mieć:

• Jeden główny typ powiązany z nią spośród typów zdefiniowanych przez typy miejsc. Na przykład typem głównym może być mexican_restaurant lub steak_house. Użyj w zapytaniu znaku primary_type, aby filtrować wyniki według głównego typu miejsca.

• Wiele wartości typu skojarzonych z nim z typów zdefiniowanych przez Typy miejsc. Na przykład restauracja może mieć te typy: seafood_restaurant, restaurant, food, point_of_interest, establishment. Użyj types w zapytaniu, aby filtrować wyniki na liście typów skojarzonych z miejscem.

Poniższe zapytanie zwraca wyniki dla wszystkich miejsc z typem podstawowym skin_care_clinic, które funkcjonują również jako 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

Filtruj według identyfikatora miejsca

Poniższy przykład oblicza średnią ocenę 5 miejsc. Miejsca są identyfikowane na podstawie 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;

Odfiltrowywanie określonych identyfikatorów miejsc

Możesz też wykluczyć z zapytania tablicę identyfikatorów miejsc.

Identyfikatory miejsc, których szukasz, możesz znaleźć za pomocą wyszukiwarki identyfikatorów miejsc lub programowo używając interfejsu API miejsc do wykonania żądania wyszukiwania tekstowego (nowe).

W przykładzie poniżej zapytanie znajduje liczbę kawiarni w Sydney w Australii, które nie występują w tablicy excluded_cafes. Takie zapytanie może być przydatne dla właściciela firmy, który chce wykluczyć swoje firmy z liczby.

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;

Filtrowanie według zdefiniowanych wartości danych

Wiele pól zbioru danych ma predefiniowane wartości. Przykład:

• Pole price_level obsługuje te predefiniowane wartości:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• Pole business_status obsługuje te predefiniowane wartości:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Uwaga: Zazwyczaj należy uwzględnić filtr business_status, aby mieć pewność, że zostanie wyświetlona liczba OPERATIONAL firm. W przeciwnym wypadku liczba ta obejmuje przedsiębiorstwa zamknięte.

W tym przykładzie zapytanie zwraca liczbę wszystkich kwiaciarni z atrybutem business_status o wartości OPERATIONAL w promieniu 1000 metrów od Empire State Building w Nowym Jorku:

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)

Filtrowanie według godzin otwarcia

W tym przykładzie zwracamy liczbę wszystkich miejsc w obszarze geograficznym, w których w piątki obowiązują happy hours:

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

Filtrowanie według regionu (komponenty adresu)

Nasz zbiór danych o miejscach zawiera też zestaw komponentów adresu, które są przydatne do filtrowania wyników na podstawie granic politycznych. Każdy komponent adresu jest identyfikowany przez nazwę kodu tekstowego (10002 w przypadku kodu pocztowego w Nowym Jorku) lub identyfikator miejsca (ChIJm5NfgIBZwokR6jLqucW0ipg) odpowiadający identyfikatorowi kodu pocztowego.

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

Filtrowanie według ładowania EV

Ten przykład podaje liczbę miejsc z co najmniej 8 ładowarkami do pojazdów elektrycznych:

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

Ten przykład zlicza liczbę miejsc, w których jest co najmniej 10 ładowarek Tesla obsługujących szybkie ładowanie:

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

Zwracanie grup wyników

Zapytania pokazane do tej pory zwracają w wyniku jeden wiersz zawierający liczbę agregacji dla zapytania. Możesz też użyć operatora GROUP BY, aby zwrócić w odpowiedzi wiele wierszy na podstawie kryteriów grupowania.

Uwaga: w Wielkiej Brytanii nie można wykonywać operacji GROUP BY na urządzeniach postal_code_names.

Na przykład to zapytanie zwraca wyniki pogrupowane według głównego typu każdego miejsca w obszarze wyszukiwania:

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

Na poniższym obrazie widać przykładowe dane wyjściowe tego zapytania:

W tym przykładzie zdefiniujesz tabelę lokalizacji. Następnie dla każdej lokalizacji oblicz liczbę pobliskich restauracji, czyli tych, które znajdują się w odległości do 1000 metrów:

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

Na poniższym obrazie widać przykładowe dane wyjściowe tego zapytania: