پرس و جو و تجسم داده های مکان در BigQuery با پلتفرم نقشه های گوگل (جاوا اسکریپت)

1. بررسی اجمالی

نقشه ها می توانند ابزار بسیار قدرتمندی برای تجسم الگوهای موجود در یک مجموعه داده باشند که به نوعی با مکان مرتبط هستند. این رابطه می‌تواند نام یک مکان، یک مقدار طول و عرض جغرافیایی خاص، یا نام منطقه‌ای باشد که دارای مرز مشخصی مانند تراکت سرشماری یا کد پستی است.

هنگامی که این مجموعه داده ها بسیار بزرگ می شوند، پرس و جو و تجسم آنها با استفاده از ابزارهای معمولی دشوار است. با استفاده از Google BigQuery برای پرس و جو از داده ها و Google Maps API برای ساخت پرس و جو و تجسم خروجی، می توانید به سرعت الگوهای جغرافیایی در داده های خود را با تنظیمات یا کدگذاری بسیار کم و بدون نیاز به مدیریت یک سیستم برای ذخیره مجموعه داده های بسیار بزرگ کاوش کنید. .

چیزی که خواهی ساخت

در این نرم‌افزار کد، تعدادی پرس‌وجو را می‌نویسید و اجرا می‌کنید که نشان می‌دهد چگونه می‌توان بینش‌های مبتنی بر مکان را در مجموعه داده‌های عمومی بسیار بزرگ با استفاده از BigQuery ارائه کرد. همچنین یک صفحه وب ایجاد خواهید کرد که با استفاده از API جاوا اسکریپت پلتفرم Google Maps، نقشه را بارگیری می کند، سپس پرس و جوهای فضایی را در برابر همان مجموعه داده های عمومی بسیار بزرگ با استفاده از Google APIs Client Library برای جاوا اسکریپت و BigQuery API اجرا و تجسم می کند.

چیزی که یاد خواهید گرفت

• نحوه پرس و جو از مجموعه داده های مکان در مقیاس پتابایت در چند ثانیه با BigQuery ، با استفاده از پرس و جوهای SQL ، توابع تعریف شده توسط کاربر و BigQuery API
• چگونه می توان از پلتفرم نقشه های گوگل برای افزودن نقشه گوگل به یک صفحه وب استفاده کرد و کاربران را قادر ساخت تا شکل هایی را روی آن بکشند
• نحوه تجسم پرس و جوها در برابر مجموعه داده های بزرگ در نقشه گوگل مانند تصویر مثال زیر، که تراکم مکان های تاکسی را در سال 2016 از سفرهایی که از بلوک در اطراف ساختمان امپایر استیت شروع شده را نشان می دهد.

آنچه شما نیاز دارید

• دانش اولیه HTML، CSS، جاوا اسکریپت، SQL، و ابزار توسعه کروم
• یک مرورگر وب مدرن، مانند نسخه‌های اخیر کروم، فایرفاکس، سافاری یا اج.
• یک ویرایشگر متن یا IDE به انتخاب شما

تکنولوژی

BigQuery

BigQuery سرویس تجزیه و تحلیل داده های گوگل برای مجموعه داده های بسیار بزرگ است. دارای یک API RESTful است و از پرس و جوهای نوشته شده در SQL پشتیبانی می کند. اگر داده‌هایی با مقادیر طول و عرض جغرافیایی دارید، می‌توان از آنها برای جست‌وجوی داده‌های شما بر اساس مکان استفاده کرد. مزیت این است که شما می توانید به صورت بصری مجموعه داده های بسیار بزرگ را برای مشاهده الگوها بدون نیاز به مدیریت هیچ سرور یا زیرساخت پایگاه داده کاوش کنید. با استفاده از مقیاس پذیری عظیم و زیرساخت مدیریت شده BigQuery، می توانید بدون توجه به اینکه جداول شما چقدر بزرگ می شوند، در چند ثانیه پاسخ سوالات خود را دریافت کنید.

پلتفرم نقشه های گوگل

پلتفرم نقشه های گوگل دسترسی برنامه ریزی شده به نقشه، مکان و داده های مسیر گوگل را فراهم می کند. در حال حاضر بیش از 2 میلیون وب سایت و برنامه از آن برای ارائه نقشه های جاسازی شده و جستجوهای مبتنی بر مکان به کاربران خود استفاده می کنند.

لایه طراحی جاوا اسکریپت API پلتفرم Google Maps به شما امکان می دهد شکل ها را روی نقشه بکشید. اینها را می توان به ورودی تبدیل کرد تا پرس و جوهایی را در مقابل جداول BigQuery اجرا کنند که مقادیر طول و عرض جغرافیایی در ستون ها ذخیره شده است.

برای شروع به یک پروژه Google Cloud Platform با فعال کردن BigQuery و Maps API نیاز دارید.

2. راه اندازی

حساب کاربری گوگل

اگر قبلاً یک حساب Google (Gmail یا Google Apps) ندارید، باید یک حساب ایجاد کنید .

یک پروژه ایجاد کنید

نکته: پروژه ای که استفاده می کنید باید صورتحساب را فعال کرده باشد. اگر می‌خواهید از یک پروژه موجود استفاده کنید، مطمئن شوید که می‌توانید صورت‌حساب آن را فعال کنید.

به کنسول Google Cloud Platform ( consol.cloud.google.com ) وارد شوید و یک پروژه جدید ایجاد کنید. در بالای صفحه شما، یک منوی کشویی Project وجود دارد:

هنگامی که روی منوی کشویی این پروژه کلیک کنید، یک آیتم منو دریافت خواهید کرد که به شما امکان می دهد یک پروژه جدید ایجاد کنید:

