-- $Id: reverse_geocoder.sql 94 2007-01-05 14:20:12Z mark $ --

drop type address cascade;

create type address as (
    streetNumber        varchar,
    street              varchar,
    placeName           varchar,
    adminArea           varchar,
    postalCode          varchar,
    latitude            numeric,
    longitude           numeric
);

drop type interpolated_point cascade;

create type interpolated_point as (
    point   geometry,
    percent numeric
);

create or replace function getPercentageDownLine (geometry, geometry) returns interpolated_point as $$
declare
    tmp     numeric := 0;
    percent numeric := 1;
    rec     interpolated_point;
    geom    geometry;
begin
    for i in 1..NumGeometries($2) loop
        tmp := line_locate_point(GeometryN($2, i), $1);
        if tmp < percent then
            percent := tmp;
            geom := line_interpolate_point(GeometryN($2, i), percent);
        end if;
    end loop;
    select geom, percent into rec;
    return rec; 
end;
$$ language plpgsql;

create or replace function getHouseNumber (geometry, geometry, numeric, numeric, numeric, numeric) returns integer as $$
declare
    houseNumber integer := 0;
    i           integer := 0;
    ipt         interpolated_point;
begin
    ipt := getPercentageDownLine($1, $2);
    if $1 &> ipt.point then
        if $6 > $5 then
            houseNumber := round(($5 + (($6 - $5) * ipt.percent)));
        else
            houseNumber := round(($6 + (($5 - $6) * ipt.percent)));
        end if;
    else
        if $4 > $3 then
            houseNumber := round(($3 + (($4 - $3) * ipt.percent)));
        else
            houseNumber := round(($4 + (($3 - $4) * ipt.percent)));
        end if;
    end if;
    if houseNumber = 0 then
        if $5 = 0 and $6 = 0 and $3 > 0 and $4 > 0 then
            houseNumber := round(($3 + (($4 - $3) * ipt.percent)));
        elsif $3 = 0 and $4 = 0 and $5 > 0 and $6 > 0 then
            houseNumber := round(($5 + (($6 - $5) * ipt.percent)));
        end if;
    end if;

    return houseNumber;
end;
$$ language plpgsql;

create or replace function getAddress(numeric, numeric) returns address as $$
declare
    addr    address;
    rec     record;
    ipt     interpolated_point;
    pt      geometry;
begin
    pt := GeomFromEWKT('SRID=4326;POINT(' || $2 || ' ' || $1 || ')');
    select l_f_add, l_t_add, r_f_add, r_t_add, l_pc, r_pc, l_laxon, r_laxon, l_order01, r_order01, fullname, geom into rec
        from usauma________gc where id = (select id from usauma________gc where geom && Expand(pt,0.003) group by id order by Min(Distance(geom,pt)) limit 1);
    addr.street := rec.fullname;
    addr.streetNumber := getHouseNumber(pt, rec.geom, rec.l_f_add, rec.l_t_add, rec.r_f_add, rec.r_t_add);
    ipt := getPercentageDownLine(pt, rec.geom);
    if pt &> ipt.point then
        addr.placeName := rec.r_laxon;
        addr.postalCode := rec.r_pc;
        addr.adminArea := rec.r_order01;
    else
        addr.placeName := rec.l_laxon;
        addr.postalCode := rec.l_pc;
        addr.adminArea := rec.l_order01;
    end if;
    addr.latitude := Y(ipt.point);
    addr.longitude := X(ipt.point);
    return addr;
end;
$$ language plpgsql;