-- This query first generates a series of vertices for a_line_4326
-- and then generates a path profile from each building to the A receiver	

-- This section of the query generates the a_line_4326 vertices on an 80 meter 
--	spacing	
	
with aline_vertex as (select	
	a_rcvr_path_uuid as unique_path_id,
	bldg_id as bldg_number,
	bldg_long,
	bldg_lat,
	bldg_meanelevation*3.28 as bldg_mean_elev,
	bldg_maxheight*3.28 as bldg_maxheight,
	bldg_address,
	a_rcvr_call_sign,
	a_rcvr_long,
	a_rcvr_lat,
	a_rcvr_meanelev as a_rcvr_grd_elev_me,
	a_rcvr_raat,
	
	-- this formula starting with ST_PointN segementizes a_line_4326 
	-- into 80 meter segments
	-- it then generates a series of vertices, 
	-- which become the sample points that will be used in the
	-- A_Rcvr_Terrain_Profile section of the query
	-- The aline vertex will be used to find the height of the point 
	-- from the DEM file
	-- In this way we end up with a line from the each bldg to the A rcvr which
	-- steps down the path toward the A Rcvr
	-- 80 meters at a time
	-- We also need to delete the end points from the vertex list
	-- this is done by starting the generate series function at the second vertex (2) 
	-- and then subtracting 1 from the last vertex in the series
   ST_PointN(
	  st_segmentize(a_line_4326::geography, 80)::geometry,
	  generate_series(2, ST_NPoints(st_segmentize(a_line_4326::geography, 80)::geometry)-1)
   ) as aline_vertex,
   
    bldg_a_rcvr_distance_km as path_distance_km,
	b_rcvr_call_sign,
	a_rcvr_point_4326
	

	from WQTF973_WQTH343_DAT_a_1)
	
-- The A_Rcvr_Terrain_Profile section starts here
	
select
	concat(bldg_number,'.',a_rcvr_call_sign,'.',b_rcvr_call_sign) as unique_path_id,
	bldg_number,
	bldg_long,
	bldg_lat,
	bldg_mean_elev*3.28 as bldg_mean_elev_ft,
	bldg_maxheight*3.28 as bldg_maxheight_ft,
	bldg_address,
	a_rcvr_call_sign,
	a_rcvr_long,
	a_rcvr_lat,
	a_rcvr_grd_elev_me,
	a_rcvr_raat as a_rcvr_raat_me,
	round (st_x(aline_vertex)::decimal,6) as incr_long,
	round (st_y(aline_vertex)::decimal,6) as incr_lat,
    round (st_value(WQTF973_WQTH343_dem.rast,aline_vertex)::numeric,4) as incr_elev_me,
    round(( st_distance(aline_vertex.aline_vertex::geography,
		aline_vertex.a_rcvr_point_4326::geography)/1000.00)::numeric,6) 
		as incr_dist_km,
	round (path_distance_km::numeric,6) as path_distance_km

from aline_vertex, WQTF973_WQTH343_dem

where st_intersects(WQTF973_WQTH343_dem.rast,aline_vertex)
	
--	and round(( st_distance(aline_vertex.aline_vertex::geography,
--		aline_vertex.a_rcvr_point_4326::geography)/1000.00)::numeric,6) < path_distance_km

order by bldg_number asc,
incr_dist_km desc