JMP Analytics - Working with geospatial properties that also varies with time

[#1]

Hi all,

I need some ideas and help, please. I'm looking to work with some data that varies by geographical location and also with time.  I have the latitudes and longitudes of the locations and I have the hourly measurements for several time periods.  How do I set this up in JMP rows and columns, such that I can graph (map) the property as it changes with time, and investigate or have JMP highlight the locations where the property exceeds a certain threshold for certain time periods, etc., etc.?

Thanks for the help

[#2]

You will want to set the data up where you have columns for the time, latitude, longitude and any measurement. There needs to be separate rows for each of the time, location instance.

[#3]

After setting up as Jim suggests, you can use the local data filter to see a time slice, or a latitude slice, or both, etc, in maps and other reports.

[#4]

A perhaps useful and informative template can be found right within the JMP Sample Data Directory. It's a Graph Builder based visualization of Napolean's March. I think the data table is called NapoleansMarch.jmp or something similar. It shows a background geographic map, and shows how variables (the size of Napolean's various armies) changed over that geographic space in size. Might come close to what you are after?

Here's a link to an HTML output from the script that generates the plot...take a look and see if it's kind of like what you are after? Napolean's March HTML Output 

[#5]

Thanks for the pointers.  I've also taken a look at the Napoleon's March JMP table.  And the visualization of Napoleon's March is similar to what I'm trying to create.

But my problem is I have only about 40 locations (40 latitudes and longitudes) but I have over 50,000 measurements (recorded hourly for over 5 years).

So, I set up the Time_stamp as the first column and the measurement associated with each location as additional columns.  I'm not sure now how to associate the Lat's and Long's with each measurement.

The Napoleon's March JMP table has Latitude and Longitude as columns, but that's not "practical" in this case, I don't think.  

I'd appreciate further help, please. 

[#6]

You could place the 40 latitudes and longitudes into a separate table, and then use a virtual join to bring them back together, but even with that, you will have to have a column that matches the rows. The end result will be a virtual table that has each latitude and longitude in each row.

[#7]

50,000 * 40 rows is large, but not too big for JMP. Your table will be in the 100-300 MB range, depending how many data columns. As Jim is suggesting, using a virtual join could get two columns removed (maybe more if there is other constant station data you need) at the expense of adding an ID column, but I don't think that is an optimization you need to get started.

What sort of audience will be looking at this? Would they prefer a video or a live graph with controls or a static image?

[#8]

As @Craige_Hales  mentioned, there is no problem with that number of rows, JMP's table is working quite efficiently.

And to start, the easier data structure is the better one.

Additionally, it is quite easy to reshape the data in JMP, e.g. distribute it to more tables, split or stack.

Where is your data now? If it is in two csv files, it is also quite easy to simply open the both files in JMP, 

they are becoming JMP data tables, and then to use the query builder to join them.

And if the data is updated regularly, you can even use the Query builder to import the new data directly from the csv files.

[#9]

Hello again,

I've got my data relatively cleaned up and in JMP as 2 separate tables.  One with the Station ID's, latitudes and longitudes and the other with a specific property measured at each station hourly for 5 years.  The Preview Join using Query Builder is not correct, so I'd like some more specific guidance as to how to create visualizations using the geographical data in one table and time-based properties in another table.

Btw, I'm extracting more of such property files from the public source because we are interested in studying about 10 such properties all across the province.

Also, to answer your question, the visualizations at this point are part of our data exploration and feature study process.  We think audiences will eventually be very interested in animated bubble charts that show what is going on close to their own locales, like within a certain radius. Right now, we're just studying the data to see what meaningful patterns might exist.

[#10]

try(close(dtLatLon,nosave));
try(close(dtTempTime,nosave));

dtLatLon = New Table( "LatLon",
	New Column( "city", Character, "Nominal", Set Values( {"New York", "London"} ) ),
	New Column( "lat", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [40.73061, 51.509865] ) ),
	New Column( "lon", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [-73.935242, -0.118092] ) )
);
dtLatLon << save( "$temp/LatLon_deleteme.jmp" ); // disk file name is required for virtual join
dtLatLon:city << Set Property( "Link ID", 1 );

dtTempTime = New Table( "TempTime",
	New Column( "city", Character, "Nominal",
		Set Values( {"New York", "London", "New York", "London", "New York", "London", "New York", "London", "New York", "London"} )
	),
	New Column( "temp", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [18, 30, 18, 12, 24, 17, 15, 30, 29, 19] ) ),
	New Column( "time", Numeric, "Continuous", Format( "yyyy-mm-ddThh:mm", 16 ), Input Format( "yyyy-mm-ddThh:mm" ),
		Set Values( [3660682600, 3660683600, 3660684600, 3660685600, 3660686600, 3660687600, 3660688600, 3660689600, 3660690600, 3660691600] )
	)
);
dtTempTime << save( "$temp/TempTime_deleteme.jmp" ); // disk file name is required
dtTempTime:city << Set Property( "Link Reference", Reference Table( dtLatLon ) );

dtTempTime << Bubble Plot(
	X( Referenced Column( "lon[city]", Reference( Column( :city ), Reference( Column( :lon ) ) ) ) ),
	Y( Referenced Column( "lat[city]", Reference( Column( :city ), Reference( Column( :lat ) ) ) ) ),
	Sizes( :temp ),
	Time( :time ),
	ID( :city ),
	Time Index( 4.0938 ),
	Title Position( -59.21926910299, 46.12 ),
	SendToReport(
		Dispatch( {}, "1", ScaleBox, {Format( "Best", 12 ), Min( -80 ), Max( 10 ), Inc( 10 ), Minor Ticks( 0 )} ),
		Dispatch(
			{},
			"2",
			ScaleBox,
			{Scale( "Geodesic" ), Format( "Best", 12 ), Min( 19.0897009966777 ), Max( 72.9102990033223 ), Inc( 2 ), Minor Ticks( 1 )}
		),
		Dispatch(
			{},
			"Bubble Plot",
			FrameBox,
			{Frame Size( 602, 360 ), Background Map( Boundaries( "World" ) ), Grid Line Order( 2 ), Reference Line Order( 3 )}
		)
	)
);

Yours will look simpler since your data already exists. You might have a numeric ID rather than a city name to link the tables together.

Bubble Plot