8 Replies Latest reply on Sep 5, 2013 8:11 AM by mark.pickett

    Creating Radius calc from lat/lon based on list in parameters

    mark.pickett

      Hi,

       

      I'm trying to create a map that shows the radius between customers and 3 types of stores.  I've tried creating parameters for a list of "type" variables and building a calculated field based on variable type i.e. cust is 1 mile from to go and 4 miles from retail.  It hasn't worked well.

       

      Been struggling trying to figure out if I need to restructure the dataset while reviewing the forum and vids for insight, but thought former wasn't an elegant solution - although I am a Tableau newbie.  I've seen the calc for radius on circle but can't get past using lat/lon based on "type."

       

      Attached is a sample dataset.  Any help is GREATLY appreciated.

        • 1. Re: Creating Radius calc from lat/lon based on list in parameters
          Jim Wahl

          Hi Mark,

           

          It looks like the Excel list you attached is a list of store locations?

           

          Do you have a list of customers and their lat/long?

           

          Jim

          • 2. Re: Creating Radius calc from lat/lon based on list in parameters
            mark.pickett

            Hi Jim,

             

            I have "Cust" embedded as part of type.  Thanks for your interest.    

             

            m.

            • 3. Re: Creating Radius calc from lat/lon based on list in parameters
              Jim Wahl

              I'm still a little confused on the data structure and goal:

               

              What do the rows in the Excel represent?

               

              What points are you trying to calculate the distance between?

              • 4. Re: Creating Radius calc from lat/lon based on list in parameters
                mark.pickett

                Each row represents a location of a specific type i.e. either Cust, Wholesale, Mom and Pop, Retail, or To Go.  I'd like to calculate the distance between Cust and other types.

                 

                Was hoping there was a way for Tableau to handle the data structure w/o having to resort to restructuring in the DB.

                • 5. Re: Re: Creating Radius calc from lat/lon based on list in parameters
                  Jim Wahl

                  Hi Mark,

                   

                  I looked at this again this evening and I don't see an easy way to do this entirely with table calculations.

                   

                  Or, perhaps, it's just a lot easier to do this with custom SQL. Custom SQL doesn't require you to modify the data source, but it does require you to enter some basic SQL---in this case a cross join---when connecting to the data source.

                   

                  A cross join will create a one-to-many mapping for each customer. Take a subset of your data with two customers, and two types of each store:

                  2013-09-05 04-15-25.png


                  In order to find the minimum distance between each customer and each type of store with n customers and m stores, you need n * m rows. With the above data, 16 rows that look like this (I've created a customer ID by concatenating the lat/long):

                  2013-09-05 04-19-20.png

                   

                  This is relatively easy to do with a SQL query (relative to table calcs, anyway), although if you're using an Excel, Access or CSV file, which use the Microsoft Jet SQL engine, the format is a bit funky.

                   

                  A cross join combines every row in the first table with every row in the second table. In Microsoft Jet it looks like this:

                  SELECT
                    TABLE1.[FieldOne],
                    TABLE2.[FieldTwo]
                  FROM TABLE1,TABLE2
                  

                   

                  In Tableau, add a new connection by selecting from the top menu bar Data > Connect to Data. For your sample data, select the same Excel worksheet, but choose Custom SQL. Click on the "..." and paste the below formula. The Jet connection is difficult to debug (a missing comma results in "file permission denied" error), and when building this from scratch it helps to go incrementally.

                   

                  Mapping this to your data: 

                  SELECT 
                    sub1.[LAT_NB] &  "|" & sub1.[LON_NB] AS [Cust ID],
                    sub1.[LAT_NB] AS [Cust_LAT_NB],
                    sub1.[LON_NB] AS [Cust_LON_NB],
                    sub2.[Type] AS [Store],
                    sub2.[LAT_NB] AS [Store_LAT_NB],
                    sub2.[LON_NB] AS [Store_LON_NB],
                    SQR(
                    ((sub2.[LON_NB]*0.01745 - sub1.[LON_NB]*0.01745) * COS((sub2.[LAT_NB]*0.01745 + sub1.[LAT_NB]*0.01745) / 2) * 
                     (sub2.[LON_NB]*0.01745 - sub1.[LON_NB]*0.01745) * COS((sub2.[LAT_NB]*0.01745 + sub1.[LAT_NB]*0.01745) / 2)) +
                    ((sub2.[LAT_NB]*0.01745 - sub1.[LAT_NB]*0.01745) * (sub2.[LAT_NB]*0.01745 - sub1.[LAT_NB]*0.01745))) * 6371 AS [Distance (km)]
                  FROM 
                      (
                          SELECT
                          [Sheet1$].[Type] AS [Type],
                          [Sheet1$].[LAT_NB] AS [LAT_NB],
                          [Sheet1$].[LON_NB] AS [LON_NB]
                          FROM [Sheet1$]
                          WHERE [Sheet1$].[Type] = "Cust"
                      ) AS [sub1], 
                      (
                          SELECT
                          [Sheet1$].[Type] AS [Type],
                          [Sheet1$].[LAT_NB] AS [LAT_NB],
                          [Sheet1$].[LON_NB] AS [LON_NB]
                          FROM [Sheet1$]
                          WHERE [Sheet1$].[Type] <> "Cust"
                      ) AS [sub2]
                  
                  

                   

                  The tables in the first example have been replaced with subqueries sub1 and sub2. The first returns just the customer rows, the second all of the other rows. I've also put the distance calculation directly in the formula. Since you need to calculate this for all combinations of customer and store locations, I don't see much advantage of doing this in Tableau. Note also that I'm using a simplified Pythagoras' theorem-based distance calculation. Most examples use the Haversine "great-circle" method, which adds a lot of complexity for (I suspect in this case) small accuracy improvements. See a nice discussion of the formulas here: Calculate distance and bearing between two Latitude/Longitude points using Haversine formula in JavaScript.

                   

                  After creating the Custom SQL connection, it's easy to generate a crosstab of the customer and min distance information by dragging Store to the Columns shelf, Cust ID to the Rows shelf, and Distance (MIN) to the Labels shelf. Note the distances are kilometers.

                  2013-09-05 04-41-36.png

                   

                  I'm not sure how you want to display these on a map, but I used the original data source to create a basic map with a circle / mark fo reach store and customer. After double-clicking on LAT_NB and LON_NB, you'll just have one circle, since by default Tableau will average these. To display a mark for each row, you can either

                  1. Convert the lat / lon measures to dimensions by right-clicking on the pills on the Row and Column shelves.
                  2. Create a new field Row ID that is unique to the row level and drop this on the Detail shelf.

                  Row ID =

                  STR([LAT_NB]) + "|" + STR([LON_NB])
                  

                   

                  I used the Row ID approach, because I wanted to add the min distance in the tooltip. Since min distance is stored in the Custom SQL table, this requires blending and a linking field. After creating Row ID, Select Data > Edit Relationships and select Custom > Add...

                  2013-09-05 04-51-08.png

                   

                  Next, in the Custom SQL data source Data pane, I created fields for the Distance to Wholesale, Distance to Mom-Pop, ... =

                  IF [Store] == 'Mom_Pop' 
                  THEN [Distance]
                  END
                  

                   

                  And I added these to the tooltip (notice the orange bar and database symbol on the pills indicating these are from the secondary, Custom SQL data source).   The result looks like this:

                  2013-09-05 04-55-55.png

                   

                  Let me know if some of this isn't clear --- or if I've missed your objective.

                   

                  Jim

                  • 6. Re: Creating Radius calc from lat/lon based on list in parameters
                    mark.pickett

                    Jim,

                     

                    You're awesome!!!  Thanks for providing such a well written and thorough explanation.  I've been looking at this for at least a few weeks and this the most comprehensive set of insights.  Albeit I'm new to Tableau and the community, this is much more guidance than I was expecting.  I'm grateful.

                    • 7. Re: Creating Radius calc from lat/lon based on list in parameters
                      Jim Wahl

                      You're welcome! The Tableau forums are one of the great things about Tableau.

                       

                      If you'll be calculating these distances for tens of thousands of customers and hundreds of stores, it may be worth looking into a database with GIS features, such as SQL Server or Postgres with PostGIS. ...

                       

                      Jim

                      1 of 1 people found this helpful
                      • 8. Re: Creating Radius calc from lat/lon based on list in parameters
                        mark.pickett

                        Thanks.  I'm in an Oracle and Teradata shop currently. 

                         

                        You've quantified the population accurately.  I also think there's an opportunity to further segment Cust for this project since a Cust is 'assigned' to a store typically in the state in which they reside; border cities notwithstanding.  For example, I don't need to know the distance between a Cust that's in Evanston, IL to a store in Albany, NY.  I'd only need to define the Cust distance to stores in within the state of IL.