Monday, June 30, 2008

How to do console queries

I have a postgresql database with a rainfall table in it.

Doing a straight query to it from psql shell would look like:




and would gain the result:




From the JGrass console we can do something similar with the following script:


String startDate = "2006-09-08 02:00";
String endDate = "2006-09-08 05:00";

jgrass {
db.query
--query "select t.dataora, t.valore from punti_monitoraggio p, metadati m, serie_temporali t where p.id_punti_monitoraggio = 1101 and p.id_punti_monitoraggio=m.punti_monitoraggio_id and t.metadati_id=m.id and m.tipologia_serie_temporali_id=2 and t.intervallo = 30 and t.dataora BETWEEN cast('2006-08-01 02:00' as Timestamp) and cast('2006-09-08 05:00' as Timestamp) order by t.dataora,p.id_punti_monitoraggio"
--otable-out "time#rain#CONSOLE"
}



and it would look like:



Note that on the console output the date is transformed to a long value of milliseconds.


There is also a g.fork command, that makes it possible to send the output to more than one output processor.
Let's see for example the script that forks the output to the normal table output on console, but also to a chart output processor:


String startDate = "2006-09-08 02:00";
String endDate = "2006-09-08 05:00";

jgrass {
g.fork --iscalar-in [
db.query
--query "select t.dataora, t.valore from punti_monitoraggio p, metadati m, serie_temporali t where p.id_punti_monitoraggio = 1101 and p.id_punti_monitoraggio=m.punti_monitoraggio_id and t.metadati_id=m.id and m.tipologia_serie_temporali_id=2 and t.intervallo = 30 and t.dataora BETWEEN cast('2006-08-01 02:00' as Timestamp) and cast('2006-09-08 05:00' as Timestamp) order by t.dataora,p.id_punti_monitoraggio"
--oscalar-out * ]
--ochart-out "TIMEHISTOGRAM#Raindata 2006-09-08 02:00 - 2006-09-08 05:00#date#mm#rain"
--otable-out "time#rain#CONSOLE"

}



And the output would then be:

No comments: