/* FINAL
--make geometry for each caridnal direction
create table "LUCAS_Cop2022".l2022_cop_radpoly_radqs_i as 
select df1.*,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnlc1n::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnlc1n::integer + 50, radians(0-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnlc1n::integer + 50, radians(0+90/2))::geometry, df1.geom_4326]))) as geomradpoly_n,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnclc1s::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnclc1s::integer + 50, radians(180-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnclc1s::integer + 50, radians(180+90/2))::geometry, df1.geom_4326]))) as geomradpoly_s,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnclc1e::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnclc1e::integer + 50, radians(90-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnclc1e::integer + 50, radians(90+90/2))::geometry, df1.geom_4326]))) as geomradpoly_e,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnclc1w::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnclc1w::integer + 50, radians(270-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnclc1w::integer + 50, radians(270+90/2))::geometry, df1.geom_4326]))) as geomradpoly_w
from "LUCAS_Cop2022".l2022_cop_geompoly_gps df1
where df1.surveycprnlc1n != ''
and df1.surveycprnclc1s != ''
and df1.surveycprnclc1e != ''
and df1.surveycprnclc1w != '';
*/

-- OFICIAL ESTAT
create table "LUCAS_Cop2022".l2022_cop_radpoly_radqs_i as 
select df1.*,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnlc1n::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnlc1n::integer + 50, radians(0-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnlc1n::integer + 50, radians(0+90/2))::geometry, df1.geom_4326]))) as geomradpoly_n,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnclc1s::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnclc1s::integer + 50, radians(180-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnclc1s::integer + 50, radians(180+90/2))::geometry, df1.geom_4326]))) as geomradpoly_s,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnclc1e::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnclc1e::integer + 50, radians(90-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnclc1e::integer + 50, radians(90+90/2))::geometry, df1.geom_4326]))) as geomradpoly_e,
	st_intersection(
		st_buffer(df1.geom_4326::geography, df1.surveycprnclc1w::integer, 50)::geometry,
		st_makepolygon(st_makeline(array[df1.geom_4326, st_project(df1.geom_4326::geography, df1.surveycprnclc1w::integer + 50, radians(270-90/2))::geometry,
		st_project(df1.geom_4326::geography, df1.surveycprnclc1w::integer + 50, radians(270+90/2))::geometry, df1.geom_4326]))) as geomradpoly_w
from "LUCAS_Cop2022".l2022_cop_geompoly_gps df1
where df1.surveycprnlc1n is not null
and df1.surveycprnclc1s is not null
and df1.surveycprnclc1e is not null
and df1.surveycprnclc1w is not null;


--union all of them together with some mitre smoothing
create table "LUCAS_Cop2022".l2022_cop_radpoly_radqs as
select radQs.*,
st_transform(st_buffer(
	st_union(
		st_union(
			st_union(radQs.geomradpoly_n, radQs.geomradpoly_e),
			radQs.geomradpoly_s),
		radQs.geomradpoly_w)
	::geography, 0.1, 'join=mitre mitre_limit=1.0')::geometry, 3035) as geomradpoly_nesw
from  "LUCAS_Cop2022".l2022_cop_radpoly_radqs_i radQs;

--set the srid
SELECT UpdateGeometrySRID('LUCAS_Cop2022','l2022_cop_radpoly_radqs','geomradpoly_nesw', 3035);

--check
SELECT Find_SRID('LUCAS_Cop2022', 'l2022_cop_radpoly_radqs', 'geomradpoly_nesw');

--calculate new area
UPDATE "LUCAS_Cop2022"."l2022_cop_radpoly_radqs" SET poly_area_sqm = ST_Area("LUCAS_Cop2022"."l2022_cop_radpoly_radqs".geomradpoly_NESW);


--remove unnecessary columns
alter table "LUCAS_Cop2022".l2022_cop_radpoly_radqs drop column geom_4326;
alter table "LUCAS_Cop2022".l2022_cop_radpoly_radqs drop column geom_poly_3035;
alter table "LUCAS_Cop2022".l2022_cop_radpoly_radqs drop column geomradpoly_n;
alter table "LUCAS_Cop2022".l2022_cop_radpoly_radqs drop column geomradpoly_s;
alter table "LUCAS_Cop2022".l2022_cop_radpoly_radqs drop column geomradpoly_e;
alter table "LUCAS_Cop2022".l2022_cop_radpoly_radqs drop column geomradpoly_w;

--create index on key column
CREATE INDEX l2022_cop_geompoly_gps_pointid_idx 
ON "LUCAS_Cop2022".l2022_cop_geompoly_gps(pointid);

CREATE INDEX l2022_cop_radpoly_radqs_pointid_idx 
ON "LUCAS_Cop2022".l2022_cop_radpoly_radqs(pointid);

