Places Insights-Abfrage schreiben

Um auf Places Insights-Daten zuzugreifen, schreiben Sie SQL-Abfragen in BigQuery, die aggregierte Informationen zu Orten zurückgeben. Die Ergebnisse werden aus dem Dataset für die in der Abfrage angegebenen Suchkriterien zurückgegeben.

Hinweis :Aggregationszahlen werden nur zurückgegeben, wenn die Anzahl mindestens 5 beträgt. Wenn Ihre Suchkriterien beispielsweise zu einer Anzahl von 1, 2, 3 oder 4 führen, wird die Ergebnisaggregation aus dem Ergebnis entfernt. ^{Funktionen für die Anzahl der Orte}

Erfolg: In BigQuery finden Sie auch Dokumentation und Beispiele für das Ausführen von Abfragen für raumbezogene Daten. Weitere Informationen finden Sie unter Einführung in raumbezogene Analysen.

Anforderungen an Abfragen

Die SELECT-Anweisung in der Abfrage muss WITH AGGREGATION_THRESHOLD enthalten und das Dataset angeben. Beispiel:

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

In diesem Beispiel verwenden Sie FROM, um das Dataset places_insights___us___sample.places_sample für die USA anzugeben.

Standortbeschränkung festlegen

Beachten Sie, dass in der vorherigen Abfrage keine Standortbeschränkung angegeben ist. Die Datenaggregation wird also auf das gesamte Dataset angewendet. Normalerweise geben Sie eine Standortbeschränkung wie unten gezeigt an:

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)

In diesem Beispiel wird in der Anfrage eine Zielbeschränkung angegeben, die auf das Empire State Building in New York City zentriert ist und einen Radius von 1.000 Metern hat.

Hinweis :Ein Ort wird nur in die Ergebnisse aufgenommen, wenn der Mittelpunkt des Orts innerhalb der Standortbeschränkung liegt. Ein Ort wie ein Park, dessen Mittelpunkt außerhalb der Standortbeschränkung liegt, wird nicht in die Ergebnisse aufgenommen.

Sie können ein Polygon verwenden, um den Suchbereich festzulegen. Wenn Sie ein Polygon verwenden, müssen die Punkte des Polygons eine geschlossene Schleife bilden, wobei der erste Punkt im Polygon mit dem letzten Punkt identisch sein muss:

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)

Im nächsten Beispiel definieren Sie den Suchbereich mit einer Linie aus verbundenen Punkten. Die Linie ähnelt einer von der Routes API berechneten Route. Die Route kann für ein Fahrzeug, ein Fahrrad oder einen Fußgänger sein:

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)

In diesem Beispiel legen Sie den Suchradius auf 100 Meter um die Linie fest.

Nach Feldern im Orts-Dataset filtern

Sie können Ihre Suche anhand der Felder verfeinern, die im Schema des Datasets definiert sind. Sie können die Ergebnisse nach Dataset-Feldern wie Ort regular_opening_hours, price_level und Kunde rating filtern.

Verweisen Sie auf alle Felder im Dataset, die durch das Datasetschema für Ihr Land definiert sind. Das Dataset-Schema für jedes Land besteht aus zwei Teilen:

• Das Kernschema, das für die Datasets aller Länder gilt.
• Ein länderspezifisches Schema, das Schemakomponenten definiert, die für das jeweilige Land spezifisch sind.

Ihre Abfrage kann beispielsweise eine WHERE-Klausel enthalten, mit der Filterkriterien für die Abfrage definiert werden. Im folgenden Beispiel werden Aggregationsdaten für Orte vom Typ restaurant mit einem business_status von OPERATIONAL zurückgegeben, die einen rating von mindestens 4,0 haben und bei denen allows_dogs auf true festgelegt ist:

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

Die nächste Abfrage gibt Ergebnisse für Orte mit mindestens acht Ladestationen für Elektrofahrzeuge zurück:

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

Nach primärem Ortstyp und Ortstyp filtern

Jeder Ort im Dataset kann Folgendes haben:

• Ein einzelner primärer Typ, der den in Tabelle A definierten Typen zugeordnet ist. Der primäre Typ kann beispielsweise mexican_restaurant oder steak_house sein. Verwenden Sie primary_type in einer Anfrage, um die Ergebnisse nach dem primären Typ eines Orts zu filtern.

• Mehrere Typwerte, die mit den in Tabelle A definierten Typen verknüpft sind. Ein Restaurant kann beispielsweise die folgenden Typen haben: seafood_restaurant, restaurant, food, point_of_interest, establishment. Verwenden Sie types in einer Anfrage, um die Ergebnisse nach der Liste der Typen zu filtern, die dem Ort zugeordnet sind.

Die folgende Abfrage gibt Ergebnisse für alle Orte mit dem primären Typ bar zurück, die aber auch als restaurant fungieren:

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

Nach vordefinierten Datenwerten filtern

Viele Dataset-Felder haben vordefinierte Werte. Beispiel:

• Das Feld price_level unterstützt die folgenden vordefinierten Werte:

  • PRICE_LEVEL_FREE
  • PRICE_LEVEL_INEXPENSIVE
  • PRICE_LEVEL_MODERATE
  • PRICE_LEVEL_EXPENSIVE
  • PRICE_LEVEL_VERY_EXPENSIVE

• Das Feld business_status unterstützt die folgenden vordefinierten Werte:

  • OPERATIONAL
  • CLOSED_TEMPORARILY
  • CLOSED_PERMANENTLY

Hinweis :In der Regel verwenden Sie den Filter business_status, um die Anzahl der OPERATIONAL-Unternehmen zu ermitteln. Andernfalls enthalten die Zählungen auch geschlossene Unternehmen.

In diesem Beispiel gibt die Abfrage die Anzahl aller Restaurants mit einem business_status von OPERATIONAL im Umkreis von 1.000 Metern um das Empire State Building in New York City zurück:

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)

Nach Geschäftszeiten filtern

In diesem Beispiel wird die Anzahl aller Orte in einem geografischen Gebiet mit Happy Hour am Freitag zurückgegeben:

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

Aggregatfunktion verwenden

Im folgenden Beispiel sehen Sie die unterstützten BigQuery-Aggregationsfunktionen. Mit dieser Abfrage werden die Bewertungen aller Orte im Umkreis von 1.000 Metern um das Empire State Building in New York City zusammengefasst, um Bewertungsstatistiken zu erstellen:

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"

Ergebnisgruppen zurückgeben

Die bisher gezeigten Abfragen geben eine einzelne Zeile im Ergebnis zurück, die die Aggregationsanzahl für die Abfrage enthält. Mit dem Operator GROUP BY können Sie auch mehrere Zeilen in der Antwort basierend auf den Gruppierungskriterien zurückgeben.

Hinweis : Der Vorgang GROUP BY ist für postal_code in Großbritannien oder postal_code_suffix in den USA nicht zulässig.

Die folgende Abfrage gibt beispielsweise Ergebnisse zurück, die nach dem primären Typ jedes Orts im Suchbereich gruppiert sind:

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

Das folgende Bild zeigt ein Beispiel für die Ausgabe dieser Abfrage:

In diesem Beispiel definieren Sie eine Tabelle mit Standorten. Für jeden Standort berechnen Sie dann die Anzahl der Restaurants in der Nähe, d. h. im Umkreis von 1.000 Metern:

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

Das folgende Bild zeigt ein Beispiel für die Ausgabe dieser Abfrage: