Distance Calculation using Latitude and Longitude

[#1]

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

[#2 · Accepted 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

[#3 · Accepted solution]

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.

[#4 · Accepted solution]

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.

[#5 · Accepted solution]

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] )

);

[#6 · Accepted solution]

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

[#7]

Hi @ron_horne ,

I tried to use this script for calculating distances, but I had an error message when I tried. Since the script is from 2016, maybe some things have changed?

An update of the script will be appreciated.

[#8]

Hi @MerGrung ,

if you can post a sample of the data and the script you are using we can fix it for you.

otherwise, it is hard to tell what is the source of the error.

one thing that most likely has changed since 2016 is the reference to the sample data. in my original script it had a reference to JMP11 x86. if you are using a later version it will for sure give you an error message.

[#9]

Hi @ron_horne 
Thanks for your quick reply!

I worked a bit more with the script, and discovered that the fault was on my side. I managed to calculate the distances without problem.

Thank you very much!

[#10 · Accepted solution]

Hi ron_horne and I hope you are doing well. I know this has taken some time since we last chatted on this thread. The project was put on hold and it is now up and running and in about few weeks, the resulting table will be deployed in production by our campaign team.

I did apply the script and it worked perfectly as expected!! Great job!! 

As you know the script calculates the distance between pairs of (HH Lat. and HH Long.) to (PS Lat and PS Long.), which is what was originally what the project was designed to capture. I have used the resulting distance to calculate the time that it will take for each household to attend one of our program that is 1, 3, or 5 hours away from their homes. 

Now, the issue is that I would like to script to calculate the distance between each pair of Lat. and Long.

For example:

In the table below, I have computed the distance and time between row 1 pairs of Lat. & Long. Which is perfect!! that will do for all 20 rows giving 20 unique distances between household location to the destination of the program. 

So, in row 1 the program # 15896 with PS Lat. 56.47 N & PS Long. 132.38 S is certain distance away from the Household whose HH Lat. & HH Long.

Since we want to know the distance of each household from each program #, how can I calculate, such that, JMP captures the distance between each household Lat and Long acrosss each Program Lat and Long and not just the first program? In this table, I have 20 rows, using what I have described we should have 400 difference distance calculation instead of just 20. Do you know a way to go around this? 

My original HH Data Table has 156,737 Unique households records with their respective HH Lat. & HH Long. The Household file has 15,655 unique HH Lat. & HH Long.

The Program Data Table with 2,747 Unique Program #s with 329 unique PS Lat. & PS Long. I want to calculate the distance from each unique household Lat. & Long to each unique Program Lat. & Long., such that, the distance is calculated across each program. That will be 15,655 *329 and the resulting table or the total distance should be about 5,150,495 records.