הרצת שאילתות והמחשה ויזואלית של נתוני מיקום ב-BigQuery באמצעות Google Maps Platform ‏ (JavaScript)

1. סקירה כללית

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

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

מה תפַתחו

ב-codelab הזה תכתבו ותריצו כמה שאילתות שמדגימות איך להפיק תובנות מבוססות-מיקום ממערכי נתונים ציבוריים גדולים מאוד באמצעות BigQuery. בנוסף, תיצרו דף אינטרנט שבו נטען מפה באמצעות JavaScript API של הפלטפורמה של מפות Google, ואז מריצים שאילתות מרחביות ומציגים אותן באופן חזותי על אותם מערכי נתונים ציבוריים גדולים מאוד באמצעות ספריית הלקוח של Google APIs ל-JavaScript ו-BigQuery API.

מה תלמדו

• איך מריצים שאילתות במערכי נתונים של מיקום בגודל פטה-בייט תוך שניות באמצעות BigQuery, באמצעות שאילתות SQL, פונקציות שהוגדרו על ידי המשתמש ו-BigQuery API
• איך משתמשים בפלטפורמה של מפות Google כדי להוסיף מפת Google לדף אינטרנט ולאפשר למשתמשים לצייר בה צורות
• איך להציג נתונים חזותיים של שאילתות על מערכי נתונים גדולים במפת Google, כמו בתמונה שבהמשך. התמונה מציגה את צפיפות המיקומים שבהם נוסעים ירדו ממוניות בשנת 2016, מתוך נסיעות שהתחילו מהבלוק שסביב בניין האמפייר סטייט.

מה נדרש

• ידע בסיסי ב-HTML‏, CSS‏, JavaScript‏, SQL ו-Chrome DevTools
• דפדפן אינטרנט מודרני, כמו גרסאות עדכניות של Chrome,‏ Firefox,‏ Safari או Edge.
• עורך טקסט או סביבת פיתוח משולבת (IDE) לפי בחירתכם

הטכנולוגיה

BigQuery

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

הפלטפורמה של מפות Google

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

‫ Google Maps Platform Javascript API Drawing Layer מאפשר לכם לצייר צורות במפה. אפשר להמיר אותם לקלט כדי להריץ שאילתות על טבלאות BigQuery שמאוחסנים בהן ערכים של קווי רוחב ואורך בעמודות.

כדי להתחיל, צריך פרויקט ב-Google Cloud Platform עם BigQuery ו-Maps APIs מופעלים.

2. הגדרה

חשבון Google

אם עדיין אין לכם חשבון Google (Gmail או Google Apps), אתם צריכים ליצור חשבון.

יצירת פרויקט

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

נכנסים אל Google Cloud Platform Console‏ ( console.cloud.google.com) ויוצרים פרויקט חדש. בחלק העליון של המסך, יש תפריט נפתח של פרויקט:

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

בתיבה 'הזנת שם חדש לפרויקט', מזינים שם לפרויקט החדש, למשל BigQuery Codelab:

מזהה פרויקט ייווצר בשבילכם. מזהה הפרויקט הוא שם ייחודי בכל הפרויקטים ב-Google Cloud. חשוב לזכור את מזהה הפרויקט, כי תצטרכו אותו בהמשך. השם שצוין למעלה כבר תפוס ולא יתאים לכם. בכל מקום שמופיע YOUR_PROJECT_ID ב-codelab הזה, צריך להזין את מזהה הפרויקט שלכם.

הפעלת חיוב

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

הערה: לא אמור להיות חיוב על השאילתות שמופעלות ב-codelab הזה, אבל אם תחרגו מהתוכנית החינמית של BigQuery (1TB של שאילתות נתונים בחודש) או מהמכסה היומית המקסימלית בחינם ל-Maps API (25,000 טעינות של מפות ביום), יכול להיות שייגבו מכם תשלומים.

האופן שבו מפעילים את החיוב תלוי בשאלה אם אתם יוצרים פרויקט חדש או מפעילים מחדש את החיוב בפרויקט קיים.

‫Google מציעה תקופת ניסיון בחינם למשך 12 חודשים, עם שימוש ב-Google Cloud Platform בשווי של עד 300$. יכול להיות שתוכלו להשתמש בקרדיט הזה כדי לנסות את ה-Codelab הזה. פרטים נוספים זמינים בכתובת https://cloud.google.com/free/.

פרויקטים חדשים

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

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

פרויקטים קיימים

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

1. עוברים אל Cloud Platform Console.
2. ברשימת הפרויקטים, בוחרים את הפרויקט שעבורו רוצים להפעיל מחדש את החיוב.
3. פותחים את התפריט הימני במסוף ולוחצים על Billing . מתבקשים לבחור חשבון לחיוב.
4. לוחצים על Set account.

יצירת חשבון חדש לחיוב

כדי ליצור חשבון חדש לחיוב:

1. עוברים אל Cloud Platform Console ונכנסים לחשבון, או נרשמים אם עדיין אין לכם חשבון.
2. פותחים את התפריט הימני במסוף ולוחצים על Billing .
3. לוחצים על הלחצן חשבון חדש לחיוב. (שימו לב: אם זה לא החשבון לחיוב הראשון שלכם, קודם צריך לפתוח את רשימת החשבונות לחיוב. לשם כך, לוחצים על השם של החשבון הקיים לחיוב בחלק העליון של הדף, ואז לוחצים על ניהול חשבונות לחיוב).
4. מזינים את השם של החשבון לחיוב ואת פרטי החיוב. האפשרויות שיוצגו תלויות במדינה של הכתובת לחיוב. שימו לב: בחשבונות בארצות הברית, אי אפשר לשנות את סטטוס המס אחרי יצירת החשבון.
5. לוחצים על שליחה והפעלת החיוב.

כברירת מחדל, האדם שיוצר את החשבון לחיוב מוגדר כאדמין לחיוב בחשבון.

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

הפעלת BigQuery API

כדי להפעיל את BigQuery API בפרויקט, עוברים אל דף BigQuery API ב-Marketplace במסוף ולוחצים על הלחצן הכחול 'הפעלה'.

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

3. שליחת שאילתות על נתוני מיקום ב-BigQuery

יש שלוש דרכים לשאילתות של נתוני מיקום שמאוחסנים כערכי קו רוחב וקו אורך ב-BigQuery.

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

כדי להתחיל, משתמשים בכלי לעריכת שאילתות בקטע BigQuery במסוף Google Cloud Platform כדי להריץ את השאילתות הבאות על נתוני המוניות בניו יורק.

SQL סטנדרטי לעומת SQL מדור קודם

‫BigQuery תומך בשתי גרסאות של SQL: ‏ Legacy SQL ו-Standard SQL. האחרון הוא תקן ANSI משנת 2011. לצורך המדריך הזה, נשתמש ב-SQL סטנדרטי כי הוא עומד בתקנים בצורה טובה יותר.

כדי להריץ SQL מדור קודם בעורך של BigQuery, אפשר לפעול לפי השלבים הבאים:

1. לוחצים על לחצן 'עוד'.
2. בתפריט הנפתח, בוחרים באפשרות 'הגדרות שאילתה'.
3. בקטע 'ניב SQL', בוחרים בלחצן הבחירה 'Legacy' (קודם).
4. לוחצים על הלחצן 'שמירה'.

שאילתות לגבי מלבנים

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

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

SELECT 
ROUND(AVG(tip_amount),2) as avg_tip, 
ROUND(AVG(fare_amount),2) as avg_fare, 
ROUND(AVG(trip_distance),2) as avg_distance, 
ROUND(AVG(tip_proportion),2) as avg_tip_pc, 
ROUND(AVG(fare_per_mile),2) as avg_fare_mile FROM