--create spatial index 
CREATE INDEX l2022_cop_geompoly_gps_geom_3035_idx
  ON "LUCAS_Cop2022".l2022_cop_geompoly_gps
  USING GIST (geom_poly_3035);
  
CREATE INDEX l2022_cop_radpoly_radqs_geom_3035_idx
  ON "LUCAS_Cop2022".l2022_cop_radpoly_radqs
  USING GIST (geomradpoly_nesw);
  
--merge with all attributes for final dataset
create table "LUCAS_Cop2022".l2022_cop_radpoly_radqs_allatr as
select t2.*, t1.poly_area_sqm, t1.geomradpoly_nesw, t1.surveycprncando, t1.surveycprnlc1n, t1.surveycprnclc1e, t1.surveycprnclc1w, t1.surveycprnclc1s, t1.surveycprnlc
from "LUCAS_Cop2022".l2022_cop_radpoly_radqs t1 join "LUCAS_Core2022".l2022_survey_core t2
on t1.pointid = t2.point_id;
  
SELECT DropGeometryColumn ('LUCAS_Cop2022','l2022_cop_radpoly_radqs_allatr','geom_surv_4326');
  
  
--upload color legend file in the same schema!!!
--merge with legend color file
create table "LUCAS_Cop2022".l2022_cop_radpoly_radqs_allatr_wcol as
select t2.*, t1.col_hex from "LUCAS_Cop2022".l2022_cop_radpoly_radqs_allatr t2
join "LUCAS_Cop2022".lcop2022_alllc1_allcol_allhex_plusclcleg t1
on t2.lc1_code  = t1.lc1_code;

--check - should return zero rows
select * from "LUCAS_Cop2022".l2022_cop_radpoly_radqs_allatr_wcol where col_hex is null;
  
  
--export to gpkg with ogr2ogr - do not run in postgres client. run in terminal:
ogr2ogr -f "GPKG" /eos/jeodpp/data/projects/REFOCUS/data/LUCAS_Copernicus_2022/data_official_estat/output/l2022_survey_cop_points.gpkg PG:"host=jeodb01.cidsn.jrc.it dbname=refocus_eucropmap_db port=54331 user=refocus_eucropmap_user password=8gsmuTJUj2xKrHbf" -nln points -sql "SELECT pointid, pointnuts0, pointlat, pointlong, pointcopernicus, surveycprncando, surveycprnlc1n, surveycprnclc1e, surveycprnclc1w, surveycprnclc1s, surveycprnlc, surveygpslat, surveygpslong, geom_4326 FROM \"LUCAS_Cop2022\".l2022_cop_geompoly"
ogr2ogr -f "GPKG" /eos/jeodpp/data/projects/REFOCUS/data/LUCAS_Copernicus_2022/data_official_estat/output/l2022_survey_cop_poly.gpkg PG:"host=jeodb01.cidsn.jrc.it dbname=refocus_eucropmap_db port=54331 user=refocus_eucropmap_user password=8gsmuTJUj2xKrHbf" -nln polygons -sql "SELECT pointid, pointnuts0, pointlat, pointlong, pointcopernicus, surveycprncando, surveycprnlc1n, surveycprnclc1e, surveycprnclc1w, surveycprnclc1s, surveycprnlc, surveygpslat, surveygpslong, surveygpslong_3035, surveygpslat_3035, geom_poly_3035, poly_area_sqm FROM \"LUCAS_Cop2022\".l2022_cop_geompoly_gps"
ogr2ogr -f "GPKG" /eos/jeodpp/data/projects/REFOCUS/data/LUCAS_Copernicus_2022/data_official_estat/output/l2022_survey_cop_poly_rad.gpkg PG:"host=jeodb01.cidsn.jrc.it dbname=refocus_eucropmap_db port=54331 user=refocus_eucropmap_user password=8gsmuTJUj2xKrHbf" -nln polygons -sql "SELECT pointid,pointnuts0,pointlat,pointlong,pointcopernicus,surveycprncando,surveycprnlc1n,surveycprnclc1e,surveycprnclc1w,surveycprnclc1s,surveycprnlc,surveygpslat,surveygpslong,surveygpslong_3035,surveygpslat_3035, poly_area_sqm, geomradpoly_nesw FROM \"LUCAS_Cop2022\".l2022_cop_radpoly_radqs"
ogr2ogr -f "GPKG" /eos/jeodpp/data/projects/REFOCUS/data/LUCAS_Copernicus_2022/data_official_estat/output/l2022_survey_cop_poly_rad_allatr.gpkg PG:"host=jeodb01.cidsn.jrc.it dbname=refocus_eucropmap_db port=54331 user=refocus_eucropmap_user password=8gsmuTJUj2xKrHbf" -nln polygons -sql "SELECT * FROM \"LUCAS_Cop2022\".l2022_cop_radpoly_radqs_allatr"

