![google mobility data google mobility data](https://www.channelnews.com.au/wp-content/uploads/2020/04/Google-Maps-mobility-data-transit-stations-Australia-768x300.jpg)
Use this query to join the confirmed case counts from the New York Times (it’s also public dataset in BigQuery): CREATE TEMPORARY FUNCTION trim_county(sub_region_2 STRING) AS ( TRIM(REPLACE(sub_region_2, 'County', '')) ) WITH agg AS ( SELECT MAX(date) AS latest_date FROM `vid19_google_mobility.mobility_report` ), latest_data AS ( SELECT country_region_code, sub_region_1, county AS trimmed_county_name, retail_and_recreation_percent_change_from_baseline, confirmed_cases FROM `bigquery-public-data`.covid19_google_mobility.mobility_report, agg JOIN `bigquery-public-data`.covid19_nyt.us_counties AS nyt ON sub_region_1 = state_name AND trim_county(sub_region_2) = county AND mobility_report.date = nyt.date WHERE nyt.date = agg.latest_date AND country_region_code = 'US' AND retail_and_recreation_percent_change_from_baseline IS NOT NULL ), with_state_fips AS ( SELECT state_fips_code AS sfc, trimmed_county_name, retail_and_recreation_percent_change_from_baseline, confirmed_cases FROM latest_data JOIN `bigquery-public-data`.utility_us.us_states_area ON sub_region_1 = state_name ) SELECT CONCAT(ST_Y(ST_CENTROID(county_geom)),',',ST_X(ST_CENTROID(county_geom))) AS marker, (100 + retail_and_recreation_percent_change_from_baseline) AS mobility, LOG(confirmed_cases) AS log_cases FROM with_state_fips JOIN `bigquery-public-data`.utility_us. Let’s now build a dashboard that also shows the number of cases. You will now have a dashboard that looks like the one at the beginning of this article.
![google mobility data google mobility data](https://cdn.statcdn.com/Infographic/images/normal/9467.jpeg)
Join against a public dataset of county information to get the centroid of each county.King County) to get rid of the word County. A baseline day represents a normal value for that day of the week.
#GOOGLE MOBILITY DATA CODE#
Use the state name (e.g., WA) to get the states FIPS code (e.g., 53) and massage the county name (e.g. The data shows how visitors to (or time spent in) categorized places change compared to our baseline days.Pull latest data of every state/county in the US.Find the latest date for which the mobility report is available.The Mobility data is in BigQuery as a public dataset, so go to and run the following query: WITH agg AS ( SELECT MAX(date) AS latest_date FROM `vid19_google_mobility.mobility_report` ), latest_data AS ( SELECT country_region_code, sub_region_1, sub_region_2, retail_and_recreation_percent_change_from_baseline FROM `vid19_google_mobility.mobility_report`, agg WHERE date = agg.latest_date AND country_region_code = 'US' AND retail_and_recreation_percent_change_from_baseline IS NOT NULL ), with_state_fips AS ( SELECT state_fips_code AS sfc, TRIM(REPLACE(sub_region_2, 'County', '')) AS trimmed_county_name, retail_and_recreation_percent_change_from_baseline FROM latest_data JOIN `bigquery-public-data`.utility_us.us_states_area ON sub_region_1 = state_name ) SELECT CONCAT(ST_Y(ST_CENTROID(county_geom)),',',ST_X(ST_CENTROID(county_geom))) AS marker, (100 + retail_and_recreation_percent_change_from_baseline) AS mobility FROM with_state_fips JOIN `bigquery-public-data`.utility_us.us_county_area ON trimmed_county_name = county_name AND sfc = state_fips_code Green dots indicate counties where visits have fallen a lot and red dots indicate where visits are about the same. How visits to retail and recreation establishments vary across the USA.