-- fix the ',' to '.'
--pointid

/* no need to do this for final dump
update "LUCAS_Cop2022".l2022_pointid_cop
set pointlat = replace(pointlat, ',', '.');

update "LUCAS_Cop2022".l2022_pointid_cop
set pointlong = replace(pointlong, ',', '.');

--survey
update "LUCAS_Cop2022".l2022_survey_cop
set surveygpslat = replace(surveygpslat, ',', '.');

update "LUCAS_Cop2022".l2022_survey_cop
set surveygpslong = replace(surveygpslong, ',', '.');
*/

--rename columns to original ones provided so as not to break already developped code down the line
alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_id to pointid;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_nuts0 to pointnuts0;
--alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_nuts1 to pointnuts1;
--alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_nuts2 to pointnuts2;
--alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_nuts3 to pointnuts3;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_lat to pointlat;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_long to pointlong;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column point_copernicus to pointcopernicus;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_gps_lat to surveygpslat;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_gps_long to surveygpslong;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_cprn_cando to surveycprncando;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_cprn_lc to surveycprnlc;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_cprn_lc1n to surveycprnlc1n;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_cprnc_lc1e to surveycprnclc1e;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_cprnc_lc1s to surveycprnclc1s;
alter table "LUCAS_Cop2022".l2022_survey_cop rename column survey_cprnc_lc1w to surveycprnclc1w;

--drop some columns - do not exist in official estat data
--alter table "LUCAS_Cop2022".l2022_survey_cop drop column user_id;
--alter table "LUCAS_Cop2022".l2022_survey_cop drop column point_nuts0;

/*
--harmonize some labels
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'A30 - Other artificial areas' where survey_lc1 = 'A3 - Other artificial areas';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'C10 - Broadleaved woodland' where survey_lc1 = 'C1 - Broadleaved woodland';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'D10 - Shrubland with sparse tree cover' where survey_lc1 = 'D1 - Shrubland with sparse tree cover';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'D20 - Shrubland without tree cover' where survey_lc1 = 'D2 - Shrubland without tree cover';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'E10 - Grassland with sparse tree/shrub cover' where survey_lc1 = 'E1 - Grassland with sparse tree/shrub cover';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'E20 - Grassland without tree/shrub cover' where survey_lc1 = 'E2 - Grassland without tree/shrub cover';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'E30 - Spontaneously vegetated surfaces' where survey_lc1 = 'E3 - Spontaneously vegetated surfaces';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'F10 - Rocks and stones' where survey_lc1 = 'F1 - Rocks and stones';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'F20 - Sand' where survey_lc1 = 'F2 - Sand';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'F40 - Other bare soil' where survey_lc1 = 'F4 - Other bare soil';
*/

--harmonize official labels - no label
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'A30' where survey_lc1 = 'A3';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'C10' where survey_lc1 = 'C1';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'D10' where survey_lc1 = 'D1';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'D20' where survey_lc1 = 'D2';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'E10' where survey_lc1 = 'E1';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'E20' where survey_lc1 = 'E2';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'E30' where survey_lc1 = 'E3';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'F10' where survey_lc1 = 'F1';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'F20' where survey_lc1 = 'F2';
update "LUCAS_Cop2022".l2022_survey_cop set survey_lc1 = 'F40' where survey_lc1 = 'F4';


/*
-- join the two tables meaningfully
create table "LUCAS_Cop2022".l2022_survey_cop_colsonly_coords as
select pid.pointid, pid.pointnuts0, pid.pointnuts1, pid.pointnuts2, pid.pointnuts3, pid.pointlat, pid.pointlong, pid.pointcopernicus, 
surv.surveycprncando, surv.surveycprnlc1n, surv.surveycprnclc1e, surv.surveycprnclc1w, surv.surveycprnclc1s, surv.surveycprnurban, surv.surveycprnlc, surv.surveygpslat, surv.surveygpslong, surv.surveygpsew
from "LUCAS_Cop2022".l2022_survey_cop surv
join "LUCAS_Cop2022".l2022_pointid_cop pid 
on surv.pointid = pid.pointid;
*/

-- join the two tables meaningfully last dump
create table "LUCAS_Cop2022".l2022_survey_cop_colsonly_coords as
select pointid, pointnuts0, pointlat, pointlong, pointcopernicus, 
surveycprncando, surveycprnlc1n, surveycprnclc1e, surveycprnclc1w, surveycprnclc1s, surveycprnlc, surveygpslat, surveygpslong
from "LUCAS_Cop2022".l2022_survey_cop;

/*
-- cast to coordinate columns to float
AALTER TABLE "LUCAS_Cop2022".l2022_survey_cop_colsonly_coords
ALTER COLUMN pointlat SET DATA TYPE numeric USING pointlat::numeric,
ALTER COLUMN pointlong SET DATA TYPE numeric USING pointlong::numeric,
ALTER COLUMN surveygpslat SET DATA TYPE numeric USING surveygpslat::numeric,
ALTER COLUMN surveygpslong SET DATA TYPE numeric USING surveygpslong::numeric;
*/

/*
--work only of the copcando=1
create table "LUCAS_Cop2022".l2022_cop_geompoly as
select * from "LUCAS_Cop2022".l2022_survey_cop_colsonly_coords
where surveycprncando = 'TRUE';
*/

create table "LUCAS_Cop2022".l2022_cop_geompoly as
select * from "LUCAS_Cop2022".l2022_survey_cop_colsonly_coords
where surveycprncando = 1;

--remove any geom info if neccessary
SELECT DropGeometryColumn ('LUCAS_Cop2022','l2022_cop_geompoly','geom');

-- Add a spatial column to the table
SELECT AddGeometryColumn ('LUCAS_Cop2022','l2022_cop_geompoly','geom_4326',4326,'POINT',2);

-- Populate geometry column
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly"
    SET geom_4326 = ST_SetSRID(
        ST_MakePoint(
             "pointlong"::double precision,
             "pointlat"::double precision
        ), 4326);
        

/*
-- convert to 3035 and extract 
-- test select stmt
SELECT 
ST_X(ST_TRANSFORM("LUCAS_Cop2022"."l2022_cop_geompoly".geom,3035)) AS pointlong_3035, -- longitude point x 3035
ST_Y(ST_TRANSFORM("LUCAS_Cop2022"."l2022_cop_geompoly".geom,3035)) AS pointlat_3035 --latitude point y 3035
FROM
"LUCAS_Cop2022"."l2022_cop_geompoly" ;
*/

-- add new column to house the 3035 coordinates
ALTER TABLE "LUCAS_Cop2022"."l2022_cop_geompoly" ADD pointlong_3035 DOUBLE PRECISION;
ALTER TABLE "LUCAS_Cop2022"."l2022_cop_geompoly" ADD pointlat_3035 DOUBLE PRECISION;

UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly" SET pointlong_3035 = ST_X(ST_TRANSFORM("LUCAS_Cop2022"."l2022_cop_geompoly".geom_4326,3035));
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly" SET pointlat_3035 = ST_Y(ST_TRANSFORM("LUCAS_Cop2022"."l2022_cop_geompoly".geom_4326,3035));

/*
--create a test subset
CREATE TABLE "LUCAS_Cop2022"."tst_geom_poly" AS
SELECT * FROM "LUCAS_Cop2022"."l2022_cop_geompoly" LIMIT 10;

-- Add a spatial column to the table
SELECT AddGeometryColumn ('LUCAS_Cop2022','tst_geom_poly','geom_poly_3035',3035,'POLYGON',2);

SELECT AddGeometryColumn ('LUCAS_Cop2022','tst_geom_poly','geom_line_3035',3035,'LINE',2);

--test select stmt
SELECT ST_MakePolygon(
    ST_MakeLine(
        ARRAY[
            ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035), 
            ST_Point(pointlong_3035,pointlat_3035-surveycprnclc1s::int),
            ST_Point(pointlong_3035+surveycprnclc1e::int,pointlat_3035),
            ST_Point(pointlong_3035,pointlat_3035+surveycprnlc1n::int),
            ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035)]))
FROM "LUCAS_Cop2022"."tst_geom_poly";

UPDATE "LUCAS_Cop2022"."tst_geom_poly"
SET geom_poly_3035 = st_setsrid(
    ST_MakePolygon(ST_MakeLine(ARRAY[ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035), 
            ST_Point(pointlong_3035,pointlat_3035-surveycprnclc1s::int),
            ST_Point(pointlong_3035+surveycprnclc1e::int,pointlat_3035),
            ST_Point(pointlong_3035,pointlat_3035+surveycprnlc1n::int),
            ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035)])),
3035);
*/

