Re: Calculating Minkowski distance between two rows

On 04/25/2016 07:07 AM, Babak Alipour wrote:
> Greetings everyone,
>
> I'm a novice plpgsql user.
> For an application, I'm trying to write a user-defined function that
> takes a row of some table (let's say with k fields) and takes another
> row from another table (again with k fields); then calculate the
> Euclidean, Manhattan or generally Minkowski distance (with some p) and
> then return an integer.
> I've written this:
>
> CREATE FUNCTION euclidean_distance(row1 table_train, row2 table_test,
> OUT distance DOUBLE PRECISION) AS $$
> DECLARE
> tmp DOUBLE PRECISION;
> BEGIN
> FOR col IN SELECT column_name FROM information_schema.columns WHERE
> table_name=table_train LOOP
>    tmp := (row1.col - row2.col);
>    distance += tmp*tmp;
> END LOOP;
> distance := sqrt(distance);
> END;
> $$ LANGUAGE plpgsql;
>
> Could anyone please help me fix this function so that I can pass any two
> rows of two tables (with same number of columns) and have their distance
> returned.

You are already doing that, so do you mean any two rows of any two tables?

>
> Best regards,
> Babak Alipour
>
> --
> */Babak Alipour ,/*
> */University of Florida/*


--
Adrian Klaver
adrian.klaver@aklaver.com