3 Replies Latest reply on Mar 28, 2016 10:03 PM by Bill Lyons

    Calculating Distance along a geographical polygon

    Beau Bellamy

      I have some data with latitude and longitude of a train location along its journey. The data table has values for the distance, but these are not distances to the latitude/longitude points, so I would like to calculate the true distance along the journey in order to do an analysis on average performance with multiple trains.

      I know to calculate the distance, I will need 2 new columns in the table to add the destination latitude and longitude, then I can use the great circle/haversine formula to calculate the distance. My problem is that im having trouble trying to insert these columns in to the table. I've tried inserting a copy of the original latitude and longitude columns offset by 1 with the Custom SQL query, but I am not familiar with SQL too much.

       

      This may be an SQL question, but I cant even seem to add a column using this SQL command

      alter table Example add NewColumn int identity(1,1)

       

      I get:

      [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax, near the keyword 'alter'.

      [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax, near ')'.

       

      Can anyone help with the SQL commands or is there an alternative way to calculate the distance?

       

      Attached is a representation of my data

        • 1. Re: Calculating Distance along a geographical polygon
          Bill Lyons

          Where are you trying to execute this command? I just tried your exact line of code in a table I just created for that purpose, and it worked perfectly using SQL Management Studio. If you are trying to use that line of code in a Tableau Custom SQL connection, that is your problem. Tableau wraps your SQL in a SELECT statement, making it an invalid query, thus the syntax error. This is an important security measure to prevent malicious tampering with your database through SQL injection.

           

          What points are you trying to calculate the distance between? Are both points in the existing table? If so, I can probably help create some Custom SQL to do that. If not, can you join a table of the other points to this table? Where is this set of points?

          • 2. Re: Calculating Distance along a geographical polygon
            Beau Bellamy

            I was trying to execute the command in the Tableau custom SQL form surrounded by the SELECT and FROM statements. I was able to extract the existing columns, but not create any.

             

            I'm trying to calculate the distance between each successive lat and long point in the table. I was trying to create a new column for each lat and long to create the second points for each record to allow for the calculation.

             

            The Custom SQL to do this would be appreciated

            • 3. Re: Calculating Distance along a geographical polygon
              Bill Lyons

              Since you only want the distance between adjacent points, it is actually much simpler. No custom SQL is needed. Just a couple of LOOKUP() functions in calculated fields, one for previous latitude (LOOKUP(AVG([Latitude]),-1), table down, and one for the previous longitude (LOOKUP(AVG([Longitude]),-1), table down. I then calculated the distance with a simple spherical-earth distance formula.

               

              Is that what you are looking for?