INSERT INTO gn_sensitivity.zonages_sensibilite (geom, cd_nom, cd_tax_lr, nom_vernac, nom_latin, source, date_enr, lib_zone) VALUES ('[encoded data, 83192 characters]', 3811, 42739, 'Pie-griêche à poitrine rose', 'Lanius minor', 'Zonage PNA du domaine vital mis à jour en octobre 2015 (DREAL LR)', '2015-10-27', 'Zonage PNA du domaine vital');
INSERT INTO gn_sensitivity.zonages_sensibilite (geom, cd_nom, cd_tax_lr, nom_vernac, nom_latin, source, date_enr, lib_zone) VALUES ('[encoded data, 16756 characters]', 2508, 42570, 'Héron pourpré', 'Ardea purpurea', 'Zonage transmis le 22/10/2015 par Méridionalis', NULL, 'La ganguisse');
INSERT INTO gn_sensitivity.zonages_sensibilite (geom, cd_nom, cd_tax_lr, nom_vernac, nom_latin, source, date_enr, lib_zone) VALUES ('01060000206A080000040000000103000000010000001F000000FD38D2AD146E2841C69789E2BAF75741291B5C524F75284138CF30DDF7F75741C2BE6BAE997F284186D3BE043BF8574106F461E1479C2841EB155211E9F8574127991D19E0B72841707C3264B3F957418015DFFF7ECB2841F373C52DF1F85741B005D8EBE7E82841AE63E68A1EF95741B895CA8417EA2841C03A167EA8F55741108FA80FC5EE28413B0A1F9DBDF357410511D54CAFE02841ED42D5BC55F2574168E7A3ADBAD62841D38766CF91F05741FE46E89E33CA28413460566E92EF57410020AA9952C52841D8967AF589EE57419BD5FEEBA0BD2841AE25A030E4ED5741C4C2FE3289B328412402C81354ED57410B3D5680D3A8284109D7ACFE56ED574135B21F57279B28419D19766DC6EE57410283BFC2CD912841325170D47EEF5741B551E75127882841A65BC0C707F0574192599EB8048628410283833CBFEF5741D4B4A004928028417D5FDC7901F057416F598B28397A28417D72A03AD5EE57411FB8B989FA6E284123972DF390EF57417C88331FCC662841A981617023EF5741F36BB85678562841C48F9393B4F05741C7F616CD33522841F1239643D4F057417A0E070A504B28412885CDB519F1574112C7C3A8B8472841F31DDF4AEFF25741A50F05BDC24D2841CCC51F44F3F4574196F6E59E255D28419BB46DCC37F65741FD38D2AD146E2841C69789E2BAF75741010300000001000000150000007BB37423F6252841B17D56BFE8F75741F89587B36F292841DBBC961B92F75741F2DBB199E325284128458E8939F557412063FFADEE242841686B965A69F257412481F6BC0D212841A78CCE949FF15741F7C4D30EE41828416A48170683F05741B7592B4D951028413822A6CF7AF057414888F00E5103284188F7820BDAEF5741EE8FF4FF2CE5274194CF050096EE574192998FFF31D42741D620EEBAF9ED574102A141291DCA2741BE9B591197ED574190E73DE16EC42741652B56989FED5741A948EBADF5C0274161C1A6F9F7ED5741212614663BBB2741B47311D7EAEE5741B215DDCC55B82741AB1E413BB8EF57414DBCE05BECB32741A2B1EE72BAF05741A5F7D77A3EDA2741647475435FF35741F3C60EF603FA27415F4D876483F45741C6C190FFB80A28412D334E90EAF55741B875B5B2EC12284172B7A1F999F757417BB37423F6252841B17D56BFE8F75741010300000001000000190000007C4030CD57262741D1E56BB4FFDF57415831FF1ECA1E274117522F15A8DF5741FD9EAF5CD11A27415A99BE471DDF5741F663977FE80D274146C238D1E8DE5741ED654680D60127416CFFA0412FDE574121AD80006FFE2641350F248B54DC5741CB4B445269EA2641F988F06162DA574199CA91194BCD2641AF67AC1774D65741020AD7946CBB26415A39B26E03D45741B75E6594D5AC2641797A7BE498D157418142177BE49E2641D671F3894BD25741B78CDAE07996264198FC6B7EE3D3574101C1B309789726415F61ACD8A2D55741EB38B12899972641B967C0678CD75741BA187A0AE89C2641830A4BC978D857417580071AFCA62641CA137BFBD0D85741B95A22805EB9264153298B4267DC5741DFEA1D1FEECD2641E190C8FBD9DF57419018049AA1DE264168C534322CE157418A3867949BF826417B5D3C92E7E35741D8A6445C51072741AB1FCD63DFE457412C89CF84441027415C8E72FDB2E457414A14420F8419274117F626F379E257410232F332A42127416C7B531FC3E157417C4030CD57262741D1E56BB4FFDF57410103000000010000001D0000007B7FFB0926822741631E5E068BEC574158B80B80D68527411C429A11C1EB574117D7F17A0A84274194826DDF3EEB5741D4F27CAE2F84274197EF6BCFD3EA5741469B2DD7AD892741C6EC41CAFDE95741A8001BBDB88827416780DA9BC9E957412120F0A301892741F342AAF7A1E95741976D1F0FEB8A2741E192267377E957413FB1908FB98A27411480E8FB33E957416D627770B48C274170BD160FC5E857418474F5B72B7E27413735275B52E7574192108142AD6927414572D7EADEE45741AB3BE9FA07642741888E46ABCFE35741C3667FD1505B2741B0FFC6CC9AE2574186C53038F25227417DBAC244A8E15741106A6A38CB4C27416C8AA28A2FE157412BE04C75153F27410E6A6F6576E05741BFACDB8E3F3E2741FC812A8309E15741CEFE1DB3583E2741F002642D7CE257418629D4894B40274114D32F62D2E3574149ACD94CED4327415C2169FFCFE5574150D850E1D24527410A81595962E657413DC32099D45027417E423CA288E75741CD7E0DDCFD5B2741006FC93B5AE9574118EF92F0746727417081D54DCDEA57410DF3D8FF8B682741A15DD2335FEC574173187BA9CA6F2741011242A3D7EC57413AB08D8F207B2741D74DA4A864EC57417B7FFB0926822741631E5E068BEC5741', 3342, 42930, 'Sterne Caugek', 'Sterna sandvicensis', 'Proposition de Patrice Cramm le 29/09/2015 d''après les contours d''unités écologiques diffusés dans le cadre du programme de suivi larolimicoles.', '2015-10-15', 'Petite Camargue fluvio-lacustre, Etang de l''Or, Etangs de Thau et du Bagnas, Etangs palavasiens ouest');
INSERT INTO gn_sensitivity.zonages_sensibilite (geom, cd_nom, cd_tax_lr, nom_vernac, nom_latin, source, date_enr, lib_zone) VALUES ('01060000206A08000003000000010300000001000000150000007BB37423F6252841B17D56BFE8F75741F89587B36F292841DBBC961B92F75741F2DBB199E325284128458E8939F557412063FFADEE242841686B965A69F257412481F6BC0D212841A78CCE949FF15741F7C4D30EE41828416A48170683F05741B7592B4D951028413822A6CF7AF057414888F00E5103284188F7820BDAEF5741EE8FF4FF2CE5274194CF050096EE574192998FFF31D42741D620EEBAF9ED574102A141291DCA2741BE9B591197ED574190E73DE16EC42741652B56989FED5741A948EBADF5C0274161C1A6F9F7ED5741212614663BBB2741B47311D7EAEE5741B215DDCC55B82741AB1E413BB8EF57414DBCE05BECB32741A2B1EE72BAF05741A5F7D77A3EDA2741647475435FF35741F3C60EF603FA27415F4D876483F45741C6C190FFB80A28412D334E90EAF55741B875B5B2EC12284172B7A1F999F757417BB37423F6252841B17D56BFE8F757410103000000010000001D0000007B7FFB0926822741631E5E068BEC574158B80B80D68527411C429A11C1EB574117D7F17A0A84274194826DDF3EEB5741D4F27CAE2F84274197EF6BCFD3EA5741469B2DD7AD892741C6EC41CAFDE95741A8001BBDB88827416780DA9BC9E957412120F0A301892741F342AAF7A1E95741976D1F0FEB8A2741E192267377E957413FB1908FB98A27411480E8FB33E957416D627770B48C274170BD160FC5E857418474F5B72B7E27413735275B52E7574192108142AD6927414572D7EADEE45741AB3BE9FA07642741888E46ABCFE35741C3667FD1505B2741B0FFC6CC9AE2574186C53038F25227417DBAC244A8E15741106A6A38CB4C27416C8AA28A2FE157412BE04C75153F27410E6A6F6576E05741BFACDB8E3F3E2741FC812A8309E15741CEFE1DB3583E2741F002642D7CE257418629D4894B40274114D32F62D2E3574149ACD94CED4327415C2169FFCFE5574150D850E1D24527410A81595962E657413DC32099D45027417E423CA288E75741CD7E0DDCFD5B2741006FC93B5AE9574118EF92F0746727417081D54DCDEA57410DF3D8FF8B682741A15DD2335FEC574173187BA9CA6F2741011242A3D7EC57413AB08D8F207B2741D74DA4A864EC57417B7FFB0926822741631E5E068BEC5741010300000001000000310000003F7461A45D472841EE8FBFCDE9F057415901938AD54B2841105DD0D421F05741C7F616CD33522841F1239643D4F057417A0E070A504B28412885CDB519F1574112C7C3A8B8472841F31DDF4AEFF25741A50F05BDC24D2841CCC51F44F3F4574196F6E59E255D28419BB46DCC37F65741FD38D2AD146E2841C69789E2BAF75741291B5C524F75284138CF30DDF7F75741C2BE6BAE997F284186D3BE043BF8574106F461E1479C2841EB155211E9F8574127991D19E0B72841707C3264B3F957418015DFFF7ECB2841F373C52DF1F85741B005D8EBE7E82841AE63E68A1EF95741B895CA8417EA2841C03A167EA8F55741108FA80FC5EE28413B0A1F9DBDF357410511D54CAFE02841ED42D5BC55F2574168E7A3ADBAD62841D38766CF91F05741FE46E89E33CA28413460566E92EF57410020AA9952C52841D8967AF589EE57419BD5FEEBA0BD2841AE25A030E4ED5741C4C2FE3289B328412402C81354ED57410B3D5680D3A8284109D7ACFE56ED574135B21F57279B28419D19766DC6EE57410283BFC2CD912841325170D47EEF5741B551E75127882841A65BC0C707F0574192599EB8048628410283833CBFEF5741D4B4A004928028417D5FDC7901F057416F598B28397A28417D72A03AD5EE57411FB8B989FA6E284123972DF390EF57417C88331FCC662841A981617023EF57413928A10AC26928412ED728B4DAEE5741281CE3DBD36C2841A526ED29BAED574123EB68331E702841FD2009DF84EC574196A5438A9C7B2841A516BF9CC2EA574181208561557B28411ED14D6A8BE95741506BF604466C28412F8948A09FE857416EA26414946C28413EEE2E02ECE75741B9E11276FE662841629BC5554EE65741DD49CD0F14672841EE7F9BB856E45741C24A28CD30612841F7876F4789E457416BA67FF0CE3F284168C870356BE5574197F34AC2E8252841376B9FEA0EE75741B99B0B6B652228417D2B56410AEA57414CE43570F8292841468243C822EC5741D653D34C872A2841595C7A44E9ED5741D7A77033B6262841988A36F4A2EF5741368B7333FD2D2841A3DA0FC795F057413F7461A45D472841EE8FBFCDE9F05741', 3332, 43356, 'Sterne Hansel', 'Recurvirostra avosetta', 'Proposition de Patrice Cramm le 29/09/2015 d''après les contours d''unités écologiques diffusés dans le cadre du programme de suivi larolimicoles.', '2015-10-15', 'Etang de l''Or, Etangs palavasiens ouest, Petite Camargue laguno_marine, Petite Camargue fluvio-lacustre');
INSERT INTO gn_sensitivity.zonages_sensibilite (geom, cd_nom, cd_tax_lr, nom_vernac, nom_latin, source, date_enr, lib_zone) VALUES ('[encoded data, 7172 characters]', 3343, 42927, 'Sterne Pierregarin', 'Sterna hirundo', 'Proposition de Patrice Cramm le 29/09/2015 d''après les contours d''unités écologiques diffusés dans le cadre du programme de suivi larolimicoles.', NULL, 'Petite Camargue fluvio-lacustre, Petite Camargue laguno-marine, Etang de l’Or, Etangs palavasiens Est, Etangs palavasiens Ouest, Etang de Thau et du Bagnas, Basses Plaines de l’Aude, Etangs du Narbonnais, Etang de Lapalme, Etang de Leucate et Bourdig');
INSERT INTO gn_sensitivity.zonages_sensibilite (geom, cd_nom, cd_tax_lr, nom_vernac, nom_latin, source, date_enr, lib_zone) VALUES ('[encoded data, 4918 characters]', 3272, 42755, 'Mouette mélanocéphale', 'Larus melanocephalus', 'Proposition de Patrice Cramm le 29/09/2015 d''après les contours d''unités écologiques diffusés dans le cadre du programme de suivi larolimicoles.', '2015-10-15', 'Petite Camargue fluvio-lacustre, Etang de l''Or, Etangs palavasiens ouest, Etangs palavasiens est, Etangs de Thau et du Bagnas, Basses plaines de l''Aude, Etangs du Narbonnais');
INSERT INTO gn_sensitivity.zonages_sensibilite (geom, cd_nom, cd_tax_lr, nom_vernac, nom_latin, source, date_enr, lib_zone) VALUES ('[encoded data, 7326 characters]', 3352, 43359, 'Sterne naine', 'Sternula albifrons', 'Proposition de Patrice Cramm le 29/09/2015 d''après les contours d''unités écologiques diffusés dans le cadre du programme de suivi larolimicoles.', '2015-10-15', 'Petite Camargue fluvio-lacustre, Petite Camargue laguno-marine, Etang de l’Or, Etangs palavasiens Est, Etangs palavasiens Ouest, Etang de Thau et du Bagnas, Basses Plaines de l’Aude, Etangs du Narbonnais, Etang de Lapalme, Etang de Leucate et Bourdig');


