In this video, you'll see how to enable serverless geospatial analytics and
machine learning (ML) with AWS and CARTO.
With this solution, you can subscribe to third- party spatial datasets in AWS Data Exchange,
query them alongside first-party data sets in Amazon Redshift,
and analyze spatial data using CARTO geomapping and visualizations.
We’ll start in Redshift Query Editor version 2.
Let’s expand this Redshift Serverless workgroup to see its schema.
For the purposes of this example, we’ve loaded the CARTO tables with
first-party data about retail stores.
We’ve also installed the CARTO Analytics Toolbox for Redshift in the Functions folder.
Let’s create a tab to start exploring our data.
We’ll use a simple query to select all the data from our table of Target stores.
Let’s review the data our query has returned.
We can enrich this data with demographic information for the area around each store.
To do this, we’ll go to AWS Data Exchange and find a relevant third-party dataset. Let’s explore the available data products. We’ll search for “ACS sociodemographics.” The American Community Survey (ACS) publishes basic demographic data for the United States. Let’s select the most recent ACS data. We’ll quickly review the product page to see what’s included. Let’s continue to the subscription page. For our purposes, let’s not automatically renew this subscription. Now we’ll subscribe. After this subscription has been verified by the provider, we can find it in our list of subscriptions. Now that we’ve subscribed to the data we need, we can query it with Amazon Redshift. Let’s navigate to our Redshift Serverless dashboard and find our workgroup. Let’s take a look at our datashares. Here’s the dataset that we subscribed to. Let’s create a database from this datashare. Now that the database has been created, let’s return to the Redshift query editor. Let’s refresh the view and expand the workgroup again. Here’s the database we just created. This database contains tables with geographic and demographic information. Next, let’s run a query to see the data we acquired through AWS Data Exchange. Here’s the census data. Next, let’s join the data we already had to the data we subscribed to. First, we’ll convert the longitude and latitude coordinates of the Target stores and some competitor stores to a geometry data type using a built-in function in Amazon Redshift. The results are stored in a materialized view for each store. We can now use additional built-in spatial functions in Amazon Redshift on the geometry points we just created for our stores. The first function we’ll use finds the distance between two points. Let’s compare the coordinates of the Empire State Building to the coordinates of all Target stores in the United States. Here we can see the stores closest to the Empire State Building. We can use another built-in function to find out which US census block group each of our Target stores is located in. The results appear alongside our demographic data. Now let’s see how we can find areas that are good candidates for future stores based on potentially high sales. This is a machine learning command that makes use of Amazon Redshift ML. Redshift ML makes it easy for data analysts and database developers to create, train, and apply machine learning models using familiar SQL commands in Amazon Redshift data warehouses. We’re going to create a new model and use a selected set of sociodemographic attributes as input features. We’ll also use a function from the CARTO Analytics Toolbox for Redshift to create a Quadbin spatial index, which we will also use as an input into our ML model. As part of the model parameters, we can optionally define the model type, the problem type, and the objective. Now that the ML is running, we can visualize our data in CARTO. Let’s log in and connect to our Redshift cluster. The connection was successful. We can now begin to visualize our data on a map. We can add a source from our Redshift cluster. We’ll select the Target stores data that we used Redshift ML to create. We can use a custom query to add US census block groups and color them based on their population. Using the third-party data that we subscribed to earlier, we can visualize average sales of our Target stores based on population. We will now create Quadbin tiles in our area of interest and use the machine learning model we created to predict average sales for these Quadbin tiles based on the sociodemographic information that we subscribed to. We can also visualize competitor stores. We now have visualizations of many valuable data points to help us accurately project the most beneficial area to create a new store. You’ve just seen how to enable serverless geospatial analytics and machine learning with AWS and CARTO. You can learn more about this topic in the description and links for this video. Thanks for watching. Now it’s your turn to try.
Let’s explore the available data products.
We’ll search for “ACS sociodemographics.”
The American Community Survey (ACS) publishes basic demographic data for the United States.
Let’s select the most recent ACS data.
We’ll quickly review the product page to see what’s included.
Let’s continue to the subscription page.
For our purposes, let’s not automatically renew this subscription.
Now we’ll subscribe.
After this subscription has been verified by the provider, we can find it in our list of subscriptions.
Now that we’ve subscribed to the data we need, we can query it with Amazon Redshift.
Let’s navigate to our Redshift Serverless dashboard and find our workgroup.
Let’s take a look at our datashares.
Here’s the dataset that we subscribed to.
Let’s create a database from this datashare.
Now that the database has been created, let’s return to the Redshift query editor.
Let’s refresh the view and expand the workgroup again.
Here’s the database we just created.
This database contains tables with geographic and demographic information.
Next, let’s run a query to see the data we acquired through AWS Data Exchange.
Here’s the census data.
Next, let’s join the data we already had to the data we subscribed to.
First, we’ll convert the longitude and latitude coordinates of the Target stores
and some competitor stores to a geometry data type using a built-in
function in Amazon Redshift.
The results are stored in a materialized view for each store.
We can now use additional built-in spatial functions in Amazon Redshift
on the geometry points we just created for our stores.
The first function we’ll use finds the distance between two points.
Let’s compare the coordinates of the Empire State Building to the coordinates
of all Target stores in the United States.
Here we can see the stores closest to the Empire State Building.
We can use another built-in function to find out which US census block group
each of our Target stores is located in.
The results appear alongside our demographic data.
Now let’s see how we can find areas that are good candidates for future
stores based on potentially high sales.
This is a machine learning command that makes use of Amazon Redshift ML.
Redshift ML makes it easy for data analysts and database developers
to create, train, and apply machine learning models using familiar SQL commands
in Amazon Redshift data warehouses.
We’re going to create a new model and use a selected set of sociodemographic
attributes as input features.
We’ll also use a function from the CARTO Analytics Toolbox for Redshift
to create a Quadbin spatial index, which we will also use as an input into our ML model.
As part of the model parameters, we can optionally define the model type,
the problem type, and the objective.
Now that the ML is running, we can visualize our data in CARTO.
Let’s log in and connect to our Redshift cluster.
The connection was successful.
We can now begin to visualize our data on a map.
We can add a source from our Redshift cluster.
We’ll select the Target stores data that we used Redshift ML to create.
We can use a custom query to add US census block groups and
color them based on their population.
Using the third-party data that we subscribed to earlier,
we can visualize average sales of our Target stores based on population.
We will now create Quadbin tiles in our area of interest and use the machine
learning model we created to predict average sales for these Quadbin tiles
based on the sociodemographic information that we subscribed to.
We can also visualize competitor stores.
We now have visualizations of many valuable data points to help us
accurately project the most beneficial area to create a new store.
You’ve just seen how to enable serverless geospatial analytics and
machine learning with AWS and CARTO.
You can learn more about this topic in the description and links for this video.
Thanks for watching. Now it’s your turn to try.