3 Replies Latest reply on Aug 26, 2013 8:43 AM by Allan Walker

    Map two sets of lat/long data

    Michael Doo

      I'm trying to map two files from a standard GTFS dataset: stops.txt and shapes.txt (see example file contents here). I was wondering if this capability has been added in Tableau 8 and if not, what an acceptable workaround is. The issue arises because the shapes.txt and stops.txt files do not match lat/long, usually as a result of different precision.

       

      My output should look like so: lines representing each route (given by the shapes.txt file) and dots representing each stop (given by the stops.txt file). In the past, I've fudged the data a bit by trying to match the two files, but it gave a suboptimal output.

       

      Please advise.

        • 1. Re: Map two sets of lat/long data
          Allan Walker

          Hi Michael,

           

          I've been considering this data as well.  Currently I have a scheduled cron job via the UTA API that parsers the output to PostgreSQL tables.  I haven't yet pulled an extract using joins, but I suspect it might be complex.  Just haven't had the time, or I would have used the sample-feed as a test first, but I had to prove out the connection ability (as a proof of concept).  It's certainly interesting.

           

          Could you want to post a screenshot to share with the community?

           

          Considering the route optimization issue, have you looked at pgrouting yet - that's why I wanted to pull into Postgres/PostGIS.

          • 2. Re: Map two sets of lat/long data
            Michael Doo

            Allan,

             

            Using your UTA example, the output would look like this, except the route lines would be dictated by the shapes.txt file in GTFS. I'm able to map the two sets (shapes.txt and stops.txt) separately so my goal is to combine those two datasets, hopefully without having to do complex joins. The real problem comes with Tableau's inability to be able to map more than one set of lat/long columns.

             

            When I tried to match the two sets before, it was basically programmatically searching through the shapes.txt file for the closest match to the given stop's lat/long. For example, if the stop in stops.txt is located at (-27.503864,152.978714) and the two closest points in shapes.txt are (-27.5038623196,152.979583709) and (-27.5038008005,152.978722116) then the accuracy is (0.0000016804, 0.000869709) and (0.0000631995, 0.000008116), respectively. These numbers vary widely so it's difficult to go about it this way.

             

            I'm hoping someone from Tableau can chime in as to whether this is built in or not. I know it wasn't in Tableau 7 but I'm not sure if it's in Tableau 8.

             

            What do your PostegreSQL tables look like?

            • 3. Re: Map two sets of lat/long data
              Allan Walker

              Hi Michael,

               

              In all honesty I just haven't had time to look, but when I had to do this for the Utah State University Fire and Police viz (two separate lat/longs) we did a left outer join to get one set of lat/longs.  I suspect it will either be the same again, or a union.  I'm busy doing other stuff for the University, department summary dashboards; and it's the first week back for students, so things are hectic, to say the least!

               

              Keep us updated, this has the makings of a very exciting project...

               

              Best Regards,

               

              Allan