עבודה עם נתונים גיאו-מרחביים

ניתוח נתונים גיאו-מרחביים מאפשר לכם לטעון, לשנות ולנתח נתונים גיאוגרפיים, שנקראים גם נתונים גיאו-מרחביים, ב-BigQuery. אתם יכולים להשתמש בפורמטים סטנדרטיים כמו GeoJSON ו-WKT כדי להריץ איחודים מרחביים שעברו אופטימיזציה, ולחלץ תובנות מבוססות-מיקום ישירות מ-BigQuery.

סוגי הנתונים

סוגי האובייקטים הנפוצים כשעובדים עם נתונים גיאו-מרחביים כוללים:

  • גיאומטריה מייצגת שטח פנים בכדור הארץ. לרוב, גיאומטריה מתוארת באמצעות נקודות, קווים, פוליגונים או אוסף של נקודות, קווים ופוליגונים.
  • אוסף גיאומטרי הוא גיאומטריה שמייצגת את האיחוד המרחבי של כל הצורות באוסף.
  • תכונה מרחבית מייצגת אובייקט מרחבי לוגי. הוא משלב גיאומטריה עם מאפיינים נוספים שספציפיים לאפליקציה.
  • אוסף של ישויות מרחביות הוא קבוצה של ישויות מרחביות.

ב-BigQuery, סוג הנתונים GEOGRAPHY מייצג ערך גיאומטרי או אוסף גיאומטרי. כדי לייצג תכונות מרחביות, יוצרים טבלה עם עמודת GEOGRAPHY לגיאומטריה ועמודות נוספות למאפיינים. כל שורה בטבלה היא תכונה מרחבית, והטבלה כולה מייצגת אוסף של תכונות מרחביות.

סוג הנתונים GEOGRAPHY מתאר קבוצת נקודות על פני כדור הארץ. קבוצת נקודות היא קבוצה של נקודות, קווים ומצולעים בספרואיד הייחוס WGS84, עם קצוות גיאודזיים. כדי להשתמש בסוג הנתונים GEOGRAPHY, צריך להפעיל את אחת מפונקציות הגיאוגרפיה של GoogleSQL.

טעינת נתונים גיאו-מרחביים

אפשר לתאר נקודות בודדות בכדור הארץ באמצעות זוג של קו אורך וקו רוחב. לדוגמה, אפשר לטעון קובץ CSV שמכיל ערכים של קווי אורך ורוחב, ואז להשתמש בפונקציה ST_GEOGPOINT כדי להמיר אותם לערכי GEOGRAPHY.

לאזורים גיאוגרפיים מורכבים יותר, אפשר לטעון את הפורמטים הבאים של נתונים גיאו-מרחביים לעמודה GEOGRAPHY:

  • טקסט מוכר (WKT)
  • ‫Well-known binary‏ (WKB)
  • GeoJSON
  • GeoParquet

טעינת נתוני WKT או WKB

‫WKT הוא פורמט טקסט לתיאור צורות גיאומטריות נפרדות באמצעות נקודות, קווים, פוליגונים עם חורים אופציונליים או אוסף של נקודות, קווים או פוליגונים. ‫WKB הוא הגרסה הבינארית של פורמט WKT. אפשר לקודד WKB בפורמט הקסדצימלי לפורמטים שלא תומכים בנתונים בינאריים, כמו JSON.

לדוגמה, הקוד הבא מגדיר נקודה ב-WKT:

POINT(-121 41)

כדי לתאר תכונה מרחבית, בדרך כלל מטמיעים את ה-WKT בפורמט קובץ מאגר, כמו קובץ CSV, או בטבלה של מסד נתונים. בדרך כלל, שורה בקובץ או בטבלה תואמת לישות המרחבית. הקובץ כולו או הטבלה כולה תואמים לאוסף התכונות. כדי לטעון נתוני WKT ל-BigQuery, צריך לספק סכימה שמציינת עמודת GEOGRAPHY לנתונים הגיאו-מרחביים.

