| 1 | CREATE OR REPLACE FUNCTION public.calculate_site_coverage_area_sq_km(
|
|---|
| 2 | p_site_id bigint
|
|---|
| 3 | )
|
|---|
| 4 | RETURNS numeric
|
|---|
| 5 | LANGUAGE sql
|
|---|
| 6 | STABLE
|
|---|
| 7 | STRICT
|
|---|
| 8 | AS $$
|
|---|
| 9 | SELECT round(
|
|---|
| 10 | (
|
|---|
| 11 | ST_Area(ST_Union(cz.coverage_area)::geography)
|
|---|
| 12 | / 1000000.0
|
|---|
| 13 | )::numeric,
|
|---|
| 14 | 3
|
|---|
| 15 | )
|
|---|
| 16 | FROM public.cell_towers ct
|
|---|
| 17 | JOIN public.tower_sectors ts ON ts.tower_id = ct.tower_id
|
|---|
| 18 | JOIN public.coverage_zones cz ON cz.sector_id = ts.sector_id
|
|---|
| 19 | WHERE ct.site_id = p_site_id
|
|---|
| 20 | AND cz.coverage_area IS NOT NULL;
|
|---|
| 21 | $$;
|
|---|
| 22 |
|
|---|
| 23 | COMMENT ON FUNCTION public.calculate_site_coverage_area_sq_km IS
|
|---|
| 24 | 'Returns the unioned coverage area for one network site in square kilometres.';
|
|---|