در کادری که می گوید «نام جدیدی برای پروژه خود وارد کنید»، نامی برای پروژه جدید خود وارد کنید، به عنوان مثال «BigQuery Codelab»:

شناسه پروژه برای شما ایجاد می شود. شناسه پروژه یک نام منحصر به فرد در تمام پروژه های Google Cloud است. ID پروژه خود را به خاطر بسپارید، زیرا بعداً از آن استفاده خواهید کرد. نام فوق قبلاً گرفته شده است و برای شما کار نخواهد کرد. شناسه پروژه خود را در هر جایی که YOUR_PROJECT_ID را در این لبه کد مشاهده کردید وارد کنید.

فعال کردن صورتحساب

برای ثبت نام در BigQuery، از پروژه انتخاب شده یا ایجاد شده در مرحله قبل استفاده کنید. صورتحساب باید در این پروژه فعال باشد. پس از فعال شدن صورتحساب، می توانید BigQuery API را فعال کنید.

توجه: برای جستارهای اجرا شده در این کد لبه نباید هزینه‌ای دریافت کنید، اما اگر از سطح رایگان BigQuery (1 ترابایت درخواست داده در ماه) یا حداکثر سهمیه روزانه رایگان برای Maps API (25000 بارگیری نقشه در روز) فراتر رفتید. این امکان وجود دارد که شما متحمل هزینه هایی شوید.

نحوه فعال کردن صورتحساب بستگی به این دارد که آیا شما در حال ایجاد یک پروژه جدید یا فعال کردن مجدد صورتحساب برای یک پروژه موجود هستید.

Google یک دوره آزمایشی رایگان 12 ماهه برای استفاده از پلتفرم Google Cloud تا 300 دلار ارائه می دهد که ممکن است بتوانید برای این Codelab از آن استفاده کنید، جزئیات بیشتر را در https://cloud.google.com/free/ بیابید.

پروژه های جدید

هنگامی که یک پروژه جدید ایجاد می کنید، از شما خواسته می شود که انتخاب کنید کدام یک از حساب های صورتحساب خود را می خواهید به پروژه پیوند دهید. اگر فقط یک حساب صورت‌حساب دارید، آن حساب به طور خودکار به پروژه شما مرتبط می‌شود.

اگر حساب صورت‌حساب ندارید، قبل از اینکه بتوانید از بسیاری از ویژگی‌های Google Cloud Platform استفاده کنید، باید حسابی ایجاد کنید و صورت‌حساب را برای پروژه خود فعال کنید. برای ایجاد یک حساب صورت‌حساب جدید و فعال کردن صورت‌حساب برای پروژه خود، دستورالعمل‌های موجود در ایجاد یک حساب صورت‌حساب جدید را دنبال کنید.

پروژه های موجود

اگر پروژه‌ای دارید که صورت‌حساب آن را موقتاً غیرفعال کرده‌اید، می‌توانید صورت‌حساب را دوباره فعال کنید:

1. به کنسول Cloud Platform بروید.
2. از لیست پروژه ها، پروژه ای را برای فعال کردن مجدد صورتحساب انتخاب کنید.
3. منوی سمت چپ کنسول را باز کرده و Billing را انتخاب کنید . از شما خواسته می شود یک حساب صورتحساب را انتخاب کنید.
4. روی تنظیم حساب کلیک کنید.

یک حساب صورتحساب جدید ایجاد کنید

برای ایجاد یک حساب صورتحساب جدید:

1. به کنسول Cloud Platform بروید و وارد شوید یا اگر از قبل حساب کاربری ندارید، ثبت نام کنید.
2. منوی سمت چپ کنسول را باز کرده و Billing را انتخاب کنید
3. روی دکمه حساب صورت‌حساب جدید کلیک کنید. (توجه داشته باشید که اگر این اولین حساب صورت‌حساب شما نیست، ابتدا باید فهرست حساب‌های صورت‌حساب را با کلیک کردن روی نام حساب صورت‌حساب موجود در بالای صفحه، و سپس روی مدیریت حساب‌های صورت‌حساب باز کنید.)
4. نام حساب صورتحساب را وارد کنید و اطلاعات صورتحساب خود را وارد کنید. گزینه هایی که مشاهده می کنید به کشور آدرس صورتحساب شما بستگی دارد. توجه داشته باشید که برای حساب های ایالات متحده، نمی توانید وضعیت مالیات را پس از ایجاد حساب تغییر دهید.
5. روی ارسال کلیک کنید و صورتحساب را فعال کنید .

به‌طور پیش‌فرض، شخصی که حساب صورت‌حساب را ایجاد می‌کند، سرپرست صورت‌حساب حساب است.

برای اطلاعات در مورد تأیید حساب‌های بانکی و افزودن روش‌های پرداخت پشتیبان، به افزودن، حذف یا به‌روزرسانی روش پرداخت مراجعه کنید.

BigQuery API را فعال کنید

برای فعال کردن BigQuery API در پروژه خود، به صفحه BigQuery API Marketplace در کنسول بروید و روی دکمه آبی «Enable» کلیک کنید.

توجه : اگر قبلاً آدرس ایمیل خود را تأیید نکرده‌اید، ممکن است لازم باشد قبل از شروع استفاده از حساب صورت‌حساب جدید این کار را انجام دهید. برای اطلاعات در مورد تأیید آدرس ایمیل خود، به مبحث زیر مراجعه کنید آدرس ایمیل خود را تأیید کنید .

3. جستجوی داده های مکان در BigQuery

