| 1 | -- QGIS polygon layer. Unique key: coverage_zone_id; geometry: coverage_area.
|
|---|
| 2 | CREATE OR REPLACE VIEW public.v_network_coverage_map AS
|
|---|
| 3 | SELECT
|
|---|
| 4 | cz.coverage_zone_id,
|
|---|
| 5 | ns.site_id,
|
|---|
| 6 | ns.site_code,
|
|---|
| 7 | ns.site_name,
|
|---|
| 8 | ns.region,
|
|---|
| 9 | ns.status AS site_status,
|
|---|
| 10 | ct.tower_id,
|
|---|
| 11 | ct.tower_code,
|
|---|
| 12 | ts.sector_id,
|
|---|
| 13 | ts.sector_label,
|
|---|
| 14 | ts.azimuth,
|
|---|
| 15 | ts.beamwidth,
|
|---|
| 16 | ts.frequency_band,
|
|---|
| 17 | nt.technology_name,
|
|---|
| 18 | nt.generation,
|
|---|
| 19 | cz.coverage_type,
|
|---|
| 20 | cz.coverage_radius_m,
|
|---|
| 21 | cz.signal_quality_score,
|
|---|
| 22 | cz.last_measured_at,
|
|---|
| 23 | ns.latitude AS site_latitude,
|
|---|
| 24 | ns.longitude AS site_longitude,
|
|---|
| 25 | cz.coverage_area
|
|---|
| 26 | FROM public.coverage_zones cz
|
|---|
| 27 | JOIN public.tower_sectors ts ON ts.sector_id = cz.sector_id
|
|---|
| 28 | JOIN public.cell_towers ct ON ct.tower_id = ts.tower_id
|
|---|
| 29 | JOIN public.network_sites ns ON ns.site_id = ct.site_id
|
|---|
| 30 | JOIN public.network_technologies nt
|
|---|
| 31 | ON nt.technology_id = ts.technology_id;
|
|---|