INSERT INTO ref_geo.bib_areas_types(type_name, type_code, type_desc, ref_name)
    VALUES ('Zone sensibilité', 'SENSITIVITY', 'Zone de sensibilité particulière défini par le sipn', 'sensibilité SIPN');
    
INSERT INTO ref_geo.l_areas(id_type, area_name, area_code, geom, centroid, source, comment)
SELECT DISTINCT type.id_type, lib_zone, 'ZONE_SENSIBILITE_' || cd_nom as area_code, z.geom, st_centroid(z.geom) as centroid, source, 
    CONCAT('cd_nom : ' || cd_nom, ' nom_vernac: '|| nom_vernac,' nom_latin: '|| nom_latin) as comment
FROM gn_sensitivity.zonages_sensibilite z, 
(SELECT id_type FROM ref_geo.bib_areas_types WHERE type_code='SENSITIVITY') type;


-- Import des données LR avec des périmètres spécifiques
DELETE FROM gn_sensitivity.cor_sensitivity_area
WHERE id_sensitivity IN (
SELECT DISTINCT id_sensitivity
FROM ref_geo.l_areas
JOIN   gn_sensitivity.t_sensitivity_rules l
ON taxonomie.find_cdref(REPLACE(area_code, 'ZONE_SENSIBILITE_','')::int) = taxonomie.find_cdref(l.cd_nom)
WHERE  id_type = (SELECT id_type FROM ref_geo.bib_areas_types  WHERE type_code ='SENSITIVITY')
AND  id_territory = 'INSEER91'
);

