topic Re: Fetch data XML to JMP in Discussions

Fetch data XML to JMP

IloinenHamsteri — Wed, 17 Jul 2024 08:35:00 GMT

Hi, I am trying to fetch weather data form XML (Finnish weather service - https://en.ilmatieteenlaitos.fi/open-data-manual-fmi-wfs-services) to JMP in a way that the user could define the time period. My code does not work, I always get the "Invalid HTTP request state" error message.

// User-defined start and end dates (modify as needed)
startDate = "2024-07-10";
endDate = "2024-07-24";

// Construct the URL with user-defined dates and parameters
url = "https://opendata.fmi.fi/wfs?service=WFS&version=2.0.0&request=getFeature&storedquery_id=fmi::observations::weather::timevaluepair&place=helsinki-vantaa_airport&timestep=1&starttime=" || startDate || "T00:00:00Z&endtime=" || endDate || "T23:59:59Z&parameters=temperature%2Chumidity%2Cpressure";

// Create HTTP request object
httpRequest = New HTTP Request();

// Send HTTP GET request
response = httpRequest << Send("GET", url);

// Check if the request was successful
If(response << Get Status Code == 200,

// Parse the XML response
    xmlResponse = response << Get As XML,
    
// Extract data from XML response
    dataNodes = xmlResponse << XPath("//wml2:MeasurementTVP"),
    
// Initialize lists to store data
    temperatureList = {},
    humidityList = {},
    pressureList = {},
    
// Loop through each data node and extract values
   ForEach(node in dataNodes,
        // Extract timestamp
        timestamp = node << XPath("wml2:time"),
        
        // Extract temperature value
        temperature = node << XPath("wml2:value[@parameter='temperature']"),
        temperatureValue = temperature << NodeText,
        
        // Extract humidity value
        humidity = node << XPath("wml2:value[@parameter='humidity']"),
        humidityValue = humidity << NodeText,
        
        // Extract pressure value
        pressure = node << XPath("wml2:value[@parameter='pressure']"),
        pressureValue = pressure << NodeText,
        
        // Convert timestamp to JMP DateTime format
        timestampDT = Parse DateTime(timestamp, "YYYY-MM-DDThh:mm:ssZ"),
        
        // Append data to lists
        Append(temperatureList, {timestampDT, As Number(temperatureValue)}),
        Append(humidityList, {timestampDT, As Number(humidityValue)}),
        Append(pressureList, {timestampDT, As Number(pressureValue)})
    );
    
    // Create a new JMP data table and add columns
    dt = New Table("Helsinki-Vantaa Weather Data",
        Add Rows(Num Rows(temperatureList)),
        New Column("Timestamp", Numeric, "DateTime"),
        New Column("Temperature (°C)", Numeric, "Continuous", Format(3)),
        New Column("Relative Humidity (%)", Numeric, "Continuous", Format(3)),
        New Column("Pressure (hPa)", Numeric, "Continuous", Format(3))
    );
    
// Populate the data table with values
    dt:"Timestamp" << Set Values(temperatureList);
    dt:"Temperature (°C)" << Set Values(humidityList);
    dt:"Relative Humidity (%)" << Set Values(pressureList);
    dt:"Pressure (hPa)" << Set Values(pressureList);
    
// Show the data table
    dt << Show;
,
// Display error if request failed
    Throw("Failed to fetch data. HTTP status code: " || Char(response << Get Status Code))
);

Re: Fetch data XML to JMP

jthi — Wed, 17 Jul 2024 08:41:38 GMT

The syntax for New HTTP Request is incorrect. Here is small example which should return some data

Names Default To Here(1);

startDate = "2024-07-10";
endDate = "2024-07-11";

// Construct the URL with user-defined dates and parameters
url = "https://opendata.fmi.fi/wfs?service=WFS&version=2.0.0&request=getFeature&storedquery_id=fmi::observations::weather::timevaluepair&place=helsinki-vantaa_airport&timestep=1&starttime=" || startDate || "T00:00:00Z&endtime=" || endDate || "T23:59:59Z&parameters=temperature%2Chumidity%2Cpressure";

// Create HTTP request object
request = New HTTP Request(
	Method("GET"),
	URL(url)
);

data = request << send;

/*
Show(request << Get MIME Type);
*/

Re: Fetch data XML to JMP

IloinenHamsteri — Wed, 17 Jul 2024 12:55:59 GMT

Thanks @jthi the HTTP request works now, but it seems that the rest of the script is faulty as well. This is the first time I am trying to get data from XML.

Re: Fetch data XML to JMP

jthi — Wed, 17 Jul 2024 13:17:56 GMT

It seems like you are using LLM generated cod? If so, they are in my opinion horrible with JSL.

I would suggest you save the resulting data as xml file and use JMP's preview to get the source script

This will require some playing around but it can be parsed with this (or could use XPath Query() or Parse XML())

You can then use the source script to automate your process further and you can end up with something like

Re: Fetch data XML to JMP

lala — Wed, 17 Jul 2024 13:32:30 GMT

Re: Fetch data XML to JMP

jthi — Wed, 17 Jul 2024 13:59:16 GMT

Below is the script I used (I have used Ilmatieteenlaitos open data in JMP earlier but I think I either used different data or parsed it in a bit different way). It does use some more advanced techniques such as open(char to blob(), "text"), but that can be avoided by just saving the file first as text file. It also has data cleanup and graph creation (verify that the data it creates is correct, I didn't do any checks for that).

Names Default To Here(1);
/*
https://en.ilmatieteenlaitos.fi/open-data
	https://en.ilmatieteenlaitos.fi/open-data-manual-accessing-data
*/

location = "helsinki-vantaa_airport";
start_date = "2024-07-10";
end_date = "2024-07-11";

fmi_url = "https://opendata.fmi.fi/wfs?service=WFS&version=2.0.0&request=getFeature&storedquery_id=fmi::observations::weather::timevaluepair&place=^location^&timestep=1&starttime=^start_date^T00:00:00Z&endtime=^end_date^T23:59:59Z&parameters=temperature%2Chumidity%2Cpressure";


// Create HTTP request object
request = New HTTP Request(
	Method("GET"),
	URL(Eval Insert(fmi_url))
);

data = request << send;

dt = Open(
	Char to blob(data),
	XML Settings(
		Stack(0),
		Row(
			"/wfs:FeatureCollection/wfs:member/omso:PointTimeSeriesObservation/om:result/wml2:MeasurementTimeseries/wml2:point"
		),
		Col(
			"/wfs:FeatureCollection/wfs:member/omso:PointTimeSeriesObservation/om:result/wml2:MeasurementTimeseries/@gml:id",
			Column Name(
				"MeasurementID"
			),
			Fill("Use Forever"),
			Type("Character"),
			Format({"Best"}),
			Modeling Type("Continuous")
		),
		Col(
			"/wfs:FeatureCollection/wfs:member/omso:PointTimeSeriesObservation/om:result/wml2:MeasurementTimeseries/wml2:point/wml2:MeasurementTVP/wml2:time",
			Column Name(
				"Timestamp"
			),
			Fill("Use Once"),
			Type("Character"),
			Format({"Best"}),
			Modeling Type("Continuous")
		),
		Col(
			"/wfs:FeatureCollection/wfs:member/omso:PointTimeSeriesObservation/om:result/wml2:MeasurementTimeseries/wml2:point/wml2:MeasurementTVP/wml2:value",
			Column Name(
				"Value"
			),
			Fill("Use Once"),
			Type("Numeric"),
			Format({"Best"}),
			Modeling Type("Continuous")
		)
	),
	XML Wizard(0),
	"text"
);

For Each Row(dt,
	:MeasurementID = Word(-1, :MeasurementID, "-");
	:Timestamp = Substitute(:Timestamp, "Z", "");
);
Column(dt, "Timestamp") << Data Type(Numeric) << Modeling Type(Continuous) << Format("yyyy-mm-ddThh:mm:ss", 19, 0);


dt_split = dt << Split(
	Split By(:MeasurementID),
	Split(:Value),
	Group(:Timestamp),
	Output Table("FMI " || location),
	Sort by Column Property
);
Close(dt, no save);

dt_split << Delete Scripts(dt_split << Get Table Script Names);


gb = dt_split << Graph Builder(
	Size(857, 524),
	Show Control Panel(0),
	Variables(X(:Timestamp), Y(:humidity), Y(:pressure), Y(:temperature)),
	Elements(Position(1, 1), Line(X, Y, Legend(15))),
	Elements(Position(1, 2), Line(X, Y, Legend(16))),
	Elements(Position(1, 3), Line(X, Y, Legend(17)))
);
gb << Save Script to Data Table("Plot");

If you wish to see how I filled XML Wizard, you can change the XML Wizard(0), to XML Wizard(1), and run code until that point. You can then see the settings in the GUI I used. And here is some sort of explanation of what those mean

You can get quite far by just using Tall Guess

But it will miss the measurement types and you have to make those few changes I did to repeat them on all rows (you could also fill once -> fill rest in JMP table).

Re: Fetch data XML to JMP

IloinenHamsteri — Thu, 18 Jul 2024 08:27:28 GMT

I tried the import XML add-in in JMP before but I got lost with it, however, now based on your explanation I understand how it works! @jthi Thank you! Also, I really appreciate that you shared the way you fetched this data using open(char to blob(), "text")!