Skip site navigation (1) Skip section navigation (2)

Interpolation and extrapolation in SQL

From: David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Interpolation and extrapolation in SQL
Date: 2004-09-13 15:03:32
Message-ID: 4145B6C4.1080605@zara.6.isreserved.com (view raw or flat)
Thread:
Lists: pgsql-sql
On my first try, interpolation and extrapolation turns out to be pretty 
easy to do. In psql:



-- the "lookup" table

CREATE TABLE p (
   x DOUBLE PRECISION NOT NULL UNIQUE,
   y DOUBLE PRECISION NOT NULL
);

INSERT INTO p VALUES (1,1);
INSERT INTO p VALUES (2,5);
INSERT INTO p VALUES (5,14);
INSERT INTO p VALUES (10,21);



-- the table that contains our x values, the y values of which will be
-- looked up in the lookup table.

CREATE TABLE q (
   x DOUBLE PRECISION NOT NULL
);

INSERT INTO q VALUES (0);
INSERT INTO q VALUES (1);
INSERT INTO q VALUES (2);
INSERT INTO q VALUES (3.5);
INSERT INTO q VALUES (5.5);
INSERT INTO q VALUES (10);
INSERT INTO q VALUES (11);



-- query A. only handles interpolation

\set x1 '(SELECT p.x FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1)'
\set x2 '(SELECT p.x FROM p WHERE p.x >= q.x ORDER BY p.x ASC  LIMIT 1)'
\set y1 '(SELECT p.y FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1)'
\set y2 '(SELECT p.y FROM p WHERE p.x >= q.x ORDER BY p.x ASC  LIMIT 1)'

SELECT
   q.x,
   CASE
     WHEN :x1 = :x2 THEN :y1
     ELSE                (:y1 + (q.x-:x1)/(:x2-:x1)*(:y2-:y1))
   END AS y
FROM q;



-- query B. also handles extrapolation, and the note column tells us
-- whether a certain y value is directly taken from p, or interpolated,
-- or extrapolated.

\set x0 '(SELECT p.x FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1 
OFFSET 1)'
\set x3 '(SELECT p.x FROM p WHERE p.x >= q.x ORDER BY p.x ASC  LIMIT 1 
OFFSET 1)'
\set y0 '(SELECT p.y FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1 
OFFSET 1)'
\set y3 '(SELECT p.y FROM p WHERE p.x >= q.x ORDER BY p.x ASC  LIMIT 1 
OFFSET 1)'

SELECT
   q.x,
   CASE
     WHEN :x1 = :x2 THEN   :y1
     WHEN :x1 IS NULL THEN (:y2 + (q.x-:x2)/(:x3-:x2)*(:y3-:y2))
     WHEN :x2 IS NULL THEN (:y0 + (q.x-:x0)/(:x1-:x0)*(:y1-:y0))
     ELSE                  (:y1 + (q.x-:x1)/(:x2-:x1)*(:y2-:y1))
   END AS y,
   CASE
     WHEN :x1 = :x2 THEN   'direct'
     WHEN :x1 IS NULL THEN 'extrapolated to the left'
     WHEN :x2 IS NULL THEN 'extrapolated to the right'
     ELSE                  'interpolated'
   END AS note
FROM q;



-- C. to handle the case where x in p is not unique, replace the x0..x3 
and y0..y3 template into:

\set x1 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x <= 
q.x ORDER BY p2.x DESC LIMIT 1)'
\set x2 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x >= 
q.x ORDER BY p2.x ASC  LIMIT 1)'
\set y1 '(SELECT p.y  FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON 
p.x=p2.x WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1)'
\set y2 '(SELECT p.y  FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON 
p.x=p2.x WHERE p2.x >= q.x ORDER BY p2.x ASC  LIMIT 1)'
\set x0 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x <= 
q.x ORDER BY p2.x DESC LIMIT 1 OFFSET 1)'
\set x3 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x >= 
q.x ORDER BY p2.x ASC  LIMIT 1 OFFSET 1)'
\set y0 '(SELECT p.y  FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON 
p.x=p2.x WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1 OFFSET 1)'
\set y3 '(SELECT p.y  FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON 
p.x=p2.x WHERE p2.x >= q.x ORDER BY p2.x ASC  LIMIT 1 OFFSET 1)'



Questions:

1) Is the above correct? (It gives me correct result, but I'm not 100% sure)

2) Does the optimizer cache the result of identical subqueries (e.g. :x1 
or :x2, which is mentioned several times in the query)? If yes, how do I 
know this?

3) Is there another (simpler, more elegant, more efficient) way to do 
interpolation/extrapolation in SQL?

-- 
dave


Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-09-13 15:16:33
Subject: Re: Interpolation and extrapolation in SQL
Previous:From: Christopher BrowneDate: 2004-09-13 09:31:30
Subject: Re: what is maximum size of "text" datatype in postgres?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group