Mit raumbezogenen Daten in Bigtable arbeiten

Auf dieser Seite wird beschrieben, wie Sie raumbezogene Daten in Bigtable mit den geografischen GoogleSQL-Funktionen speichern und abfragen. Geodaten umfassen geometrische Darstellungen der Erdoberfläche in Form von Punkten, Linien und Polygonen. Sie können diese Elemente in Sammlungen gruppieren, um Standorte, Routen oder andere interessante Bereiche zu verfolgen.

Grundlegende Vorgänge

Mit den speziellen geografischen GoogleSQL-Funktionen kann Bigtable die Datenbankleistung für raumbezogene Berechnungen und Abfragezeiten optimieren. In den Beispielen zur grundlegenden Verwendung verwenden wir eine tourist_points_of_interest-Tabelle mit einer Spaltenfamilie namens poi_data.

	poi_data
Zeilenschlüssel	Name	Standort
p1	Eiffelturm	POINT(2.2945 48.8584)

Geografische Daten schreiben

Wenn Sie raumbezogene Daten in Bigtable-Spalten schreiben möchten, müssen Sie Stringdarstellungen von geografischen Einheiten im GeoJSON- oder Well-Known Text (WKT)-Format angeben. In Bigtable werden die zugrunde liegenden Daten als Rohbytes gespeichert. Sie können also eine beliebige Kombination von Entitäten in einer einzelnen Spalte angeben, z. B. Points, LineStrings, Polygons, MultiPoints, MultiLineStrings oder MultiPolygons. Der Typ der von Ihnen angegebenen geografischen Entität ist bei der Abfrage wichtig, da Sie die entsprechenden geografischen Funktionen verwenden müssen, um die Daten zu interpretieren.

Wenn Sie beispielsweise der Tabelle tourist_points_of_interest einige Attraktionen hinzufügen möchten, können Sie das cbt-CLI-Tool so verwenden:

Mit WKT:

cbt set tourist_points_of_interest p2 poi_data:name='Louvre Museum'
cbt set tourist_points_of_interest p2 poi_data:location='POINT(2.3376 48.8611)'

cbt set tourist_points_of_interest p3 poi_data:name='Place Charles de Gaulle'
cbt set tourist_points_of_interest p3 poi_data:location='POLYGON(2.2941 48.8733, 2.2941 48.8742, 2.2957 48.8742, 2.2958 48.8732, 2.2941 48.8733)'

Mit GeoJSON:

cbt set tourist_points_of_interest p2 poi_data:name='Louvre Museum'
cbt set tourist_points_of_interest p2 poi_data:location='{"type": "Point", "coordinates": [2.3376, 48.8611]}'

cbt set tourist_points_of_interest p3 poi_data:name='Place Charles de Gaulle'
cbt set tourist_points_of_interest p3 poi_data:location='{"type": "Polygon", "coordinates": [[ [2.2941, 48.8733], [2.2941, 48.8742], [2.2957, 48.8742], [2.2958, 48.8732], [2.2941, 48.8733] ]] }'

Geografische Daten abfragen

Sie können die Tabelle tourist_points_of_interest mit GoogleSQL in Bigtable Studio abfragen und die Ergebnisse mit geografischen Funktionen filtern. Wenn Sie beispielsweise alle Sehenswürdigkeiten im Umkreis von 1.500 Metern vom Trocadéro finden möchten, können Sie die Funktion ST_DWITHIN verwenden:

SELECT _key, poi_data['name'], poi_data['location']
FROM points_of_interest
WHERE ST_DWITHIN(CAST(poi_data['location'] AS STRING), ST_GEOGPOINT(2.2874, 48.86322), 1500);

Das Ergebnis würde etwa so aussehen:

Screenshot der Tabelle „tourist_points_of_interest“ in Bigtable Studio mit Abfrageergebnissen, die mit der geografischen Funktion „ST_DWITHIN“ gefiltert wurden.

