Learning more about your store locations with the CartoDB's Data Observatory

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[1200]  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[1200]::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'
 )
 

‍

9. Enjoy

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!