Optimization (Some Complementary Remarks to Lect. 4)

Optimization (some complementary remarks to lect. 4)

By default, the robustfit algorithm uses iteratively reweighted least squares with a bisquare weighting function.

Iteratively reweighted least-squares: minimizing

Least-squares assigns equal weight to each observation; theweights for the Huber estimator decline when |e| > k; and the weights for the bisquare decline as soon as edeparts from 0, and are 0 for |e| > k. where k-tuning constant

General syntax: b = robustfit(X,y,wfun,tune,const)

Default weight function is ‘bisquare’ i.e. w = (abs(r)<1) .* (1 - r.^2).^2

where r = resid/(tune*s*sqrt(1-h)), all parameters from previous iteration

Decreasing the tuning constant increases the downweight assigned to large residuals; increasing the tuning constant decreases the downweight assigned to large residuals Default value for tune parameter is 4.685 if ‘bisquare’ fn. used.

Robustfit example:

Generate data with the trend y = 10-2*x, then change one value to simulate an outlier:

x = (1:10)';

y = 10 - 2*x + randn(10,1);

y(10) = 0; % outlier

Use both ordinary least squares and robust regression to estimate a straight-line fit:

bls = regress(y,[ones(10,1) x])

brob = robustfit(x,y,'bisquare',4.685)

Using databases (MySQL)

1. Loading data from database

% Database Server
host = 'localhost';

user = 'root';
password = '';

dbName = 'raamatutellimus';

% JDBC Parameters
jdbcString = sprintf('jdbc:mysql://%s/%s', host, dbName);
jdbcDriver = 'com.mysql.jdbc.Driver';

% Set this to the path to your MySQL Connector/J JAR

javaaddpath('mysql-connector-java-5.1.10-bin.jar')

% Create the database connection object
dbConn = database(dbName, user , password, jdbcDriver, jdbcString);

% Check to make sure that we successfully connected
if isconnection(dbConn)

% building cursor object
%general syntax: curs = exec(conn, sqlquery);

curs=exec(dbConn, 'SELECT * FROM raamatud');

% Import data into MATLAB workspace from cursor object created by exec

%general syntax :

curs = fetch(curs, RowLimit)

curs=fetch(curs)

% If the connection failed, print the error message
else
disp(sprintf('Connection failed: %s', dbConn.Message));
end
% Close the connection
close(dbConn);

% output results

data=curs.data

%Results are obtained as cell arrays

% In order to obtain numerical results use the following settings

setdbprefs('DataReturnFormat','numeric');

%back to cell arrays:

setdbprefs('DataReturnFormat','cellarray');

% In order to convert only some columns from cellarray to numeric:

hind = curs.Data(:,4)

[hind{:}]

1. Inserting data to database

% Insert MATLAB data into database

% general syntax:

fastinsert(conn, 'tablename', colnames, exdata)

% Example

exdata = {'343434343', 'Jutumees','Jutt',55.23};

colnames = {'isbn', 'autor','pealkiri','hind'};

fastinsert(dbConn, 'raamatud', colnames, exdata);

Exercise:

import exel data into Matlab, and store to database

% step1: database creation (using php)

<?php

$yhendus=mysql_connect("localhost","root","") or die("Ei saa andmebaasi lahti");

mysql_select_db("raamatutellimus",$yhendus);

$abi2="create table numbers

(

Id integer unsigned auto_increment primary key,

a float(10,3) not null,

b float(10,3) not null,

c float(10,3) not null,

d float(10,3) not null,

e float(10,3) not null

)";

mysql_query($abi2) or die(mysql_error());

?>

% step2: import exel data into MATLAB

exdata = xlsread('testmatlab.xls', 1, 'A5:E104')

% step3: store data from MATLAB to database (table numbers)

colnames = {'a', 'b','c','d','e'}

fastinsert(dbConn, 'numbers', colnames, exdata)

% step4: check results by loading data from database (from table numbers)

sqlcom='select * from numbers'

setdbprefs('DataReturnFormat','numeric')

1. Updating data in database

% General syntax:

update(dbCconn, 'table', colnames, newdata, 'whereclause')

colnames = {'d'}

newdata(1,1) = {1500}

update(conn, 'numbers', colnames, newdata, 'where d >1500’)

% using string inside string in sql query:

'where First_Name = ''Tom'''

% multiple conditions and changes

update(dbConn, 'numbers', {'e'}, {100;1500},{'where e<100'; 'where e >1500'})

% update code example in mysql language

UPDATE people SET age = 7, date = "2006-06-02 16:21:00", height = 1.22 WHERE name = "Peggy"

% In Matlab direct sql command execution not available, syntax differs from direct % SQL syntax.

Exercise:

a)Consider that the column ‘c’ is a price and calculate off 10% of the price

b)Add 10% of price conditionally (say for d>1000).

1. Stored procedures

% General syntax:

results = runstoredprocedure(dbCconn, sp_name, parms_in, types_out)

% sample code for calling

x = runstoredprocedure(c,'myproc',{2500,'Jones'},{java.sql.Types.NUMERIC})

% one simple sample of stored procedure (mysql)

CREATE PROCEDURE procedure1

(IN parameter1 INTEGER)

BEGIN

DECLARE variable1 CHAR(10);

IF parameter1 = 17 THEN

SET variable1 = 'birds';

ELSE

SET variable1 = 'beasts';

END IF;

INSERT INTO table1 VALUES (variable1);

END