title: Non-linear regression models in PostgreSQL using R
body: <h3>Background</h3>
  
  <p>I have climate data (temperature, precipitation, snow depth) for all of Canada between 1900 and 2009. I have written a basic website and the simplest page allows users to choose category and city. They then get back a very simple report (without the parameters and calculations section):</p>
  
  <p><img src="https://i.imgur.com/7eQ45.png" /></p>
  
  <p>The primary purpose of the web application is to provide a simple user interface so that the general public can explore the data in meaningful ways. (A list of numbers is not meaningful to the general public, nor is a website that provides <a href="http://www7.ncdc.noaa.gov/CDO/CDODivisionalSelect.jsp" rel="nofollow noreferrer">too many inputs</a>.) The secondary purpose of the application is to provide climatologists and other scientists with deeper ways to view the data. (Using too many inputs, of course.)</p>
  
  <h3>Tool Set</h3>
  
  <p>The database is PostgreSQL with R (mostly) installed. The reports are written using iReport and generated using JasperReports.</p>
  
  <h3>Poor Model Choice</h3>
  
  <p>Currently, a linear regression model is applied against annual averages of daily data. The linear regression model is calculated within a PostgreSQL function as follows:</p>
  
  <pre><code>SELECT 
    regr_slope( amount, year_taken ),
    regr_intercept( amount, year_taken ),
    corr( amount, year_taken )
  FROM
    temp_regression
  INTO STRICT slope, intercept, correlation;
  </code></pre>
  
  <p>The results are returned to JasperReports using:</p>
  
  <pre><code>SELECT
    year_taken,
    amount,
    year_taken * slope + intercept,
    slope,
    intercept,
    correlation,
    total_measurements
  INTO result;
  </code></pre>
  
  <p>JasperReports calls into PostgreSQL using the following parameterized analysis function:</p>
  
  <pre><code>SELECT
    year_taken,
    amount,
    measurements,
    regression_line,
    slope,
    intercept,
    correlation,
    total_measurements,
    execute_time
  FROM
    climate.analysis(
      $P{CityId},
      $P{Elevation1},
      $P{Elevation2},
      $P{Radius},
      $P{CategoryId},
      $P{Year1},
      $P{Year2}
    )
  ORDER BY year_taken
  </code></pre>
  
  <p>This is not an optimal solution because it gives the false impression that the climate is changing at a slow, but steady rate. </p>
  
  <h3>Questions</h3>
  
  <p>Using functions that take two parameters (e.g., year [X] and amount [Y]), such as PostgreSQL's <code>regr_slope</code>:</p>
  
  <ul>
  <li>What is a better regression model to apply?</li>
  <li>What CPAN-R packages provide such models? (Installable, ideally, using <code>apt-get</code>.)</li>
  <li>How can the R functions be called within a PostgreSQL function?</li>
  </ul>
  
  <p>If no such functions exist:</p>
  
  <ul>
  <li>What parameters should I try to obtain for functions that will produce the desired fit?</li>
  <li>How would you recommend showing the best fit curve?</li>
  </ul>
  
  <p>Keep in mind that this is a web app for use by the general public. If the only way to analyse the data is from an R shell, then the purpose has been defeated. (I know this is not the case for most R functions I have looked at so far.)</p>
  
  <p>Thank you!</p>