1 Reply Latest reply on Nov 12, 2014 12:13 PM by brad.earle.1

    How to map a path between two cities with a stop in between?

    Anton Heyl

      Hi,

       

      I have some 30000 rows of data with orgin city and final destination, all of which pass through the same city in between.

      Is it possible to map these flows just using one row?

       

      OriginViaFinal destination
      City ACity BCity C
      City DCity BCity E

       

      I have a lot more columns of data in addition so it makes the file a bit difficult to handle if I have to duplicate the rows in order to map the flows.

      What I want is simply a point-to-point-to-point map using just one row. (or at least keeping the amount of rows to a minimum)

        • 1. Re: How to map a path between two cities with a stop in between?
          brad.earle.1

          Most folks have handled the duplicating of rows through a Custom SQL connection to their data -- creating a union statement so that the path sequence (4 in your case) become copies of the 'master table' with the Path sequence number, the origin / destination and common pathname created in the SQL statement.  Obviously, you'll have to get the latitude/longitude for the various cities from somewhere in order to place the cities onto a map.

           

          For your data, this would look something like:

          Select '1' as PathSequence,

          Origin as startpoint,

          via as endpoint,

          originlatitude as latitude, originlongitude as longitude,

          origin + ' -> ' + via as PathName,

          Origin, Via, [Final Destination]

          union

          Select '2' as PathSequence,

          via as startpoint,

          Origin as endpoint,

          vialatitude as latitude, vialongitude as longitude,

          origin + ' -> ' + via as PathName,

          Origin, Via, [Final Destination]

          union

          Select '3' as PathSequence,

          via as startpoint,

          [Final Destination] as endpoint,

          vialatitude as latitude, vialongitude as longitude,

          via + ' -> ' + [Final Destination] as PathName,

          Origin, Via, [Final Destination]

          union

          Select '4' as PathSequence,

          [Final Destination] as startpoint,

          via as endpoint,

          [Final Destinationlatitude] as latitude, [Final Destinationlongitude] as longitude,

          via + ' -> ' + [Final Destination] as PathName,

          Origin, Via, [Final Destination]