# line equation: y = m x + D
# where m is slope, and D is y-intercept.

drop table tmp_n;

create table tmp_n (
    ang double precision,
    d double precision,
    x double precision,
    y double precision
);

perl -e'
# generate a large sample of inputs.
use Math::Trig;
$pi = 4*atan(1);                                # 3.14...
for($a=-89;$a<90;$a+=int(rand(10))){
    my $ang = $a * $pi / 180;                   # angle in radiants.
    my $d = int((rand()-0.5) * 1000);           # y intercept
    for($x = 10;$x < 10000;$x+=10){             # take many samples
        $y = (rand()-0.5) * 10;                 # normally distributed NOISE around line.
        $ox = $x * cos($ang) - $y * sin($ang);  # rotate by angle.
        $oy = $x * sin($ang) + $y * cos($ang) + $d;
        print "$a,$d,$ox,$oy\n";
    }
}
' | loadthisintodb...

# Note, that if your database has: REGR_SLOPE, REGR_INTERCEPT, then use those!

select 
    ang,d,      -- original inputs.
    slope,intercept, angle,     -- calculated.
    abs(d - intercept) as intercept_err,
    abs(ang - angle) as angle_err
from (
    -- linear regression (least squares)
    select 
        ang,d,
        -- slope
        (count(*) / (sum(x*x)*count(*) - sum(x)*sum(x))) * sum(x*y) + 
        (-sum(x) / (sum(x*x)*count(*) - sum(x)*sum(x))) * sum(y) as slope,
        -- intercept
        ( -sum(x) / (sum(x*x)*count(*) - sum(x)*sum(x)) ) * sum(x*y) + 
        ( sum(x*x) / (sum(x*x)*count(*) - sum(x)*sum(x)) ) * sum(y) as intercept,
        -- angle
        atan((count(*) / (sum(x*x)*count(*) - sum(x)*sum(x))) * sum(x*y) + 
        (-sum(x) / (sum(x*x)*count(*) - sum(x)*sum(x))) * sum(y)) * 45/atan(1) as angle
    from tmp_n
    group by ang,d
) a
order by 1,2