Subscribe Bookmark RSS Feed

Distance Calculation using Latitude and Longitude

jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Does anyone has idea how to calculate the distance between states in the US using either zip code or latitude and longitude?

Jenkins Macedo
1 ACCEPTED SOLUTION

Accepted Solutions
Solution

why not start here?

Add-In: Spatial Data Analysis

and here

Distance Tool Add-In

or just check my attached script for another option using a formula.

ron

5 REPLIES
Solution

why not start here?

Add-In: Spatial Data Analysis

and here

Distance Tool Add-In

or just check my attached script for another option using a formula.

ron

jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Great! Thanks ron_horne​. I initially started using tjhe Geocoder-Map. But I also see that I had SDA. I also downloaded the Distance Tool. All pretty cool. What I am trying to do is pretty simple and straight forward. Using the location by City and State- (zip code/ geographic coordinates) of our programs and the zip code of households in our databases. I am developing a simple file which shows programs that are under 50 miles of their residence compare to those that are 100 miles and proposed to the campaign team 2 to 3 programs that are either within an hour or two drive for a routine email marketing campaign based on their previous interests.

Jenkins Macedo
jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Hi ron_horne​ I tried using you "Distance to next" script to calculate the distance between two pairs of Lat and Long coordinates, but the script isn't working or maybe I am not doing something right. Attached is the first 20 rows of the table table. I would like to know the distance (in miles) between HH Lat and HH Long to PS Lat and PS long. I think once I can get the formula fitted to my data, it can be faster. I am not so sure how the Distance Tool would work here as I am wondering if I have to draw a line for each coordinate as that would take too much time as my table has over 2 million rows or can that be automated. I used the Mapping: JMP Geocoder to generate the coordinates below. How would you ron_horne​ or some else customize these in Distance to next script posted previously? Any help would be appreciated. Thanks

Some information of the below variables:

Zip Code: is the matched household (HH) to the Progra Start Zip Code (PS).

Program Number: these numbers reflect programs we offer for which we would like to promote to those households within the same program-to-household zip code.

HH Latitude: Latitude associated with the Household zip code

HH Longitude: Longitude associated with the Household Zip Code

PS Latitude: Latitude associated with program start zip code. That is the location where the program started.

PS Longitude: Longitude associated with the program start zip code. That is the location where the program started.

11935_Untitled.jpg

Jenkins Macedo
ron_horne

Super User

Joined:

Jun 23, 2011

hi jenkins.macedo​,

my original script calculated the distance between two consecutive rows in kilometers.

i have now amended it to produce the miles distance for each row.

try this on your table

Clear Globals();

Names Default To Here( 1 );

// reference the data table

dt = current data table();

// introduce the column with the distance to be updated in the dots table

dt << add multiple columns( "Distance", 1, after( 6 ), numeric );

// extract the longitude and the latitude values from the table - as radians

HHx = (Column( dt, "HH Longitude" ) << getValues) * Pi() / 180 ;

HHy = (Column( dt, "HH Latitude" ) << getValues) * Pi() / 180 ;

PSx = (Column( dt, "PS Longitude" ) << getValues) * Pi() / 180 ;

PSy = (Column( dt, "PS Latitude" ) << getValues) * Pi() / 180 ;

// declare the calculation function in Miles

haversine = Function( {long1, lat1, long2, lat2, R = 6371},

      {Default Local},

      a = Sin( (lat1 - lat2) / 2 ) ^ 2 + Cos( lat1 ) * Cos( lat2 ) * Sin( (long1 - long2) / 2 ) ^ 2;

      c = 2 * ATan( a ^ 0.5, (1 - a) ^ 0.5 );

      d = (R * c)*0.6213712;// miles

);

wait (0.01);

// run the function along the rows of the table

For( i = 1, i <= N Row( dt ) - 1, i++,

      dt:distance[i] = haversine( HHx[i], HHy[i], PSx[i], PSy[i] )

);

jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Thanks ron_horne​. I will let you know the outcome once I tried it. But I think it looks great! Big tx

Jenkins Macedo