Creating web images from postgres data

Hi!

On Wed, 28 Oct 1998, Lincoln Spiteri wrote:

I am currently starting up a pilot project to demonstrate the power of
the linux/postgres/php trio in an enterprise setting.

I would like to be able to output graphs from data stored in a postgres
table for viewing on a web browser as a means of data presentation.
Could anyone give me some suggestions as to what the best approach would
be to achieve this.

As you already have (or will have in the near future) php, I want to
stress there is php<->gd interface (gd is GIF drawing library). You should
write plotting program by youself.

Another solution is to use gnuplot program. The gnuplot solution is much
simpler, but there are drawbacks - often gnuplot is TOO simple.

Example.
This is datafile (data you drawn from database):
21 12 12
22 12 10
23 12 12
24 14 16
25 14 17
26 15 18
27 16 18.5
28 17 21
29 20 25
30 18 24

This datafile means: data for 21, 22, ..., 30 June are:
21 June: min = 12, max = 12... and so on.
This is command file you feed into gnuplot's stdin:
set term pbm color
plot "demo-gif.dat" using "%f%f" title "Min" with linespoints, \
"demo-gif.dat" using "%f%*f%f" title "Max" with linespoints

After this, gnuplot will generate ppm file on stdout. Filter it through
ppmchange (to correct colors) and ppmtogif to produce GIF. To make results
a bit nicer, make the GIF transparent (ppmtogif -transparent) and put the
GIF onto nice HTML background.
Latest version of gnuplot can be linked with gd library and can produce
GIF files without intermediate ppm.

Example in the real world: http://rinaco.ice.ru/analytics/q_c/
Generated with gnuplot.

Oleg.
----
Oleg Broytmann National Research Surgery Centre http://sun.med.ru/~phd/
Programmers don't die, they just GOSUB without RETURN.

Hello all,

I am currently starting up a pilot project to demonstrate the power of
the linux/postgres/php trio in an enterprise setting.

I would like to be able to output graphs from data stored in a
postgres
table for viewing on a web browser as a means of data presentation.
Could anyone give me some suggestions as to what the best approach
would
be to achieve this.

I personally would use PHP (http://www.php.net/) to accomplish this
goal. The PHP project has a GD interface which allows you to
dynamically create images. You could also generate the graph using
gnuplot and display the image using PHP. You can find a graphing
example at http://phplib.shonline.de/ (get PHPLIB-6). It also has some
fairly good session management and form management routines. I'm not
sure if they are including a db_pgsql.inc, but I've made my own (this
will make more since as you look at PHPLIB). Let me know if you have
any questions.
-DEJ

I have written a simple perl script that makes use of gnuplot. It is
quite austere and was designed to be used with PSQL. The idea is to run
it like this

psql> \o | pp [a bunch of options]
psql> select a,b,c,d from some_table order by a;
psql> \o

The perl script pp and gnuplot create a postscript graph with
a == the_x_axis and b,c,d on the y-axis.

This may be way too simple for your application, but if it is of interest
I'll post it for your use.

mike

Dr Michael A. Koerber
MIT/LL
Radar Imaging Techniques

On Wed, 28 Oct 1998, Jackson, DeJuan wrote:

"Michael A. Koerber SR" wrote:

I have written a simple perl script that makes use of gnuplot. It is
quite austere and was designed to be used with PSQL. The idea is to run
it like this

psql> \o | pp [a bunch of options]
psql> select a,b,c,d from some_table order by a;
psql> \o

The perl script pp and gnuplot create a postscript graph with
a == the_x_axis and b,c,d on the y-axis.

This may be way too simple for your application, but if it is of interest
I'll post it for your use.

Please do.

I have a need for such a thing but I hadn't yet got round to thinking how
to do it.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"If ye then be risen with Christ, seek those things
which are above, where Christ sitteth on the right
hand of God. Set your affection on things above, not
on things on the earth." Colossians 3:1,2

The perl script, pp, is included below. After saving it in the file pp,
run pod2man (pod2man pp > pp.1). Type man ./pp.1 to read the manual page.

There are alot of things that can be done to improve on this script, but
it "answers the mail" for my applications today. Happy hacking :-).

mike

Dr Michael A. Koerber
MIT/LL
Radar Imaging Techniques

On Thu, 29 Oct 1998, Michael A. Koerber SR wrote:

The perl script, pp, is included below. After saving it in the file pp,

Can't see anything below?

-Egon

Opps...

#!/usr/bin/perl -w

# $Source: /home/mak/CVS/misc_util/pp,v $
# $Author: mak $
# $Date: 1998/08/12 19:01:55 $
# $Revision: 1.5 $

