begin;

---
--- Clear out the old data tables and make new ones
---
drop table control_points_filtered;
drop table control_points_albers;
create table control_points_filtered (
	model integer,
	pt_num smallint,
	pt_type smallint,
	utmzone smallint,
	x float8,
	y float8,
	z float8,
	easting integer,
	northing integer,
	weight integer,
	source integer
	);

---
--- Insert all vertical control points (pt_type 1) from
--- the PTM (source 1) files
---
insert into control_points_filtered (
	select 
		model, 
		pt_num, 
		pt_type,
		utmzone, 
		avg(x) as x, 
		avg(y) as y,
		avg(z) as z,
		round(x,-3) as easting,
		round(y,-3) as northing,
		weight,
		source
	from control_points_original
	where source = 1 and pt_type = 1 and x > 0 and y > 0 and z > 0
	group by utmzone,model,pt_num,weight,source,pt_type,northing,easting
	);

---
--- Insert all vertical control points (pt_type 1) from
--- the TIV (source 0) files
---
insert into control_points_filtered (
	select 
		model, 
		pt_num, 
		pt_type,
		utmzone, 
		avg(x) as x, 
		avg(y) as y,
		avg(z2) as z,
		round(x,-3) as easting,
		round(y,-3) as northing,
		weight,
		source
	from control_points_original
	where 
		source = 0 and 
		weight = 10 and 
		pt_type = 1 and 
		x > 0 and 
		y > 0 and 
		z > 0
	group by utmzone,model,pt_num,weight,source,pt_type,northing,easting
	);

---
--- Insert all tie points (pt_type 2,6,8,9) from
--- the PTM (source 1) files
---
insert into control_points_filtered (
	select 
		model, 
		pt_num, 
		pt_type,
		utmzone, 
		avg(x) as x, 
		avg(y) as y,
		avg(z) as z,
		round(x,-3) as easting,
		round(y,-3) as northing,
		weight,
		source
	from control_points_original
	where 
		source = 1 and 
		pt_type in (2,6,8,9) and 
		x > 0 and 
		y > 0 and 
		z > 0
	group by utmzone,model,pt_num,weight,source,pt_type,northing,easting
	);

---
--- Insert all tie points (pt_type 2,6,8,9) from
--- the TIV (source 0) files
---
insert into control_points_filtered (
	select 
		model, 
		pt_num, 
		pt_type,
		utmzone, 
		avg(x2) as x, 
		avg(y2) as y,
		avg(z2) as z,
		round(x2,-3) as easting,
		round(y2,-3) as northing,
		weight,
		source
	from control_points_original
	where 
		source = 0 and 
		pt_type in (2,6,8,9) and 
		x2 > 0 and 
		y2 > 0 and
		weight = '101010'
	group by utmzone,model,pt_num,weight,source,pt_type,northing,easting
	);

---
--- Create geometries for export, converting from
--- UTM to BC Albers as we go.
---
select transform(
		GeometryFromText(
			'POINT(' || x || ' ' || y || ' ' || z || ')',
			26900 + utmzone), 100000) as geom, 
		model, 
		pt_num, 
		pt_type,
		utmzone, 
		x, y, z, 
		easting,northing,
		weight, 
		source 
	into control_points_albers 
	from control_points_filtered;

commit;
