Site Selection with Mapbox Movement & Big Query

Data scientists and analysts can now access Mapbox Movement data through BigQuery, making it easier for companies to combine Mapbox Movement with their essential business data like customer locations, point of sale information, and demographic information. With this data, business analysts can unlock insights around their most important locations and use these insights to perform advanced geospatial analytics like retail site selection.

This post will demonstrate how to use Mapbox Movement inside of BigQuery to select the best location for a new restaurant. We'll start with a list of potential restaurant locations, similar to those that would be provided by a commercial real estate broker. We will score each location based on Mapbox Movement activity during lunch and during dinner to understand the best properties to lease for those meals.

The query below looks at each potential location and determines the average activity index within the surrounding area, defined by an H3 hexagons and identified with their H3 Hex ID. Cities are geographically complex areas to analyze and the hexagons enable us to examine these areas with comparable size and shapes while retaining a consistent identifier for selection and filtering. 
First, we look at weekdays at noon because it is the peak lunch hour for restaurants. The day of the week and the time of day to examine are specified in the ON statement.

 select AVG(movement.activity_index_total) as avgMovement,bqcarto.h3.ST_ASH3(ST_GEOGPOINT(restLocs.lng,restLocs.lat), 9) as hexid,restLocs.address_name 
 FROM  `movement-307122.mapBoxSampleData.portlandMovementHourly` as movement 
 join  `movement-307122.mapBoxSampleData.restaurantLocations` as restLocson
 ST_WITHIN( ``` --this will select all the movement points that are within the HEX boundary ```
 ST_GEOGPOINT(movement.xlon,movement.xlat),``` --ST_GEOPOINT turns the lat/long into point for use in the ST_WITHIN ``` 
 bqcarto.h3.ST_BOUNDARY(bqcarto.h3.ST_ASH3(ST_GEOGPOINT(restLocs.lng,restLocs.lat), 9))) ``` -- get the geometry for the HEX. 9 is the H3 cell resolution``` 
 where movement.agg_day_period = 0 and movement.agg_time_period = 12 ```-- 0 for agg day period is weekday, 12 for agg time period is 12:00 ``` 
 group by hexid, restLocs.address_name
 order by avgMovement

The results of this query shows the movement score for each location, with the height of the bar representing the overall Average movement index. 

Based on this analysis, the property at 6 SW 3rd Ave would be a great choice for a restaurant specializing in sandwiches or other lunch menu items. However, restaurants typically make significantly more revenue per sqft during dinner, so we will adjust the time parameters to examine activity at 6 PM and rerun the query to see if the results change. Here is the update query:

 select AVG(movement.activity_index_total) as avgMovement,bqcarto.h3.ST_ASH3(ST_GEOGPOINT(restLocs.lng,restLocs.lat), 9) as hexid,restLocs.address_name 
 FROM  `movement-307122.mapBoxSampleData.portlandMovementHourly` as movement 
 join  `movement-307122.mapBoxSampleData.restaurantLocations` as restLocson
 ST_WITHIN( ``` --this will select all the movement points that are within the HEX boundary ```
 ST_GEOGPOINT(movement.xlon,movement.xlat),``` --ST_GEOPOINT turns the lat/long into point for use in the ST_WITHIN ``` 
 bqcarto.h3.ST_BOUNDARY(bqcarto.h3.ST_ASH3(ST_GEOGPOINT(restLocs.lng,restLocs.lat), 9))) ``` -- get the geometry for the HEX. 9 is the H3 cell resolution``` 
 where movement.agg_day_period = 0 and movement.agg_time_period = 18 ```-- 0 for agg day period is weekday, 18 for agg time period is 18:00 ``` 
 group by hexid, restLocs.address_name
 order by avgMovement

And here are the results, which show that the vicinity around the 9225 SW Allen Rd property would be the busiest during dinnertime.

Using insights from Mapbox Movement in BigQuery we’ve identified 9225 SW Allen Rd as the best location for our new restaurant. This location has the highest average activity during the dinner service and also in the top 3 for lunch service.
Newcomers to BigQuery can check out the quick start guide for the basics. We have made sample data from this post available in a public Google Cloud Bucket so developers can quickly create the tables and run these queries exactly as shown:

• portlandMovementHourly.csv - contains hourly Mapbox Movement data for the city of Portland, Oregon
• restuarantLocations.csv - contains a sample of commercial real estate listing similar to what would be provided by a broker
  

Create a BigQuery account and get started using Mapbox Movement data to identify the right location for your business today.