Advice on importing XML data from a URL and storing it on DB

General Discussion Other

kaihansen..

Advice on importing XML data from a URL and storing it on DB

in General Discussion • Other • 1 year ago

Hello,

I'm working on a project and I want to import XML data from a URL ( http://api.wunderground.com/weatherstation/WXCurrentObXML.asp?ID=KILCHICA30), then store it to tables on my postgreSQL server for immediate and later uses.

Additionally, I want the program to make certain decisions based on the data and send/receive data to an Arduino through the serial com port.

I'm not terribly concerned about the decision making methods that will take place in the app. It's the communication between program-URL and program-DB, as well as the conversion of XML that I'm unsure about. I would really appreciate any advice, or being pointed to the right resources.

Thanks!

Replies(5)

PhiLho

Re: Advice on importing XML data from a URL and storing it on DB

1 year ago

Your message is very vague, so I can only give vague advices: use XMLElement to parse the XML (it can take an URL as input), use the SQLibrary to put the data in the database, depending on your DB design.

kaihansen..

Re: Advice on importing XML data from a URL and storing it on DB

1 year ago

This is a great start! Sorry, for being so vague but many of the parameters for this project are still undecided. And thanks for pointing out the SQLibrary and XMLElement.

I want to bring in the weather data from the URL and store it on two different tables (current_weather, longterm_weather) in the DB as the website updates every 10 minutes. The program then needs to query the DB table current_weather for the local outdoor environment data I've specified. At the same time, the program needs to collect the sensor and actuator data being collected by the Arduino FIO. This data will represent the indoor environmental data for a small enclosed space capped by an adaptive facade mock-up. The Arduino will be able to respond autonomously to the real-time conditions, given it's provided with the weather data by the program. However, by using the DB and Arduino data-sets, the program should be able to construct adaptive behaviors on the fly and communicate them back to the Arduino.

I'm going to try to tackle this on my own...but I may be back.

Thanks again.

kaihansen..

Re: Advice on importing XML data from a URL and storing it on DB

1 year ago

Does anyone know how to make the following query loop?

import de.bezier.data.sql.*;

PostgreSQL pgsql;

void setup()

{

//weather import start

//weather import end

//query write start

size( 300, 300 );

// this example assumes that you are running the

// postgresql server locally (on "localhost").

// replace with your own postgresql-account.

String user = "username";

String pass = "pw";

// name of the database to use

String database = "iituim";

// connect to database on "localhost"

pgsql = new PostgreSQL( this, "ip", database, user, pass );

// connected?

if ( pgsql.connect() )

{

// query the number of entries in table "weather"

pgsql.query( "SELECT COUNT(*) FROM adaptive_weather_longterm" );

// results found?

if ( pgsql.next() )

{

// nice, then let's report them back

println( "number of rows in table weather: " + pgsql.getInt(1) );

}

// now let's query for last 10 entries in "weather"

pgsql.query( "SELECT * FROM adaptive_weather_longterm LIMIT 10" );

// anything found?

while( pgsql.next() )

{

// splendid, here's what we've found ..

println( pgsql.getString("city") );

}

else

{

// yay, connection failed !

}

delay (6000); //add 00 to make 10 minutes

//query write end

}

void loop(){

println( "number of rows in table weather: " + pgsql.getInt(1) );

}

Jonny Stu..

Re: Advice on importing XML data from a URL and storing it on DB

1 year ago

Loop() doesn't do what you think it does - it's used to activate repeated calling of draw().

Try moving all your query code from setup() to a separate function called e.g. doQuery() then do something like the following which queries once a second:

int lastQueryTime;
void setup() {
lastQueryTime = millis();
}
void draw() {
if (lastQueryTime + 1000 < millis()) {
doQuery();
lastQueryTime = millis();
}
}

kaihansen..

Re: Advice on importing XML data from a URL and storing it on DB

1 year ago

Awesome, thank you so much for the quick response. It works great.

Top Reply