8 Replies Latest reply on Mar 12, 2012 5:48 AM by Andy Piper

    Connecting coordinates in maps

    Martin Luxhøj

      Hello EXPERTS!,

       

      Ok I have a problem. I want to plot deliveries from a set of lat and long coordinates to a lot of different sets of lat and long coordinates. I do not want to use the approach in this tutorial http://kb.tableausoftware.com/articles/knowledgebase/using-path-shelf-pattern-analysis as I think it is too manual. Could any one please help me?

       

      Thank you.

       

      Martin

        • 1. Re: Connecting coordinates in maps
          Andy Piper

          Currently, the method described in which there is one record for each point in the route, one lat field used, one long field used, one leg field used and one routeid field used. I've been using this method for years, and it is manual; but I do know that this is on the radar to make this process easier.

           

          I'm not a SQL expert, but I wonder if there would be any way to duplicate the data set, add the leg info and one lat long pairing, then with SQL to join the two data sets together to form the multiple records needed to draw the line.

          • 2. Re: Connecting coordinates in maps
            Andy Piper

            At the end of the first sentence, I meant to add that this is the only way I know how to achieve the proper point-to-point routes.

            • 3. Re: Connecting coordinates in maps
              Shawn Wallwork

              Martin, sure it's a little bit manual, but it's not that manual. I was able to reshape your data in less than 2 minutes with just a few copy & pastes. Then I connected and create this workbook in less than a minute (and I've never done one before). A three minute viz, not sure you can automate anything that'll trim much time. The trick to speed is to use numbers instead of strings so you can let Excel auto-fill the values. The sort of the records doesn't matter, so I left it divided into the two sections. Here's what the viz looks like:

              Martin.PNG

              Find both the workbook and excel file attached. Hope this helps,

               

              --Shawn

              1 of 1 people found this helpful
              • 4. Re: Connecting coordinates in maps
                Richard Leeke

                Here's how to do it Andy's way with a bit of custom SQL to handle transforming that data source. No manual editing of the Excel - but it took me longer than it took Shawn because I missed a comma in the SQL and spent 10 minutes staring at before I noticed what I'd done!

                 

                Paths.png

                • 5. Re: Connecting coordinates in maps
                  Shawn Wallwork

                  Nice Richard! You're of course taking the Green Star on this on this one, but I'm awarding myself the Git-R-Done badge:

                  GitRdone.jpg

                   

                   

                   

                  --Shawn

                  • 6. Re: Connecting coordinates in maps
                    Martin Luxhøj

                    Gentlemen.......I bow respectfully in the dust!!!

                     

                    Shawn....Quick and dirty....I like it!

                     

                    Richard....this is what I was looking for thank you very much!!!!

                     

                    Originally I tried to do the viz i R because that software can do arches and also weigh the vectors after for example count which will help the rather messy picture......but alas I could not get it to work....I haven't given up though!

                     

                    Martin

                    • 7. Re: Connecting coordinates in maps
                      Richard Leeke

                      You can put count on the Size shelf which will give you fatter lines for the bigger coiunts, is that what you mean?

                      • 8. Re: Connecting coordinates in maps
                        Andy Piper

                        Excellent! I knew there had to be a way with SQL. I'll be giving this a try myself -- it could save some time when the data set only contains OD-Pairings instead of multi-stop routes.

                         

                        Come to think of it, I suppose there may even be ways to work with multi-stop routes within SQL provided there were additional fields that contained the extra stop information (i.e.: Origin, Stop 1, Stop 2, Stop 3, Stop 4, and Lat & Long fields for the origin and each Stop field). If Stop 4 was null, then you would only have a route of 4 locations (origin + 3 additional stops); if  Stop2 was null, you would only have the simple OD-Pairing.