سه راه برای جستجوی داده های مکان ذخیره شده به عنوان مقادیر طول و عرض جغرافیایی در BigQuery وجود دارد.

• پرس و جوهای مستطیلی : ناحیه مورد نظر را به عنوان پرس و جوی مشخص کنید که همه ردیف ها را در محدوده حداقل و حداکثر طول و عرض جغرافیایی انتخاب می کند.
• پرس و جوهای شعاع : ناحیه مورد نظر را با محاسبه یک دایره به دور یک نقطه با استفاده از فرمول هاورسین و توابع ریاضی برای مدل سازی شکل زمین مشخص کنید.
• پرس و جوهای چند ضلعی : یک شکل سفارشی را مشخص کنید و از یک تابع تعریف شده توسط کاربر برای بیان منطق نقطه در چند ضلعی مورد نیاز برای آزمایش اینکه آیا طول و عرض جغرافیایی هر ردیف در داخل شکل قرار می گیرد استفاده کنید.

برای شروع، از ویرایشگر Query در بخش Big Query کنسول Google Cloud Platform استفاده کنید تا درخواست‌های زیر را در برابر داده‌های تاکسی نیویورک اجرا کنید.

SQL استاندارد در مقابل SQL قدیمی

BigQuery از دو نسخه SQL پشتیبانی می کند: Legacy SQL و Standard SQL . مورد دوم استاندارد ANSI 2011 است. برای اهداف این آموزش، ما از استاندارد SQL استفاده خواهیم کرد زیرا مطابقت بهتری با استانداردها دارد.

اگر می خواهید Legacy SQL را در ویرایشگر BigQuery اجرا کنید، می توانید این کار را با انجام موارد زیر انجام دهید:

1. روی دکمه "بیشتر" کلیک کنید
2. از منوی کشویی گزینه Query settings را انتخاب کنید
3. در زیر «گویش SQL»، دکمه رادیویی «Legacy» را انتخاب کنید
4. روی دکمه "ذخیره" کلیک کنید

پرس و جوهای مستطیل

ساخت کوئری های مستطیل در BigQuery کاملاً ساده است. شما فقط باید یک بند WHERE اضافه کنید که نتایج بازگردانده شده را محدود به نتایجی می کند که مکان هایی بین حداقل و حداکثر مقادیر برای طول و عرض جغرافیایی دارند.

مثال زیر را در کنسول BigQuery امتحان کنید. این پرس و جو برای برخی از آمار میانگین سفر برای سواری هایی است که در یک منطقه مستطیلی که شامل شهر میانی و منهتن پایینی است شروع شده است. دو مکان مختلف وجود دارد که می توانید امتحان کنید، بند دوم WHERE را برای اجرای پرس و جو در سفرهایی که در فرودگاه JFK شروع شده اند، لغو نظر کنید.

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_tip_pc	avg_fare_mile
2.52	12.03	9.97	22.39	5.97

JFK

avg_tip	avg_fare	میانگین_فاصله	avg_tip_pc	avg_fare_mile
9.22	48.49	41.19	22.48	4.36

پرس و جوهای شعاع

اگر کمی ریاضی بدانید، پرس و جوهای Radius نیز در SQL آسان ساخته می شوند. با استفاده از توابع ریاضی SQL Legacy BigQuery می‌توانید با استفاده از فرمول Haversine که یک ناحیه دایره‌ای یا کلاهک کروی روی سطح زمین را تقریب می‌کند، یک پرس و جوی SQL بسازید.

در اینجا یک عبارت BigQuery SQL برای یک پرس و جو دایره ای با مرکز 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

نتایج پرس و جو در زیر آمده است. می بینید که تفاوت های زیادی در میانگین انعام، کرایه، مسافت سفر، اندازه متناسب انعام با کرایه و میانگین کرایه در هر مایل رانندگی وجود دارد.

ساختمان امپایر استیت:

avg_tip	avg_fare	میانگین_فاصله	avg_tip_pc	avg_fare_mile
1.17	11.08	45.28	10.53	6.42

برانکس

avg_tip	avg_fare	میانگین_فاصله	avg_tip_pc	avg_fare_mile
0.52	17.63	4.75	4.74	10.9

پرس و جوهای چند ضلعی

SQL از پرس و جو با استفاده از اشکال دلخواه به جز مستطیل و دایره پشتیبانی نمی کند. BigQuery هیچ نوع داده هندسی بومی یا شاخص فضایی ندارد، بنابراین برای اجرای پرس‌و‌جوها با استفاده از اشکال چند ضلعی، به رویکرد متفاوتی برای جستجوهای ساده SQL نیاز دارید. یک روش این است که یک تابع هندسه را در جاوا اسکریپت تعریف کنیم و آن را به عنوان یک تابع تعریف شده توسط کاربر (UDF) در BigQuery اجرا کنیم.

بسیاری از عملیات هندسه را می توان در جاوا اسکریپت نوشت، بنابراین به راحتی می توان یکی را برداشت و آن را در مقابل جدول BigQuery که حاوی مقادیر طول و عرض جغرافیایی است، اجرا کرد. شما باید چند ضلعی سفارشی را از طریق یک UDF بگذرانید و آزمایشی را در مقابل هر ردیف انجام دهید و فقط ردیف‌هایی را که عرض و طول جغرافیایی در داخل چند ضلعی قرار می‌گیرند برگردانید. درباره UDF ها در مرجع BigQuery اطلاعات بیشتری کسب کنید .

الگوریتم Point In Polygon

راه های زیادی برای محاسبه قرار گرفتن یک نقطه در یک چندضلعی در جاوا اسکریپت وجود دارد. در اینجا یکی از پورت های 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;
}

