| 1 | -- Pairs coverage zones from different sites and maps only their shared area.
|
|---|
| 2 | -- QGIS polygon layer. Unique key: first_zone_id + second_zone_id.
|
|---|
| 3 | CREATE OR REPLACE VIEW public.v_coverage_overlap_analysis AS
|
|---|
| 4 | WITH overlaps AS
|
|---|
| 5 | (
|
|---|
| 6 | SELECT
|
|---|
| 7 | first_zone.coverage_zone_id AS first_zone_id,
|
|---|
| 8 | first_zone.site_id AS first_site_id,
|
|---|
| 9 | first_zone.site_code AS first_site_code,
|
|---|
| 10 | first_zone.sector_label AS first_sector,
|
|---|
| 11 | second_zone.coverage_zone_id AS second_zone_id,
|
|---|
| 12 | second_zone.site_id AS second_site_id,
|
|---|
| 13 | second_zone.site_code AS second_site_code,
|
|---|
| 14 | second_zone.sector_label AS second_sector,
|
|---|
| 15 | ST_Multi(
|
|---|
| 16 | ST_CollectionExtract(
|
|---|
| 17 | ST_Intersection(
|
|---|
| 18 | first_zone.coverage_area,
|
|---|
| 19 | second_zone.coverage_area
|
|---|
| 20 | ),
|
|---|
| 21 | 3
|
|---|
| 22 | )
|
|---|
| 23 | )::geometry(MultiPolygon, 4326) AS overlap_area
|
|---|
| 24 | FROM public.v_network_coverage_map first_zone
|
|---|
| 25 | JOIN public.v_network_coverage_map second_zone
|
|---|
| 26 | ON first_zone.coverage_zone_id < second_zone.coverage_zone_id
|
|---|
| 27 | AND first_zone.site_id <> second_zone.site_id
|
|---|
| 28 | AND first_zone.coverage_area && second_zone.coverage_area
|
|---|
| 29 | AND ST_Intersects(first_zone.coverage_area, second_zone.coverage_area)
|
|---|
| 30 | )
|
|---|
| 31 | SELECT
|
|---|
| 32 | first_zone_id,
|
|---|
| 33 | first_site_id,
|
|---|
| 34 | first_site_code,
|
|---|
| 35 | first_sector,
|
|---|
| 36 | second_zone_id,
|
|---|
| 37 | second_site_id,
|
|---|
| 38 | second_site_code,
|
|---|
| 39 | second_sector,
|
|---|
| 40 | round(
|
|---|
| 41 | (ST_Area(overlap_area::geography) / 1000000.0)::numeric,
|
|---|
| 42 | 3
|
|---|
| 43 | ) AS overlap_area_sq_km,
|
|---|
| 44 | overlap_area
|
|---|
| 45 | FROM overlaps
|
|---|
| 46 | WHERE NOT ST_IsEmpty(overlap_area);
|
|---|
