def upgrade():
logger.info("Create temporary departments table…")
op.execute(f"""
CREATE TABLE {schema}.{temp_table_name} (
gid integer NOT NULL,
id character varying(24),
nom_dep character varying(30),
nom_dep_m character varying(30),
insee_dep character varying(3),
insee_reg character varying(2),
chf_dep character varying(5),
geom public.geometry(MultiPolygon,2154),
geojson character varying
)
""")
op.execute(f"""
ALTER TABLE ONLY {schema}.{temp_table_name}
ADD CONSTRAINT {temp_table_name}_pkey PRIMARY KEY (gid)
""")
cursor = op.get_bind().connection.cursor()
with open_remote_file(base_url, filename) as geofile:
logger.info("Inserting departments data in temporary table…")
cursor.copy_expert(f'COPY {schema}.{temp_table_name} FROM STDIN',
geofile)
logger.info("Copy departments data in l_areas…")
op.execute(f"""
INSERT INTO {schema}.l_areas (id_type, area_code, area_name, geom, geojson_4326)
SELECT {schema}.get_id_area_type('DEP') AS id_type, insee_dep, nom_dep, geom, geojson
FROM {schema}.{temp_table_name}
""")
logger.info("Re-indexing…")
op.execute(f'REINDEX INDEX {schema}.index_l_areas_geom')
logger.info("Dropping temporary departments table…")
op.execute(f'DROP TABLE {schema}.{temp_table_name}')
def upgrade():
try:
local_srid = context.get_x_argument(as_dictionary=True)['local-srid']
except KeyError:
raise Exception("Missing local srid, please use -x local-srid=...")
with TemporaryDirectory() as temp_dir:
with open_remote_file(base_url, archive_name,
open_fct=ZipFile) as archive:
archive.extract(file_name, path=temp_dir)
path = os.path.join(temp_dir, file_name)
cmd = f"raster2pgsql -s {local_srid} -c -C -I -M -d -t 5x5 {path} ref_geo.dem | psql"
db_uri = urlsplit(config['SQLALCHEMY_DATABASE_URI'])
env = {
'PGHOST': db_uri.hostname,
'PGPORT': str(db_uri.port),
'PGUSER': db_uri.username,
'PGPASSWORD': db_uri.password,
'PGDATABASE': db_uri.path.lstrip('/'),
}
subprocess.run(cmd,
stdout=subprocess.DEVNULL,
shell=True,
check=True,
env=env)
logger.info("Refresh DEM spatial index…")
op.execute("REINDEX INDEX ref_geo.dem_st_convexhull_idx")
def upgrade():
create_temporary_grids_table(schema, temp_table_name)
cursor = op.get_bind().connection.cursor()
with open_remote_file(base_url, filename) as geofile:
logger.info("Inserting grids data in temporary table…")
cursor.copy_expert(f'COPY {schema}.{temp_table_name} FROM STDIN',
geofile)
insert_grids_and_drop_temporary_table(schema, temp_table_name, area_type)
def upgrade():
recompute_sensitivity = context.get_x_argument(as_dictionary=True).get('recompute-sensitivity')
if recompute_sensitivity is not None:
recompute_sensitivity = bool(strtobool(recompute_sensitivity))
else:
recompute_sensitivity = True
op.execute("""
CREATE OR REPLACE FUNCTION gn_sensitivity.get_id_nomenclature_sensitivity(my_date_obs date, my_cd_ref integer, my_geom geometry, my_criterias jsonb)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $function$
DECLARE
sensitivity integer;
BEGIN
-- Paramètres durée, zone géographique, période de l'observation et critères biologique
SELECT INTO sensitivity r.id_nomenclature_sensitivity
FROM gn_sensitivity.t_sensitivity_rules_cd_ref r
JOIN ref_nomenclatures.t_nomenclatures n ON n.id_nomenclature = r.id_nomenclature_sensitivity
LEFT OUTER JOIN gn_sensitivity.cor_sensitivity_area USING(id_sensitivity)
LEFT OUTER JOIN ref_geo.l_areas a USING(id_area)
LEFT OUTER JOIN gn_sensitivity.cor_sensitivity_criteria c USING(id_sensitivity)
WHERE
( -- taxon
my_cd_ref = r.cd_ref
) AND ( -- zone géographique de validité
a.geom IS NULL -- pas de restriction géographique à la validité de la règle
OR
st_intersects(my_geom, a.geom)
) AND ( -- période de validité
to_char(my_date_obs, 'MMDD') between to_char(r.date_min, 'MMDD') and to_char(r.date_max, 'MMDD')
) AND ( -- durée de validité
(date_part('year', CURRENT_TIMESTAMP) - r.sensitivity_duration) <= date_part('year', my_date_obs)
) AND ( -- critère
c.id_criteria IS NULL -- règle sans restriction de critère
OR
-- Note: no need to check criteria type, as we use id_nomenclature which can not conflict
c.id_criteria IN (SELECT value::int FROM jsonb_each_text(my_criterias))
)
ORDER BY n.cd_nomenclature DESC;
IF sensitivity IS NULL THEN
sensitivity := (SELECT ref_nomenclatures.get_id_nomenclature('SENSIBILITE'::text, '0'::text));
END IF;
return sensitivity;
END;
$function$
""")
if recompute_sensitivity:
logger.info("Recompute sensitivity…")
count = op.get_bind().execute("SELECT gn_synthese.update_sensitivity()").scalar()
logger.info(f"Sensitivity updated for {count} rows")
def create_temporary_grids_table(schema, temp_table_name):
logger.info("Create temporary grids table…")
op.execute(f"""
CREATE TABLE {schema}.{temp_table_name} (
gid integer NOT NULL,
cd_sig character varying(21),
code character varying(10),
geom public.geometry(MultiPolygon,2154),
geojson character varying
)
""")
op.execute(f"""
ALTER TABLE ONLY {schema}.{temp_table_name}
ADD CONSTRAINT {temp_table_name}_pkey PRIMARY KEY (gid)
""")
def upgrade():
cursor = op.get_bind().connection.cursor()
with open_remote_file(base_url, 'HABREF_50.zip',
open_fct=ZipFile) as archive:
for table, filename in table_files.items():
with archive.open(filename) as f:
logger.info(f"Insert INPN data in {table}…")
cursor.copy_expert(
f"""
COPY ref_habitats.{table} FROM STDIN WITH CSV HEADER DELIMITER E';'
""", f)
logger.info("Populate table autocomplete_habitat…")
op.execute("""
INSERT INTO ref_habitats.autocomplete_habitat
SELECT
cd_hab,
h.cd_typo,
lb_code,
lb_nom_typo,
concat(lb_code, ' - ', lb_hab_fr, ' ', lb_hab_fr_complet)
FROM ref_habitats.habref h
JOIN ref_habitats.typoref t ON t.cd_typo = h.cd_typo
""")
def insert_grids_and_drop_temporary_table(schema, temp_table_name, area_type):
logger.info("Copy grids in l_areas…")
op.execute(f"""
INSERT INTO {schema}.l_areas (id_type, area_code, area_name, geom, geojson_4326)
SELECT {schema}.get_id_area_type('{area_type}') AS id_type, cd_sig, code, geom, geojson
FROM {schema}.{temp_table_name}
""")
logger.info("Copy grids in li_grids…")
op.execute(f"""
INSERT INTO {schema}.li_grids(id_grid, id_area, cxmin, cxmax, cymin, cymax)
SELECT
l.area_code,
l.id_area,
ST_XMin(g.geom),
ST_XMax(g.geom),
ST_YMin(g.geom),
ST_YMax(g.geom)
FROM {schema}.{temp_table_name} g
JOIN {schema}.l_areas l ON l.area_code = cd_sig;
""")
logger.info("Re-indexing…")
op.execute(f'REINDEX INDEX {schema}.index_l_areas_geom')
logger.info("Dropping temporary grids table…")
op.execute(f'DROP TABLE {schema}.{temp_table_name}')
def upgrade():
clear_diffusion_level = context.get_x_argument(
as_dictionary=True).get('clear-diffusion-level')
if clear_diffusion_level is not None:
clear_diffusion_level = bool(strtobool(clear_diffusion_level))
else:
clear_diffusion_level = True
op.execute("""
DROP TRIGGER tri_insert_calculate_sensitivity ON gn_synthese.synthese
""")
op.execute("""
DROP TRIGGER tri_update_calculate_sensitivity ON gn_synthese.synthese
""")
op.execute("""
DROP FUNCTION gn_synthese.fct_tri_cal_sensi_diff_level_on_each_statement
""")
op.execute("""
DROP FUNCTION gn_synthese.fct_tri_cal_sensi_diff_level_on_each_row
""")
op.execute("""
CREATE FUNCTION gn_synthese.fct_tri_calculate_sensitivity_on_each_statement()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
-- Calculate sensitivity on insert in synthese
BEGIN
WITH cte AS (
SELECT
id_synthese,
gn_sensitivity.get_id_nomenclature_sensitivity(
new_row.date_min::date,
taxonomie.find_cdref(new_row.cd_nom),
new_row.the_geom_local,
jsonb_build_object(
'STATUT_BIO', new_row.id_nomenclature_bio_status,
'OCC_COMPORTEMENT', new_row.id_nomenclature_behaviour
)
) AS id_nomenclature_sensitivity
FROM
NEW AS new_row
)
UPDATE
gn_synthese.synthese AS s
SET
id_nomenclature_sensitivity = c.id_nomenclature_sensitivity
FROM
cte AS c
WHERE
c.id_synthese = s.id_synthese
;
RETURN NULL;
END;
$function$
;
""")
op.execute("""
CREATE FUNCTION gn_synthese.fct_tri_update_sensitivity_on_each_row()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
-- Calculate sensitivity on update in synthese
BEGIN
NEW.id_nomenclature_sensitivity = gn_sensitivity.get_id_nomenclature_sensitivity(
NEW.date_min::date,
taxonomie.find_cdref(NEW.cd_nom),
NEW.the_geom_local,
jsonb_build_object(
'STATUT_BIO', NEW.id_nomenclature_bio_status,
'OCC_COMPORTEMENT', NEW.id_nomenclature_behaviour
)
);
RETURN NEW;
END;
$function$
;
""")
op.execute("""
CREATE TRIGGER
tri_insert_calculate_sensitivity
AFTER
INSERT
ON
gn_synthese.synthese
REFERENCING
NEW TABLE AS NEW
FOR EACH
STATEMENT
EXECUTE PROCEDURE
gn_synthese.fct_tri_calculate_sensitivity_on_each_statement()
""")
op.execute("""
CREATE TRIGGER
tri_update_calculate_sensitivity
BEFORE UPDATE OF
date_min,
date_max,
cd_nom,
the_geom_local,
id_nomenclature_bio_status,
id_nomenclature_behaviour
ON
gn_synthese.synthese
FOR EACH
ROW
EXECUTE PROCEDURE
gn_synthese.fct_tri_update_sensitivity_on_each_row()
""")
if clear_diffusion_level:
logger.info("Clearing diffusion level…")
count = op.get_bind().execute("""
WITH cleared_rows AS (
UPDATE
gn_synthese.synthese s
SET
id_nomenclature_diffusion_level = NULL
FROM
ref_nomenclatures.t_nomenclatures nomenc_sensitivity,
ref_nomenclatures.t_nomenclatures nomenc_diff_level
WHERE
nomenc_sensitivity.id_nomenclature = s.id_nomenclature_sensitivity
AND nomenc_diff_level.id_nomenclature = s.id_nomenclature_diffusion_level
AND nomenc_diff_level.cd_nomenclature = gn_sensitivity.calculate_cd_diffusion_level(NULL, nomenc_sensitivity.cd_nomenclature)
RETURNING s.id_synthese
)
SELECT
count(*)
FROM
cleared_rows;
""").scalar()
logger.info("Cleared diffusion level on {} rows.".format(count))
def downgrade():
restore_diffusion_level = context.get_x_argument(
as_dictionary=True).get('restore-diffusion-level')
if restore_diffusion_level is not None:
restore_diffusion_level = bool(strtobool(restore_diffusion_level))
else:
restore_diffusion_level = True
if restore_diffusion_level:
logger.info("Restore diffusion level…")
count = op.get_bind().execute("""
WITH restored_rows AS (
UPDATE
gn_synthese.synthese s
SET
id_nomenclature_diffusion_level = ref_nomenclatures.get_id_nomenclature(
'NIV_PRECIS',
gn_sensitivity.calculate_cd_diffusion_level(
NULL,
nomenc_sensitivity.cd_nomenclature
)
)
FROM
ref_nomenclatures.t_nomenclatures nomenc_sensitivity
WHERE
nomenc_sensitivity.id_nomenclature = s.id_nomenclature_sensitivity
AND s.id_nomenclature_diffusion_level IS NULL
RETURNING s.id_synthese
)
SELECT
count(*)
FROM
restored_rows
""").scalar()
logger.info("Restored diffusion level on {} rows.".format(count))
op.execute("""
DROP TRIGGER tri_insert_calculate_sensitivity ON gn_synthese.synthese
""")
op.execute("""
DROP TRIGGER tri_update_calculate_sensitivity ON gn_synthese.synthese
""")
op.execute("""
DROP FUNCTION gn_synthese.fct_tri_calculate_sensitivity_on_each_statement
""")
op.execute("""
DROP FUNCTION gn_synthese.fct_tri_update_sensitivity_on_each_row
""")
op.execute("""
CREATE FUNCTION gn_synthese.fct_tri_cal_sensi_diff_level_on_each_statement()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
-- Calculate sensitivity and diffusion level on insert in synthese
BEGIN
WITH cte AS (
SELECT
gn_sensitivity.get_id_nomenclature_sensitivity(
updated_rows.date_min::date,
taxonomie.find_cdref(updated_rows.cd_nom),
updated_rows.the_geom_local,
('{"STATUT_BIO": ' || updated_rows.id_nomenclature_bio_status::text || '}')::jsonb
) AS id_nomenclature_sensitivity,
id_synthese,
t_diff.cd_nomenclature as cd_nomenclature_diffusion_level
FROM NEW AS updated_rows
LEFT JOIN ref_nomenclatures.t_nomenclatures t_diff ON t_diff.id_nomenclature = updated_rows.id_nomenclature_diffusion_level
WHERE updated_rows.id_nomenclature_sensitivity IS NULL
)
UPDATE gn_synthese.synthese AS s
SET
id_nomenclature_sensitivity = c.id_nomenclature_sensitivity,
id_nomenclature_diffusion_level = ref_nomenclatures.get_id_nomenclature(
'NIV_PRECIS',
gn_sensitivity.calculate_cd_diffusion_level(
c.cd_nomenclature_diffusion_level,
t_sensi.cd_nomenclature
)
)
FROM cte AS c
LEFT JOIN ref_nomenclatures.t_nomenclatures t_sensi ON t_sensi.id_nomenclature = c.id_nomenclature_sensitivity
WHERE c.id_synthese = s.id_synthese
;
RETURN NULL;
END;
$function$
;
""")
op.execute("""
CREATE TRIGGER tri_insert_calculate_sensitivity AFTER
INSERT
ON
gn_synthese.synthese REFERENCING NEW TABLE AS NEW FOR EACH STATEMENT EXECUTE PROCEDURE gn_synthese.fct_tri_cal_sensi_diff_level_on_each_statement()
""")
op.execute("""
CREATE FUNCTION gn_synthese.fct_tri_cal_sensi_diff_level_on_each_row()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
-- Calculate sensitivity and diffusion level on update in synthese
DECLARE calculated_id_sensi integer;
BEGIN
SELECT
gn_sensitivity.get_id_nomenclature_sensitivity(
NEW.date_min::date,
taxonomie.find_cdref(NEW.cd_nom),
NEW.the_geom_local,
('{"STATUT_BIO": ' || NEW.id_nomenclature_bio_status::text || '}')::jsonb
) INTO calculated_id_sensi;
UPDATE gn_synthese.synthese
SET
id_nomenclature_sensitivity = calculated_id_sensi,
-- On ne met pas à jour le niveau de diffusion s'il a déjà une valeur
id_nomenclature_diffusion_level = CASE WHEN OLD.id_nomenclature_diffusion_level IS NULL THEN (
SELECT ref_nomenclatures.get_id_nomenclature(
'NIV_PRECIS',
gn_sensitivity.calculate_cd_diffusion_level(
ref_nomenclatures.get_cd_nomenclature(OLD.id_nomenclature_diffusion_level),
ref_nomenclatures.get_cd_nomenclature(calculated_id_sensi)
)
)
)
ELSE OLD.id_nomenclature_diffusion_level
END
WHERE id_synthese = OLD.id_synthese
;
RETURN NULL;
END;
$function$
;
""")
op.execute("""
CREATE TRIGGER tri_update_calculate_sensitivity AFTER
UPDATE
OF date_min,
date_max,
cd_nom,
the_geom_local,
id_nomenclature_bio_status ON
gn_synthese.synthese FOR EACH ROW EXECUTE PROCEDURE gn_synthese.fct_tri_cal_sensi_diff_level_on_each_row()
""")
def upgrade():
logger.info("Create temporary municipalities table…")
op.execute(f"""
CREATE TABLE {schema}.{temp_table_name} (
gid integer NOT NULL,
id character varying(24),
nom_com character varying(50),
nom_com_m character varying(50),
insee_com character varying(5),
statut character varying(24),
insee_can character varying(2),
insee_arr character varying(2),
insee_dep character varying(3),
insee_reg character varying(2),
code_epci character varying(21),
population bigint,
type character varying(3),
geom public.geometry(MultiPolygon,2154),
geojson character varying
)
""")
op.execute(f"""
ALTER TABLE ONLY {schema}.{temp_table_name}
ADD CONSTRAINT {temp_table_name}_pkey PRIMARY KEY (gid)
""")
cursor = op.get_bind().connection.cursor()
with open_remote_file(base_url, filename) as geofile:
logger.info("Inserting municipalities data in temporary table…")
cursor.copy_expert(f'COPY {schema}.{temp_table_name} FROM STDIN', geofile)
logger.info("Copy municipalities in l_areas…")
op.execute(f"""
INSERT INTO {schema}.l_areas (id_type, area_code, area_name, geom, geojson_4326)
SELECT {schema}.get_id_area_type('COM') AS id_type, insee_com, nom_com, geom, geojson
FROM {schema}.{temp_table_name}
""")
logger.info("Copy municipalities in li_municipalities…")
op.execute(f"""
INSERT INTO ref_geo.li_municipalities
(id_municipality, id_area, status, insee_com, nom_com, insee_arr, insee_dep, insee_reg, code_epci)
SELECT id, a.id_area, statut, insee_com, nom_com, insee_arr, insee_dep, insee_reg, code_epci
FROM ref_geo.temp_fr_municipalities t
JOIN ref_geo.l_areas a ON a.area_code = t.insee_com
""")
logger.info("Re-indexing…")
op.execute(f'REINDEX INDEX {schema}.index_l_areas_geom')
logger.info("Dropping temporary municipalities table…")
op.execute(f'DROP TABLE {schema}.{temp_table_name}')