AdvancedTopic: MajStore_PostGis.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_suggest_posts_for_worker(p_worker_id int, p_limit int default 10)
    returns table (post_id int, title text, distance_meters float)
    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
        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_suggest_posts_for_worker(6630361, 10);  -- works remote = false
select * from fn_suggest_posts_for_worker(6630362, 10000);  -- works remote = true








-- 2
create or replace function fn_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_draw_worker_location(6630361);
select * from fn_draw_worker_location(6630362);






-- 3
create or replace function fn_draw_active_posts_in_worker_region(p_worker_id integer, p_limit int default 10)
    returns table(post_id integer, title text, distance float, 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;

    return query
    select
        p.post_id,
        p.title,
        ST_DISTANCE(worker_loc.geom::geography, post_loc.geom::geography)::float as distance_m,
        post_loc.geom as geom
    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
        join post_status s on p.status_id = s.status_id
    where w.worker_id = p_worker_id
        and s.status_name = 'Active'
        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))
    order by worker_loc.geom <-> post_loc.geom
    limit p_limit;
end;
$$;

select * from fn_draw_active_posts_in_worker_region(6630361); -- works remote = false
select * from fn_draw_active_posts_in_worker_region(6630362); -- works remote = true





-- 4
create or replace function fn_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_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_m,
        st_makeline(worker_loc.geom, post_loc.geom) as geom
    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
        join post_status s on p.status_id = s.status_id
    where w.worker_id = p_worker_id
        and s.status_name = 'Active'
        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))
    order by worker_loc.geom <-> post_loc.geom
    limit p_limit;
end;
$$;

select * from fn_draw_worker_to_post_lines(6630361); -- works remote = false
select * from fn_draw_worker_to_post_lines(6630362); -- works remote = true







-- 5
create or replace function fn_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_draw_worker_notRemote_region(6630361); -- works remote = false
select * from fn_draw_worker_notRemote_region(6630362); -- works remote = true





-- POST
-- 6
create or replace function fn_application_workers_for_post(p_post_id int, p_limit int default 10)
        returns table(worker_id int, first_name text, last_name text, bio text, distance_meters float)
language plpgsql as $$
begin
    return query
    select w.worker_id, u.first_name, u.last_name, a.message,
        ST_Distance(worker_loc.geom::geography, post_loc.geom::geography)::float as distance_meters
    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_application_workers_for_post(1787990);



-- 7
create or replace function fn_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_draw_post_location(1787990);









-- 8
create or replace function fn_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 $$
begin
    return query
    select
        w.worker_id,
        st_distance(post_loc.geom::geography, worker_loc.geom::geography)::float as distance_m,
        worker_loc.geom as geom
    from post p
        join location post_loc on p.location_id = post_loc.location_id
        join application a on a.post_id = p.post_id
        join worker w on w.worker_id = a.worker_id
        join location worker_loc on w.location_id = worker_loc.location_id
    where p.post_id = p_post_id
    order by post_loc.geom <-> worker_loc.geom
    limit p_limit;
end;
$$;

select * from fn_draw_workers_applied_to_post(1787990);





-- 9
create or replace function fn_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 $$
begin
    return query
    select
        w.worker_id,
        st_distance(post_loc.geom::geography, worker_loc.geom::geography)::float as distance_m,
        st_makeline(post_loc.geom, worker_loc.geom) as geom
    from post p
        join location post_loc on p.location_id = post_loc.location_id
        join application a on a.post_id = p.post_id
        join worker w on w.worker_id = a.worker_id
        join location worker_loc on w.location_id = worker_loc.location_id
    where p.post_id = p_post_id
    order by post_loc.geom <-> worker_loc.geom
    limit p_limit;
end;
$$;

select * from fn_draw_post_to_applicant_lines(1787990);