Wiki Wiki

Bytea Graphing Example

The following example returns values from the logistic map that are indexed by a dummy date variable. The final result is a png of a heatmap showing the behavior over "time" for various values of r. The result is returned as a bytea object. This bytea object can be passed directly to a webserver for display in a browser. All off the following commands are to be entered into a postgresql terminal, or saved in a file and sourced in.

First, a new type is created. This allows functions to return sets of this type.

create type t_logistic as (r numeric, x numeric, index date);

Next, a new function is created that takes an initial value (x0, between 0 and 1), the length of the desired logistic map, and the multiplying factor (r, between 0 and 4). The return value is a set of rows of the type defined above. Note the index must be passed to as.character() before it's returned to postgresql.

create function r_logistic(x0 numeric, length integer, r numeric) returns setof t_logistic as $$
    ret = rep(x0, length)
    index = as.character(as.Date(1:length, origin='1970-01-01'))
    for (i in 2:length) { 
        ret[i] = r * ret[i-1] * ( 1 - ret[i-1] )
    ret  = cbind(r, ret, index); 
$$ language 'plr';

The next function calls the previous function over a range of r values and joins the results together into a single array, which is returned as a set of rows as above. Note the use of sprintf to easily construct the query using input variables.

create function r_logistic_set(x0 numeric, length integer, r_from numeric, r_to numeric, r_by numeric) returns setof t_logistic as $$
    r_seq = seq(r_from, r_to, r_by)
    for (i in 1:length(r_seq)) { 
        my.qry = sprintf("select * from r_logistic(%s, %s, %s);", x0, length, r_seq[i])
        tmp =  dbGetQuery('', my.qry)
        if (i ==1) {
            ret = tmp
        } else {
             ret = rbind(ret, tmp)
$$ language 'plr';

Calling these functions would look like this:

select * from r_logistic(.5, 10, 3.5);
select * from r_logistic_set(.5, 10, 3.5, 3.6, 0.02);

Now we can use the above function to populate a new table with the above function using postgresql's CREATE TABLE AS.

create table test_logistic as select * from r_logistic_set(.5, 250, 0, 4, 0.02);
The above call is computationally intensive. The resulting table can be rapidly queried.

Finally, the resulting table is queried from within the plotting function. Note the query of the above table returns a data frame whose column names are determined by the initial type definition. Also, as.Date() must be used to convert the character vector back into dates for plotting.

create function r_plot_heat_logistic(r_from numeric, r_to numeric, width integer, height integer) returns bytea as $$
    ## uses plr functions r_logistic_set() and r_logistic()
    ## function to make jpeg as bytea to hand out of database
    ## Uses RColorBrewer to make nice color paletter
    ## and lattice to make levelplot
    ## try width = 1600 and height = 1200

    ## necessary libraries

    ## create colormap and plotting device
    mycuts = 11
    mypal = brewer.pal(mycuts, 'Spectral')
    pixmap <- gdkPixmapNew(w=width, h=height, depth=24)

    ## get data
    my.qry = sprintf("select * from test_logistic where r between %s and %s;", r_from, r_to)
    tmp = dbGetQuery('', my.qry)
    tmp$index = as.Date(tmp$index)

    ## plot data
    myplot = levelplot( x ~ index *r, data=tmp,
            col.regions=mypal, cuts=(mycuts-1)
    plotPixbuf <- gdkPixbufGetFromDrawable(NULL, pixmap, # 0, 0, 0, 0, width, height)
               pixmap$getColormap(), 0, 0, 0, 0, width, height)
    buffer <- gdkPixbufSaveToBufferv(plotPixbuf, 'png', character(0), character(0))$buffer
$$ language plr;

Using a python MVC framework such as pylons, the above would be called from a function that looks something like this:

    def plotlogistic(self):
        response.headers['Content-type'] = 'image/png'
        c.plotheat = meta.Session.bind.execute("select plr_get_raw(r_plot_heat_logistic(2.5, 4, 1200, 800))").fetchone().values().pop()
        return c.plotheat

Here, a session is already bound to the database. An sql query is executed on the database, where our function is wrapped in a call to plr_get_raw(). A header is set on the result, and it's ready to be rendered. A form can be used to pass values into r_plot_heat_logistic.

The final result is shown below:

To remove the relations in the above example, use the following:

drop type t_logistic cascade;  -- also drops all functions that use this type
drop table test_logistic ;
drop function r_plot_heat_logistic ( numeric, numeric, integer, integer) ;
1 Attachments 1 Attachments

Average (0 Votes)
Copyright (c) 2010 Joseph E Conway. All rights reserved.