(SELECT 

pickup_latitude, pickup_longitude, tip_amount, fare_amount, trip_distance, (tip_amount / fare_amount)*100.0 as tip_proportion, fare_amount / trip_distance as fare_per_mile

FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`

WHERE trip_distance > 0.01 AND fare_amount <100 AND payment_type = "1" AND fare_amount > 0
)

--Manhattan
WHERE pickup_latitude < 40.7679 AND pickup_latitude > 40.7000 AND pickup_longitude < -73.97 and pickup_longitude > -74.01

--JFK
--WHERE pickup_latitude < 40.654626 AND pickup_latitude > 40.639547 AND pickup_longitude < -73.771497 and pickup_longitude > -73.793755

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

מנהטן

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
2.52	12.03	9.97	22.39	5.97

JFK

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
9.22	48.49	41.19	22.48	4.36

שאילתות לגבי רדיוס

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

הנה דוגמה להצהרת SQL של BigQuery לשאילתת עיגול עם מרכז ב-40.73943, -73.99585 ורדיוס של 0.1 ק"מ.

היא משתמשת בערך קבוע של 111.045 קילומטרים כדי לחשב את המרחק שמיוצג על ידי מעלה אחת.

הדוגמה הזו מבוססת על דוגמה שמופיעה בכתובת http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/:

SELECT pickup_latitude, pickup_longitude, 
    (111.045 * DEGREES( 
      ACOS( 
        COS( RADIANS(40.73943) ) * 
        COS( RADIANS( pickup_latitude ) ) * 
        COS( 
          RADIANS( -73.99585 ) - 
          RADIANS( pickup_longitude ) 
        ) + 
        SIN( RADIANS(40.73943) ) * 
        SIN( RADIANS( pickup_latitude ) ) 
      ) 
     ) 
    ) AS distance FROM `project.dataset.tableName` 
    HAVING distance < 0.1 

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

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

‫BigQuery תומך בשתי גרסאות של SQL: ‏ Legacy SQL ו-Standard SQL. האחרון הוא תקן ANSI משנת 2011.

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

#standardSQL
CREATE TEMPORARY FUNCTION Degrees(radians FLOAT64) RETURNS FLOAT64 AS
(
  (radians*180)/(22/7)
);

CREATE TEMPORARY FUNCTION Radians(degrees FLOAT64) AS (
  (degrees*(22/7))/180
);

CREATE TEMPORARY FUNCTION DistanceKm(lat FLOAT64, lon FLOAT64, lat1 FLOAT64, lon1 FLOAT64) AS (
     Degrees( 
      ACOS( 
        COS( Radians(lat1) ) * 
        COS( Radians(lat) ) *  
        COS( Radians(lon1 ) -  
        Radians( lon ) ) +  
        SIN( Radians(lat1) ) *  
        SIN( Radians( lat ) ) 
        ) 
    ) * 111.045
);

SELECT 

ROUND(AVG(tip_amount),2) as avg_tip,
ROUND(AVG(fare_amount),2) as avg_fare,
ROUND(AVG(trip_distance),2) as avg_distance,
ROUND(AVG(tip_proportion), 2) as avg_tip_pc,
ROUND(AVG(fare_per_mile),2) as avg_fare_mile

FROM

-- EMPIRE STATE BLDG 40.748459, -73.985731
-- BRONX 40.895597, -73.856085

(SELECT pickup_latitude, pickup_longitude, tip_amount, fare_amount, trip_distance, tip_amount/fare_amount*100 as tip_proportion, fare_amount / trip_distance as fare_per_mile, DistanceKm(pickup_latitude, pickup_longitude, 40.748459, -73.985731)


FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`

WHERE 
  DistanceKm(pickup_latitude, pickup_longitude, 40.748459, -73.985731) < 0.1
  AND fare_amount > 0 and trip_distance > 0
  )
WHERE fare_amount < 100

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

Empire State Building:

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
1.17	11.08	45.28	10.53	6.42

ברונקס

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
0.52	17.63	4.75	4.74	10.9

שאילתות פוליגון

‫SQL לא תומך בשאילתות באמצעות צורות שרירותיות שאינן מלבנים ועיגולים. ל-BigQuery אין סוג נתונים גיאומטרי מקורי או אינדקס מרחבי, ולכן כדי להריץ שאילתות באמצעות צורות של מצולעים, צריך להשתמש בגישה שונה משל שאילתות SQL פשוטות. אחת הגישות היא להגדיר פונקציית גיאומטריה ב-JavaScript ולהריץ אותה כפונקציה בהגדרת המשתמש (UDF) ב-BigQuery.

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

אלגוריתם Point In Polygon

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

function pointInPoly(nvert, vertx, verty, testx, testy){
  var i, j, c = 0;
  for (i = 0, j = nvert-1; i < nvert; j = i++) {
    if ( ((verty[i]>testy) != (verty[j]>testy)) &&
                (testx < (vertx[j]-vertx[i]) * (testy-verty[i]) / (verty[j]-verty[i]) + vertx[i]) )
      c = !c;
  }
  return c;
}

העברה ל-JavaScript

גרסת JavaScript של האלגוריתם הזה נראית כך:

/* This function includes a port of C code to calculate point in polygon
* see http://www.ecse.rpi.edu/Homepages/wrf/Research/Short_Notes/pnpoly.html for license
*/

function pointInPoly(polygon, point){
    // Convert a JSON poly into two arrays and a vertex count.
    let vertx = [],
        verty = [],
        nvert = 0,
        testx = point[0],
        testy = point[1];
    for (let coord of polygon){
      vertx[nvert] = coord[0];
      verty[nvert] = coord[1];
      nvert ++;
    }

        
    // The rest of this function is the ported implementation.
    for (let i = 0, let j = nvert - 1; i < nvert; j = i++) {
      if ( ((verty[i] > testy) != (verty[j] > testy)) &&
         (testx < (vertx[j] - vertx[i]) * (testy - verty[i]) / (verty[j] - verty[i]) + vertx[i]) )
        c = !c;
    }
    return c;
}

כשמשתמשים ב-SQL סטנדרטי ב-BigQuery, הגישה של UDF דורשת רק הצהרה אחת, אבל צריך להגדיר את ה-UDF כפונקציה זמנית בהצהרה. נראה דוגמה. מדביקים את הצהרת ה-SQL שבהמשך בחלון Query Editor.

CREATE TEMPORARY FUNCTION pointInPolygon(latitude FLOAT64, longitude FLOAT64)
RETURNS BOOL LANGUAGE js AS """
  let polygon=[[-73.98925602436066,40.743249676056955],[-73.98836016654968,40.74280666503313],[-73.98915946483612,40.741676770346295],[-73.98967981338501,40.74191656974406]];

  let vertx = [],
    verty = [],
    nvert = 0,
    testx = longitude,
    testy = latitude,
    c = false,
    j = nvert - 1;

  for (let coord of polygon){
    vertx[nvert] = coord[0];
    verty[nvert] = coord[1];
    nvert ++;
  }

  // The rest of this function is the ported implementation.
  for (let i = 0; i < nvert; j = i++) {
    if ( ((verty[i] > testy) != (verty[j] > testy)) &&
 (testx < (vertx[j] - vertx[i]) * (testy - verty[i]) / (verty[j] - verty[i]) + vertx[i]) ) {
      c = !c;
    }
  }

  return c;
""";

SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, pickup_datetime
FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2016`
WHERE pointInPolygon(pickup_latitude, pickup_longitude) = TRUE
AND (pickup_datetime BETWEEN CAST("2016-01-01 00:00:01" AS DATETIME) AND CAST("2016-02-28 23:59:59" AS DATETIME))
LIMIT 1000

מעולה!

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

הלוואי שהיינו יכולים להציג את הנתונים במפה ולחקור אותם על ידי הגדרת אזורים שרירותיים שמעניינים אותנו. אפשר לעשות את זה באמצעות Google Maps APIs. קודם צריך להפעיל את Maps API, להגדיר דף אינטרנט פשוט שפועל במחשב המקומי ולהתחיל להשתמש ב-BigQuery API כדי לשלוח שאילתות מדף האינטרנט.

4. עבודה עם ממשקי Google Maps API

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

הפעלת Maps JavaScript API

ב-Codelab הזה תצטרכו להפעיל את ממשק ה-API של JavaScript במפות Google בפרויקט שלכם. כדי לעשות זאת, בצע את הפעולות הבאות:

1. במסוף Google Cloud Platform, עוברים אל Marketplace.
2. ב-Marketplace, מחפשים את Maps JavaScript API.
3. לוחצים על הכרטיס Maps JavaScript API בתוצאות החיפוש
4. לוחצים על הלחצן 'הפעלה'.

יצירת מפתח API

כדי לשלוח בקשות אל Google Maps Platform, צריך ליצור מפתח API ולשלוח אותו עם כל הבקשות. כדי ליצור מפתח API:

1. במסוף Google Cloud Platform, לוחצים על סמל האפשרויות הנוספות (3 קווים) כדי לפתוח את חלונית הניווט השמאלית.
2. בוחרים באפשרות APIs & Service' > ‘Credentials'‎
3. לוחצים על הלחצן 'יצירת אמצעי אימות' ואז בוחרים באפשרות 'מפתח API'.
4. מעתיקים את מפתח ה-API החדש.

הורדה של הקוד והגדרת שרת אינטרנט

כדי להוריד את כל הקוד של ה-codelab הזה, לוחצים על הלחצן הבא:

הורדת הקוד

מחלצים את קובץ ה-ZIP שהורד. הפעולה הזו תחלץ תיקיית בסיס (bigquery), שמכילה תיקייה אחת לכל שלב ב-codelab, יחד עם כל המשאבים שתצטרכו.

התיקיות stepN מכילות את מצב הסיום הרצוי של כל שלב ב-codelab הזה. הן מופיעות שם לצורך השוואה. כל עבודת התכנות שלנו תתבצע בספרייה שנקראת work.

הגדרת שרת אינטרנט מקומי

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

התקנה של Web Server for Chrome

אחרי ההתקנה, פותחים את האפליקציה. ב-Chrome אפשר לעשות את זה כך:

עזרה נוספת: הוספה ופתיחה של אפליקציות Chrome

1. פתיחת Chrome
2. בסרגל הכתובות שבחלק העליון, מקלידים chrome://apps
3. הקש Enter
4. בחלון שנפתח, לוחצים על סמל שרת האינטרנט. אפשר גם ללחוץ לחיצה ימנית על אפליקציה כדי לפתוח אותה בכרטיסייה רגילה או מוצמדת, במסך מלא או בחלון חדש מוצג הדו-שיח הבא, שמאפשר להגדיר את שרת האינטרנט המקומי:
5. לוחצים על 'בחירת תיקייה' ובוחרים את המיקום שאליו הורדתם את קובצי הדוגמה של ה-codelab.
6. בקטע 'אפשרויות', מסמנים את התיבה לצד 'הצגה אוטומטית של index.html':
7. מזיזים את המתג עם התווית 'שרת אינטרנט: הופעל' שמאלה ואז ימינה כדי לעצור את שרת האינטרנט ולהפעיל אותו מחדש.

5. טעינת המפה וכלי השרטוט

יצירת דף מפה בסיסי

מתחילים עם דף HTML פשוט שטוען מפת Google באמצעות Maps JavaScript API וכמה שורות של JavaScript. הקוד מתוך דוגמה למפה פשוטה בפלטפורמה של מפות Google הוא מקום מצוין להתחיל בו. הוא מופיע כאן כדי שתוכלו להעתיק ולהדביק אותו בכלי לעריכת טקסט או בסביבת פיתוח משולבת (IDE) לפי בחירתכם, או שתוכלו לפתוח את הקובץ index.html ממאגר המידע שהורדתם.

1. מעתיקים את index.html לתיקייה work בעותק המקומי של המאגר
2. מעתיקים את התיקייה img/ לתיקייה work/ בעותק המקומי של המאגר
3. פותחים את work/index.html בכלי לעריכת טקסט או בסביבת פיתוח משולבת (IDE)
4. מחליפים את הערך YOUR_API_KEY במפתח ה-API שיצרתם קודם.

<script src="https://maps.googleapis.com/maps/api/js?key=YOUR_API_KEY&callback=initMap"
    async defer></script>

5. בדפדפן, פותחים את localhost:<port>/work, כאשר port הוא מספר היציאה שצוין בהגדרות של שרת האינטרנט המקומי. יציאת ברירת המחדל היא 8887. המפות הראשונות אמורות להופיע.

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

שינוי מיקום ברירת המחדל ורמת הזום

הקוד שקובע את המיקום ואת רמת הזום נמצא בשורות 27 ו-28 של index.html, ובשלב הזה הוא ממוקד בסידני, אוסטרליה:

<script>
      let map;
      function initMap() {
        map = new google.maps.Map(document.getElementById('map'), {
          center: {lat: -34.397, lng: 150.644},
          zoom: 8
        });
      }
</script>

במדריך הזה נעשה שימוש בנתוני נסיעות במוניות ב-BigQuery בניו יורק, ולכן בשלב הבא תשנו את קוד ההפעלה של המפה כך שהמיקום המרכזי יהיה בניו יורק, ברמת זום מתאימה – רמה 13 או 14 אמורה להתאים.

טיפ: כדי לגלות את קווי הרוחב והאורך של מקום מסוים, אפשר להיכנס לכתובת maps.google.com, לחפש את המקום, ללחוץ לחיצה ימנית ולבחור באפשרות'מה יש כאן?' בתפריט ההקשר שמופיע.

כדי לעשות את זה, מעדכנים את בלוק הקוד שלמעלה כך:

<script>
      let map;
      function initMap() {
        map = new google.maps.Map(document.getElementById('map'), {
          center: {lat: 40.7484405, lng: -73.9878531},
          zoom: 14
        });
      }
</script>

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

טעינת ספריות הציור וההדמיה

כדי להוסיף יכולות שרטוט למפה, צריך לשנות את הסקריפט שבו נטען Maps JavaScript API. לשם כך, מוסיפים פרמטר אופציונלי שמורה ל-Google Maps Platform להפעיל את ספריית השרטוט.

ב-codelab הזה נעשה שימוש גם ב-HeatmapLayer, ולכן תצטרכו לעדכן את הסקריפט כדי לבקש את ספריית הוויזואליזציה. כדי לעשות זאת, מוסיפים את הפרמטר libraries ומציינים את הספריות visualization ו-drawing כערכים שמופרדים בפסיקים, למשל libraries=visualization,drawing

הוא אמור להיראות כך:

<script src='http://maps.googleapis.com/maps/api/js?libraries=visualization,drawing&callback=initMap&key=YOUR_API_KEY' async defer></script>

הוספת DrawingManager

כדי להשתמש בצורות שמשורטטות על ידי המשתמש כקלט לשאילתה, מוסיפים את DrawingManager למפה, כשהכלים Circle, Rectangle ו-Polygon מופעלים.

מומלץ להוסיף את כל DrawingManager קוד ההגדרה לפונקציה חדשה. לכן, בעותק של index.html, מבצעים את הפעולות הבאות:

1. מוסיפים פונקציה בשם setUpDrawingTools() עם הקוד הבא כדי ליצור את DrawingManager ולהגדיר את המאפיין map שלו כך שיפנה לאובייקט המפה בדף.

האפשרויות שמועברות אל google.maps.drawing.DrawingManager(options) מגדירות את סוג ברירת המחדל של צורת השרטוט ואת אפשרויות התצוגה של צורות ששורטטו. כדי לבחור אזורים במפה לשליחה כשאילתות, הצורות צריכות להיות שקופות לחלוטין. מידע נוסף על האפשרויות הזמינות מופיע במאמר DrawingManager Options.

function setUpDrawingTools() {
  // Initialize drawing manager
  drawingManager = new google.maps.drawing.DrawingManager({
    drawingMode: google.maps.drawing.OverlayType.CIRCLE,
    drawingControl: true,
    drawingControlOptions: {
      position: google.maps.ControlPosition.TOP_LEFT,
      drawingModes: [
        google.maps.drawing.OverlayType.CIRCLE,
        google.maps.drawing.OverlayType.POLYGON,
        google.maps.drawing.OverlayType.RECTANGLE
      ]
    },
    circleOptions: {
      fillOpacity: 0
    },
    polygonOptions: {
      fillOpacity: 0
    },
    rectangleOptions: {
      fillOpacity: 0
    }
  });
  drawingManager.setMap(map);
}

2. קוראים לפונקציה setUpDrawingTools() בפונקציה initMap() אחרי שיוצרים את אובייקט המפה

function initMap() {
  map = new google.maps.Map(document.getElementById('map'), {
    center: {lat: 40.744593, lng: -73.990370}, // Manhattan, New York.
    zoom: 12
  });

  setUpDrawingTools();
}

3. טוענים מחדש את index.html ומוודאים שכלי הציור מוצגים. כדאי גם לבדוק שאפשר להשתמש בהם כדי לצייר עיגולים, מלבנים וצורות של מצולעים.

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

טיפול באירועי שרטוט

כדי ליצור שאילתות SQL, צריך קוד שיטפל באירועים שמופעלים כשמשתמש מסיים לצייר צורה, בדיוק כמו שצריך את הקואורדינטות של הצורות המצוירות.

נוסיף קוד בשביל זה בשלב מאוחר יותר, אבל בינתיים ניצור שלוש פונקציות ריקות לטיפול באירועים rectanglecomplete, circlecomplete ו-polygoncomplete. בשלב הזה, אין צורך להפעיל קוד במטפלים.

מוסיפים את הקוד הבא לתחתית הפונקציה setUpDrawingTools():

drawingManager.addListener('rectanglecomplete', rectangle => {
    // We will add code here in a later step.
});
drawingManager.addListener('circlecomplete', circle => {
  // We will add code here in a later step.
});

drawingManager.addListener('polygoncomplete', polygon => {
  // We will add code here in a later step.
});

אפשר למצוא דוגמה פעילה של הקוד הזה בעותק המקומי של המאגר, בתיקייה step2: step2/map.html.

6. שימוש ב-BigQuery Client API

השימוש ב-Google BigQuery Client API יחסוך לכם את הצורך לכתוב הרבה קוד סטנדרטי שנדרש כדי ליצור את הבקשות, לנתח את התשובות ולטפל באימות. ב-codelab הזה נעשה שימוש ב-BigQuery API דרך ספריית הלקוח של Google APIs ל-JavaScript, כי אנחנו מפתחים אפליקציה מבוססת-דפדפן.

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

הערה: באפליקציות שלכם, אתם יכולים להשתמש בכל לקוח שתרצו כדי לשלוח שאילתות. ‫ BigQuery API תומך בבקשות HTTP ומשתמש בנתוני JSON לבקשות ולתשובות.

הוספת Google Client API ל-JavaScript

תשתמשו ב-Google Client API for Javascript כדי להריץ שאילתות ב-BigQuery. בעותק של index.html (בתיקייה work), טוענים את ה-API באמצעות תג <script> כמו בדוגמה הבאה. ממקמים את התג מיד מתחת לתג <script> שבו נטען Maps API:

<script src='https://apis.google.com/js/client.js'></script>

אחרי טעינת Google Client API, צריך לתת למשתמש הרשאה לגשת לנתונים ב-BigQuery. כדי לעשות את זה, אפשר להשתמש ב-OAuth 2.0. קודם צריך להגדיר פרטי כניסה בפרויקט במסוף Google Cloud.

יצירת פרטי כניסה מסוג OAuth 2.0

1. במסוף Google Cloud, בתפריט Navigation, בוחרים באפשרות APIs & Services > Credentials.

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

כדי לעשות זאת, לוחצים על הכרטיסייה מסך הסכמה ל-OAuth. 2. צריך להוסיף את BigQuery API להיקפים של האסימון הזה. בקטע Scopes for Google APIs (היקפי הרשאות לממשקי API של Google), לוחצים על הלחצן Add Scope (הוספת היקף הרשאות). 3. ברשימה, מסמנים את התיבה לצד הערך Big Query API עם ההיקף ../auth/bigquery. 4. לוחצים על הוספה. 5. מזינים שם בשדה 'שם האפליקציה'. 6. לוחצים על שמירה כדי לשמור את ההגדרות. 7. בשלב הבא תיצרו את מזהה הלקוח ב-OAuth. כדי לעשות זאת, לוחצים על Create Credentials (יצירת פרטי כניסה):

8. בתפריט הנפתח, לוחצים על מזהה לקוח OAuth.
9. בקטע Application Type (סוג האפליקציה) בוחרים באפשרות Web application (אפליקציית אינטרנט).
10. בשדה Application Name (שם האפליקציה), מקלידים שם לפרויקט. לדוגמה, BigQuery and Maps.
11. בקטע Restrictions (הגבלות), בשדה Authorized JavaScript Origins (מקורות JavaScript מורשים), מזינים את כתובת ה-URL של localhost, כולל מספרי היציאות. לדוגמה: http://localhost:8887

טיפ: צריך להזין את כתובת ה-URL של המקור כ-localhost ולא כ-127.0.0.1.

12. לוחצים על הלחצן יצירה.

יופיע חלון קופץ עם מזהה הלקוח וסוד הלקוח. כדי לבצע אימות מול BigQuery, צריך את מזהה הלקוח. מעתיקים את הקוד ומדביקים אותו ב-work/index.html כמשתנה JavaScript גלובלי חדש בשם clientId.

let clientId = 'YOUR_CLIENT_ID';

7. הרשאה ואתחול

כדי לאתחל את המפה, צריך לתת למשתמש הרשאה לגשת ל-BigQuery בדף האינטרנט. בדוגמה הזו אנחנו משתמשים ב-OAuth 2.0 כמו שמתואר בקטע ההרשאה במסמכי התיעוד של JavaScript Client API. כדי לשלוח שאילתות, צריך להשתמש במזהה הלקוח ב-OAuth ובמזהה הפרויקט.

כש-Google Client API נטען בדף האינטרנט, צריך לבצע את השלבים הבאים:

• נותנים הרשאה למשתמש.
• אם יש הרשאה, טוענים את BigQuery API.
• טוענים ומאתחלים את המפה.

בדוגמה step3/map.html אפשר לראות איך ייראה דף ה-HTML המוגמר.

אישור המשתמש

משתמש הקצה של האפליקציה צריך לאשר לאפליקציה לגשת לנתונים ב-BigQuery בשמו. הלוגיקה של OAuth מנוהלת על ידי Google Client API for JavaScript.

ביישום בעולם האמיתי, יש הרבה אפשרויות לשילוב של שלב ההרשאה.

לדוגמה, אפשר לקרוא לפונקציה authorize() מרכיב בממשק המשתמש כמו לחצן, או לעשות זאת כשהדף נטען. בדוגמה הזו בחרנו לאשר את המשתמש אחרי ש-Google Client API for JavaScript נטען, באמצעות פונקציית קריאה חוזרת בשיטה gapi.load().

כותבים קוד מיד אחרי התג <script> שמעמיס את Google Client API for Javascript כדי להעמיס גם את ספריית הלקוח וגם את מודול האימות, וכך אפשר לאמת את המשתמש באופן מיידי.

<script src='https://apis.google.com/js/client.js'></script>
<script type='text/javascript'>
  gapi.load('client:auth', authorize);
</script>

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

לאחר ההרשאה, טוענים את BigQuery API

אחרי שהמשתמש מקבל הרשאה, טוענים את BigQuery API.

קודם כול, מתקשרים אל gapi.auth.authorize() עם המשתנה clientId שהוספתם בשלב הקודם. הטיפול בתגובה מתבצע בפונקציית קריאה חוזרת בשם handleAuthResult.

הפרמטר immediate קובע אם יוצג למשתמש חלון קופץ. מגדירים את הערך true כדי למנוע את הצגת חלון הקופץ של ההרשאה אם המשתמש כבר קיבל הרשאה.

מוסיפים לדף פונקציה בשם handleAuthResult(). הפונקציה צריכה לקבל פרמטר authresult, שיאפשר לכם לשלוט בזרימת הלוגיקה בהתאם להצלחה או לכישלון של אישור המשתמש.

מוסיפים גם פונקציה בשם loadApi לטעינת BigQuery API אם המשתמש מורשה בהצלחה.

מוסיפים לוגיקה לפונקציה handleAuthResult() כדי להפעיל את loadApi() אם מועבר לפונקציה אובייקט authResult, ואם למאפיין error של האובייקט יש ערך של false.

מוסיפים קוד לפונקציה loadApi() כדי לטעון את BigQuery API באמצעות השיטה gapi.client.load().

let clientId = 'your-client-id-here';
let scopes = 'https://www.googleapis.com/auth/bigquery';

// Check if the user is authorized.
function authorize(event) {
  gapi.auth.authorize({client_id: clientId, scope: scopes, immediate: false}, handleAuthResult);
  return false;
}

// If authorized, load BigQuery API
function handleAuthResult(authResult) {
  if (authResult && !authResult.error) {
    loadApi();
    return;
  }
  console.error('Not authorized.')  
}

// Load BigQuery client API
function loadApi(){
  gapi.client.load('bigquery', 'v2');
}

טעינת המפה

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

כדי לעשות את זה, קוראים לפונקציה initMap() מהשיטה then() אחרי השיטה load() באובייקט gapi.client.

// Load BigQuery client API
function loadApi(){
  gapi.client.load('bigquery', 'v2').then(
   () => initMap()
  );
}

טיפ: מסירים את הפרמטר &callback=initMap מתג ה-JavaScript של Maps API. הפונקציה initMap נקראת עכשיו מהפונקציה loadApi() אחרי ש-BigQuery API נטען.

8. מושגים ב-BigQuery API

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

הקוד המלא של השלב הזה נמצא בקובץ step4/map.html.

שליחת בקשה

מוסיפים פונקציית JavaScript ל-work/index.html כדי לשלוח שאילתה באמצעות ה-API, וכמה משתנים לאחסון הערכים של מערך הנתונים ושל הפרויקט ב-BigQuery שמכילים את הטבלה לשאילתה, ושל מזהה הפרויקט שיחויב על כל העלויות.

הערה: לפני שמריצים את הקוד, מחליפים את 'datasetId', 'publicProjectId' ו-'billingProjectId' בערכים מפרויקטי BigQuery הרלוונטיים. הסבר על כך מופיע בשלב הבא.

let datasetId = 'your_dataset_id';
let billingProjectId = 'your_project_id';
let publicProjectId = 'bigquery-public-data';

function sendQuery(queryString){
  let request = gapi.client.bigquery.jobs.query({
      'query': queryString,
      'timeoutMs': 30000,
      'datasetId': datasetId,
      'projectId': billingProjectId,
      'useLegacySql':false
  });
  request.execute(response => {
      //code to handle the query response goes here.
  });
}

הערה: מערך נתונים הוא אוסף של טבלאות. פרטים נוספים זמינים במאמרי העזרה של BigQuery.

בדיקת הסטטוס של משימה

הפונקציה checkJobStatus שבהמשך מראה איך לבדוק את סטטוס העבודה באופן תקופתי, באמצעות שיטת ה-API‏ get וה-jobId שמוחזר מבקשת השאילתה המקורית. הנה דוגמה להפעלה כל 500 אלפיות השנייה עד לסיום העבודה.

let jobCheckTimer;

function checkJobStatus(jobId){
  let request = gapi.client.bigquery.jobs.get({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response =>{
    if (response.status.errorResult){
      // Handle any errors.
      console.log(response.status.error);
      return;
    }

    if (response.status.state == 'DONE'){
      // Get the results.
      clearTimeout(jobCheckTimer);
      getQueryResults(jobId);
      return;
    }
    // Not finished, check again in a moment.
    jobCheckTimer = setTimeout(checkJobStatus, 500, [jobId]);    
  });
}

משנים את method‏ sendQuery כדי להפעיל את method‏ checkJobStatus() כקריאה חוזרת בשיחה request.execute(). מעבירים את מזהה המשרה אל checkJobStatus. הערך הזה מוצג באובייקט התגובה כ-jobReference.jobId.

function sendQuery(queryString){
  let request = gapi.client.bigquery.jobs.query({
      'query': queryString,
      'timeoutMs': 30000,
      'datasetId': datasetId,
      'projectId': billingProjectId,
      'useLegacySql':false
  });
  request.execute(response => checkJobStatus(response.jobReference.jobId));
}

קבלת התוצאות של שאילתה

כדי לקבל את תוצאות השאילתה אחרי שהיא מסיימת לפעול, משתמשים בקריאת ה-API‏ jobs.getQueryResults. מוסיפים לדף פונקציה בשם getQueryResults() שמקבלת פרמטר של jobId:

function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    // Do something with the results.
  })
}

9. שליחת שאילתות לנתוני מיקום באמצעות BigQuery API

יש שלוש דרכים להשתמש ב-SQL כדי להריץ שאילתות מרחביות על נתונים ב-BigQuery:

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

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

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

דוגמה לקוד שעובד בשלב הזה נמצאת בקובץ step4/map.html.

שאילתות מלבניות

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

כדי להשתמש בצורה שמשתמש צייר, צריך לשנות את הקוד בקובץ index.html כדי לטפל באירוע הציור שמופעל כשמציירים מלבן. בדוגמה הזו, הקוד משתמש ב-getBounds() באובייקט המלבן כדי לקבל אובייקט שמייצג את גודל המלבן בקואורדינטות של המפה, ומעביר אותו לפונקציה שנקראת rectangleQuery:

drawingManager.addListener('rectanglecomplete', rectangle => rectangleQuery(rectangle.getBounds()));

הפונקציה rectangleQuery צריכה להשתמש בקואורדינטות של הפינה הימנית העליונה (צפון-מזרח) והפינה השמאלית התחתונה (דרום-מערב) כדי ליצור השוואה של גדול מ/קטן מול כל שורה בטבלת BigQuery. הנה דוגמה לשאילתה של טבלה עם עמודות בשם 'pickup_latitude' ו-'pickup_longitude' שבהן מאוחסנים ערכי המיקום.

ציון הטבלה ב-BigQuery

כדי לשלוח שאילתה לטבלה באמצעות BigQuery API, צריך לספק את שם הטבלה בפורמט מלא בשאילתת ה-SQL. הפורמט ב-SQL סטנדרטי הוא project.dataset.tablename. ב-SQL מדור קודם, התוצאה היא project.dataset.tablename.

יש הרבה טבלאות של נסיעות במוניות בניו יורק. כדי לראות אותם, עוברים אל מסוף BigQuery באינטרנט ומרחיבים את פריט התפריט 'מערכי נתונים ציבוריים'. מוצאים את מערך הנתונים שנקרא new_york ומרחיבים אותו כדי לראות את הטבלאות. בוחרים את הטבלה Yellow Taxi trips‏ (bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2016).

ציון מזהה הפרויקט

בקריאה ל-API צריך לציין את שם הפרויקט שלכם ב-Google Cloud Platform למטרות חיוב. ב-codelab הזה, זה לא אותו פרויקט שמכיל את הטבלה. אם הייתם עובדים עם טבלה שיצרתם בפרויקט שלכם על ידי העלאת נתונים, מזהה הפרויקט הזה היה זהה לזה שמופיע בהצהרת ה-SQL.

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

מוסיפים עותק של index.html למשתני JavaScript גלובליים בשם billingProjectId, publicProjectId, datasetId ו-tableName.

מאתחלים את המשתנים 'publicProjectId', 'datasetId' ו-'tableName' עם הפרטים מפרויקט מערכי הנתונים הציבוריים של BigQuery. מפעילים את billingProjectId עם מזהה הפרויקט שלכם (זה שיצרתם בשלב 'הגדרת הסביבה' בתחילת ה-codelab הזה).

let billingProjectId = 'YOUR_PROJECT_ID';
let publicProjectId = 'bigquery-public-data';
let datasetId = 'new_york_taxi_trips';
let tableName = 'tlc_yellow_trips_2016';

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

עכשיו מוסיפים לקוד שתי פונקציות כדי ליצור את ה-SQL ולשלוח את השאילתה ל-BigQuery באמצעות הפונקציה sendQuery שיצרתם בשלב הקודם.

הפונקציה הראשונה צריכה להיקרא rectangleSQL() ולקבל שני ארגומנטים, זוג אובייקטים מסוג google.Maps.LatLng שמייצגים את פינות המלבן בקואורדינטות של המפה.

הפונקציה השנייה צריכה להיקרא rectangleQuery(). הפעולה הזו מעבירה את טקסט השאילתה לפונקציה sendQuery.

let billingProjectId = 'YOUR_PROJECT_ID';
let publicProjectId = 'bigquery-public-data';
let datasetId = 'new_york';
let tableName = 'tlc_yellow_trips_2016';

function rectangleQuery(latLngBounds){
  let queryString = rectangleSQL(latLngBounds.getNorthEast(), latLngBounds.getSouthWest());
  sendQuery(queryString);
}

function rectangleSQL(ne, sw){
  let queryString = 'SELECT pickup_latitude, pickup_longitude '
  queryString +=  'FROM `' + publicProjectId +'.' + datasetId + '.' + tableName + '`'
  queryString += ' WHERE pickup_latitude > ' + sw.lat();
  queryString += ' AND pickup_latitude < ' + ne.lat();
  queryString += ' AND pickup_longitude > ' + sw.lng();
  queryString += ' AND pickup_longitude < ' + ne.lng();
  return queryString;
}

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

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

10. הדמיה של התשובה

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

הקוד המלא של השלב הזה זמין בקובץ step5/map.html.

כדי שכמות הנתונים שמועברת לדף האינטרנט תהיה בגודל סביר במסגרת ה-codelab הזה, צריך לשנות את הפונקציה rectangleSQL() ולהוסיף הצהרה שמגבילה את התגובה ל-10,000 שורות. בדוגמה שלמטה, הערך הזה מצוין במשתנה גלובלי שנקרא recordLimit, כדי שכל פונקציות השאילתות יוכלו להשתמש באותו ערך.

let recordLimit = 10000;
function rectangleSQL(ne, sw){
  var queryString = 'SELECT pickup_latitude, pickup_longitude '
  queryString +=  'FROM `' + publicProjectId +'.' + datasetId + '.' + tableName + '`'
  queryString += ' WHERE pickup_latitude > ' + sw.lat();
  queryString += ' AND pickup_latitude < ' + ne.lat();
  queryString += ' AND pickup_longitude > ' + sw.lng();
  queryString += ' AND pickup_longitude < ' + ne.lng();
  queryString += ' LIMIT ' + recordLimit;
  return queryString;
}

כדי לראות את צפיפות המיקומים, אפשר להשתמש במפת חום. לשם כך, יש בממשק Maps JavaScript API מחלקה בשם HeatmapLayer. השכבה HeatmapLayer מקבלת מערך של קואורדינטות של קו רוחב וקו אורך, כך שקל מאוד להמיר את השורות שמוחזרות מהשאילתה למפת חום.

בפונקציה getQueryResults, מעבירים את המערך response.result.rows לפונקציית JavaScript חדשה בשם doHeatMap(), שתצור מפת חום.

לכל שורה יהיה מאפיין בשם f שהוא מערך של עמודות. לכל עמודה יהיה מאפיין v שמכיל את הערך.

הקוד צריך לעבור בלולאה על העמודות בכל שורה ולחלץ את הערכים.

בשאילתת ה-SQL, ביקשתם רק את ערכי קו הרוחב וקו האורך של נקודות האיסוף של המוניות, ולכן יהיו רק שתי עמודות בתגובה.

אל תשכחו להתקשר אל setMap() בשכבת מפת החום אחרי שהקציתם לה את מערך המיקומים. כך הוא יוצג במפה.

לדוגמה:

function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => doHeatMap(response.result.rows))
}

let heatmap;

function doHeatMap(rows){
  let heatmapData = [];
  if (heatmap != null){
    heatmap.setMap(null);
  }
  for (let i = 0; i < rows.length; i++) {
      let f = rows[i].f;
      let coords = { lat: parseFloat(f[0].v), lng: parseFloat(f[1].v) };
      let latLng = new google.maps.LatLng(coords);
      heatmapData.push(latLng);
  }
  heatmap = new google.maps.visualization.HeatmapLayer({
      data: heatmapData
  });
  heatmap.setMap(map);
}

בשלב הזה, תוכלו:

• פתיחת הדף ומתן הרשאה ל-BigQuery
• ציור מלבן במקום כלשהו בניו יורק
• תוצאות השאילתה יוצגו כתרשים מפת חום.

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

11. שאילתה לפי רדיוס סביב נקודה

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

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

דוגמה לקוד של השלב הזה כלולה במאגר הקוד כ-step6/map.html.

drawingManager.addListener('circlecomplete', circle => circleQuery(circle));

בעותק של index.html, מוסיפים שתי פונקציות ריקות חדשות: circleQuery() ו-haversineSQL().

לאחר מכן, מוסיפים circlecomplete event handler שמעביר את המרכז והרדיוס לפונקציה חדשה שנקראת circleQuery().

הפונקציה circleQuery() תפעיל את הפונקציה haversineSQL() כדי ליצור את ה-SQL של השאילתה, ואז תשלח את השאילתה על ידי הפעלת הפונקציה sendQuery(), כמו שמוצג בקוד הדוגמה הבא.

function circleQuery(circle){
  let queryString = haversineSQL(circle.getCenter(), circle.radius);
  sendQuery(queryString);
}

// Calculate a circular area on the surface of a sphere based on a center and radius.
function haversineSQL(center, radius){
  let queryString;
  let centerLat = center.lat();
  let centerLng = center.lng();
  let kmPerDegree = 111.045;

  queryString = 'CREATE TEMPORARY FUNCTION Degrees(radians FLOAT64) RETURNS FLOAT64 LANGUAGE js AS ';
  queryString += '""" ';
  queryString += 'return (radians*180)/(22/7);';
  queryString += '"""; ';

  queryString += 'CREATE TEMPORARY FUNCTION Radians(degrees FLOAT64) RETURNS FLOAT64 LANGUAGE js AS';
  queryString += '""" ';
  queryString += 'return (degrees*(22/7))/180;';
  queryString += '"""; ';

  queryString += 'SELECT pickup_latitude, pickup_longitude '
  queryString += 'FROM `' + publicProjectId +'.' + datasetId + '.' + tableName + '` ';
  queryString += 'WHERE '
  queryString += '(' + kmPerDegree + ' * DEGREES( ACOS( COS( RADIANS('
  queryString += centerLat;
  queryString += ') ) * COS( RADIANS( pickup_latitude ) ) * COS( RADIANS( ' + centerLng + ' ) - RADIANS('
  queryString += ' pickup_longitude ';
  queryString += ') ) + SIN( RADIANS('
  queryString += centerLat;
  queryString += ') ) * SIN( RADIANS( pickup_latitude ) ) ) ) ) ';

  queryString += ' < ' + radius/1000;
  queryString += ' LIMIT ' + recordLimit;
  return queryString;
}

נסה בעצמך!

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

12. שאילתות על צורות שרירותיות

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

אחת התכונות החזקות מאוד של BigQuery שאפשר להשתמש בהן למטרה הזו היא פונקציות שהוגדרו על ידי המשתמש (UDF). פונקציות UDF מריצות קוד JavaScript בתוך שאילתת SQL.

קוד עובד לשלב הזה נמצא ב- step7/map.html.

פונקציות UDF ב-BigQuery API

הגישה ל-UDF באמצעות BigQuery API שונה מעט מהגישה דרך מסוף האינטרנט: צריך לקרוא ל-jobs.insert method.

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

דוגמה שעובדת לגישה הזו נמצאת בקובץ step7/map.html.

function polygonQuery(polygon) {
  let request = gapi.client.bigquery.jobs.insert({
    'projectId' : billingProjectId,
      'resource' : {
        'configuration':
          {
            'query':
            {
              'query': polygonSql(polygon),
              'useLegacySql': false
            }
          }
      }
  });
  request.execute(response => checkJobStatus(response.jobReference.jobId));
}

שאילתת ה-SQL בנויה כך:

function polygonSql(poly){
  let queryString = 'CREATE TEMPORARY FUNCTION pointInPolygon(latitude FLOAT64, longitude FLOAT64) ';
  queryString += 'RETURNS BOOL LANGUAGE js AS """ ';
  queryString += 'var polygon=' + JSON.stringify(poly) + ';';
  queryString += 'var vertx = [];';
  queryString += 'var verty = [];';
  queryString += 'var nvert = 0;';
  queryString += 'var testx = longitude;';
  queryString += 'var testy = latitude;';
  queryString += 'for(coord in polygon){';
  queryString += '  vertx[nvert] = polygon[coord][0];';
  queryString += '  verty[nvert] = polygon[coord][1];';
  queryString += '  nvert ++;';
  queryString += '}';
  queryString += 'var i, j, c = 0;';
  queryString += 'for (i = 0, j = nvert-1; i < nvert; j = i++) {';
  queryString += '  if ( ((verty[i]>testy) != (verty[j]>testy)) &&(testx < (vertx[j]-vertx[i]) * (testy-verty[i]) / (verty[j]-verty[i]) + vertx[i]) ){';
  queryString += '    c = !c;';
  queryString += '  }';
  queryString += '}';
  queryString += 'return c;';
  queryString += '"""; ';
  queryString += 'SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, pickup_datetime ';
  queryString += 'FROM `' + publicProjectId + '.' + datasetId + '.' + tableName + '` ';
  queryString += 'WHERE pointInPolygon(pickup_latitude, pickup_longitude) = TRUE ';
  queryString += 'LIMIT ' + recordLimit;
  return queryString;
}

יש כאן שני דברים שקורים. קודם כול, הקוד יוצר את ההצהרה CREATE TEMPORARY FUNCTION שמכילה את קוד JavaScript כדי לבדוק אם נקודה מסוימת נמצאת בתוך פוליגון. הקואורדינטות של הפוליגון מוכנסות באמצעות קריאה לשיטה JSON.stringify(poly) כדי להמיר מערך JavaScript של זוגות קואורדינטות x,y למחרוזת. אובייקט הפוליגון מועבר כארגומנט לפונקציה שיוצרת את ה-SQL.

בשלב השני, הקוד יוצר את הצהרת ה-SQL הראשית SELECT. הפונקציה UDF נקראת בביטוי WHERE בדוגמה הזו.

שילוב עם Maps API

כדי להשתמש בזה עם ספריית הציור של Maps API, צריך לשמור את הפוליגון שצייר המשתמש ולהעביר אותו לחלק של ה-UDF בשאילתת ה-SQL.

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

drawingManager.addListener('polygoncomplete', polygon => {
  let path = polygon.getPaths().getAt(0);
  let queryPolygon = path.map(element => {
    return [element.lng(), element.lat()];
  });
  polygonQuery(queryPolygon);
});

אז הפונקציה polygonQuery יכולה ליצור את פונקציות ה-JavaScript של ה-UDF כמחרוזת, וגם את הצהרת ה-SQL שתפעיל את פונקציית ה-UDF.

תוכלו לראות דוגמה שעובדת בקובץ step7/map.html.

פלט לדוגמה

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

13. להרחיב את הידע

ריכזנו כאן כמה הצעות להרחבת ה-codelab הזה כדי לבחון היבטים אחרים של הנתונים. דוגמה מעשית לרעיונות האלה אפשר למצוא בכתובת step8/map.html במאגר הקוד.

נטישות בתהליך המיפוי

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

לדוגמה, נבדוק איפה נהגי מוניות נוטים להוריד אנשים שמזמינים איסוף באזור בניין האמפייר סטייט.

משנים את הקוד של הצהרת ה-SQL ב-polygonSql() כדי לבקש את העמודות האלה בנוסף למיקום האיסוף.

function polygonSql(poly){
  let queryString = 'CREATE TEMPORARY FUNCTION pointInPolygon(latitude FLOAT64, longitude FLOAT64) ';
  queryString += 'RETURNS BOOL LANGUAGE js AS """ ';
  queryString += 'var polygon=' + JSON.stringify(poly) + ';';
  queryString += 'var vertx = [];';
  queryString += 'var verty = [];';
  queryString += 'var nvert = 0;';
  queryString += 'var testx = longitude;';
  queryString += 'var testy = latitude;';
  queryString += 'for(coord in polygon){';
  queryString += '  vertx[nvert] = polygon[coord][0];';
  queryString += '  verty[nvert] = polygon[coord][1];';
  queryString += '  nvert ++;';
  queryString += '}';
  queryString += 'var i, j, c = 0;';
  queryString += 'for (i = 0, j = nvert-1; i < nvert; j = i++) {';
  queryString += '  if ( ((verty[i]>testy) != (verty[j]>testy)) &&(testx < (vertx[j]-vertx[i]) * (testy-verty[i]) / (verty[j]-verty[i]) + vertx[i]) ){';
  queryString += '    c = !c;';
  queryString += '  }';
  queryString += '}';
  queryString += 'return c;';
  queryString += '"""; ';

  queryString += 'SELECT pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude, pickup_datetime ';
  queryString += 'FROM `' + publicProjectId + '.' + datasetId + '.' + tableName + '` ';
  queryString += 'WHERE pointInPolygon(pickup_latitude, pickup_longitude) = TRUE ';
  queryString += 'LIMIT ' + recordLimit;
  return queryString;
}

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

מוסיפים כמה משתנים כדי לאפשר לכם לשנות את העמודות שבהן אתם משתמשים לנתוני מפת החום.

// Show query results as a Heatmap.
function doHeatMap(rows){
  let latCol = 2;
  let lngCol = 3;
  let heatmapData = [];
  if (heatmap!=null){
    heatmap.setMap(null);
  }
  for (let i = 0; i < rows.length; i++) {
      let f = rows[i].f;
      let coords = { lat: parseFloat(f[latCol].v), lng: parseFloat(f[lngCol].v) };
      let latLng = new google.maps.LatLng(coords);
      heatmapData.push(latLng);
  }
  heatmap = new google.maps.visualization.HeatmapLayer({
      data: heatmapData,
      maxIntensity: 20
  });
  heatmap.setMap(map);
}

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

עיצוב המפה הבסיסית

כשיוצרים מפת Google באמצעות Maps JavaScript API, אפשר להגדיר את סגנון המפה באמצעות אובייקט JSON. כשיוצרים תרשימים של נתונים, לפעמים כדאי להשתיק את הצבעים במפה. אתם יכולים ליצור סגנונות מפה ולנסות אותם באמצעות אשף הסגנונות של Google Maps API בכתובת mapstyle.withgoogle.com.

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

function initMap() {
  map = new google.maps.Map(document.getElementById('map'), {
        center: {lat: 40.744593, lng: -73.990370}, // Manhattan, New York.
  zoom: 12,
  styles: [
    {
        "elementType": "geometry",
          "stylers": [
            {
              "color": "#f5f5f5"
            }
          ]
        },
        {
          "elementType": "labels.icon",
            "stylers": [
              {
                "visibility": "on"
              }
            ]
        },
        {
          "featureType": "water",
            "elementType": "labels.text.fill",
              "stylers": [
                {
                  "color": "#9e9e9e"
                }
              ]
        }
      ]
    });
  setUpDrawingTools();
}

בדוגמה של סגנון המפה שבהמשך מוצגת מפה בגווני אפור עם תוויות של נקודות עניין.

[
  {
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#f5f5f5"
      }
    ]
  },
  {
    "elementType": "labels.icon",
    "stylers": [
      {
        "visibility": "on"
      }
    ]
  },
  {
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#616161"
      }
    ]
  },
  {
    "elementType": "labels.text.stroke",
    "stylers": [
      {
        "color": "#f5f5f5"
      }
    ]
  },
  {
    "featureType": "administrative.land_parcel",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#bdbdbd"
      }
    ]
  },
  {
    "featureType": "poi",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#eeeeee"
      }
    ]
  },
  {
    "featureType": "poi",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#757575"
      }
    ]
  },
  {
    "featureType": "poi.park",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#e5e5e5"
      }
    ]
  },
  {
    "featureType": "poi.park",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#9e9e9e"
      }
    ]
  },
  {
    "featureType": "road",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#ffffff"
      }
    ]
  },
  {
    "featureType": "road.arterial",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#757575"
      }
    ]
  },
  {
    "featureType": "road.highway",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#dadada"
      }
    ]
  },
  {
    "featureType": "road.highway",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#616161"
      }
    ]
  },
  {
    "featureType": "road.local",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#9e9e9e"
      }
    ]
  },
  {
    "featureType": "transit.line",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#e5e5e5"
      }
    ]
  },
  {
    "featureType": "transit.station",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#eeeeee"
      }
    ]
  },
  {
    "featureType": "water",
    "elementType": "geometry",
    "stylers": [
      {
        "color": "#c9c9c9"
      }
    ]
  },
  {
    "featureType": "water",
    "elementType": "labels.text.fill",
    "stylers": [
      {
        "color": "#9e9e9e"
      }
    ]
  }
]

שליחת משוב למשתמש

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

מוסיפים לממשק המשתמש של דף האינטרנט את התגובה של הפונקציה checkJobStatus(), וגרפיקה מונפשת שמציינת שהשאילתה נמצאת בתהליך.

המידע שמוצג כולל את משך השאילתה, כמות הנתונים שהוחזרו וכמות הנתונים שעברו עיבוד.

מוסיפים קוד HTML אחרי המפה <div> כדי ליצור חלונית בדף שתציג את מספר השורות שהוחזרו על ידי שאילתה, את הזמן שלקח להריץ את השאילתה ואת כמות הנתונים שעברו עיבוד.

<div id="menu">
    <div id="stats">
        <h3>Statistics:</h3>
        <table>
            <tr>
                <td>Total Locations:</td><td id="rowCount"> - </td>
            </tr>
            <tr>
                <td>Query Execution:</td><td id="duration"> - </td>
            </tr>
            <tr>
                <td>Data Processed:</td><td id="bytes"> - </td>
            </tr>
        </table>
    </div>
</div>

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

#menu {
  position: absolute; 
  background: rgba(255, 255, 255, 0.8); 
  z-index: 1000; 
  top: 50px; 
  left: 10px; 
  padding: 15px;
}
#menu h1 {
  margin: 0 0 10px 0;
  font-size: 1.75em;
}
#menu div {
  margin: 5px 0px;
}

אפשר להוסיף את הגרפיקה המונפשת לדף אבל להסתיר אותה עד שצריך, ולהשתמש בקוד JavaScript ו-CSS כדי להציג אותה כשפועלת משימת BigQuery.

מוסיפים קוד HTML כדי להציג גרפיקה מונפשת. יש קובץ תמונה בשם loader.gif בתיקייה img במאגר הקוד.

<img id="spinner" src="img/loader.gif">

מוסיפים קצת CSS כדי למקם את התמונה ולהסתיר אותה כברירת מחדל עד שצריך אותה.

#spinner {
  position: absolute; 
  top: 50%; 
  left: 50%; 
  margin-left: -32px; 
  margin-top: -32px; 
  opacity: 0; 
  z-index: -1000;
}

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

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

function updateStatus(response){
  if(response.statistics){
    let durationMs = response.statistics.endTime - response.statistics.startTime;
    let durationS = durationMs/1000;
    let suffix = (durationS ==1) ? '':'s';
    let durationTd = document.getElementById("duration");
    durationTd.innerHTML = durationS + ' second' + suffix;
  }
  if(response.totalRows){
    let rowsTd = document.getElementById("rowCount");
    rowsTd.innerHTML = response.totalRows;
  }
  if(response.totalBytesProcessed){
    let bytesTd = document.getElementById("bytes");
    bytesTd.innerHTML = (response.totalBytesProcessed/1073741824) + ' GB';
  }
}

מפעילים את השיטה הזו כשמתקבלת תשובה לקריאה ל-checkJobStatus() וכשמאחזרים את תוצאות השאילתה. לדוגמה:

// Poll a job to see if it has finished executing.
function checkJobStatus(jobId){
  let request = gapi.client.bigquery.jobs.get({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    //Show progress to the user
    updateStatus(response);

    if (response.status.errorResult){
      // Handle any errors.
      console.log(response.status.error);
      return;
    }
    if (response.status.state == 'DONE'){
      // Get the results.
      clearTimeout(jobCheckTimer);
      getQueryResults(jobId);
      return;
    }
    // Not finished, check again in a moment.
    jobCheckTimer = setTimeout(checkJobStatus, 500, [jobId]); 
  });
}

// When a BigQuery job has completed, fetch the results.
function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    doHeatMap(response.result.rows);
    updateStatus(response);
  })
}

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

function fadeToggle(obj){
    if(obj.style.opacity==1){
        obj.style.opacity = 0;
        setTimeout(() => {obj.style.zIndex = -1000;}, 1000);
    } else {
        obj.style.zIndex = 1000;
        obj.style.opacity = 1;
    }
}

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

הקוד הזה קורא לפונקציה fadeToggle כשהמשתמש מסיים לצייר מלבן.

drawingManager.addListener('rectanglecomplete', rectangle => {
  //show an animation to indicate that something is happening.
  fadeToggle(document.getElementById('spinner'));
  rectangleQuery(rectangle.getBounds());
});

אחרי שמקבלים את התשובה לשאילתה, מתקשרים שוב אל fadeToggle() כדי להסתיר את הגרפיקה המונפשת.

// When a BigQuery job has completed, fetch the results.
function getQueryResults(jobId){
  let request = gapi.client.bigquery.jobs.getQueryResults({
    'projectId': billingProjectId,
    'jobId': jobId
  });
  request.execute(response => {
    doHeatMap(response.result.rows);
    //hide the animation.
    fadeToggle(document.getElementById('spinner'));
    updateStatus(response);
  })
}

הדף אמור להיראות בערך כך.

אפשר לראות את הדוגמה המלאה בקובץ step8/map.html.

14. מה חשוב לקחת בחשבון

יותר מדי סמנים

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

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

אופטימיזציה של שאילתות

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

השאילתות יהיו מהירות יותר אם תאחסנו את קווי הרוחב והאורך כערכים מסוג float ולא כמחרוזות.

ייצוא תוצאות מעניינות

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

החלק המשפטי המשעמם

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

משחקים עם יותר נתונים!

יש כמה מערכי נתונים ציבוריים ב-BigQuery עם עמודות של קווי רוחב ואורך, למשל מערכי הנתונים של מוניות בניו יורק משנת 2009 עד 2016, נתוני נסיעות של Uber ו-Lyft בניו יורק ומערך הנתונים GDELT.

15. מעולה!

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

מה השלב הבא?

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

במאמר מהו BigQuery אפשר לקרוא מידע נוסף על שירות מחסן הנתונים של Google, שפועל במודל ללא שרת (serverless) ויש לו מספיק מקום לפטה-בייט של מידע.

במדריך הזה מוסבר איך ליצור אפליקציה פשוטה באמצעות BigQuery API.

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

אפשר לעיין בדרכים אחרות להצגת נתונים במפות Google.

במדריך לתחילת העבודה עם Javascript Client API מוסבר על המושגים הבסיסיים של שימוש ב-Client API כדי לגשת לממשקי Google API אחרים.