[R-sig-DB] How to save a model in DB and retrieve It

[Joe Conway]

On 04/02/2010 01:37 AM, Daniele Amberti wrote:
> I'm wondering how to save an object (models like lm, loess, etc) in a DB to retrieve and use it afterwards, an example:

> # Up to this it is working correctly,
> # in DB I have the "modX" variable
> # Problem arise retrieving data and 64kb limit:

> Above code is working for simplier models that have a shorter representation in variable "modX".
> Any advice on how to store and retieve this kind of objects?

You can mostly do what you want with PL/R and PostgreSQL
(see http://www.joeconway.com)

For example:

8<------------------------------
CREATE OR REPLACE FUNCTION get_model() RETURNS bytea AS $$
  wind_ms <- abs(rnorm(24*30)*4+8)
  air_kgm3 <- rnorm(24*30, 0.1)*0.1 + 1.1
  wind_dg <- rnorm(24*30) * 360/7
  ms <- c(0:25)
  kw_mm92 <- c(0,0,0,20,94,205,391,645,979,1375,1795,2000,2040)
  kw_mm92 <- c(kw_mm92, rep(2050, length(ms)-length(kw_mm92)))
  modelspline <- splinefun(ms, kw_mm92)
  kw <- abs(modelspline(wind_ms)
        - (wind_dg)*2 + (air_kgm3 - 1.15)*300
        + rnorm(length(wind_ms))*10)
  windDat <- data.frame(kw, wind_ms, air_kgm3, wind_dg)
  windDat[windDat$wind_ms < 3, 'kw'] <- 0
  model <- loess(kw ~ wind_ms + air_kgm3 + wind_dg,
                 data = windDat, enp.target = 10*5*3) #, span = 0.1)
  return(model)
$$ LANGUAGE plr;


CREATE OR REPLACE FUNCTION extract_fitted(bytea) RETURNS SETOF float8 AS $$
  return(arg1$fitted)
$$ LANGUAGE plr;
8<------------------------------

select length(get_model());
 length
--------
 219361
(1 row)

select * from extract_fitted(get_model());
   extract_fitted
---------------------
    429.108839675228
    994.109512291517
    858.011203975038
[...]
     349.87845982039
     884.26297556709
    155.996698202327
    117.920754039095
(720 rows)
8<------------------------------


However while working up this example I discovered a bug in PL/R. In the
*next* release you will be able to do the following, which with the
current release does not quite work:

8<------------------------------
CREATE TABLE model_store (id int primary key, model bytea);
INSERT INTO model_store VALUES (1, get_model()), (2, get_model()), (3,
get_model());

select *
 from extract_fitted((select model from model_store where id = 1));
   extract_fitted
---------------------
   -29.4074157828657
    -3.7532027827115
    803.524262857045
[...]
    1463.03742124991
    2051.01939505223
    13.4600408221842
(720 rows)
8<------------------------------

Joe


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 899 bytes
Desc: OpenPGP digital signature
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20100402/664f491f/attachment.sig>