לדוגמה, יכול להיות שיש לכם קובץ CSV שמכיל את הנתונים הבאים:

"POLYGON((-124.49 47.35,-124.49 40.73,-116.49 40.73,-116.49 47.35,-124.49 47.35))",poly1
"POLYGON((-85.6 31.66,-85.6 24.29,-78.22 24.29,-78.22 31.66,-85.6 31.66))",poly2
"POINT(1 2)",point1

כדי לטעון את הקובץ הזה, מריצים את הפקודה load של כלי שורת הפקודה של BigQuery:

bq load --source_format=CSV \
  --schema="geography:GEOGRAPHY,name:STRING" \
  mydataset.mytable filename1.csv

למידע נוסף על טעינת נתונים ב-BigQuery, קראו את המאמר מבוא לטעינת נתונים.

כדי להעביר בסטרימינג נתוני WKT לטבלה קיימת ב-BigQuery עם עמודת GEOGRAPHY, צריך להעביר את הנתונים ב-serialize כמחרוזת בבקשת ה-API.

BQ

מריצים את הפקודה insert של כלי שורת הפקודה של BigQuery:

echo '{"geo": "LINESTRING (-118.4085 33.9416, -73.7781 40.6413)"}' \
    | bq insert my_dataset.geo_table

Python

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי הוראות ההגדרה של Python במדריך למתחילים של BigQuery באמצעות ספריות לקוח. למידע נוסף, קראו את מאמרי העזרה של BigQuery Python API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח. 

