Advanced: 06 Find nearest network sites.sql

CREATE OR REPLACE FUNCTION public.find_nearest_network_sites(
    p_latitude double precision,
    p_longitude double precision,
    p_max_distance_m double precision DEFAULT 10000,
    p_limit integer DEFAULT 5
)
RETURNS TABLE
(
    site_id bigint,
    site_code text,
    site_name text,
    region text,
    distance_m numeric,
    active_technologies text
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    IF p_latitude < -90 OR p_latitude > 90 THEN
        RAISE EXCEPTION 'Latitude must be between -90 and 90';
    END IF;

    IF p_longitude < -180 OR p_longitude > 180 THEN
        RAISE EXCEPTION 'Longitude must be between -180 and 180';
    END IF;

    IF p_max_distance_m <= 0 THEN
        RAISE EXCEPTION 'Maximum distance must be positive';
    END IF;

    IF p_limit < 1 OR p_limit > 100 THEN
        RAISE EXCEPTION 'Limit must be between 1 and 100';
    END IF;

    RETURN QUERY
    WITH search_point AS
    (
        SELECT ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326) AS location
    )
    SELECT
        ns.site_id,
        ns.site_code,
        ns.site_name,
        ns.region,
        round(
            ST_Distance(ns.location::geography, sp.location::geography)::numeric,
            1
        ) AS distance_m,
        coalesce(technologies.names, 'No active sectors') AS active_technologies
    FROM public.network_sites ns
    CROSS JOIN search_point sp
    LEFT JOIN LATERAL
    (
        SELECT string_agg(
                   DISTINCT concat(nt.generation, ' ', nt.technology_name),
                   ', '
                   ORDER BY concat(nt.generation, ' ', nt.technology_name)
               ) AS names
        FROM public.cell_towers ct
        JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
        JOIN public.network_technologies nt
          ON nt.technology_id = ts.technology_id
        WHERE ct.site_id = ns.site_id
          AND ct.status = 'active'
          AND ts.status = 'active'
          AND nt.status = 'active'
    ) technologies ON true
    WHERE ns.status = 'active'
      AND ns.location IS NOT NULL
      AND ST_DWithin(
              ns.location::geography,
              sp.location::geography,
              p_max_distance_m
          )
    ORDER BY ns.location::geography <-> sp.location::geography
    LIMIT p_limit;
END;
$$;