Explore the sample data

This tutorial uses a dataset available through theGoogle Cloud Public Dataset Program.A public dataset is any dataset that is stored in BigQuery andmade available to the general public. Thepublic datasets are datasets that BigQuery hosts foryou to access and integrate into your applications. Google pays for the storageof these datasets and provides public access to the data by using aproject. You pay only for the queries thatyou perform on the data (the first 1 TB per month is free, subject toquery pricing details.)

The NYC Citi Bike Trips dataset

NYC Citi Bike Trips

Citi Bike is the nation's largest bike share program, with 10,000 bikes and 600stations across Manhattan, Brooklyn, Queens, and Jersey City. This datasetincludes Citi Bike trips since Citi Bike launched in September 2013 and isupdated daily. The data is processed by Citi Bike to remove trips that aretaken by staff to service and inspect the system and any trips that are lessthan 60 seconds in duration, which are considered false starts.

You can start exploring this data in the BigQuery console byviewing the details of thecitibike_stations table:

Go to citibike_stations schema

Three columns in this table are relevant to this tutorial:

• bike_stations.longitude: the longitude of a station. The values arevalid WGS 84 longitudes in decimal degrees format.
• bike_stations.latitude: the latitude of a station. The values arevalid WGS 84 latitudes in decimal degrees format.
• num_bikes_available: the number of bikes available for rental.

Query the bike stations with more than 30 bikes available

In this section of the tutorial, you run a GoogleSQL query that finds all theCiti Bike stations in New York City with more than 30 bikes available to rent.

Query details

The following GoogleSQL query is used to find the Citi Bike stations withmore than 30 bikes.

SELECTST_GeogPoint(longitude,latitude)ASWKT,num_bikes_availableFROM`bigquery-public-data.new_york.citibike_stations`WHEREnum_bikes_available>30

The query clauses do the following:

• SELECT ST_GeogPoint(longitude, latitude) AS WKT, num_bikes_available

  TheSELECT clause selects thenum_bikes_available column and uses theST_GeogPoint function to convert the values in thelatitude andlongitudecolumns toGEOGRAPHY types (points).

• FROM `bigquery-public-data.new_york.citibike_stations`

  TheFROM clause specifies the table being queried:citibike_stations.

• WHERE num_bikes_available > 30

  TheWHERE clause filters the values in thenum_bikes_available column tojust those stations with more than 30 bikes.

Run the query

To run the query by using the Google Cloud console:

1. Go to the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. Enter the following GoogleSQL query in theQuery editor text area.

   -- Finds Citi Bike stations with > 30 bikesSELECTST_GeogPoint(longitude,latitude)ASWKT,num_bikes_availableFROM`bigquery-public-data.new_york.citibike_stations`WHEREnum_bikes_available>30

3. ClickRun.

   The query takes a moment to complete. After the query runs, your resultsappear in theQuery results pane.

   

Visualize the results in BigQuery

To visualize the results in an interactive map, follow these steps:

1. In theQuery results pane, clickVisualization.

   

   The points on the map show the locations of each bike station.

2. You can apply uniform or data driven styling to your map.To visualize how many bikes are available at each station, forData column selectnum_bikes_available.

3. To improve visibility, try adjusting theOpacity,Color, orPoint size. If your data contains outliers, you can adjust theMinandMax values. Geographies with values outside this range are stilldisplayed on the map, but no color is applied.

4. To view the properties of a geography, click it.

5. To view the map in satellite mode, clickSatellite.

Visualize the query results in Geo Viz

You can also visualize your results using BigQuery Geo Viz: aweb tool for visualization of geospatial data in BigQuery usingGoogle Maps APIs.

Launch Geo Viz and authenticate

Before using Geo Viz, you must authenticate and grant access to data inBigQuery.

To set up Geo Viz:

1. Open the Geo Viz web tool.

   Open the Geo Viz web tool

   You might need to enable cookies to authorize and use this tool.

2. Under step one,Query, clickAuthorize.

   

3. In theChoose an account dialog, click your Google Account.

   

4. In the access dialog, clickAllow to give Geo Viz access to yourBigQuery data.

   

Run a GoogleSQL query on geospatial data

After you authenticate and grant access, the next step is to run the query inGeo Viz.

To run the query:

1. For step one,Select data, enter your project ID in theProject IDfield.

2. In the query window, enter the following GoogleSQL query.

   -- Finds Citi Bike stations with > 30 bikesSELECTST_GeogPoint(longitude,latitude)ASWKT,num_bikes_availableFROM`bigquery-public-data.new_york.citibike_stations`WHEREnum_bikes_available>30

3. ClickRun.

4. When the query completes, clickShow results. You can also click step twoDefine columns.

   

5. This moves you to step two. In step two, forGeometry column, chooseWKT. This plots the points corresponding to the bike stations on yourmap.

   

Format your visualization

The Style section provides a list of visual styles for customization. Certainproperties apply only to certain types of data. For example,circleRadiusaffects only points.

Supported style properties include:

• fillColor. The fill color of a polygon or point. For example,"linear" or "interval" functions can be used to map numeric values to a colorgradient.
• fillOpacity. The fill opacity of a polygon or point. Values must bein the range 0 to 1, where0 = transparent and1 = opaque.
• strokeColor. The stroke or outline color of a polygon or line.
• strokeOpacity. The stroke or outline opacity of polygon or line.Values must be in the range 0 to 1, where0 = transparent and1= opaque.
• strokeWeight. The stroke or outline width in pixels of a polygon orline.
• circleRadius. The radius of the circle representing a point inpixels. For example, a "linear" function can be used to map numeric valuesto point sizes to create a scatterplot style.

Each style can be given either a global value (applied to every result) or adata-driven value (applied in different ways depending on data in each resultrow). For data-driven values, the following are used to determine the result:

• function. A function used to compute a style value from a field'svalues.
• identity. The data value of each field is used as the stylingvalue.
• categorical. The data values of each field listed in the domain aremapped one to one with corresponding styles in the range.
• interval. Data values of each field are rounded down to the nearestvalue in the domain and are then styled with the corresponding style in therange.
• linear. Data values of each field are interpolated linearly acrossvalues in the domain and are then styled with a blend of the correspondingstyles in the range.
• field. The specified field in the data is used as the input to thestyling function.
• domain. An ordered list of sample input values from a field. Sampleinputs (domain) are paired with sample outputs (range) based on the givenfunction and are used to infer style values for all inputs (even those notlisted in the domain). Values in the domain must have the same type (text,number, and so on) as the values of the field you are visualizing.
• range. A list of sample output values for the style rule. Values inthe range must have the same type (color or number) as the style property youare controlling. For example, the range of thefillColor property shouldcontain only colors.

To format your map:

1. ClickAdd styles in step two or click step 3Style.

2. Change the color of your points. ClickfillColor.

3. In theValue field, enter#0000FF, the HTML color code for blue.

4. ClickApply Style.

   

5. Examine your map. If you click one of your points, the value is displayed.

   

6. ClickfillOpacity.

7. In theValue field, enter0.5 and clickApply Style.

   

8. Examine your map. The fill color of the points is now semi-transparent.

   

9. Change the size of the points based on the number of bikes available. ClickcircleRadius.

10. In thecircleRadius panel:

    1. ClickData driven.
    2. ForFunction, chooselinear.
    3. ForField, choosenum_bikes_available.
    4. ForDomain, enter30 in the first box and60 in thesecond.

    5. ForRange, enter5 in the first box and20 in the second.

       

11. Examine your map. The radius of each circle now corresponds to the number ofbikes available at that location.

    

12. Close Geo Viz.