topic Re: Distance Calculation using Latitude and Longitude in Discussions

Distance Calculation using Latitude and Longitude

jenkins_macedo — Mon, 27 Jun 2016 13:05:00 GMT

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

Re: Distance Calculation using Latitude and Longitude

ron_horne — Fri, 28 Oct 2016 13:28:04 GMT

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

Re: Distance Calculation using Latitude and Longitude

jenkins_macedo — Mon, 27 Jun 2016 14:21:50 GMT

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.

Re: Distance Calculation using Latitude and Longitude

jenkins_macedo — Wed, 19 Oct 2016 03:17:12 GMT

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.

Re: Distance Calculation using Latitude and Longitude

ron_horne — Thu, 30 Jun 2016 20:02:47 GMT

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

);

Re: Distance Calculation using Latitude and Longitude

jenkins_macedo — Fri, 01 Jul 2016 11:51:40 GMT

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

Re: Distance Calculation using Latitude and Longitude

jenkins_macedo — Thu, 24 Aug 2017 21:05:58 GMT

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.

Re: Distance Calculation using Latitude and Longitude

mjvincent87 — Wed, 28 Aug 2019 19:28:17 GMT

Another thank you to ron_horne! Just a couple questions:

Just want to confirm the output is statute miles, correct?

Also, do you have a script for bearing between two coordinates?

Re: Distance Calculation using Latitude and Longitude

Craige_Hales — Thu, 29 Aug 2019 01:01:46 GMT

Amazing collection of formulas:

http://www.movable-type.co.uk/scripts/latlong.html

Re: Distance Calculation using Latitude and Longitude

ron_horne — Sun, 01 Sep 2019 00:30:32 GMT

Hi @mjvincent87

have a look at this script and let me know if it is the direction you are thinking of:


Clear Globals();
Names Default To Here( 1 );

// open data file (dots) - this includes the observation we want to update
dt = Open( "$SAMPLE_DATA/Hurricanes.jmp" );

// introduce the column with the distance to be updated in the dots table
dots << New column ( "Distance", Numeric );

// introduce the column with the distance to be updated in the dots table
Dots << New Column("Bearing", Numeric, "Continuous", Format("Latitude DMS", "PUNSGN", 15, 0));

// extract the longitude and the latitude values from the table - as radians
x = (Column( dots, "Longitude" ) << getValues) * Pi() / 180 ; 
y = (Column( dots, "Latitude" ) << getValues) * Pi() / 180 ; 


// declare the distance calculation function in KM based on world radius of 6371 KM.
// For statute miles multiply final distance d by 0.621371.
// For nautical miles multiply final distance d by 0.539957
haversine = Function( {long1, lat1, long2, lat2, R = 6371.009},
	{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.621371 For statute miles;
);

wait (0.01);

// run the function along the rows of the table
For( i = 1, i <= N Row( dots ) - 1, i++,
	dots:distance[i] = haversine( x[i], y[i], x[i + 1], y[i + 1] )
);


// Declare the Bearing calculation function
Bearing = function ({long1, lat1, long2, lat2 },
	{Default Local},
	abc = sin (long2 - long1) * cos (lat2);
	def = cos (lat1) * sin (lat2) - sin (lat1) *cos (lat2) * cos (long2 - long1) ;
	brng = mod (( (atan ( abc, def )) * 180 / pi() ) + 360 , 360)  ;

);
wait (0.01);

// run the Bearing function along the rows of the table
For( i = 1, i <= N Row( dots ) - 1, i++,
	dots:Bearing[i] = Bearing( x[i], y[i], x[i + 1], y[i + 1] )
);

Best,

ron

Re: Distance Calculation using Latitude and Longitude

ron_horne — Sun, 01 Sep 2019 01:06:19 GMT

Hi @mjvincent87 and @jenkins_macedo

another way of doing this is with column formula in a data table. try the following script

Clear Globals();
Names Default To Here( 1 );

// Open data file
dt = Open( "$SAMPLE_DATA/Cities.jmp" );

// add column for distance
dt << New Column( "latrad", formula( :Latitude * (Pi() / 180) ) );
dt << New Column( "longrad", formula( :Longitude * (Pi() / 180) ) );

dt << New Column( "Distance", Numeric,
	formula(
		6371.00 * 2 * ATan(
			(Sin( (:latrad - Lag( :latrad, -1 )) / 2 ) ^ 2 + Cos( :latrad ) * Cos( Lag( :latrad, -1 ) ) * Sin( (:longrad - Lag( :longrad, -1 )) / 2 )
			 ^ 2) ^ 0.5,
			(1 - (Sin( (:latrad - Lag( :latrad, -1 )) / 2 ) ^ 2 + Cos( :latrad ) * Cos( Lag( :latrad, -1 ) ) * Sin(
				(:longrad - Lag( :longrad, -1 )) / 2
			) ^ 2)) ^ 0.5
		)
	)
);

dt << New Column( "Bearing", Numeric, "Continuous", Format( "Latitude DMS", "PUNSGN", 15, 0 ),
	formula(
		brng = Mod(
			((ATan(
				Sin( Lag( :longrad, -1 ) - :longrad ) * Cos( Lag( :latrad, -1 ) ),
				Cos( :latrad ) * Sin( Lag( :latrad, -1 ) ) - Sin( :latrad ) * Cos( Lag( :latrad, -1 ) ) * Cos( Lag( :longrad, -1 ) - :longrad )
			)) * 180 / Pi()) + 360,
			360
		)
	)
);

Re: Distance Calculation using Latitude and Longitude

MerGrung — Fri, 24 Apr 2020 10:12:42 GMT

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.

Re: Distance Calculation using Latitude and Longitude

ron_horne — Fri, 24 Apr 2020 11:36:47 GMT

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.

Re: Distance Calculation using Latitude and Longitude

MerGrung — Tue, 28 Apr 2020 21:24:11 GMT

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!