[R-sig-DB] How to save a model in DB and retrieve It
Joe Conway
m@|| @end|ng |rom joeconw@y@com
Fri Apr 2 22:45:35 CEST 2010
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>
More information about the R-sig-DB
mailing list