In der Spalte location wird die ursprüngliche Textdarstellung der im Schreibvorgang angegebenen Daten angezeigt. Bigtable speichert sie jedoch als Byte. In diesem Beispiel wird Place Charles de Gaulle in den Suchergebnissen berücksichtigt, obwohl es als Polygon definiert ist, das sich über die 1.500-Meter-Grenze hinaus erstreckt. Das liegt daran, dass die Funktion ST_DWITHIN „true“ zurückgibt, wenn ein beliebiger Punkt einer geografischen Einheit innerhalb der erwarteten Entfernung liegt.

Fortgeschrittene Nutzung

In den folgenden Abschnitten wird beschrieben, wie Sie geografische Funktionen für komplexe Szenarien wie die Analyse von Geofencing verwenden. Im Abschnitt Abfrageoptimierung wird beschrieben, wie Sie kontinuierliche materialisierte Ansichten verwenden, um die Leistung zu verbessern.

Zur Veranschaulichung der erweiterten Verwendung von geografischen Funktionen in Bigtable stellen Sie sich vor, Sie sind ein Forscher, der das Verhalten von Kaiserpinguinen auf der Südseite von Snow Hill Island beobachtet.

Ihre Ausrüstung liefert Hunderte von Standort-Pings für jeden Pinguin. Sie erstellen daher die Tabelle penguin_movements, um sie zu speichern:

	penguin_details
Zeilenschlüssel	penguin_id	timestamp	Standort
ping#123	pen_01	2025-12-06 08:15:22+00	POINT(-57.51 -64.42)
ping#124	pen_01	2025-12-06 10:22:05+00	POINT(-57.55 -64.43)
ping#125	pen_01	2025-12-07 12:35:45+00	POINT(-57.58 -64.41)
ping#126	pen_02	2025-12-12 06:05:11+00	POINT(-57.49 -64.39)

Beispiel: Ermitteln, wer den Geofence überquert hat

Während der Recherche beobachten Sie das Verhalten von Pinguinen in der Nähe eines bestimmten Futterplatzes. Gebiete, die mit virtuellen Grenzen definiert werden, werden allgemein als Geofences bezeichnet. Das Futtergebiet kann als geofenced Area betrachtet werden. Sie möchten herausfinden, ob am 3. Dezember 2025 Pinguine das Futtergebiet besucht haben. Um die Frage zu beantworten, verwenden Sie die Funktion ST_CONTAINS:

SELECT
  penguin_details['penguin_id'],
  penguin_details['location'],
  penguin_details['timestamp']
FROM
  penguin_movements
WHERE
  penguin_details['timestamp'] >= '2025-12-03 00:00:00 UTC'
  AND penguin_details['timestamp'] < '2025-12-04 00:00:00 UTC'
  -- The feeding ground boundary is defined with a WKT POLYGON entity
  -- Polygon's last point must be equal to its first point to close the loop
  AND ST_CONTAINS(
        ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'),
        ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING))
      )

Die Ergebnisse sollten in etwa so aussehen:

+------------+----------------------+------------------------+
| penguin_id |       location       |       timestamp        |
+------------+----------------------+------------------------+
| pen_01     | POINT(-57.15 -64.53) | 2025-12-03 08:15:22+00 |
| pen_02     | POINT(-57.18 -64.54) | 2025-12-03 14:30:05+00 |
| pen_10     | POINT(-57.10 -64.52) | 2025-12-03 11:45:10+00 |
+------------+----------------------+------------------------+

Beispiel: Punkte in Routen umwandeln

Um besser zu visualisieren, wie sich die Pinguine bewegen, organisieren Sie einzelne Standort-Pings in LineStrings. Dazu verwenden Sie die Funktion ST_MAKELINE:

SELECT
  penguin_id,
  DATE(timestamp, 'UTC') AS route_date,
  ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
FROM (
  -- Sub-expression to sort all location pings by timestamp
  -- This way you make sure individual points can form a realistic route
  SELECT
    penguin_details['penguin_id'] AS penguin_id,
    -- Extract and cast timestamp once
    CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
    -- Extract and cast location once
    ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
  FROM
    penguin_movements
  ORDER BY
    -- Pre-sorts location pings for the ARRAY_AGG function
    penguin_id, timestamp ASC
)
GROUP BY
  penguin_id,
  route_date

Mit dieser Abfrage erhalten Sie detailliertere Statistiken für Ihre Recherche. Erweitern Sie die folgenden Abschnitte, um Beispielabfragen und ‑ergebnisse zu sehen.

Wie weit legt jeder Pinguin täglich zurück?

Um zu berechnen, wie viel Strecke jeder Pinguin täglich zurücklegt, können Sie die Funktion ST_LENGTH verwenden.

SELECT
  penguin_id,
  route_date,
  ST_LENGTH(route) AS daily_distance_meters
FROM (
  -- Subquery to aggregate points into daily routes
  SELECT
    penguin_id,
    DATE(timestamp, 'UTC') AS route_date,
    ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
  FROM (
    -- Sub-expression to sort all location pings by timestamp
    -- This way you make sure individual points can form a realistic route
    SELECT
      penguin_details['penguin_id'] AS penguin_id,
      -- Extract and cast timestamp once
      CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
      -- Extract and cast location once
      ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
    FROM
      penguin_movements
    ORDER BY
      -- Pre-sorts location pings for the ARRAY_AGG function
      penguin_id, timestamp ASC
  )
  GROUP BY
    penguin_id,
    route_date
) AS DailyRoutes;

Diese Abfrage gibt ein Ergebnis ähnlich dem folgenden zurück:

+------------+------------+-----------------------+
| penguin_id | route_date | daily_distance_meters |
+------------+------------+-----------------------+
| pen_01     | 2025-12-02 | 15420.7               |
| pen_03     | 2025-12-03 | 22105.1               |
| pen_32     | 2025-12-03 | 9850.3                |
+------------+------------+-----------------------+

Wie viel der täglichen Route liegt innerhalb des Geofence-Bereichs?

Um diese Frage zu beantworten, filtern Sie die Ergebnisse mit der Funktion ST_INTERSECTION, um zu prüfen, welche Teile der Route des Pinguins sich mit dem eingegrenzten Bereich überschneiden. Mit der Funktion ST_LENGTH kann dann das Verhältnis der Bewegung innerhalb der Futterplätze zur an diesem Tag zurückgelegten Gesamtstrecke berechnet werden.

