Thread: RE: [GENERAL] Creating web images from postgres data
> 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 > Regards > > Lincoln Spiteri > STMicroelectronics (Malta)
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: > > 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 > > > Regards > > > > Lincoln Spiteri > > STMicroelectronics (Malta) >
"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 > 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
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