INSERT INTO gn_sensitivity.cor_sensitivity_area
SELECT DISTINCT id_sensitivity, id_area
FROM ref_geo.l_areas
JOIN   gn_sensitivity.t_sensitivity_rules l
ON taxonomie.find_cdref(REPLACE(area_code, 'ZONE_SENSIBILITE_','')::int) = taxonomie.find_cdref(l.cd_nom)
WHERE  id_type = (SELECT id_type FROM ref_geo.bib_areas_types  WHERE type_code ='SENSITIVITY')
AND  id_territory = 'INSEER91';



-- Activation règles LR

UPDATE  gn_sensitivity.t_sensitivity_rules SET active = TRUE 
WHERE id_territory='INSEER91';

UPDATE  gn_sensitivity.t_sensitivity_rules SET active = TRUE 
WHERE id_territory='INSEED48';

UPDATE  gn_sensitivity.t_sensitivity_rules SET active = TRUE 
WHERE id_territory='INSEED30';

UPDATE  gn_sensitivity.t_sensitivity_rules SET active = TRUE 
WHERE id_territory='INSEED12';
UPDATE  gn_sensitivity.t_sensitivity_rules SET active = TRUE 
WHERE id_territory='INSEED34';



--Désactivation des règles nationales si une règle régionale est définie
UPDATE gn_sensitivity.t_sensitivity_rules SET active = false
WHERE  id_territory='TERFXFR' AND  taxonomie.find_cdref(cd_nom) IN (
	SELECT taxonomie.find_cdref(cd_nom)
	FROM gn_sensitivity.t_sensitivity_rules
	WHERE active = true
	AND NOT id_territory='TERFXFR' 
);


DROP TABLE gn_sensitivity.zonages_sensibilite;
