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:
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.
Heading 1
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
Heading 2
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
Heading 3
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
Heading 4
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
Heading 5
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
Heading 6
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.
"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque euismod maximus porta. Pellentesque vitae augue elit. Nam semper eros leo, nec hendrerit nunc aliquet quis. Morbi ac sapien tortor. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia curae; Curabitur porta imperdiet lorem, ut aliquet orci pellentesque quis. Donec magna augue, molestie aliquam enim eu, auctor feugiat metus."
.png)
.png)
