Optimization (Some Complementary Remarks to Lect. 4)

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

Top View