# ----------------------------------------------------------------------
# Parse command line options
use Getopt::Std;
%opts = ();
getopts('t:x:y:l:f:q',\%opts);

# If the 'l' (legend) option exists, parse it based on the ':' character
@legend = split(':',$opts{l}) if exists($opts{l});

if (exists($opts{f})) {
$out_base = $opts{f};
}
else {
$out_base = 'gnuplot';
}

# ----------------------------------------------------------------------
# Begin parsing input
while (<>) {

# --------------------------------------------------
# strip out | characters
s/\|/ /g;

# --------------------------------------------------
# work only on strings which are purely numeric. This is a bit tricky
# but we allow the columns of numbers to contain unit specifier and
# just suck out the numeric data
@line = ();
while (m/\G[^-\d\s]*\s*([-+]*\d+\.?\d*[-+eE]*\d*)[\s\n]*[^-\d\s]*/g) {
push @line, $1;}

if (@line) {
$cols = @line unless $cols;

# Testing line
# print join(' : ',@line), "\n";

# Open an output file for pairs of columns ($line[0,1],
$line[0,2], etc)

unless (@filenames) {
# This will only happen the first time through
@filenames = map { "A$_" } (1..$#line);

foreach $fn (@filenames) {
$cmd = "open $fn, \">\$fn\" or die \"Unable to open file
\$fn\\n\"";
eval($cmd);
}
}

# Begin output of this line of data
if (@line == $cols) {
# Only output if the number of lines is consistant with the
first valid row
@cmds = map { "$filenames[$_] \"$line[0] $line[$_+1] \n\"" }
(0..$#filenames);
foreach (@cmds) {
# print "$_\n";
eval("print $_ \n");
}
}
else {
warn "Number of columns changed. Trashing this line:\n";
warn "$_";
}

}
}

# ----------------------------------------------------------------------
# Close all the files
foreach $fn (@filenames) {
$cmd = "close $fn";
eval($cmd);
}

# ----------------------------------------------------------------------
# set up the gnuplot command file

$cmd = "plot ";
foreach (@filenames) {
if (@legend) {
$this_legend = shift @legend;
$cmd .= qq('$_' using 1:2 title '$this_legend' with linespoints,
);

}
else {
$cmd .= "'$_' using 1:2 with linespoints, ";
}
}
chop $cmd;
chop $cmd;

open( OUT, ">$out_base.cmd") or die "Unable to open $out_base.cmd\n";

push @cmd_opts, qq(set title '$opts{"t"}') if exists($opts{"t"});
push @cmd_opts, qq(set xlabel '$opts{"x"}') if exists($opts{"x"});
push @cmd_opts, qq(set ylabel '$opts{"y"}') if exists($opts{"y"});
$cmd_list = join(';',@cmd_opts);

if (exists $opts{'q'}) {
# Then quiet mode was selected. Only output to a postscript file
print OUT <<END;
set terminal postscript landscape color
set output \'$out_base.ps\'
set grid
$cmd_list
$cmd

END

close OUT;
}
else {
# Output to screen and to postscript file
print OUT <<END;
set grid
$cmd_list
$cmd
pause 5

set terminal postscript landscape color
set output \'$out_base.ps\'
replot

END

close OUT;
}

system(qq(gnuplot $out_base.cmd));

print "See $out_base.ps in current directory for last plot\n";

__END__

=head1 NAME

pp - Postgres Plotting Script

=head1 SYNOPSIS

C<pp [-t -x -y -l -f -q]>

=head1 DESCRIPTION

This script will take the tabular output of a Postgres Query and generate
a B<gnuplot> command file (F<gnuplot.cmd>) which is then used to generate
a
plot of the data.

The follow command line options are available

=over 4

=item B<-t title> places a title on the plot

=item B<-x xlabel> places a label on the x-axis

=item B<-y ylabel> places a label on the y-axis

=item B<-l legend_1:legend_2:...> places legends on the plot. Note the
use
of the ':' for delimiting legends. Extra legend will be ignored. If
there are
not enough legends, the ones specified will be used. The remaining
legends are
then F<gnuplot> defaults.

=item B<-f out_file_base_name> creates output files
F<out_file_base_name.cmd> and
F<out_file_base_name.ps>. By default, F<gnuplot.[cmd|ps]> are used.

=item B<-q> is quiet mode. If specified, then the postscript files are
generated
but no screen output is shown. If not specified, then a screen output is
displayed
for 5 seconds, then the postscript file written.

=back

=head1 NOTES

$Source: /home/mak/CVS/misc_util/pp,v $
$Author: mak $
$Date: 1998/08/12 19:01:55 $
$Revision: 1.5 $

Dr Michael A. Koerber
MIT/LL
Radar Imaging Techniques