from google.cloud import bigquery
import shapely.geometry
import shapely.wkt

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the Shapely library to generate WKT of a line from LAX to
# JFK airports. Alternatively, you may define WKT data directly.
my_geography = shapely.geometry.LineString(
    [(-118.4085, 33.9416), (-73.7781, 40.6413)]
)
rows = [
    # Convert data into a WKT string.
    {"geo": shapely.wkt.dumps(my_geography)},
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

למידע נוסף על הזרמת נתונים ב-BigQuery, קראו את המאמר הזרמת נתונים ל-BigQuery.

אפשר גם להמיר מחרוזת טקסט של WKT לערך GEOGRAPHY באמצעות הפונקציה ST_GEOGFROMTEXT.

טעינת נתוני GeoJSON

‫GeoJSON הוא פורמט מבוסס-JSON לגיאומטריות ולתכונות מרחביות. לדוגמה, הקוד הבא מגדיר נקודה ב-GeoJSON:

{ "type": "Point", "coordinates": [-121,41] }

נתוני GeoJSON יכולים להכיל כל אחד מסוגי האובייקטים הבאים:

  • אובייקטים גיאומטריים. אובייקט גיאומטרי הוא צורה מרחבית, שמתוארת כאיחוד של נקודות, קווים ופוליגונים עם חורים אופציונליים.
  • אובייקטים של תכונות. אובייקט של ישות מכיל גיאומטריה וצמדי שם/ערך נוספים, שהמשמעות שלהם ספציפית לאפליקציה.
  • אוספי תכונות. אוסף מאפיינים הוא קבוצה של אובייקטים של מאפיינים.

יש שתי דרכים לטעון נתוני GeoJSON ל-BigQuery:

טעינת קובצי GeoJSON שמופרדים בשורה חדשה

קובץ GeoJSON מופרד בשורות חדשות מכיל רשימה של אובייקטים של תכונות GeoJSON, אחד בכל שורה בקובץ. אובייקט תכונה של GeoJSON הוא אובייקט JSON עם האיברים הבאים:

  • ‫type. באובייקטים של תכונות, הערך חייב להיות Feature. ‫BigQuery מאמת את הערך אבל לא כולל אותו בסכימת הטבלה.

  • ‫geometry. הערך הוא אובייקט GeoJSON Geometry או null. ‫BigQuery ממיר את החבר הזה לערך GEOGRAPHY.

  • ‫properties. הערך הוא אובייקט JSON או null. אם הערך הוא לא null, BigQuery טוען כל איבר של אובייקט ה-JSON כעמודה נפרדת בטבלה. במאמר פרטים על טעינת נתוני JSON מוסבר איך BigQuery מנתח סוגי נתונים של JSON.

  • ‫id. אופציונלי. אם הוא קיים, הערך הוא מחרוזת או מספר. ‫BigQuery טוען את הערך הזה לעמודה בשם id.

אם אובייקט התכונה מכיל איברים אחרים שלא מופיעים כאן, BigQuery ממיר את האיברים האלה ישירות לעמודות בטבלה.

כדי לטעון קובץ GeoJSON שמופרד בשורות חדשות, משתמשים בפקודה bq load של כלי שורת הפקודה של BigQuery, באופן הבא:

bq load \
 --source_format=NEWLINE_DELIMITED_JSON \
 --json_extension=GEOJSON \
 --autodetect \
 DATASET.TABLE \
 FILE_PATH_OR_URI

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DATASET הוא השם של מערך הנתונים.
  • ‫TABLE הוא השם של טבלת היעד.
  • ‫FILE_PATH_OR_URI הוא נתיב לקובץ מקומי או מזהה URI של Cloud Storage.

בדוגמה הקודמת מופעל זיהוי אוטומטי של סכימה. כדי לקבוע איך BigQuery ימיר את הערכים בתוך האובייקט properties, אתם יכולים לספק סכימה מפורשת. מידע נוסף זמין במאמר ציון סכימות. אם אתם מספקים סכימה מפורשת, אל תכללו עמודת type ברמה העליונה בהגדרת הסכימה. לכל איבר של האיבר properties, מגדירים עמודות נפרדות, ולא עמודה אחת בתצוגת עץ.

כפי שמוגדר ב-RFC 7946, מבנה נתונים מלא של GeoJSON הוא אובייקט JSON יחיד. מערכות רבות מייצאות נתוני GeoJSON כאובייקט FeatureCollection יחיד שמכיל את כל הנתונים הגיאומטריים. כדי לטעון את הפורמט הזה ל-BigQuery, צריך להמיר את הקובץ על ידי הסרת האובייקט FeatureCollection ברמת הבסיס ופיצול אובייקטי התכונות הנפרדים לשורות נפרדות. לדוגמה, הפקודה הבאה משתמשת בכלי שורת הפקודה jq כדי לפצל קובץ GeoJSON לפורמט של ערכים מופרדים בשורה חדשה:

cat ~/file1.json | jq -c '.features[]' > converted.json

יצירת טבלה חיצונית מקובץ GeoJSON שמופרד בשורות חדשות

כדי לשלוח שאילתה לקובץ GeoJSON שמופרד בשורות חדשות ומאוחסן ב-Cloud Storage, צריך ליצור טבלה חיצונית. כדי ליצור את הטבלה החיצונית, משתמשים בהצהרת ה-DDL‏ CREATE EXTERNAL TABLE. בסעיף OPTIONS, מגדירים את האפשרות format לערך NEWLINE_DELIMITED_JSON ואת האפשרות json_extension לערך GEOJSON.

דוגמה:

CREATE EXTERNAL TABLE mydataset.table1 OPTIONS (
  format="NEWLINE_DELIMITED_JSON",
  json_extension = 'GEOJSON',
  uris = ['gs://mybucket/geofile.json']
);

מתבצעת טעינה של נתוני הגיאומטריה של GeoJSON

ניתוח נתונים גיאו-מרחביים תומך בטעינה של אובייקטים גיאומטריים מסוג GeoJSON שמוטמעים כמחרוזות טקסט בסוגי קבצים אחרים. לדוגמה, אפשר לטעון קובץ CSV שבו אחת מהעמודות מכילה אובייקט גיאומטרי של GeoJSON.

כדי לטעון את הסוג הזה של נתוני GeoJSON ל-BigQuery, צריך לספק סכימה שמציינת עמודת GEOGRAPHY לנתוני GeoJSON. צריך לספק את הסכימה באופן ידני. אחרת, אם הזיהוי האוטומטי מופעל, BigQuery טוען את הנתונים כערך STRING.

בניתוח נתונים גיאו-מרחביים אי אפשר לטעון אובייקטים של תכונות GeoJSON או אוספי תכונות בגישה הזו. אם אתם צריכים לטעון אובייקטים של תכונות, כדאי להשתמש בקובצי GeoJSON שמופרדים בשורות חדשות.

כדי להעביר בסטרימינג נתוני GeoJSON לטבלה קיימת ב-BigQuery עם עמודת GEOGRAPHY, צריך להמיר את הנתונים לסדרה כמחרוזת בבקשת ה-API.

BQ

מריצים את הפקודה insert של כלי שורת הפקודה של BigQuery:

echo '{"geo": "{\"type\": \"LineString\", \"coordinates\": [[-118.4085, 33.9416], [-73.7781, 40.6413]]}"}' \
  | bq insert my_dataset.geo_table

Python

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי הוראות ההגדרה של Python במדריך למתחילים של BigQuery באמצעות ספריות לקוח. למידע נוסף, קראו את מאמרי העזרה של BigQuery Python API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח. 

import geojson
from google.cloud import bigquery

bigquery_client = bigquery.Client()

# This example uses a table containing a column named "geo" with the
# GEOGRAPHY data type.
table_id = "my-project.my_dataset.my_table"

# Use the python-geojson library to generate GeoJSON of a line from LAX to
# JFK airports. Alternatively, you may define GeoJSON data directly, but it
# must be converted to a string before loading it into BigQuery.
my_geography = geojson.LineString([(-118.4085, 33.9416), (-73.7781, 40.6413)])
rows = [
    # Convert GeoJSON data into a string.
    {"geo": geojson.dumps(my_geography)}
]

#  table already exists and has a column
# named "geo" with data type GEOGRAPHY.
errors = bigquery_client.insert_rows_json(table_id, rows)
if errors:
    raise RuntimeError(f"row insert failed: {errors}")
else:
    print(f"wrote 1 row to {table_id}")

אפשר גם להמיר אובייקט גיאומטרי של GeoJSON לערך GEOGRAPHY באמצעות הפונקציה ST_GEOGFROMGEOJSON. לדוגמה, אתם יכולים לאחסן את הנתונים הגיאומטריים כערכי STRING ואז להריץ שאילתה שקוראת ל-ST_GEOGFROMGEOJSON.

טעינת קובצי GeoParquet

GeoParquet הוא מפרט שמוסיף סוגים גיאו-מרחביים לפורמט הקובץ Parquet. ‫GeoParquet כולל מטא-נתונים שמספקים סמנטיקה מוגדרת לנתונים הגיאו-מרחביים שהוא מכיל, וכך נמנעות בעיות הפרשנות שמתרחשות בפורמטים אחרים של נתונים גיאו-מרחביים.

כשאתם טוענים קובצי Parquet, ‏ BigQuery בודק אם יש מטא-נתונים של GeoParquet. אם יש מטא-נתונים של GeoParquet, כברירת מחדל BigQuery טוען את כל העמודות שהם מתארים לעמודת GEOGRAPHY תואמת. למידע נוסף על טעינת קובצי Parquet, ראו טעינת נתונים בפורמט Parquet.

יצירת טבלה חיצונית מנתוני GeoParquet

טבלאות חיצוניות שמפנות לקובצי GeoParquet ממפות עמודות רלוונטיות לסוג GEOGRAPHY.

הנתונים הסטטיסטיים שזמינים בקובץ GeoParquet‏ (bbox, covering) לא משמשים להאצת שאילתות בטבלה חיצונית.

מערכות קואורדינטות וקצוות

בניתוח גיאו-מרחבי, נקודות הן מיקומים על פני השטח של ספרואיד WGS84, שמיוצגים כקו אורך וקו רוחב גיאודטי. צלע היא קו גיאודזי כדורי בין שתי נקודות קצה. (כלומר, הקצוות הם הנתיב הקצר ביותר על פני השטח של כדור).

פורמט WKT לא מספק מערכת קואורדינטות. כשמטעינים נתוני WKT, ניתוח נתונים גיאו-מרחביים מניח שהנתונים משתמשים בקואורדינטות WGS84 עם קצוות כדוריים. חשוב לוודא שנתוני המקור תואמים למערכת הקואורדינטות הזו, אלא אם האזורים הגיאוגרפיים קטנים מספיק כדי להתעלם מההבדל בין קצוות כדוריים לקצוות מישוריים.

ב-GeoJSON משתמשים באופן מפורש בקואורדינטות WGS84 עם קצוות מישוריים. בזמן הטעינה של נתוני GeoJSON, ניתוח נתונים גיאו-מרחביים ממיר קצוות מישוריים לקצוות כדוריים. ניתוח נתונים גיאו-מרחביים מוסיף נקודות לקו לפי הצורך, כך שרצף הקצוות שהומר נשאר בטווח של 10 מטרים מהקו המקורי. התהליך הזה נקרא ריצוף או העשרה לא אחידה. אי אפשר לשלוט ישירות בתהליך הריצוף.

כדי לטעון נתונים גיאוגרפיים עם קצוות כדוריים, צריך להשתמש ב-WKT. כדי לטעון אזורים גיאוגרפיים עם קצוות מישוריים, שלעיתים קרובות נקראים גיאומטריות, הכי פשוט להשתמש ב-GeoJSON. עם זאת, אם הנתונים הגיאומטריים כבר בפורמט WKT, אפשר גם לטעון את הנתונים כסוג STRING ואז להשתמש בפונקציה ST_GEOGFROMTEXT כדי להמיר לערכי GEOGRAPHY. מגדירים את הפרמטר planar לערך TRUE כדי לפרש את הנתונים כמישוריים.

קובצי GeoParquet כוללים מטא-נתונים על מערכת הקואורדינטות והקצוות ששימשו ליצירת הנתונים. כשקוראים קובצי GeoParquet עם קצוות מישוריים, ניתוח הנתונים הגיאו-מרחביים ממיר את הקצוות המישוריים לקצוות כדוריים. קבצי GeoParquet עם מערכות קואורדינטות שאינן WGS84 נדחים.

כשבוחרים פורמט להחלפת נתונים, חשוב להבין את מערכת הקואורדינטות שבה משתמשים נתוני המקור. רוב המערכות תומכות באופן מפורש בניתוח של נתונים גיאוגרפיים (בניגוד לנתונים גיאומטריים) מ-WKT, או שהן מניחות שהקצוות מישוריים.

הקואורדינטות צריכות להיות בסדר הבא: קו אורך ואז קו רוחב. אם יש קטעים או קצוות ארוכים בנתונים הגיאוגרפיים, צריך לחלק אותם למקטעים, כי ניתוח נתונים גיאו-מרחביים מפרש אותם כקווים גיאודזיים כדוריים, שלא בהכרח תואמים למערכת הקואורדינטות שממנה הגיעו הנתונים.

כיוון הפוליגון

בכדור, לכל פוליגון יש פוליגון משלים. לדוגמה, לפוליגון שמתאר את היבשות של כדור הארץ יהיה פוליגון משלים שמתאר את האוקיינוסים של כדור הארץ. מכיוון ששני הפוליגונים מתוארים על ידי אותן טבעות גבול, נדרשים כללים כדי לפתור את חוסר הבהירות לגבי איזה משני הפוליגונים מתואר על ידי מחרוזת WKT נתונה.

כשאתם טוענים מחרוזות WKT ו-WKB מקבצים או באמצעות הטמעת עדכונים בזמן אמת, ניתוח הנתונים הגיאוגרפיים מניח שהפוליגונים בקלט מכוונים באופן הבא: אם אתם חוצים את גבול הפוליגון בסדר של קודקודי הקלט, החלק הפנימי של הפוליגון נמצא בצד שמאל. בניתוח נתונים גיאו-מרחביים נעשה שימוש באותו כלל כשמייצאים אובייקטים גיאוגרפיים למחרוזות WKT ו-WKB.

אם משתמשים בפונקציה ST_GEOGFROMTEXT כדי להמיר מחרוזת WKT לערך GEOGRAPHY, הפרמטר oriented מציין איך הפונקציה קובעת את הפוליגון:

  • ‫FALSE: המערכת מפרשת את הקלט כמצולע עם השטח הקטן יותר. זאת התנהגות ברירת המחדל.

  • ‫TRUE: משתמשים בכלל הכיוון של יד שמאל שמתואר למעלה. האפשרות הזו מאפשרת לטעון פוליגונים עם שטח שגדול מחצי כדור.

מכיוון שמחרוזות GeoJSON מוגדרות במפה מישורית, אפשר לקבוע את הכיוון באופן חד-משמעי, גם אם הקלט לא תואם לכלל הכיוון שמוגדר במפרטים של פורמטים של GeoJSON,‏ RFC 7946.

טיפול בנתונים מרחביים בפורמט שגוי

כשאתם טוענים נתונים מרחביים מכלים אחרים ל-BigQuery, יכול להיות שתיתקלו בשגיאות המרה בגלל נתוני WKT או GeoJSON לא תקינים. לדוגמה, שגיאה כמו Edge K has duplicate vertex with edge N מציינת שלפוליגון יש קודקודים כפולים (חוץ מהראשון והאחרון).

כדי להימנע מבעיות בפורמט, אפשר להשתמש בפונקציה שיוצרת פלט שתואם לתקנים. לדוגמה, כשמייצאים נתונים מ-PostGIS, אפשר להשתמש בפונקציה ST_MakeValid של PostGIS כדי ליצור פלט סטנדרטי. לחלופין, אפשר לייבא את הנתונים כטקסט ואז להמיר אותם באמצעות קריאה ל-ST_GEOGFROMTEXT או ל-ST_GEOGFROMGEOJSON עם הפרמטר make_valid. כשהערך של make_valid הוא TRUE, הפונקציות האלה מנסות לתקן פוליגונים לא חוקיים.

כדי למצוא או להתעלם מהנתונים בפורמט הלא תקין, משתמשים בקידומת הפונקציה SAFE כדי להפיק את הנתונים הבעייתיים. לדוגמה, בשאילתה הבאה נעשה שימוש בקידומת SAFE כדי לאחזר נתונים מרחביים בפורמט שגוי.

SELECT
  geojson AS bad_geojson
FROM
  mytable
WHERE
  geojson IS NOT NULL
  AND SAFE.ST_GEOGFROMGEOJSON(geojson) IS NULL

מגבלות

ניתוח נתונים גיאו-מרחביים לא תומך בתכונות הבאות בפורמטים גיאו-מרחביים:

  • צורות גיאומטריות תלת-ממדיות. זה כולל את הסיומת "Z" בפורמט WKT ואת קואורדינטת הגובה בפורמט GeoJSON.
  • מערכות ייחוס לינאריות. הפורמט הזה כולל את הסיומת "M" בפורמט WKT.
  • אובייקטים גיאומטריים בפורמט WKT, מלבד אובייקטים גיאומטריים פרימיטיביים או אובייקטים גיאומטריים מרובי חלקים. באופן ספציפי, ניתוח נתונים גיאו-מרחביים תומך רק ב-Point,‏ MultiPoint,‏ LineString,‏ MultiLineString,‏ Polygon,‏ MultiPolygon ו-GeometryCollection.

במאמרים ST_GEOGFROMGEOJSON ו-ST_GEOGFROMTEXT מפורטים האילוצים הספציפיים לפורמטים של קלט GeoJSON ו-WKT.

שילוב נתוני רסטר גיאו-מרחביים עם Google Earth Engine

תובנות גיאוגרפיות מוצגות לעיתים קרובות כנתונים מבוססי-רשת או כנתוני רסטר. נתוני רסטר מארגנים נתונים רציפים אזוריים, כמו צילומי לוויין, תחזיות מזג אוויר ותכסית קרקע, ברשת של פיקסלים. למרות ש-BigQuery מתמחה בעיקר בנתונים וקטוריים בטבלאות, שמייצגים מאפיינים עם גבולות ונקודות, אתם יכולים לשלב נתוני רסטר בניתוחים של BigQuery באמצעות הפונקציה ST_REGIONSTATS. הפונקציה הזו משתמשת ב-Earth Engine, פלטפורמת ניתוח הרסטר של Google, כדי לבצע חישובים וצבירות של נתוני רסטר לניתוח גיאו-מרחבי משופר. איך עובדים עם נתוני רסטר

איך מייצאים נתונים מ-Earth Engine ל-BigQuery למידע נוסף על שילובים בין Earth Engine ל-BigQuery, קראו את המאמר שילוב BigQuery במסמכי התיעוד של Earth Engine.

טרנספורמציה של נתונים גיאו-מרחביים

אם הטבלה מכילה עמודות נפרדות לקווי אורך ורוחב, אפשר להמיר את הערכים לנתונים גיאוגרפיים באמצעות פונקציות גיאוגרפיות של GoogleSQL, כמו ST_GEOGPOINT. לדוגמה, אם יש לכם שתי עמודות DOUBLE לקווי אורך ורוחב, אתם יכולים ליצור עמודה גיאוגרפית באמצעות השאילתה הבאה:

SELECT
  *,
  ST_GEOGPOINT(longitude, latitude) AS g
FROM
  mytable

ב-BigQuery אפשר להמיר מחרוזות WKT ו-GeoJSON לסוגים גיאוגרפיים. אם הנתונים שלכם בפורמט אחר, כמו Shapefiles, השתמשו בכלי חיצוני כדי להמיר את הנתונים לפורמט קובץ קלט נתמך, כמו קובץ CSV, עם עמודות GEOGRAPHY שמקודדות כמחרוזות WKT או GeoJSON.

חלוקה למחיצות (partitioning) ואשכולות של נתונים גיאו-מרחביים

אפשר לחלק למחיצות ולאשכולות טבלאות שמכילות עמודות מסוג GEOGRAPHY. אפשר להשתמש בעמודה מסוג GEOGRAPHY כעמודת אשכולות, אבל אי אפשר להשתמש בעמודה מסוג GEOGRAPHY כעמודת חלוקה למחיצות.

אם אתם מאחסנים נתוני GEOGRAPHY בטבלה והשאילתות שלכם מסננות נתונים באמצעות פרדיקט מרחבי, ודאו שהטבלה מקובצת לפי העמודה GEOGRAPHY. בדרך כלל, הפעולה הזו משפרת את ביצועי השאילתה ועשויה להפחית את העלות. פרדיקט מרחבי קורא לפונקציה גיאוגרפית בוליאנית, ואחד מהארגומנטים שלו הוא עמודת GEOGRAPHY. הדוגמה הבאה מציגה פרדיקט מרחבי שמשתמש בפונקציה ST_DWITHIN:

WHERE ST_DWITHIN(geo, ST_GeogPoint(longitude, latitude), 100)

שימוש ב-JOIN עם נתונים מרחביים

פעולות JOIN מרחביות הן פעולות JOIN של שתי טבלאות עם פונקציה גיאוגרפית של פרדיקט בסעיף WHERE. לדוגמה:

-- how many stations within 1 mile range of each zip code?
SELECT
    zip_code AS zip,
    ANY_VALUE(zip_code_geom) AS polygon,
    COUNT(*) AS bike_stations
FROM
    `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
    `bigquery-public-data.geo_us_boundaries.zip_codes` AS zip_codes
WHERE ST_DWITHIN(
         zip_codes.zip_code_geom,
         ST_GEOGPOINT(bike_stations.longitude, bike_stations.latitude),
         1609.34)
GROUP BY zip
ORDER BY bike_stations DESC

הביצועים של איחודים מרחביים טובים יותר כשנתוני המיקום הגיאוגרפי נשמרים. בדוגמה הקודמת, ערכי המיקום הגיאוגרפי נוצרים בשאילתה. כדי לשפר את הביצועים, כדאי לאחסן את הערכים הגיאוגרפיים בטבלה ב-BigQuery.

לדוגמה, השאילתה הבאה מאחזרת זוגות של קווי אורך ורוחב וממירה אותם לנקודות גיאוגרפיות. כשמריצים את השאילתה הזו, מציינים טבלת יעד חדשה לאחסון תוצאות השאילתה:

SELECT
  *,
  ST_GEOGPOINT(pLongitude, pLatitude) AS p
FROM
  mytable

ב-BigQuery מיושמים איחודי JOIN מרחביים שעברו אופטימיזציה לאופרטורים INNER JOIN ו-CROSS JOIN, עם פונקציות הפרדיקט הבאות של GoogleSQL:

האיחודים המרחביים לא עברו אופטימיזציה:

  • ל-LEFT, RIGHT או FULL OUTER מצטרפים
  • במקרים שכוללים anti-join
  • כשהפרדיקט המרחבי נשלל

JOIN שמשתמש בפרדיקט ST_DWITHIN עובר אופטימיזציה רק כשפרמטר המרחק הוא ביטוי קבוע.

ייצוא נתונים מרחביים

כשמייצאים נתונים מרחביים מ-BigQuery, הערכים בעמודה GEOGRAPHY תמיד בפורמט של מחרוזות WKT. כדי לייצא נתונים בפורמט GeoJSON, משתמשים בפונקציה ST_ASGEOJSON.

אם הכלים שבהם אתם משתמשים כדי לנתח את הנתונים שיוצאו לא מזהים את סוג הנתונים GEOGRAPHY, אתם יכולים להמיר את ערכי העמודות למחרוזות באמצעות פונקציה גיאוגרפית כמו ST_ASTEXT או ST_ASGEOJSON. ניתוח נתונים גיאו-מרחביים מוסיף נקודות נוספות לקו במקרה הצורך, כדי שרצף הקצוות שהומר יישאר בטווח של 10 מטרים מהקו הגיאודזי המקורי.

לדוגמה, בשאילתה הבאה נעשה שימוש ב-ST_ASGEOJSON כדי להמיר ערכי GeoJSON למחרוזות.

SELECT
  ST_ASGEOJSON(ST_MAKELINE(ST_GEOGPOINT(1,1), ST_GEOGPOINT(3,2)))

הנתונים שיתקבלו ייראו כך:

{ "type": "LineString", "coordinates": [ [1, 1], [1.99977145571783, 1.50022838764041], [2.49981908082299, 1.75018082434274], [3, 2] ] }

לקו GeoJSON יש שתי נקודות נוספות. ניתוח נתונים גיאו-מרחביים מוסיף את הנקודות האלה כדי שהקו ב-GeoJSON יעקוב מקרוב אחרי אותו נתיב על הקרקע כמו הקו המקורי.

המאמרים הבאים