Google Maps Draw Circle Around Location

Query and Visualize Location Information in BigQuery with Google Maps Platform (JavaScript)

1. Overview

Maps tin exist a very powerful tool when visualizing the patterns in a dataset that are related to location in some mode. This relation could be the proper noun of a place, a specific breadth and longitude value, or the proper name of an area that has a specific boundary like a census tract or a postal code.

When these datasets become very large, they can be hard to query and visualize using conventional tools. By using Google BigQuery to query the data and the Google Maps APIs to construct the query and visualize the output, you can chop-chop explore geographic patterns in your data with very little setup or coding, and without having to manage a organization to store very big datasets.

What you'll build

In this codelab, you'll write and run some queries that demonstrate how to provide location based insights into very large public datasets using BigQuery. You lot'll also build a web page that loads a map using the Google Maps Platform JavaScript API, then runs and visualizes spatial queries against the same very large public datasets using the Google APIs Client Library for Javascript and the BigQuery API.

What you'll learn

• How to query petabyte-scale location datasets in seconds with BigQuery, using SQL queries, User Defined Functions and the BigQuery API
• How to use the Google Maps Platform to add a Google Map to a web folio and enable users to draw shapes on it
• How to visualize queries against large datasets on a Google Map like in the example image beneath, which shows the density of taxi drib off locations in 2016 from journeys that started from the block around the Empire Land Building.

What you lot'll need

• Basic knowledge of HTML, CSS, JavaScript, SQL, and Chrome DevTools
• A modernistic web browser, such as recent versions of Chrome, Firefox, Safari, or Edge.
• A text editor or IDE of your choice

The Engineering

BigQuery

BigQuery is Google'southward data analytics service for very large datasets. It has a RESTful API and supports queries written in SQL. If yous have data with latitude and longitude values they tin be used to query your data past location. The advantage is you can visually explore very large datasets to look at the patterns without having to manage whatsoever server or database infrastructure. You can get answers to your questions in a few seconds no matter how large your tables grow using BigQuery'south massive scalability and managed infrastructure.

Google Maps Platform

Google Maps Platform provides programmatic admission to Google's map, place, and road information. Over 2 million websites and apps currently utilise it to provide embedded maps and location based queries to their users.

The Google Maps Platform Javascript API Drawing Layer allows you to draw shapes on the map. These can be converted into input to run queries against BigQuery tables that accept latitude and longitude values stored in columns.

To get started you need a Google Cloud Platform projection with BigQuery and Maps APIs enabled.

2. Getting Ready

Google Business relationship

If y'all don't already have a Google Account (Gmail or Google Apps), you must create ane.

Create a Project

Sign in to Google Cloud Platform console ( console.deject.google.com) and create a new projection. At the tiptop of your screen, in that location is a Project drop down menu:

Once you click on this project drop down carte, you lot volition get a carte item that allows y'all to create a new projection:

In the box that says "Enter a new name for your project", enter a name for your new project, for case "BigQuery Codelab":

A Projection ID volition be generated for y'all. The Project ID is a unique name across all Google Deject projects. Remember your Project ID, every bit you lot'll employ information technology subsequently. The name above has already been taken and will not piece of work for you. Insert your ain Project ID wherever y'all see YOUR_PROJECT_ID in this codelab.

Enable Billing

To sign upwards for BigQuery, use the project selected or created in the previous step. Billing must exist enabled on this project. Once billing is enabled, y'all tin can enable the BigQuery API.

How you lot enable billing depends on whether yous're creating a new project or yous're re-enabling billing for an existing project.

Google offers a 12 calendar month free trial for up to $300 worth of Google Cloud Platform usage which you may be able to use for this Codelab, find out more than details at https://cloud.google.com/free/.

New projects

When you create a new project, you're prompted to choose which of your billing accounts you lot want to link to the project. If you have only one billing account, that account is automatically linked to your projection.

If you don't have a billing account, you must create one and enable billing for your projection before you can use many Google Cloud Platform features. To create a new billing business relationship and enable billing for your project, follow the instructions in Create a new billing account.

Existing projects

If you have a project that yous temporarily disabled billing for, you tin re-enable billing:

1. Go to the Cloud Platform Console.
2. From the projects list, select the project to re-enable billing for.
3. Open the console left side menu and select Billing . You're prompted to select a billing account.
4. Click Set business relationship.

Create a new Billing account

To create a new billing account:

1. Get to the Cloud Platform Console and sign in or, if you don't already have an account, sign upwards.
2. Open the console left side menu and select Billing
3. Click the New billing account button. (Note that if this is not your first billing account, first you demand to open the billing account listing by clicking the name of your existing billing business relationship well-nigh the pinnacle of the page, and then clicking Manage billing accounts.)
4. Enter the proper name of the billing account and enter your billing data. The options you encounter depend on the land of your billing address. Annotation that for U.s. accounts, yous cannot change tax condition after the account is created.
5. Click Submit and enable billing.

