gis Database

Parameters

Name	Type	Mode

Definition

BEGIN
	UPDATE imports.import_segment
	SET id_segment = (SELECT veloroutes.insert_import_veloroutes_segment(id_import))
	WHERE avancement IS NOT null
	AND statut IS NOT null
	-- check that enumerate types are correct
	AND (EXISTS (SELECT 1 FROM veloroutes.etat_avancement_val WHERE CAST (code AS text) = avancement)
		 OR EXISTS (SELECT 1 FROM veloroutes.etat_avancement_val WHERE UPPER(libelle) = UPPER(avancement)))
	AND (EXISTS (SELECT 1 FROM veloroutes.statut_segment_val WHERE UPPER(code) = UPPER(statut))
		 OR EXISTS (SELECT 1 FROM veloroutes.statut_segment_val WHERE UPPER(libelle) = UPPER(statut)))
	AND (EXISTS (SELECT 1 FROM veloroutes.revetement_val WHERE UPPER(code) = UPPER(revetement))
		 OR EXISTS (SELECT 1 FROM veloroutes.revetement_val WHERE UPPER(libelle) = UPPER(revetement))
		 OR revetement IS NULL)
	AND (EXISTS (SELECT 1 FROM veloroutes.booleen_val WHERE UPPER(code) = UPPER(sens_unique))
		 OR EXISTS (SELECT 1 FROM veloroutes.booleen_val WHERE UPPER(libelle) = UPPER(sens_unique))
		 OR sens_unique IS NULL)
	AND (EXISTS (SELECT 1 FROM veloroutes.booleen_val WHERE UPPER(code) = UPPER(geometrie_fictive))
		 OR EXISTS (SELECT 1 FROM veloroutes.booleen_val WHERE UPPER(libelle) = UPPER(geometrie_fictive))
		 OR geometrie_fictive IS NULL);

RAISE NOTICE 'Les lignes correctes de segment ont été importées dans veloroutes';

--mise à jour de element avec les id_segment de veloroutes
	IF EXISTS (
		SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'imports'
        AND table_name = 'import_element')
	THEN
	UPDATE imports.import_element
	SET id_segment =(
		SELECT CAST(iis.id_segment AS integer)
		FROM imports.import_segment as iis
		WHERE iis.id_import = imports.import_element.id_segment);

RAISE NOTICE 'La table element a été mise à jour dans le schéma d import';

INSERT INTO veloroutes.element(id_portion, id_segment)
		SELECT id_portion, id_segment
		FROM imports.import_element as iel
		WHERE iel.id_portion IS NOT NULL
		AND iel.id_segment IS NOT NULL;

RAISE NOTICE 'La table element a été insérée dans véloroutes';

END IF;

RETURN 1;
END