AdvancedTopic: MajStore_PostGis_refactored.sql

CREATE EXTENSION IF NOT EXISTS postgis;

alter table location add column geom GEOMETRY(Point, 4326);
update location set geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

create index idx_location_geom on Location USING GIST(geom);



-- WORKER
-- 1
create or replace function fn_v2_suggest_posts_for_worker(p_worker_id int, p_limit int default 10)
    returns table (post_id int, title text, distance_meters float, location_id int, post_address text, post_region text, post_city text)
    language plpgsql
as $$
declare
    v_works_remote bool;
begin
    select w.works_remote
    into v_works_remote
    from worker w
    where w.worker_id = p_worker_id;

    return query
    select p.post_id,
           p.title,
           ST_Distance(worker_loc.geom::geography, post_loc.geom::geography)::float as distance_meters,
           post_loc.location_id,
           post_loc.address,
           post_loc.region,
           post_loc.city
    from worker w
        join location worker_loc on w.location_id = worker_loc.location_id
        join post p on p.status_id = 1
        join location post_loc on p.location_id = post_loc.location_id
    where w.worker_id = p_worker_id
        and exists(select 1
                 from worker_specialty ws
                 join post_specialty ps on ws.specialty_id = ps.specialty_id
                 where ws.worker_id = w.worker_id and ps.post_id = p.post_id)
        and (v_works_remote = true or ST_DWithin(worker_loc.geom::geography, post_loc.geom::geography, 1500))
        and p.status_id = 1
    order by worker_loc.geom <-> post_loc.geom
    limit p_limit;
end;
$$;

select * from fn_v2_suggest_posts_for_worker(6630361, 10);
select * from fn_v2_suggest_posts_for_worker(6630362, 10000);





-- 2
create or replace function fn_v2_draw_worker_location(p_worker_id integer)
    returns table(worker_id integer, geom geometry)
    language plpgsql
as $$
begin
    return query
    select
        w.worker_id,
        l.geom
    from worker w
    join location l on w.location_id = l.location_id
    where w.worker_id = p_worker_id;
end;
$$;

select * from fn_v2_draw_worker_location(6630361);
select * from fn_v2_draw_worker_location(6630362);






-- 3
create or replace function fn_v2_draw_active_posts_in_worker_region(p_worker_id integer, p_limit int default 10)
    returns table(post_id integer, distance_meters float, geom geometry)
    language plpgsql
as $$
begin
    return query
    select p.post_id, p.distance_meters, post_loc.geom
    from fn_v2_suggest_posts_for_worker(p_worker_id, p_limit) p
                join location post_loc on post_loc.location_id = p.location_id;
end;
$$;

select * from fn_v2_draw_active_posts_in_worker_region(6630361);
select * from fn_v2_draw_active_posts_in_worker_region(6630362);







-- 4
create or replace function fn_v2_draw_worker_to_post_lines(p_worker_id integer, p_limit int default 10)
    returns table(post_id integer, title text, distance_m float, geom geometry)
    language plpgsql
as $$
declare
    v_worker_geom geometry;
begin
    select l.geom
    into v_worker_geom
    from worker w
    join location l on w.location_id = l.location_id
    where w.worker_id = p_worker_id;

    return query
    select
        p.post_id,
        p.title,
        p.distance_meters,
        ST_MakeLine(v_worker_geom, post_loc.geom) as geom
    from fn_v2_suggest_posts_for_worker(p_worker_id, p_limit) p
        join location post_loc on post_loc.location_id = p.location_id;
end;
$$;

select * from fn_v2_draw_worker_to_post_lines(6630361);
select * from fn_v2_draw_worker_to_post_lines(6630362);





-- 5
create or replace function fn_v2_draw_worker_notRemote_region(p_worker_id integer)
    returns table(worker_id integer, geom geometry)
    language plpgsql
as $$
declare
    v_works_remote bool;
begin
    select w.works_remote into v_works_remote
    from worker w
    where w.worker_id = p_worker_id;

    if v_works_remote = true then
        return;
    end if;

    return query
    select
        w.worker_id,
        ST_Buffer(l.geom::geography, 1000)::geometry as geom
    from worker w
    join location l on w.location_id = l.location_id
    where w.worker_id = p_worker_id;
end;
$$;

select * from fn_v2_draw_worker_notRemote_region(6630361);
select * from fn_v2_draw_worker_notRemote_region(6630362);








-- POSTS
-- 6
drop function fn_v2_application_workers_for_post
create or replace function fn_v2_application_workers_for_post(p_post_id int, p_limit int default 10)
    returns table(worker_id int, first_name text, last_name text, message text, expected_price float, distance_meters float, location_id int, worker_address text, wokrer_region text, worker_city text)
    language plpgsql
as $$
begin
    return query
    select w.worker_id,
        u.first_name,
        u.last_name,
        a.message,
        a.expected_price,
        ST_Distance(worker_loc.geom::geography, post_loc.geom::geography)::float as distance_meters,
        worker_loc.location_id,
        worker_loc.address,
        worker_loc.region,
        worker_loc.city
    from worker w
    join "User" u on w.user_id = u.user_id
    join location worker_loc on w.location_id = worker_loc.location_id
    join post p on p.post_id = p_post_id
    join application a on p.post_id = a.post_id and a.worker_id = w.worker_id
    join location post_loc on p.location_id = post_loc.location_id
    where (w.works_remote = true
       or ST_DWithin(worker_loc.geom::geography, post_loc.geom::geography, 1500))
    and p.status_id = 1
    order by worker_loc.geom <-> post_loc.geom
    limit p_limit;
end;
$$;

select * from fn_v2_application_workers_for_post(1787990);





-- 7
create or replace function fn_v2_draw_post_location(p_post_id integer)
    returns table(post_id integer, title text, geom geometry)
    language plpgsql
as $$
begin
    return query
    select
        p.post_id,
        p.title,
        l.geom
    from post p
    join location l on p.location_id = l.location_id
    where p.post_id = p_post_id;
end;
$$;

select * from fn_v2_draw_post_location(1787990);







-- 8
create or replace function fn_v2_draw_workers_applied_to_post(p_post_id integer, p_limit int default 10)
    returns table(worker_id integer, distance_m float, geom geometry)
    language plpgsql
as $$
declare
    v_post_geom geometry;
begin
    select p.geom
    into v_post_geom
    from fn_draw_post_location(p_post_id) p;

    return query
    select
        w.worker_id,
        ST_Distance(v_post_geom::geography, worker_loc.geom::geography)::float as distance_m,
        worker_loc.geom as geom
    from application a
        join worker w on w.worker_id = a.worker_id
        join location worker_loc on w.location_id = worker_loc.location_id
    where a.post_id = p_post_id
    order by v_post_geom <-> worker_loc.geom
    limit p_limit;
end;
$$;

select * from fn_v2_draw_workers_applied_to_post(1787990);






-- 9
create or replace function fn_v2_draw_post_to_applicant_lines(p_post_id integer, p_limit int default 10)
    returns table(worker_id integer, distance_m float, geom geometry)
    language plpgsql
as $$
declare
    v_post_geom geometry;
begin
    select p.geom
    into v_post_geom
    from fn_draw_post_location(p_post_id) p;

    return query
    select
        w.worker_id,
        w.distance_m,
        ST_MakeLine(v_post_geom, w.geom) as geom
    from fn_draw_workers_applied_to_post(p_post_id, p_limit) w;
end;
$$;

select * from fn_v2_draw_post_to_applicant_lines(1787990);