By default, the person who creates the billing account is a billing ambassador for the account.

For information about verifying bank accounts and adding fill-in methods of payment, see Add, remove, or update a payment method.

Enable the BigQuery API

To enable the BigQuery API in your project, go to the BigQuery API page Marketplace in the panel and click the blue 'Enable' push.

iii. Querying Location data in BigQuery

There are three ways to query location data stored as latitude, longitude values in BigQuery.

• Rectangle queries: specify the area of interest equally a query that selects all rows within a minimum and maximum latitude and longitude range.
• Radius queries: specify the area of involvement by calculating a circle around a betoken using the Haversine formula and Math functions to model the shape of the globe.
• Polygon queries: specify a custom shape and use a User Divers Office to express the point-in-polygon logic needed to test if each row'due south breadth and longitude fall inside the shape.

To become started, use the Query Editor in the Big Query section of the Google Cloud Platform console to run the post-obit queries confronting the NYC taxi data.

Standard SQL vs. Legacy SQL

BigQuery supports ii versions of SQL: Legacy SQL and Standard SQL. The latter is the 2011 ANSI standard. For the purposes of this tutorial, we will use Standard SQL because information technology has better standards compliance.

If you wish to execute Legacy SQL in the BigQuery editor, you lot may do then past doing the following:

1. Click the 'More' push button
2. Select 'Query settings' from the dropdown card
3. Under 'SQL dialect', select the 'Legacy' radio push
4. Click the 'Save' button

Rectangle Queries

Rectangle queries are quite straightforward to construct in BigQuery. Yous but need to add a WHERE clause that limits the results returned to those with locations between the minimum and maximum values for latitude and longitude.

Try the example below in the BigQuery console. This queries for some average trip statistics for rides that started in a rectangular area that contains midtown and lower Manhattan. In that location are two different locations yous tin try, uncomment the second WHERE clause to run the query on rides that started at JFK airport.

                        SELECT  Round(AVG(tip_amount),2) every bit avg_tip,  Circular(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-information.new_york_taxi_trips.tlc_yellow_trips_2015`  WHERE trip_distance > 0.01 AND fare_amount <100 AND payment_type = "i" 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                                              

The results for the ii queries show that at that place are big differences in average trip distance, fare, and tip for pick ups in the two locations.

Manhattan

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
two.52	12.03	9.97	22.39	5.97

JFK

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
nine.22	48.49	41.xix	22.48	4.36

Radius Queries

Radius queries are besides easy to construct in SQL if y'all know a bit of math. Using BigQuery's Legacy SQL Math functions you can construct an SQL query using the Haversine Formula which approximates a circular area or spherical cap on the earth's surface.

Here's an instance BigQuery SQL statement for a circle query centred at 40.73943, -73.99585 with a radius of 0.1km.

It uses a constant value of 111.045 kilometers to approximate the altitude represented by one degree.

This is based on an example establish at http://world wide web.plumislandmedia.internet/mysql/haversine-mysql-nearest-loc/:

                        SELECT pickup_latitude, pickup_longitude,      (111.045 * DEGREES(        ACOS(          COS( RADIANS(twoscore.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                                              

The SQL for the Haversine Formula looks complicated just all you demand to do is plug in your circle centre coordinate, the radius and the project, dataset and table names for BigQuery.

Here's an case query that computes some average trip statistics for pickups within 100m of the Empire State Building. Copy and paste this into the BigQuery web console to see the results. Modify the latitude and longitude to compare with other areas such as the location in the Bronx.

                        #standardSQL CREATE TEMPORARY Part 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   Circular(AVG(tip_amount),two) as avg_tip, ROUND(AVG(fare_amount),2) as avg_fare, ROUND(AVG(trip_distance),2) as avg_distance, ROUND(AVG(tip_proportion), 2) every bit avg_tip_pc, ROUND(AVG(fare_per_mile),2) as avg_fare_mile  FROM  -- EMPIRE Land BLDG forty.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, forty.748459, -73.985731)   FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015`  WHERE    DistanceKm(pickup_latitude, pickup_longitude, forty.748459, -73.985731) < 0.one   AND fare_amount > 0 and trip_distance > 0   ) WHERE fare_amount < 100                                              

The results of the query are below. You lot tin see that at that place are big differences in the average tip, fare, trip distance, the proportionate size of the tip to the fare, and the boilerplate fare per mile driven.

Empire State Building:

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
i.17	11.08	45.28	10.53	half-dozen.42

The Bronx

avg_tip	avg_fare	avg_distance	avg_tip_pc	avg_fare_mile
0.52	17.63	four.75	iv.74	10.9

