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}')