There are a lot of ways for you to analyze store locations using CartoDB. The combination of Data Observatory, Location Data Services, and Deep Insights gives you a complete toolkit for building actionable dashboards and delivering insights using maps. Today, we are going to really quickly show you a few ways to interact with location data using three CartoDB tools: the Data Observatory, Location Data Services, and Deep Insights.
1. Upload a new dataset of store locations
I’m going to use one I have lying around. You can grab it here if you want to follow along. It’s just a few handfuls of locations from the Boston area.
2. Georeference our point locations
CartoDB makes it incredibly simple to turn street addresses into mappable coordinates. This process, called georeferencing, can be done right in the CartoDB Editor.
3. Enrich your data using the Data Observatory
Next, we can explore our data using the map in the CartoDB Editor. We can also enrich it by using the Data Observatory as a source of augmentation. You can currently write SQL functions to update your tables of location data on demand. Take a look:
The relevant SQL to create a column and augment it with the Data Observatory at the same time looks like this:
ALTER TABLE store_performance_data ADD COLUMN pop_density NUMERIC; UPDATE andrew.store_performance_data SET pop_density = OBS_GetMeasure(the_geom, 'us.census.acs.B01003001')
This is just one of hundreds of measures you can grab. Read about them in the documentation.
4. Combine your data with Data Observatory data
Once you have enriched your table, it’s pretty simple to combine two or more columns through simple operations. For example, here we will divide the total sales of each store by the population pulled in from the Data Observatory.
5. Grab boundary data from local regions
There are a few different ways to grab boundary data from the Data Observatory. One of my favorites is by using point data to supply an envelope to the Data Observatory and then gather all the boundaries within that envelope. In this example, I’m grabbing all the Census Block Groups.
The SQL may look long, but we’ll be working to make this easier and easier over time. The key thing to notice is that it writes results into an existing table,
new_table_name with a geometry column (default in CartoDB), and an attribute column called
geom_refs. Inside the request, it uses a Data Observatory function called
OBS_GetBoundariesByGeometry. We are accessing a boundary layer called
us.census.tiger.block_group_clipped, which is our custom coastline clipped block group data.
INSERT INTO new_table_name (the_geom, geom_refs) SELECT the_geom, geom_refs FROM ( SELECT (OBS_GetBoundariesByGeometry( ST_Envelope(ST_Collect(the_geom)), 'us.census.tiger.block_group_clipped')).* FROM andrew.store_performance_data) as results GROUP BY the_geom, geom_refs
You can always use those to pull in data about the areas. Here we’ll grab Per Capita Income for each block group.
6. Overlay trade areas
There are many different ways to define trade regions in CartoDB. One of the most interesting is by defining polygons by walking or driving distance. Here, we’ll take each store location and define a 20 minute walking distance around them. We’ll write those into a new table of polygons to overlay on our map.
This is pretty cool in CartoDB because you can calculate these walking distances right in the Editor pretty easily. In the SQL example, we define
walk as the method, and we provide an array with a single value,
ARRAY, which means just 1200 seconds, or 20 minutes. We can actually get multiple walking distances at once by adding multiple values to the array.
INSERT into untitled_table_54 (the_geom, data_range) SELECT the_geom, data_range FROM ( SELECT (CDB_Isochrone(the_geom, 'walk', ARRAY::integer)).* FROM andrew.store_performance_data ) as results
7. Use Deep Insights for a custom dashboard
Once you’ve seen your data on the map, you can improve your analysis by exploring it in a dynamic and custom interface. We have a library called Deep Insights that allows for you to build amazing dashboards around your data. In the following example, I’ve built a dashboard that allows you to drill-down into the Per Capita Income, the overlap of the block groups, and the trade areas, thereby allowing you to identify possible markets and potentially new territories. See it here.
8. Explore local segmentation data
This morning we released a new dataset of segmentation for the USA. This is a really interesting dataset for exploring location trends in businesses, people, and events. I wanted to quickly test adding each store’s local segment classification as a new column and then building a Deep Insights dashboard. Take a look at the results:
You can access segmentation from the Data Observatory with a simple SQL UPDATE like this:
UPDATE untitled_table_57 SET segment55 = OBS_GetCategory( st_pointonsurface(the_geom), 'us.census.spielman_singleton_segments.X55' )
These are just a few peeks into the powerful capabilities inside of CartoDB. Pull them apart and start applying them to your workflows. We’re excited to see what you start to build!
Happy data mapping!