-- DO THE WHOLE SET
-- Add a spatial column to the table
SELECT AddGeometryColumn ('LUCAS_Cop2022','l2022_cop_geompoly','geom_poly_3035',3035,'POLYGON',2);

/*
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly"
SET geom_poly_3035 = st_setsrid(
    ST_MakePolygon(ST_MakeLine(ARRAY[ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035), 
            ST_Point(pointlong_3035,pointlat_3035-surveycprnclc1s::int),
            ST_Point(pointlong_3035+surveycprnclc1e::int,pointlat_3035),
            ST_Point(pointlong_3035,pointlat_3035+surveycprnlc1n::int),
            ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035)])),
3035)
WHERE surveycprncando = 'TRUE' 
AND surveycprnlc1n != 'NA'
AND surveycprnlc1n != ''
AND surveycprnclc1s != 'NA'
AND surveycprnclc1s != ''
AND surveycprnclc1w != 'NA'
AND surveycprnclc1w != ''
AND surveycprnclc1e != 'NA'
AND surveycprnclc1e != '';
*/

/* FINAL
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly"
SET geom_poly_3035 = st_setsrid(
    ST_MakePolygon(ST_MakeLine(ARRAY[ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035), 
            ST_Point(pointlong_3035,pointlat_3035-surveycprnclc1s::int),
            ST_Point(pointlong_3035+surveycprnclc1e::int,pointlat_3035),
            ST_Point(pointlong_3035,pointlat_3035+surveycprnlc1n::int),
            ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035)])),
3035)
WHERE surveycprncando = 'Yes'
AND surveycprnlc1n != 'NA'
AND surveycprnlc1n != ''
AND surveycprnclc1s != 'NA'
AND surveycprnclc1s != ''
AND surveycprnclc1w != 'NA'
AND surveycprnclc1w != ''
AND surveycprnclc1e != 'NA'
AND surveycprnclc1e != '';
*/


--OFICIAL ESTAT
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly"
SET geom_poly_3035 = st_setsrid(
    ST_MakePolygon(ST_MakeLine(ARRAY[ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035), 
            ST_Point(pointlong_3035,pointlat_3035-surveycprnclc1s::int),
            ST_Point(pointlong_3035+surveycprnclc1e::int,pointlat_3035),
            ST_Point(pointlong_3035,pointlat_3035+surveycprnlc1n::int),
            ST_Point(pointlong_3035-surveycprnclc1w::int,pointlat_3035)])),
3035)
WHERE surveycprncando = 1
AND surveycprnlc1n is not null
AND surveycprnclc1s is not null
AND surveycprnclc1w is not null
AND surveycprnclc1e is not null;

-- make geometry from GPS LOC
--work only of the copcando=1
/*
create table "LUCAS_Cop2022".l2022_cop_geompoly_gps as
select * from "LUCAS_Cop2022".l2022_survey_cop_colsonly_coords
where surveycprncando = 'TRUE'
and surveygpslat != ''
and surveygpslong != '';
*/

create table "LUCAS_Cop2022".l2022_cop_geompoly_gps as
select * from "LUCAS_Cop2022".l2022_survey_cop_colsonly_coords
where surveycprncando = 1
and surveygpslat is not null
and surveygpslong is not null;

--remove any geom info
--SELECT DropGeometryColumn ('LUCAS_Cop2022','l2022_cop_geompoly_gps','geom');

-- Add a spatial column to the table
SELECT AddGeometryColumn ('LUCAS_Cop2022','l2022_cop_geompoly_gps','geom_4326',4326,'POINT',2);

-- Populate geometry column
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly_gps"
    SET geom_4326 = ST_SetSRID(
        ST_MakePoint(
             "surveygpslong"::double precision,
             "surveygpslat"::double precision
        ), 4326);
        


-- add new column to house the 3035 coordinates
ALTER TABLE "LUCAS_Cop2022"."l2022_cop_geompoly_gps" ADD surveygpslong_3035 DOUBLE PRECISION;
ALTER TABLE "LUCAS_Cop2022"."l2022_cop_geompoly_gps" ADD surveygpslat_3035 DOUBLE PRECISION;

UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly_gps" SET surveygpslong_3035 = ST_X(ST_TRANSFORM("LUCAS_Cop2022"."l2022_cop_geompoly_gps".geom_4326,3035));
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly_gps" SET surveygpslat_3035 = ST_Y(ST_TRANSFORM("LUCAS_Cop2022"."l2022_cop_geompoly_gps".geom_4326,3035));

-- DO THE WHOLE SET
-- Add a spatial column to the table
SELECT AddGeometryColumn ('LUCAS_Cop2022','l2022_cop_geompoly_gps','geom_poly_3035',3035,'POLYGON',2);

/*
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly_gps"
SET geom_poly_3035 = st_setsrid(
    ST_MakePolygon(ST_MakeLine(ARRAY[ST_Point(surveygpslong_3035-surveycprnclc1w::int,surveygpslat_3035), 
            ST_Point(surveygpslong_3035,surveygpslat_3035-surveycprnclc1s::int),
            ST_Point(surveygpslong_3035+surveycprnclc1e::int,surveygpslat_3035),
            ST_Point(surveygpslong_3035,surveygpslat_3035+surveycprnlc1n::int),
            ST_Point(surveygpslong_3035-surveycprnclc1w::int,surveygpslat_3035)])),
3035)
WHERE surveycprncando = 'TRUE' 
AND surveycprnlc1n != 'NA'
AND surveycprnlc1n != ''
AND surveycprnclc1s != 'NA'
AND surveycprnclc1s != ''
AND surveycprnclc1w != 'NA'
AND surveycprnclc1w != ''
AND surveycprnclc1e != 'NA'
AND surveycprnclc1e != '';
*/

/* FINAL
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly_gps"
SET geom_poly_3035 = st_setsrid(
    ST_MakePolygon(ST_MakeLine(ARRAY[ST_Point(surveygpslong_3035-surveycprnclc1w::int,surveygpslat_3035), 
            ST_Point(surveygpslong_3035,surveygpslat_3035-surveycprnclc1s::int),
            ST_Point(surveygpslong_3035+surveycprnclc1e::int,surveygpslat_3035),
            ST_Point(surveygpslong_3035,surveygpslat_3035+surveycprnlc1n::int),
            ST_Point(surveygpslong_3035-surveycprnclc1w::int,surveygpslat_3035)])),
3035)
WHERE surveycprncando = 'Yes' 
AND surveycprnlc1n != 'NA'
AND surveycprnlc1n != ''
AND surveycprnclc1s != 'NA'
AND surveycprnclc1s != ''
AND surveycprnclc1w != 'NA'
AND surveycprnclc1w != ''
AND surveycprnclc1e != 'NA'
AND surveycprnclc1e != '';
*/

--OFICIAL ESTAT
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly_gps"
SET geom_poly_3035 = st_setsrid(
    ST_MakePolygon(ST_MakeLine(ARRAY[ST_Point(surveygpslong_3035-surveycprnclc1w::int,surveygpslat_3035), 
            ST_Point(surveygpslong_3035,surveygpslat_3035-surveycprnclc1s::int),
            ST_Point(surveygpslong_3035+surveycprnclc1e::int,surveygpslat_3035),
            ST_Point(surveygpslong_3035,surveygpslat_3035+surveycprnlc1n::int),
            ST_Point(surveygpslong_3035-surveycprnclc1w::int,surveygpslat_3035)])),
3035)
WHERE surveycprncando = 1 
AND surveycprnlc1n is not null
AND surveycprnclc1s is not null
AND surveycprnclc1w is not null
AND surveycprnclc1e is not null;

--add area
ALTER TABLE "LUCAS_Cop2022"."l2022_cop_geompoly_gps" DROP COLUMN poly_area;
ALTER TABLE "LUCAS_Cop2022"."l2022_cop_geompoly_gps" ADD poly_area_sqm DOUBLE PRECISION;
UPDATE "LUCAS_Cop2022"."l2022_cop_geompoly_gps" SET poly_area_sqm = ST_Area("LUCAS_Cop2022"."l2022_cop_geompoly_gps".geom_poly_3035);