Polygon Queries

SQL doesn't support querying using arbitrary shapes other than rectangles and circles. BigQuery doesn't have any native geometry data type or spatial index, so to run queries using polygon shapes you need a unlike approach to straightforward SQL queries. One arroyo is to define a geometry role in JavaScript and execute information technology as a User Defined Part (UDF) in BigQuery.

Many geometry operations can exist written in JavaScript so it's easy to have one and execute it against a BigQuery table that contains breadth and longitude values. You need to laissez passer the custom polygon in via a UDF and perform a test against each row, returning only rows where the latitude and longitude fall within the polygon. Find out more about UDFs in the BigQuery reference.

Point In Polygon algorithm

In that location are many means to compute whether a point falls inside a polygon in JavaScript. Here'south one that is a port from C of a well-known implementation that uses a ray-tracing algorithm to determine if a point is inside or outside a polygon by counting the number of times an infinitely long line crosses the boundary of the shape. It only takes up a few lines of code:

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

Porting to JavaScript

The JavaScript version of this algorithm looks like this:

                        /* This function includes a port of C code to calculate betoken in polygon * see http://www.ecse.rpi.edu/Homepages/wrf/Research/Short_Notes/pnpoly.html for license */  part pointInPoly(polygon, point){     // Catechumen a JSON poly into two arrays and a vertex count.     let vertx = [],         verty = [],         nvert = 0,         testx = point[0],         testy = point[one];     for (let coord of polygon){       vertx[nvert] = coord[0];       verty[nvert] = coord[1];       nvert ++;     }               // The rest of this office 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;     }     render c; }                                              

When using Standard SQL in BigQuery, the UDF approach requires only a single statement, but the UDF must be divers every bit a temporary function in the statement. Here's an instance. Paste the SQL argument beneath into the Query Editor window.

                        CREATE TEMPORARY Role pointInPolygon(latitude FLOAT64, longitude FLOAT64) RETURNS BOOL LANGUAGE js As """   allow polygon=[[-73.98925602436066,40.743249676056955],[-73.98836016654968,40.74280666503313],[-73.98915946483612,40.741676770346295],[-73.98967981338501,40.74191656974406]];    allow vertx = [],     verty = [],     nvert = 0,     testx = longitude,     testy = breadth,     c = false,     j = nvert - 1;    for (permit coord of polygon){     vertx[nvert] = coord[0];     verty[nvert] = coord[one];     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) = Truthful 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                                              

Congratulations!

You have now run three types of spatial query using BigQuery. As you have seen, location makes a large difference to the issue information for the queries against this dataset, but unless you judge where to run your queries, it is hard to discover spatial patterns advertizement-hoc using just SQL queries.

If only we could visualise the data on a map, and explore the data by defining arbitrary areas of interest! Well, using the Google Maps APIs you can do just that. Beginning, you need to enable the Maps API, prepare a elementary web page running on your local machine, and start using the BigQuery API to send queries from your web page.

four. Working with the Google Maps APIs

Having run some simple spatial queries, the next step is to visualize the output to see the patterns. To practice this you will enable the Maps API, build a web page that sends queries from a map to BigQuery, then draws the results on the map.

Enable the Maps JavaScript API

For this Codelab, you will need to enable the Google Maps Platform's Maps Javascript API in your project. To practise this, do the following:

1. In the Google Cloud Platform console, become to the Marketplace
2. In the Marketplace, search for 'Maps JavaScript API'
3. Click the tile for Maps JavaScript API in the search results
4. Click the 'Enable' button

Generate an API Cardinal

To make requests to the Google Maps Platform, you lot will demand to generate an API key and send it with all requests. To generate an API cardinal, do the following:

1. In the Google Deject Platform console, click the hamburger menu to open the left nav
2. Select 'APIs & Service' > 'Credentials'
3. Click the 'Create Credential' button, then select 'API Primal'
4. Re-create the new API fundamental

Download the lawmaking and set a spider web server

Click the post-obit button to download all the code for this codelab:

Unpack the downloaded cipher file. This volition unpack a root folder (bigquery), which contains one binder for each step of this codelab, along with all of the resource you lot volition demand.

The stepN folders contain the desired terminate state of each step of this codelab. They are there for reference. We'll be doing all our coding piece of work in the directory called work.

Gear up a local web server

While you're free to use your own web server, this codelab is designed to work well with the Chrome Web Server. If you don't have that app installed yet, you can install it from the Chrome Web Store.

Once installed open up the app. In Chrome you lot can practise this every bit follows:

1. Open up Chrome
2. In the address bar at the top, blazon chrome://apps
3. Press Enter
4. In the window that opens, click on the Spider web Server icon You lot may too right-click an app to open up information technology in a regular or pinned tab, full screen, or new window Yous'll run across this dialog next, which allows you to configure your local web server:
5. Click 'Cull FOLDER' and select the location that you downloaded the codelab sample files to
6. In the 'Options' department, check the box next to 'Automatically show alphabetize.html':
7. Slide the toggle labeled 'Web Server: STARTED' to the left and and so back to the right to stop and so restart the spider web server

5. Loading the map and drawing tools

Create a basic map page

Start with a simple HTML page that loads a Google Map using the Maps JavaScript API and a few lines of Javascript. The lawmaking from the Google Maps Platform's Uncomplicated Map Sample is a great place to start. It's reproduced hither for you to copy and paste into the text editor or IDE of your choice, or you tin can discover information technology by opening index.html from the repo you downloaded.

1. Copy index.html to the work folder in your local re-create of the repo
2. Copy the img/ folder to the piece of work/ folder in your local copy of the repo
3. Open up work/index.html in your text editor or IDE
4. Replace YOUR_API_KEY with the API primal you created earlier

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

5. In your browser, open localhost:<port>/piece of work, where port is the port number specified in your local spider web server configuration. The default port is 8887. You should encounter your commencement maps displayed.

If you lot receive an mistake message in the browser, cheque that your API key is correct and your local web server is active.

Alter the default location and zoom level

The code that sets the location and zoom level is on lines 27 & 28 of index.html, and is currently centered on Sydney, Australia:

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

This tutorial works with BigQuery taxi trip data for New York, then next you will change the map initialization code to middle on a location in New York Urban center at an advisable zoom level - xiii or 14 should work well.

The practise this, update the lawmaking block above to the following to center the map on the Empire State Edifice and adjust the zoom level to 14:

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

Next, reload the map in your browser to see the results.

Load the drawing and visualization libraries

To add together drawing capabilities to your map, y'all will change the script that loads the Maps JavaScript API by adding an optional parameter that tells Google Maps Platform to enable the drawing library.

This codelab likewise uses the HeatmapLayer, and then you will also update the script to request the visualization library. To exercise this, add together the libraries parameter, and specify the visualization and drawing libraries as comma-separated values, due east.k. libraries= visualization,cartoon

It should expect like this:

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

Add the DrawingManager

To use user-drawn shapes as input to a query, add the DrawingManager to your map, with the Circle, Rectangle and Polygon tools enabled.

It is a good idea to put all of the DrawingManager set upward code into a new function, so in your copy of index.html, do the following:

1. Add a function called setUpDrawingTools()with the following code to create the DrawingManager and set its map property to reference the map object in the page.

The options passed to google.maps.drawing.DrawingManager(options) set the default shape cartoon type and display options for drawn shapes. For selecting areas of map to send as queries, the shapes should have an opacity of goose egg. For more information on available options, see DrawingManager Options.

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

2. Telephone call setUpDrawingTools() in your initMap() function after the map object is created

                        part initMap() {   map = new google.maps.Map(certificate.getElementById('map'), {     center: {lat: twoscore.744593, lng: -73.990370}, // Manhattan, New York.     zoom: 12   });    setUpDrawingTools(); }                                              

3. Reload index.html and check that you have the drawing tools visible. Too check that you can employ them to depict circles, rectangles and polygon shapes.

You lot tin click and drag to draw circles and rectangles simply polygons need to exist drawn by clicking for each vertex, and double-clicking to finish the shape.

Handle Drawing Events

You need some code to handle the events that are fired when a user finishes drawing a shape, just as yous demand the coordinates of the drawn shapes to construct SQL queries.

We will add code for this in a later on stride, only for now nosotros will stub out 3 empty consequence handlers to handle the rectanglecomplete, circlecomplete and polygoncomplete events. The handlers do not take to run any code at this stage.

Add together the post-obit to the bottom of your setUpDrawingTools() function:

                        drawingManager.addListener('rectanglecomplete', rectangle => {     // Nosotros will add together code here in a after step. }); drawingManager.addListener('circlecomplete', circle => {   // Nosotros will add code hither in a later on step. });  drawingManager.addListener('polygoncomplete', polygon => {   // Nosotros will add together code hither in a later stride. });                                              

You tin can discover a working example of this code in your local copy of the repo, in the step2 folder: step2/map.html.

six. Using the BigQuery Client API

The Google BigQuery Client API will help you avoid writing lots of average code needed to build the requests, parse responses, and handle authentication. This codelab uses the BigQuery API via Google APIs Client Library for JavaScript since nosotros volition be developing a browser based awarding.

Adjacent, you will add together code to load this API in a spider web page and use information technology to interact with BigQuery.

Add the Google Client API for JavaScript

Y'all will use the Google Client API for Javascript to run queries confronting BigQuery. In your copy of alphabetize.html (in your work folder), load the API using a <script> tag like this. Put the tag immediately below the <script> tag that loads the Maps API:

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

After loading the Google Client API, authorize the user to access the data in BigQuery. To practice this you can use OAuth 2.0. Offset, yous need to set up upward some credentials in your Google Cloud Console Project.

Create OAuth ii.0 Credentials

1. In the Google Cloud Console, from the Navigation menu, select APIs & Services > Credentials.

Before yous tin set your credentials, you demand to add some configuration for the Authorization screen that an finish user of your application will see when they authorize your app to access BigQuery data on their behalf.

To practise this, click the OAuth consent screen tab. 2. You need to add the Big Query API to the scopes for this token. Click the Add Scope button in the Scopes for Google APIs department. 3. From the list, check the box next to the Big Query API entry with the ../auth/bigquery scope. four. Click Add. five. Enter a name in the 'Application proper name' field. 6. Click Salvage to save your settings. 7. Adjacent yous will create your OAuth Customer ID. To do this, click Create Credentials:

8. In the dropdown carte, click OAuth Customer ID.
9. Nether Application Type select Web application.
10. In the Application Proper noun field, type a proper noun for your project. For case "BigQuery and Maps".
11. Under Restrictions, in the Authorized JavaScript Origins field, enter the URL of localhost, including port numbers. For example: http://localhost:8887

12. Click the Create push.

A pop up shows you the client ID and client secret. You need the client ID to perform authentication confronting BigQuery. Copy information technology and paste information technology into work/index.html every bit a new global JavaScript variable called clientId.

                        let clientId = 'YOUR_CLIENT_ID';                                              

7. Potency and Initialization

Your spider web page volition need to authorize the user to admission BigQuery before initializing the map. In this example we use OAuth two.0 every bit described in the say-so section of the JavaScript Client API documentation. You lot need to utilize the OAuth customer ID and your Project ID to send queries.

When the Google Client API is loaded in the web page you lot need to perform the post-obit steps:

• Qualify the user.
• If authorized, load the BigQuery API.
• Load and initialize the map.

Run into step3/map.html for an example of how the finished HTML folio would look.

The terminate user of the application needs to qualify the application to admission data in BigQuery on their behalf. The Google Client API for JavaScript handles the OAuth logic to do this.

In a existent-world application you lot have many choices nearly how to integrate the authorisation pace.

For example, y'all could phone call authorize() from a UI element like a push, or do information technology when the page has loaded. Here we have chosen to authorize the user subsequently the Google Customer API for JavaScript has been loaded, by using a callback role in the gapi.load()method.

Write some code immediately after the <script> tag that loads the Google Client API for Javascript to load both the client library and the auth module so that we can authenticate the user straight away.

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

On authorization, load the BigQuery API

After the user has been authorized, load the BigQuery API.

Firstly, call gapi.auth.qualify() with the clientId variable you added in the previous step. Handle the response in a callback function called handleAuthResult.

The immediate parameter controls whether a popular up is shown to the user. Fix it to true to suppress the authority pop upwards if the user is already authorized.

Add a function to your page called handleAuthResult(). The function needs to take an authresult parameter, which volition let you control the flow of logic depending on whether the user was successfully authorized or non.

Likewise add together a role called loadApi to load the BigQuery API if the user is successfully authorized.

Add logic in the handleAuthResult() function to telephone call loadApi()if in that location is an authResult object passed to the function, and if the object's error property has a value of false.

Add together lawmaking to the loadApi() function to load the BigQuery API using the gapi.customer.load() method.

                        let clientId = 'your-client-id-hither'; let scopes = 'https://www.googleapis.com/auth/bigquery';  // Check if the user is authorized. office authorize(consequence) {   gapi.auth.authorize({client_id: clientId, telescopic: scopes, firsthand: false}, handleAuthResult);   return false; }  // If authorized, load BigQuery API function handleAuthResult(authResult) {   if (authResult && !authResult.error) {     loadApi();     return;   }   console.error('Not authorized.')   }  // Load BigQuery customer API function loadApi(){   gapi.client.load('bigquery', 'v2'); }                                              

Load the map

The last step is to initialize the map. You lot demand to change the order of the logic slightly to practise this. Currently it initializes when the Maps API JavaScript has loaded.

You can do this by calling the initMap() function from the then() method afterwards the load() method on the gapi.client object.

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

8. BigQuery API concepts

BigQuery API calls usually execute in seconds merely may not return a response immediately. You demand some logic to poll BigQuery to find out the status of long-running jobs, and only fetch the results when the job is complete.

The consummate lawmaking for this step is in step4/map.html.

Sending a request

Add a Javascript function to work/alphabetize.html to transport a query using the API, and some variables to store the values of the BigQuery dataset and projection containing the table to query, and the project ID that will be billed for any charges.

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

Check the status of a task

The checkJobStatus role below shows how to bank check the condition of a task periodically, using the get API method and the jobId returned by the original query asking. Here'due south an case that runs every 500 milliseconds until the job is consummate.

                        let jobCheckTimer;  function checkJobStatus(jobId){   allow request = gapi.client.bigquery.jobs.get({     'projectId': billingProjectId,     'jobId': jobId   });   asking.execute(response =>{     if (response.condition.errorResult){       // Handle any errors.       console.log(response.condition.error);       return;     }      if (response.status.state == 'DONE'){       // Go the results.       clearTimeout(jobCheckTimer);       getQueryResults(jobId);       return;     }     // Not finished, cheque again in a moment.     jobCheckTimer = setTimeout(checkJobStatus, 500, [jobId]);       }); }                                              

Amend the sendQuery method to call the checkJobStatus() method as a callback in the request.execute() call. Laissez passer the job ID to checkJobStatus. This is exposed by the response object every bit jobReference.jobId.

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

Getting the results of a query

To get the results of a query when it has finished running, use the jobs.getQueryResults API call. Add a function to your page called getQueryResults(), that accepts a parameter of jobId:

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

ix. Querying location data with the BigQuery API

There are iii ways to employ SQL to run spatial queries confronting data in BigQuery:

• select past rectangle (otherwise known as bounding box),
• select by radius, and
• the powerful User Defined Functions characteristic.

There are examples of bounding box and radius queries in the Mathematical Functions section of the BigQuery legacy SQL reference, under 'Advanced Examples'.

For bounding box and radius queries, you tin telephone call the BigQuery API query method. Construct the SQL for each query and laissez passer it to the sendQuery role you created in the previous step.

A working example of the lawmaking for this pace is in step4/map.html.

Rectangle queries

The simplest manner to display BigQuery data on a map is to asking all rows where the latitude and longitude autumn within a rectangle, using a less than and greater than comparison. This could exist the current map view or a shape drawn on the map.

To use a shape drawn by the user, change the code in index.html to handle the drawing event fired when a rectangle is completed. In this example the code uses getBounds() on the rectangle object to go an object representing the extent of the rectangle in map coordinates, and passes it to a function called rectangleQuery:

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

The rectangleQuery part only needs to use the meridian right (north due east) and lower left (s west) coordinates to construct a less than/greater than comparison against each row in your BigQuery table. Here's an case that queries a table that has columns called 'pickup_latitude' and 'pickup_longitude' which store the location values.

Specifying the BigQuery table

To query a table using the BigQuery API y'all need to supply the name of the table in fully qualified course in your SQL query. The format in Standard SQL is project.dataset.tablename. In Legacy SQL it'southward project.dataset.tablename.

There are many tables of NYC Taxi trips available. To see them, go to the BigQuery web console and expand the "public datasets" carte item. Find the dataset called new_york and aggrandize it to see the tables. Choose the Yellow Taxi trips table: bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2016).

Specifying the Projection ID

In the API call, y'all demand to specify the proper name of your Google Cloud Platform project for billing purposes. In this codelab, this is not the same project equally the one containing the tabular array. If you were working with a table that yous had created in your own project past uploading information, then this Projection ID would be the same as the one in your SQL argument.

Add JavaScript variables to your code to hold references to the Public Datasets project that contains the table you are querying, plus the table name and dataset name. You lot also need a separate variable to refer to your own billing Projection ID.

Add global Javascript variables called billingProjectId, publicProjectId, datasetId and tableName to your copy of index.html.

Initialize the variables 'publicProjectId', 'datasetId' and 'tableName' with the details from the BigQuery Public Datasets projection. Initialize billingProjectId with your own Project ID (the one yous created in "Getting Prepare" before in this codelab).

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

Now add together ii functions to your code to generate the SQL and to send the query to BigQuery using the sendQuery function you created in the previous step.

The outset part should be called rectangleSQL() and needs to accept two arguments, a pair of google.Maps.LatLng objects representing the corners of the rectangle in map coordinates.

The second office should be called rectangleQuery(). This passes the query text to the sendQuery part.

                        let billingProjectId = 'YOUR_PROJECT_ID'; let publicProjectId = 'bigquery-public-information'; let datasetId = 'new_york'; allow tableName = 'tlc_yellow_trips_2016';  function rectangleQuery(latLngBounds){   let queryString = rectangleSQL(latLngBounds.getNorthEast(), latLngBounds.getSouthWest());   sendQuery(queryString); }  function rectangleSQL(ne, sw){   permit 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; }                                              

At this betoken, y'all have enough code to send a query to BigQuery for all rows independent by a rectangle drawn by the user. Earlier nosotros add other query methods for circles and freehand shapes, let'southward take a await at how to handle the data that comes back from a query.

10. Visualizing the response

BigQuery tables can be very large—Petabytes of information—and can grow by hundreds of thousands of rows per 2nd. And then it'south important to try and limit the amount of data returned so that it tin be drawn on the map. Drawing the location of every row in a very large result set (tens of thousands of rows or greater) volition 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, change the rectangleSQL() function to add together a argument that limits the response to 10000 rows. In the example beneath 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 employ a heatmap. The Maps Javascript API has a HeatmapLayer course for this purpose. The HeatmapLayer takes an array of latitude, longitude coordinates so information technology is quite easy to catechumen the rows returned from the query into a heatmap.

In the getQueryResults function, pass the response.issue.rows assortment to a new Javascript function called doHeatMap() that volition create a heatmap.

Each row will have a belongings called f which is an assortment of columns. Each column will have a v holding containing the value.

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

In the SQL query, yous have only asked for the Breadth and Longitude values of the taxi pickups so there will only be ii 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 get in visible on the map.

Hither's an instance:

                        function getQueryResults(jobId){   allow request = gapi.client.bigquery.jobs.getQueryResults({     'projectId': billingProjectId,     'jobId': jobId   });   request.execute(response => doHeatMap(response.upshot.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[ane].5) };       let latLng = new google.maps.LatLng(coords);       heatmapData.push(latLng);   }   heatmap = new google.maps.visualization.HeatmapLayer({       data: heatmapData   });   heatmap.setMap(map); }                                              

At this indicate, y'all should be able to:

• Open the page and authorize confronting 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 confronting the 2016 NYC Yellowish Taxi information, drawn as a heatmap. This shows the distribution of pickups around the Empire Country Building on a Saturday in July:

eleven. Querying by radius around a bespeak

Radius queries are very like. Using BigQuery's Legacy SQL Math functions y'all tin 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 centre 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 stride is included in the code repository every bit step6/map.html.

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

In your copy of alphabetize.html, add together 2 new empty functions: circleQuery() and haversineSQL().

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

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

                        function circleQuery(circle){   permit queryString = haversineSQL(circumvolve.getCenter(), circumvolve.radius);   sendQuery(queryString); }  // Calculate a circular expanse on the surface of a sphere based on a centre and radius. role haversineSQL(center, radius){   let queryString;   permit centerLat = eye.lat();   permit centerLng = center.lng();   allow 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/one thousand;   queryString += ' LIMIT ' + recordLimit;   return queryString; }                                              

Attempt it!

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

12. Querying arbitrary shapes

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

One very powerful BigQuery feature that tin be used for this is User Defined Functions (UDF). UDFs execute Javascript code within 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 unlike 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 Part. The value of useLegacySql must exist set to imitation. The JavaScript example beneath shows a function that creates and sends a asking object to insert a new job, in this case a query with a User Defined Role.

A working case of this approach is in step7/map.html.

                        role polygonQuery(polygon) {   let asking = gapi.client.bigquery.jobs.insert({     'projectId' : billingProjectId,       'resources' : {         'configuration':           {             'query':             {               'query': polygonSql(polygon),               'useLegacySql': faux             }           }       }   });   asking.execute(response => checkJobStatus(response.jobReference.jobId)); }                                              

The SQL query is synthetic every bit follows:

                        function polygonSql(poly){   permit queryString = 'CREATE TEMPORARY FUNCTION pointInPolygon(breadth 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][i];';   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;   render queryString; }                                              

There are two things going on hither. Firstly the code is creating the CREATE TEMPORARY FUNCTION statement that encapsulates the JavaScript code to work out if a given point is within a polygon. The polygon coordinates are inserted using the JSON.stringify(poly) method call to catechumen a JavaScript array of x,y coordinate pairs into a cord. 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 chosen in the WHERE expression in this instance.

Integrating with the Maps API

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

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

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

The polygonQuery function can so construct the UDF Javascript functions as a string, every bit well as the SQL statement which volition call the UDF office.

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

Example output

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

thirteen. Taking information technology Further

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

Mapping driblet offs

So far we've just mapped option upwards locations. Past requesting the dropoff_latitude and dropoff_longitude columns and modifying the heatmap code to plot these instead, yous can see the destinations of taxi journeys that started at a specific location.

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

Alter 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 Office pointInPolygon(latitude FLOAT64, longitude FLOAT64) ';   queryString += 'RETURNS BOOL Language js Every bit """ ';   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 office can then use the dropoff values instead. The result object has a schema that tin can exist inspected to find the location of these columns in the array. In this instance they would exist at index positions 2 and three. These indices can be read from a variable to brand the code more manageable. NB the maxIntensity of the heatmap is set to show density of 20 drib offs per pixel every bit the maximum.

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

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

Hither is a heatmap showing the distribution of drop offs from all pickups immediately around the Empire Country Building in 2016. You lot tin can run into big concentrations (the red blobs) of midtown destinations peculiarly effectually 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 Merchandise Center and Battery Park.

Styling the basemap

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

You can gear up a map style when yous initialize a map object, or at any subsequent time later. Here'southward how yous'd add a custom way in the initMap() function:

                        role 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": "h2o",             "elementType": "labels.text.fill",               "stylers": [                 {                   "color": "#9e9e9e"                 }               ]         }       ]     });   setUpDrawingTools(); }                                              

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

                        [   {     "elementType": "geometry",     "stylers": [       {         "colour": "#f5f5f5"       }     ]   },   {     "elementType": "labels.icon",     "stylers": [       {         "visibility": "on"       }     ]   },   {     "elementType": "labels.text.make full",     "stylers": [       {         "colour": "#616161"       }     ]   },   {     "elementType": "labels.text.stroke",     "stylers": [       {         "color": "#f5f5f5"       }     ]   },   {     "featureType": "authoritative.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": [       {         "colour": "#9e9e9e"       }     ]   },   {     "featureType": "road",     "elementType": "geometry",     "stylers": [       {         "colour": "#ffffff"       }     ]   },   {     "featureType": "road.arterial",     "elementType": "labels.text.fill",     "stylers": [       {         "colour": "#757575"       }     ]   },   {     "featureType": "road.highway",     "elementType": "geometry",     "stylers": [       {         "color": "#dadada"       }     ]   },   {     "featureType": "road.highway",     "elementType": "labels.text.fill up",     "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 together some UI to your spider web page that shows the response of the checkJobStatus() office, and an animated graphic to indicate that the query is in progress.

Data you tin display includes query duration, amount of data returned, and amount of information candy.

Add some HTML later the map <div> to create a console to the folio that will testify 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 past CSS. Add CSS to position the panel in the meridian left corner of the page beneath the map type buttons and the drawing toolbar equally in the snippet beneath.

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

The animated graphic tin be added to the page merely subconscious until required, and some JavaScript and CSS code used to show it when a BigQuery task is running.

Add some HTML to evidence an animated graphic. At that place is an prototype file chosen loader.gif in the img binder in the code repository.

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

Add some CSS to position the image and hibernate it by default until it'south needed.

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

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

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

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

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

                        // Poll a job to see if it has finished executing. part checkJobStatus(jobId){   allow request = gapi.customer.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.condition.error);       return;     }     if (response.status.land == 'DONE'){       // Get the results.       clearTimeout(jobCheckTimer);       getQueryResults(jobId);       render;     }     // Not finished, bank check over 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   });   asking.execute(response => {     doHeatMap(response.result.rows);     updateStatus(response);   }) }                                              

To toggle the blithe graphic, add together a function to command its visibility. This function will toggle the opacity of whatever HTML DOM Element passed to it.

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

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

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

                        drawingManager.addListener('rectanglecomplete', rectangle => {   //prove 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){   permit request = gapi.client.bigquery.jobs.getQueryResults({     'projectId': billingProjectId,     'jobId': jobId   });   asking.execute(response => {     doHeatMap(response.upshot.rows);     //hide the animation.     fadeToggle(document.getElementById('spinner'));     updateStatus(response);   }) }                                              

The page should expect something like this.

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

14. Things to Consider

Also Many Markers

If you're working with very large tables, your query may render 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 evidence 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 volition scan the unabridged 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 breadth and longitude every bit floats rather than strings.

Consign Interesting Results

The examples here crave the stop user to be authenticated confronting the BigQuery tabular array, which won't adjust every use example. When you accept 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 Information Layer.

The Boring Legal Bit

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

Play With More Data!

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

15. Congratulations!

Nosotros hope this helps you lot get upward and running quickly with some geo queries confronting BigQuery tables and then yous can find patterns and visualize them on a Google Map. Happy mapping!

What's adjacent?

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

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

Take a look at the how-to guide to create a unproblematic application using the BigQuery API.

Come across the developer guide for the drawing library for more details on enabling user interaction to depict shapes on a Google Map.

Take a await at other ways to visualize data on a Google Map.

See the Getting Started guide for the Javascript Customer API to understand the bones concepts of using the Client API to access other Google APIs.

Except equally otherwise noted, the content of this page is licensed under the Artistic Eatables Attribution 4.0 License, and lawmaking samples are licensed under the Apache 2.0 License. For details, meet the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

hoppermatur1941.blogspot.com

Source: https://developers.google.com/codelabs/maps-platform/bigquery-maps-api

Share this post