SELECT
  penguin_id,
  route_date,
  ST_LENGTH(ST_INTERSECTION(route, ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'))) / ST_LENGTH(route) AS proportion_in_ground
FROM (
  -- Subquery to aggregate points into daily routes
  SELECT
    penguin_id,
    DATE(timestamp, 'UTC') AS route_date,
    ST_MAKELINE(ARRAY_AGG(location_cast_to_geog)) AS route
  FROM (
    -- Sub-expression to sort all location pings by timestamp
    -- This way you make sure individual points can form a realistic route
    SELECT
      penguin_details['penguin_id'] AS penguin_id,
      -- Extract and cast timestamp once
      CAST(CAST(penguin_details['timestamp'] AS STRING) AS TIMESTAMP) AS timestamp,
      -- Extract and cast location once
      ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)) AS location_cast_to_geog
    FROM
      penguin_movements
    ORDER BY
      -- Pre-sorts location pings for the ARRAY_AGG function
      penguin_id, timestamp ASC
  )
  GROUP BY
    penguin_id,
    route_date
) AS DailyRoutes
WHERE
  ST_INTERSECTS(route, ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'))

Diese Abfrage gibt ein Ergebnis ähnlich dem folgenden zurück:

+------------+------------+----------------------+
| penguin_id | route_date | proportion_in_ground |
+------------+------------+----------------------+
| pen_01     | 2025-12-03 | 0.652                |
| pen_03     | 2025-12-03 | 0.918                |
| pen_32     | 2025-12-04 | 0.231                |
+------------+------------+----------------------+

Abfragen mit kontinuierlichen materialisierten Ansichten optimieren

Geografische Abfragen für große Datasets können langsam sein, da möglicherweise viele Zeilen gescannt werden müssen. Bigtable unterstützt keine dedizierten geospatialen Indexe, aber Sie können die Abfrageleistung mit kontinuierlichen materialisierten Ansichten verbessern.

Wenn Sie einen eindeutigen Zeilenschlüssel für solche Ansichten erstellen möchten, können Sie den Typ GEOGRAPHY in eine S2-Zelle umwandeln. S2 ist eine Bibliothek, mit der Sie komplexe Trigonometrie für die Kugelgeometrie ausführen können. Die Berechnung von Entfernungen auf Grundlage geografischer Koordinaten kann rechenintensiv sein. S2-Zellen können einen bestimmten Bereich auf der Erdoberfläche als 64-Bit-Ganzzahl darstellen. Daher eignen sie sich ideal für die räumliche Indexierung mit kontinuierlichen materialisierten Ansichten.

Um eine S2-Zelle zu definieren, rufen Sie die Funktion S2_CELLIDFROMPOINT(location, level) auf und geben das Granularitätsargument level an. Dieses Argument ist eine Zahl zwischen 0 und 30 und definiert die Größe der einzelnen Zellen: Je höher die Zahl, desto kleiner die Zelle.

Im Beispiel zur Pinguinforschung können Sie eine kontinuierliche materialisierte Ansicht erstellen, die nach dem Standort und dem Zeitstempel jedes Pinguins indexiert wird:

-- Query used to create the continuous materialized view
SELECT
  -- Create S2 cells for each penguin's location.
  -- Note that the `level` value is the same for each location so that
  -- every cell is the same size. This ensures consistency for your data.
  S2_CELLIDFROMPOINT(ST_GEOGFROMTEXT(CAST(penguin_details['location'] AS STRING)), level => 16) AS s2_cell_id,
  penguin_details['timestamp'] AS observation_time,
  penguin_details['penguin_id'] AS penguin_id,
  penguin_details['location'] AS location
FROM
  penguin_movements

Die Abfrage dieser Ansicht zur Beantwortung der Frage Wer hat den Geofence überquert? wird mit einem zweiphasigen Ansatz viel effizienter. Sie verwenden zuerst den cell_id-Index in der kontinuierlichen materialisierten Ansicht, um schnell in der Nähe befindliche Kandidatenzeilen zu finden, und wenden dann die genaue ST_CONTAINS-Funktion auf das reduzierte Dataset an:

SELECT
  idx.penguin_id,
  idx.location,
  idx.observation_time
FROM
  penguin_s2_time_index AS idx
WHERE
  -- Part one: use an approximate spatial filter and timestamp filter
  -- for fast scans on continuous materialized view keys.
  -- Use the S2_COVERINGCELLIDS function to create an array of S2 cells
  -- that cover the feeding ground polygon. The `level` argument must be the
  -- same value as the one you used to create the continuous materialized view.
  idx.s2_cell_id IN UNNEST(S2_COVERINGCELLIDS(
    ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'),
    min_level => 16,
    max_level => 16,
    max_cells => 500
  ))
  AND idx.observation_time >= '2025-12-03 00:00:00 UTC'
  AND idx.observation_time < '2025-12-04 00:00:00 UTC'
  -- Part two: use ST_CONTAINS() on the returned set to ensure precision.
  -- S2 cells are squares, so they don't equal arbitrary geofence polygons.
  -- You still need to check if a specific point is contained within the area,
  -- but the filter applies to a smaller data set and is much faster.
  AND ST_CONTAINS(ST_GEOGFROMTEXT('POLYGON((-57.21 -64.51, -57.23 -64.55, -57.08 -64.56, -57.06 -64.51, -57.21 -64.51))'), idx.location);

Beschränkungen

Bigtable unterstützt die folgenden Funktionen nicht, wenn Sie mit geografischen Daten arbeiten:

Dreidimensionale Geometrien. Dazu gehören das Suffix „Z“ im WKT-Format und die Höhenkoordinaten im GeoJSON-Format.
Lineare Referenzsysteme. Dazu gehört auch das Suffix „M“ im WKT-Format.
WKT-Geometrieobjekte außer den Geometrie-Primitiven und mehrteiligen Geometrien. Insbesondere unterstützt Bigtable nur Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon und GeometryCollection.
Die Funktion ST_CLUSTERDBSCAN wird nicht unterstützt.

Weitere Informationen zu spezifischen Einschränkungen für GeoJson- und WKT-Eingabeformate finden Sie unter ST_GEOGFROMGEOJSON und ST_GEOGFROMTEXT.

Nächste Schritte

Weitere Informationen zu verfügbaren geografischen Funktionen Übersicht über geografische Funktionen in GoogleSQL