انتقال به جاوا اسکریپت

نسخه جاوا اسکریپت این الگوریتم به شکل زیر است:

/* 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 اجرا کرده اید. همانطور که مشاهده کردید، مکان تفاوت بزرگی در داده‌های نتیجه پرس‌و‌جوها در برابر این مجموعه داده ایجاد می‌کند، اما مگر اینکه حدس بزنید کجا کوئری‌های خود را اجرا کنید، کشف الگوهای فضایی ad-hoc فقط با استفاده از پرس‌و‌جوهای SQL دشوار است.

اگر فقط می توانستیم داده ها را روی نقشه تجسم کنیم و با تعریف مناطق مورد علاقه دلخواه، داده ها را کشف کنیم! خوب، با استفاده از API های Google Maps می توانید این کار را انجام دهید. ابتدا باید Maps API را فعال کنید، یک صفحه وب ساده در حال اجرا بر روی دستگاه محلی خود راه اندازی کنید و شروع به استفاده از BigQuery API برای ارسال پرس و جو از صفحه وب خود کنید.

4. کار با Google Maps APIs

با اجرای برخی پرس‌و‌جوهای فضایی ساده، گام بعدی تجسم خروجی برای دیدن الگوها است. برای انجام این کار، Maps API را فعال می‌کنید، یک صفحه وب ایجاد می‌کنید که درخواست‌ها را از نقشه به BigQuery ارسال می‌کند، سپس نتایج را روی نقشه ترسیم می‌کند.

Maps JavaScript API را فعال کنید

برای این Codelab، باید API جاوا اسکریپت Maps Platform Google Maps را در پروژه خود فعال کنید. برای این کار موارد زیر را انجام دهید:

1. در کنسول Google Cloud Platform، به Marketplace بروید
2. در Marketplace، «Maps JavaScript API» را جستجو کنید
3. روی کاشی Maps JavaScript API در نتایج جستجو کلیک کنید
4. روی دکمه "فعال کردن" کلیک کنید

یک کلید API ایجاد کنید

برای درخواست به پلتفرم نقشه های گوگل، باید یک کلید API ایجاد کنید و آن را با تمام درخواست ها ارسال کنید. برای ایجاد یک کلید API، موارد زیر را انجام دهید:

1. در کنسول Google Cloud Platform، روی منوی همبرگر کلیک کنید تا ناوبری سمت چپ باز شود
2. «APIs & Service» > «Credentials» را انتخاب کنید
3. روی دکمه «ایجاد اعتبار» کلیک کنید، سپس «کلید API» را انتخاب کنید.
4. کلید API جدید را کپی کنید

کد را دانلود کنید و یک وب سرور راه اندازی کنید

برای دانلود تمامی کدهای این کد لبه روی دکمه زیر کلیک کنید:

کد را دانلود کنید

فایل فشرده دانلود شده را باز کنید. با این کار یک پوشه ریشه ( bigquery ) باز می شود که شامل یک پوشه برای هر مرحله از این کد لبه به همراه تمام منابعی است که شما نیاز دارید.

پوشه های stepN شامل حالت پایان مطلوب هر مرحله از این کد لبه هستند. آنها برای مرجع وجود دارد. ما تمام کارهای کدنویسی خود را در دایرکتوری به نام work خواهیم داد.

یک وب سرور محلی راه اندازی کنید

در حالی که شما آزاد هستید که از وب سرور خود استفاده کنید، این کد لبه برای کار با وب سرور Chrome طراحی شده است. اگر هنوز آن برنامه را نصب نکرده‌اید، می‌توانید آن را از فروشگاه وب Chrome نصب کنید.

وب سرور را برای کروم نصب کنید

پس از نصب برنامه را باز کنید. در کروم می توانید این کار را به صورت زیر انجام دهید:

راهنمایی بیشتر: برنامه‌های Chrome را اضافه و باز کنید

1. کروم را باز کنید
2. در نوار آدرس در بالا، chrome://apps را تایپ کنید
3. Enter را فشار دهید
4. در پنجره ای که باز می شود، روی نماد وب سرور کلیک کنید، همچنین می توانید روی یک برنامه کلیک راست کنید تا در یک برگه معمولی یا پین شده، تمام صفحه یا پنجره جدید باز شود. در ادامه این گفتگو را خواهید دید که به شما امکان می دهد وب سرور محلی خود را پیکربندی کنید:
5. روی «انتخاب پوشه» کلیک کنید و مکانی را انتخاب کنید که فایل های نمونه Codelab را در آن دانلود کرده اید
6. در بخش «گزینه‌ها»، کادر کنار «نمایش خودکار index.html» را علامت بزنید:
7. کلید ضامن با برچسب "وب سرور: شروع شد" را به سمت چپ و سپس به راست برگردید تا متوقف شود و سپس وب سرور را مجددا راه اندازی کنید.

5. بارگذاری نقشه و ابزار ترسیم

یک صفحه نقشه اولیه ایجاد کنید

با یک صفحه HTML ساده شروع کنید که نقشه گوگل را با استفاده از Maps JavaScript API و چند خط جاوا اسکریپت بارگیری می کند. کد از نمونه نقشه ساده پلتفرم نقشه های گوگل مکانی عالی برای شروع است. در اینجا تکثیر شده است تا بتوانید آن را در ویرایشگر متن یا IDE انتخابی خود کپی و جایگذاری کنید، یا می توانید با باز کردن index.html از مخزن بارگیری شده، آن را پیدا کنید.

1. index.html را در پوشه work موجود در نسخه محلی مخزن کپی کنید
2. پوشه img/ را در پوشه work/ موجود در نسخه محلی مخزن کپی کنید
3. work/ index.html را در ویرایشگر متن یا IDE خود باز کنید
4. کلید API را که قبلا ایجاد کردید جایگزین YOUR_API_KEY کنید

<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 برای پیدا کردن مکان استفاده کنید، سپس کلیک راست کرده و "What's Heres" را انتخاب کنید. از منوی زمینه که ظاهر می شود.

برای انجام این کار، بلوک کد بالا را به شکل زیر به روز کنید تا نقشه را در مرکز امپایر استیت قرار دهید و سطح بزرگنمایی را روی 14 تنظیم کنید:

<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 می‌گوید کتابخانه طراحی را فعال کند، تغییر می‌دهید.

این کد لبه همچنین از 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. برای ایجاد DrawingManager تابعی به نام setUpDrawingTools() با کد زیر اضافه کنید و ویژگی map آن را برای ارجاع به شی نقشه در صفحه تنظیم کنید.

گزینه‌های ارسال شده به google.maps.drawing.DrawingManager(options) نوع طراحی شکل پیش‌فرض و گزینه‌های نمایش شکل‌های ترسیم شده را تنظیم می‌کنند. برای انتخاب مناطق نقشه برای ارسال به عنوان پرس و جو، اشکال باید کدورت صفر داشته باشند. برای اطلاعات بیشتر در مورد گزینه های موجود، به گزینه های DrawingManager مراجعه کنید .

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. پس از ایجاد شی نقشه، در تابع initMap() setUpDrawingTools() فراخوانی کنید.

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 به شما کمک می‌کند تا از نوشتن تعداد زیادی کدهای مورد نیاز برای ایجاد درخواست‌ها، تجزیه پاسخ‌ها و رسیدگی به احراز هویت جلوگیری کنید. این کد لبه از BigQuery API از طریق Google APIs Client Library برای جاوا اسکریپت استفاده می کند زیرا ما در حال توسعه یک برنامه مبتنی بر مرورگر خواهیم بود.

در مرحله بعد، کدی را برای بارگیری این API در یک صفحه وب اضافه می‌کنید و از آن برای تعامل با BigQuery استفاده می‌کنید.

توجه: در برنامه های خود، می توانید از هر کلاینتی که دوست دارید برای ارسال درخواست استفاده کنید. BigQuery API از درخواست های HTTP پشتیبانی می کند و از داده های JSON برای درخواست ها و پاسخ ها استفاده می کند.

Google Client API را برای جاوا اسکریپت اضافه کنید

شما از Google Client API برای جاوا اسکریپت برای اجرای پرس و جوها در برابر 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 Console خود تنظیم کنید.

اعتبارنامه OAuth 2.0 را ایجاد کنید

1. در Google Cloud Console، از منوی Navigation ، APIs & Services > Credentials را انتخاب کنید.

قبل از اینکه بتوانید اعتبارنامه خود را تنظیم کنید، باید تنظیماتی را برای صفحه مجوز اضافه کنید که کاربر نهایی برنامه شما زمانی که برنامه شما را برای دسترسی به داده‌های BigQuery از طرف خود مجوز می‌دهد، ببیند.

برای انجام این کار، روی برگه صفحه رضایت OAuth کلیک کنید. 2. باید Big Query API را به محدوده های این توکن اضافه کنید. در بخش Scopes for Google APIs روی دکمه Add Scope کلیک کنید. 3. از لیست، کادر کنار ورودی Big Query API را با ../auth/bigquery scope علامت بزنید. 4. روی افزودن کلیک کنید. 5. نامی را در قسمت "نام برنامه" وارد کنید. 6. برای ذخیره تنظیمات خود روی Save کلیک کنید. 7. در مرحله بعد شما OAuth Client ID خود را ایجاد می کنید. برای انجام این کار روی Create Credentials کلیک کنید:

8. در منوی کشویی، روی OAuth Client ID کلیک کنید.
9. در قسمت Application Type، Web application را انتخاب کنید.
10. در قسمت Application Name یک نام برای پروژه خود تایپ کنید. به عنوان مثال "BigQuery and Maps".
11. در قسمت محدودیت‌ها ، در قسمت Authorized JavaScript Origins، URL لوکال هاست را شامل شماره پورت وارد کنید. به عنوان مثال: http://localhost:8887

نکته : باید URL مبدا را به‌عنوان «localhost» به جای «127.0.0.1» وارد کنید.

12. روی دکمه Create کلیک کنید.

یک پاپ آپ شناسه مشتری و راز مشتری را به شما نشان می دهد. برای انجام احراز هویت در برابر BigQuery به شناسه مشتری نیاز دارید. آن را کپی کرده و به عنوان یک متغیر جهانی جاوا اسکریپت جدید به نام clientId در work/index.html قرار دهید.

let clientId = 'YOUR_CLIENT_ID';

7. مجوز و اولیه

صفحه وب شما باید قبل از شروع اولیه نقشه، به کاربر اجازه دسترسی به BigQuery را بدهد. در این مثال ما از OAuth 2.0 همانطور که در بخش مجوز اسناد JavaScript Client API توضیح داده شده است استفاده می کنیم. شما باید از شناسه مشتری OAuth و شناسه پروژه خود برای ارسال پرس و جو استفاده کنید.

هنگامی که Google Client API در صفحه وب بارگیری می شود، باید مراحل زیر را انجام دهید:

• اجازه دادن به کاربر
• در صورت مجوز، BigQuery API را بارگیری کنید.
• نقشه را بارگیری و مقداردهی اولیه کنید.

برای مثالی از نحوه ظاهر صفحه تمام شده HTML به step3/map.html مراجعه کنید.

اجازه دادن به کاربر

کاربر نهایی برنامه باید به برنامه اجازه دسترسی به داده ها در BigQuery از طرف خود را بدهد. Google Client API برای جاوا اسکریپت منطق OAuth را برای انجام این کار کنترل می کند.

در یک برنامه دنیای واقعی، انتخاب های زیادی در مورد نحوه ادغام مرحله مجوز دارید.

برای مثال، می‌توانید authorize() را از یک عنصر رابط کاربری مانند یک دکمه فراخوانی کنید، یا این کار را زمانی که صفحه بارگذاری شد انجام دهید. در اینجا ما انتخاب کرده‌ایم که پس از بارگیری Google Client API برای جاوا اسکریپت، با استفاده از تابع callback در gapi.load() .

بلافاصله بعد از تگ <script> کدی بنویسید که Google Client API را برای جاوا اسکریپت بارگیری می کند تا هم کتابخانه مشتری و هم ماژول auth بارگیری شود تا بتوانیم فوراً کاربر را احراز هویت کنیم.

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

توجه: فرآیند مجوز از یک پنجره بازشو استفاده می‌کند، بنابراین بررسی کنید که وقتی کد را برای اولین بار اجرا می‌کنید، آن را مسدود نکنید.

در صورت مجوز، BigQuery API را بارگیری کنید

پس از اینکه کاربر مجاز شد، BigQuery API را بارگیری کنید.

ابتدا با متغیر clientId که در مرحله قبل اضافه کردید، gapi.auth.authorize() را فراخوانی کنید. پاسخ را در یک تابع تماس به نام handleAuthResult کنید.

پارامتر immediate کنترل می کند که آیا یک پاپ آپ به کاربر نشان داده شود. آن را روی true تنظیم کنید تا در صورتی که کاربر قبلاً مجاز است، باز شدن مجوز متوقف شود.

تابعی به نام handleAuthResult() به صفحه خود اضافه کنید. تابع باید یک پارامتر authresult را بگیرد که به شما امکان می دهد جریان منطق را بسته به اینکه کاربر با موفقیت تأیید شده است یا خیر کنترل کنید.

همچنین تابعی به نام loadApi برای بارگیری BigQuery API در صورتی که کاربر با موفقیت مجاز شد، اضافه کنید.

اگر یک شی authResult به تابع ارسال شده باشد، و اگر خاصیت error شی دارای مقدار false باشد، منطق تابع handleAuthResult() را برای فراخوانی loadApi() اضافه کنید.

برای بارگذاری BigQuery API با استفاده از gapi.client.load() کد را به تابع loadApi() اضافه کنید.

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 بارگیری می شود، مقداردهی اولیه می شود.

می توانید این کار را با فراخوانی تابع initMap() از متد then() بعد از متد load() روی شی gapi.client انجام دهید.

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

نکته : پارامتر &callback=initMap را از تگ جاوا اسکریپت Maps API حذف کنید. initMap اکنون از تابع loadApi() پس از بارگیری BigQuery API فراخوانی می شود.

8. مفاهیم API BigQuery

فراخوان‌های BigQuery API معمولاً در چند ثانیه اجرا می‌شوند، اما ممکن است بلافاصله پاسخی دریافت نکنند. برای نظرسنجی BigQuery برای اطلاع از وضعیت مشاغل طولانی مدت نیاز به منطق دارید و فقط زمانی که کار کامل شد نتایج را دریافت کنید.

کد کامل این مرحله در step4/map.html است.

ارسال درخواست

یک تابع جاوا اسکریپت را به 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 در زیر نشان می‌دهد که چگونه می‌توان وضعیت یک کار را به صورت دوره‌ای بررسی کرد، با استفاده از متد get API و 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]);    
  });
}

متد sendQuery را اصلاح کنید تا متد checkJobStatus() را به عنوان یک callback در فراخوانی 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 است. در Legacy SQL پروژه. project.dataset.tablename است.

جداول زیادی از سفرهای تاکسی نیویورک موجود است. برای دیدن آنها، به کنسول وب BigQuery بروید و آیتم منو "مجموعه داده های عمومی" را گسترش دهید. مجموعه داده ای به نام new_york را پیدا کنید و آن را برای دیدن جداول گسترش دهید. جدول سفرهای تاکسی زرد را انتخاب کنید: bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2016 ).

تعیین شناسه پروژه

در تماس API، باید نام پروژه Google Cloud Platform خود را برای اهداف صورت‌حساب مشخص کنید. در این کد لبه، این پروژه مشابه پروژه حاوی جدول نیست . اگر با جدولی کار می‌کردید که در پروژه خود با آپلود داده‌ها ایجاد کرده‌اید، این شناسه پروژه مانند آنچه در دستور SQL شما است، خواهد بود.

متغیرهای جاوا اسکریپت را به کد خود اضافه کنید تا ارجاعاتی را به پروژه مجموعه داده های عمومی که شامل جدولی است که در حال پرس و جو هستید، به اضافه نام جدول و نام مجموعه داده می شود. همچنین برای ارجاع به شناسه پروژه صورتحساب خود به یک متغیر جداگانه نیاز دارید.

متغیرهای جاوا اسکریپت جهانی به نام billingProjectId, publicProjectId, datasetId و tableName را به کپی index.html خود اضافه کنید.

متغیرهای 'publicProjectId' ، 'datasetId' و 'tableName' با جزئیات پروژه BigQuery Public Datasets راه اندازی کنید. billingProjectId را با شناسه پروژه خود (همانی که قبلاً در "Getting Set Up" در این کد لبه ایجاد کردید) راه اندازی کنید.

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

توجه: هر پرس و جو بخشی از سهمیه 1 ترابایتی رایگان ماهانه شما را مصرف می کند. هر کوئری BigQuery باعث اسکن کامل جدول می شود، بنابراین اندازه جدول را قبل از پرس و جو بررسی کنید و فقط ستون های مورد نیاز خود را مشخص کنید. از عبارت LIMIT برای کاهش مقدار داده های برگشتی استفاده کنید.

اکنون دو تابع به کد خود اضافه کنید تا SQL تولید شود و با استفاده از تابع sendQuery که در مرحله قبل ایجاد کردید، کوئری را به BigQuery ارسال کنید.

تابع اول باید 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 برای تمام سطرهایی که توسط یک مستطیل کشیده شده توسط کاربر وجود دارد، دارید. Before we add other query methods for circles and freehand shapes, let's have a look at how to handle the data that comes back from a query.

Note : a rectangular shape on a Google Map, or any map in the Web Mercator projection, does not represent a rectangular area on the ground. This is because lines of longitude converge towards the poles where they meet (Google Maps doesn't show areas above about 85 degrees north or south of the equator). The larger the ground area covered, the bigger the distortion of the true shape and area on the ground will be, but the effect should be negligible over short distances of up to a few kilometers.

10. Visualizing the response

BigQuery tables can be very large—Petabytes of data—and can grow by hundreds of thousands of rows per second. So it's important to try and limit the amount of data returned so that it can be drawn on the map. Drawing the location of every row in a very large result set (tens of thousands of rows or greater) will result in an unreadable map. There are many techniques for aggregating the locations both in the SQL query and on the map, and you can limit the results a query will return.

Full code for this step is available in step5/map.html .

To keep the amount of data transferred to your web page down to a reasonable size for this codelab, modify the rectangleSQL() function to add a statement that limits the response to 10000 rows. In the example below this is specified in a global variable called recordLimit , so that all query functions can use the same value.

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

To visualize the density of locations you can use a heatmap. The Maps Javascript API has a HeatmapLayer class for this purpose. The HeatmapLayer takes an array of latitude, longitude coordinates so it is quite easy to convert the rows returned from the query into a heatmap.

In the getQueryResults function, pass the response.result.rows array to a new Javascript function called doHeatMap() that will create a heatmap.

Each row will have a property called f which is an array of columns. Each column will have a v property containing the value.

Your code needs to loop through the columns in each row and extract the values.

In the SQL query, you have only asked for the Latitude and Longitude values of the taxi pickups so there will only be two columns in the response.

Don't forget to call setMap() on the heatmap layer when you have assigned the array of positions to it. This will make it visible on the map.

Here's an example:

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

At this point, you should be able to:

• Open the page and authorize against BigQuery
• Draw a rectangle somewhere in NYC
• See the resulting query results visualized as a heatmap.

Here is an example of the result from a rectangle query against the 2016 NYC Yellow Taxi data, drawn as a heatmap. This shows the distribution of pickups around the Empire State Building on a Saturday in July:

11. Querying by radius around a point

Radius queries are very similar. Using BigQuery's Legacy SQL Math functions you can construct a SQL query using the Haversine Formula which approximates a circular area on the earth's surface.

Using the same technique for rectangles, you can handle an OverlayComplete event to get the center and radius of a user-drawn circle, and build up the SQL for the query in the same way.

A working example of the code for this step is included in the code repository as step6/map.html .

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

In your copy of index.html, add two new empty functions: circleQuery() and haversineSQL() .

Then, add a circlecomplete event handler that passes the centre and radius to a new function called circleQuery().

The circleQuery() function will call haversineSQL() to construct the SQL for the query and then send the query by calling the sendQuery() function as per the following example code.

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

Try it!

Add the code above and try the 'Circle' tool to select an area of map. The result should look something like this:

12. Querying arbitrary shapes

Recap: SQL doesn't support querying using arbitrary shapes other than rectangles and circles. BigQuery doesn't have any native geometry data type, so to run queries using polygon shapes you need a different approach to straightforward SQL queries.

One very powerful BigQuery feature that can be used for this is User Defined Functions (UDF). UDFs execute Javascript code inside an SQL query.

Working code for this step is in step7/map.html .

UDFs in the BigQuery API

The BigQuery API approach for UDFs is slightly different to the web console: you'll need to call the jobs.insert method .

For Standard SQL queries via the API, just a single SQL statement is needed to use a User Defined Function. The value of useLegacySql must be set to false . The JavaScript example below shows a function that creates and sends a request object to insert a new job, in this case a query with a User Defined Function.

A working example of this approach is in 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));
}

The SQL query is constructed as follows:

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

There are two things going on here. Firstly the code is creating the CREATE TEMPORARY FUNCTION statement that encapsulates the JavaScript code to work out if a given point is inside a polygon. The polygon coordinates are inserted using the JSON.stringify(poly) method call to convert a JavaScript array of x,y coordinate pairs into a string. The polygon object is passed as an argument to the function that builds the SQL.

Secondly the code builds the main SQL SELECT statement. The UDF is called in the WHERE expression in this example.

Integrating with the Maps API

To use this with the Maps API drawing library, we need to save the polygon drawn by the user and pass this into the UDF part of the SQL query.

First, we need to handle the polygoncomplete drawing event, to get the coordinates of the shape as an array of longitude and latitude pairs:

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

The polygonQuery function can then construct the UDF Javascript functions as a string, as well as the SQL statement which will call the UDF function.

See step7/map.html for a working example of this.

Example output

Here's an example result of querying pickups from The 2016 NYC TLC Yellow Taxi data in BigQuery using a freehand polygon, with the selected data drawn as a heatmap.

13. Taking it Further

Here are some suggestions for ways to extend this codelab to look at other aspects of the data. You can find a working example of these ideas at step8/map.html in the code repository.

Mapping drop offs

So far we've only mapped pick up locations. By requesting the dropoff_latitude and dropoff_longitude columns and modifying the heatmap code to plot these instead, you can see the destinations of taxi journeys that started at a specific location.

For example, let's see where taxis tend to drop people off when they request a pick up around the Empire State Building.

Change the code for the SQL statement in polygonSql() to request these columns in addition to the pickup location.

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

The doHeatMap function can then use the dropoff values instead. The result object has a schema that can be inspected to find the location of these columns in the array. In this case they would be at index positions 2 and 3. These indices can be read from a variable to make the code more manageable. NB the maxIntensity of the heatmap is set to show density of 20 drop offs per pixel as the maximum.

Add some variables to allow you to change which columns you use for the heatmap data.

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

Here is a heatmap showing the distribution of drop offs from all pickups immediately around the Empire State Building in 2016. You can see large concentrations (the red blobs) of midtown destinations especially around Times Square, as well as along 5th Avenue between 23rd St and 14th St. Other high density locations not shown at this zoom level include La Guardia and JFK airports, the World Trade Center and Battery Park.

Styling the basemap

When you create a Google Map using the Maps JavaScript API, you can set the map style using a JSON object. For data visualizations it can be useful to mute the colors in the map. You can create and try out map styles using the Google Maps API Styling Wizard at mapstyle.withgoogle.com .

You can set a map style when you initialize a map object, or at any subsequent time afterwards. Here's how you'd add a custom style in the initMap() function:

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

The sample style below shows a greyscale map with points of interest labels.

[
  {
    "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"
      }
    ]
  }
]

Giving the user feedback

Even though BigQuery will usually give a response in seconds, it is sometimes useful to show the user that something is happening while the query is running.

Add some UI to your web page that shows the response of the checkJobStatus() function, and an animated graphic to indicate that the query is in progress.

Information you can display includes query duration, amount of data returned, and amount of data processed.

Add some HTML after the map <div> to create a panel to the page that will show the number of rows returned by a query, the time the query took, and the amount of data processed.

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

The appearance and position of this panel is controlled by CSS. Add CSS to position the panel in the top left corner of the page below the map type buttons and the drawing toolbar as in the snippet below.

#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;
}

The animated graphic can be added to the page but hidden until required, and some JavaScript and CSS code used to show it when a BigQuery job is running.

Add some HTML to show an animated graphic. There is an image file called loader.gif in the img folder in the code repository.

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

Add some CSS to position the image and hide it by default until it's needed.

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

Finally add some JavaScript to update the status panel and show or hide the graphic when a query is running. You can use the response object to update the panel depending on what information is available.

When checking a current job, there is a response.statistics property you can use. When the job is complete you can access the response.totalRows and response.totalBytesProcessed properties. It is helpful to the user to convert milliseconds to seconds and bytes to gigabytes for display as shown in the code sample below.

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';
  }
}

Call this method when there is a response to a checkJobStatus() call and when the query results are fetched. For example:

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

To toggle the animated graphic, add a function to control its visibility. This function will toggle the opacity of any HTML DOM Element passed to it.

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

Finally, call this method before processing a query, and after the query result has come back from BigQuery.

This code calls the fadeToggle function when the user has finished drawing a rectangle.

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

When the query response has been received, call fadeToggle() again to hide the animated graphic.

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

The page should look something like this.

Have a look at the complete example in step8/map.html .

14. Things to Consider

Too Many Markers

If you're working with very large tables, your query may return too many rows to efficiently display on a map. Limit the results by adding a WHERE clause or a LIMIT statement.

Drawing many markers can make the map unreadable. Consider using a HeatmapLayer to show the density, or cluster markers to indicate where many data points lie using a single symbol per cluster. There are more details in our Marker Clustering tutorial .

Optimizing Queries

BigQuery will scan the entire table with every query. To optimize your BigQuery quota usage, only select the columns you need in your query.

Queries will be faster if you store latitude and longitude as floats rather than strings.

Export Interesting Results

The examples here require the end user to be authenticated against the BigQuery table, which won't suit every use case. When you have discovered some interesting patterns, it may be easier to share these with a wider audience by exporting the results from BigQuery and creating a static dataset using the Google Maps Data Layer .

The Boring Legal Bit

Bear in mind the Google Maps Platform Terms of Service . For more details on Google Maps Platform pricing, see the online documentation .

Play With More Data!

There are a number of public datasets in BigQuery that have latitude and longitude columns, for example the NYC Taxi datasets from 2009-2016 , Uber and Lyft NYC trip data , and the GDELT dataset .

15. Congratulations!

We hope this helps you get up and running quickly with some geo queries against BigQuery tables so you can discover patterns and visualize them on a Google Map. Happy mapping!

بعدش چی؟

If you'd like to learn more about the Google Maps Platform or BigQuery, have a look at the following suggestions.

See What is BigQuery to learn more about Google's serverless, petabyte-scale data warehouse service.

Have a look at the how-to guide to create a simple application using the BigQuery API .

See the developer guide for the drawing library for more details on enabling user interaction to draw shapes on a Google Map.

Have a look at other ways to visualize data on a Google Map.

See the Getting Started guide for the Javascript Client AP I to understand the basic concepts of using the Client API to access other Google APIs.