UPDATE `cel_export_total` SET `latitude` = ST_Y(ST_StartPoint(ST_GeomFromGeoJSON(geometry))), `longitude` =  ST_X(ST_StartPoint(ST_GeomFromGeoJSON(geometry))), `lat_prive` = ST_Y(ST_StartPoint(ST_GeomFromGeoJSON(geometry))), `long_prive` =  ST_X(ST_StartPoint(ST_GeomFromGeoJSON(geometry))) WHERE ST_Dimension(ST_GeomFromGeoJSON(geometry)) = 1 and latitude is null

UPDATE `cel_export` SET `latitude` = ST_Y(ST_StartPoint(ST_GeomFromGeoJSON(geometry))), `longitude` = ST_X(ST_StartPoint(ST_GeomFromGeoJSON(geometry))) WHERE ST_Dimension(ST_GeomFromGeoJSON(geometry)) = 1 and latitude is null

UPDATE `cel_export_total` SET `latitude` = ST_Y(ST_Centroid(ST_GeomFromGeoJSON(geometry))), `longitude` = ST_X(ST_Centroid(ST_GeomFromGeoJSON(geometry))), `lat_prive` = ST_Y(ST_Centroid(ST_GeomFromGeoJSON(geometry))), `long_prive` = ST_X(ST_Centroid(ST_GeomFromGeoJSON(geometry))) WHERE ST_Dimension(ST_GeomFromGeoJSON(geometry)) = 2 and latitude is null

UPDATE `photo` SET `content_url`= concat('/home/test/www/images/', substring(lpad(id, 9, '0'), 1, 3),'/', substring(lpad(id, 9, '0'), 4, 3),'/O/', substring(lpad(id, 9, '0'), 1, 3),'_', substring(lpad(id, 9, '0'), 4, 3),'_', substring(lpad(id, 9, '0'), 7, 3),'_O.', case mime_type when "image/png" then "png" else "jpg" end) WHERE content_url like "/home/test/www/tmp/cel/imgtmp%"

INSERT INTO cel_export_total (guid, id_observation, donnees_standard, ce_utilisateur, pseudo_utilisateur, courriel_utilisateur, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, famille, nom_referentiel,  transmission,
		ce_zone_geo, zone_geo, pays, dept, 
		lieudit, station, milieu, longitude, latitude,altitude, geodatum, geometry, long_prive, lat_prive, localisation_precision, localisation_floutage, localisation_coherence,
		date_observation, programme, commentaire, date_creation, date_modification, abondance, certitude, phenologie,
		spontaneite, observateur, observateur_structure, type_donnees, biblio, source,
		 herbier, determinateur, score_identiplante, validation_identiplante, date_transmission, grade) 
		SELECT 
		concat('urn:lsid:tela-botanica.org:cel:', id), id,NULL, user_id, user_pseudo, user_email, user_sci_name, 
		user_sci_name_id, accepted_sci_name, accepted_sci_name_id, 
		family, taxo_repo, is_public, locality_insee_code, locality, osm_country, substr(`locality_insee_code`,1,2), sublocality, station, environment, 
		NULL, NULL, elevation, geodatum, NULL, ST_X(ST_GeomFromGeoJSON(geometry)), ST_Y(ST_GeomFromGeoJSON(geometry)), location_accuracy, published_location, 
		locality_consistency,
		date_observed, project, annotation, date_created, date_updated, coef, certainty, phenology,
		is_wild, observer, observer_institution, occurrence_type, bibliography_source, input_source,
		sample_herbarium, identification_author, identiplante_score, is_identiplante_validated, date_published, NULL FROM occurrence

UPDATE `cel_export_total` cet join occurrence o on `id_observation` = id SET  latitude =	floor(ST_Y(ST_GeomFromGeoJSON(o.geometry))*10)/10,
		longitude = floor(ST_X(ST_GeomFromGeoJSON(o.geometry))*10)/10,
		cet.geometry = NULL where  `localisation_floutage` = "10x10km"

UPDATE `cel_export_total` cet join cel_zones_geo o on `code` = ce_zone_geo SET  latitude =wgs84_latitude,
		longitude =wgs84_longitude,
		cet.geometry = NULL where  `localisation_floutage` = "localité"

UPDATE `cel_export_total` cet join occurrence o on `id_observation` = id SET  latitude =	ST_Y(ST_GeomFromGeoJSON(o.geometry)),
		longitude = ST_X(ST_GeomFromGeoJSON(o.geometry)),
		cet.geometry =  o.geometry where  `localisation_floutage` = "précise" or localisation_floutage IS NULL

UPDATE `cel_export_total` RIGHT JOIN (SELECT `occurrence_id`, group_concat(`url` SEPARATOR ', ') as photos FROM `photo` WHERE `occurrence_id` IS NOT NULL group by `occurrence_id`) a on `id_observation` = occurrence_id SET `url_identiplante`= concat("https://www.tela-botanica.org/appli:identiplante?#obs~", occurrence_id) , `images`= photos

UPDATE `cel_export_total` right join (SELECT occurrence_id, group_concat(name) grp from (SELECT occurrence_id, user_occurrence_tag_id, name FROM `user_occurrence_tag` t join occurrence_user_occurrence_tag o on t.`id` = user_occurrence_tag_id) mcobs group by occurrence_id) mc on `id_observation` = occurrence_id SET `mots_cles_texte` = mc.grp

UPDATE `cel_export_total` cet join tb_eflore.bdtfx_v6_00 bd on `nom_sel_nn` = `num_nom` SET cet.`cd_nom`= if (bd.`cd_nom` = '', NULL, bd.`cd_nom`)
 WHERE `nom_referentiel` = "bdtfx"

UPDATE `cel_export_total` SET grade = 1

UPDATE `cel_export_total` SET grade =  2 where transmission = "1" AND 
(date_observation IS NOT NULL) AND 
(nom_sel != "" AND nom_sel NOT LIKE "?" AND nom_sel IS NOT NULL)  AND courriel_utilisateur REGEXP "^.*@.*$"  
AND ((`geometry` != "" AND `geometry` IS NOT NULL) OR (zone_geo != "" AND zone_geo is not NULL)) AND (localisation_coherence = 1 OR localisation_coherence IS NULL) 

UPDATE `cel_export_total` SET grade =  3 where grade = 2 and (certitude = "certain" OR certitude is NULL) AND (score_identiplante >= 0 OR score_identiplante IS NULL)

UPDATE `cel_export_total` SET grade =  4 where grade = 3 and (validation_identiplante = 1 OR score_identiplante > 10)

UPDATE `cel_export_total` SET `donnees_standard` = 1 where grade >= 3

UPDATE `cel_export_total` SET `donnees_standard` = 0 where grade < 3

INSERT INTO `cel_export`(`guid`, `id_observation`, `ce_utilisateur`, `pseudo_utilisateur`, `courriel_utilisateur`, `nom_sel`, `nom_sel_nn`, `nom_ret`, `nom_ret_nn`, `famille`, `nom_referentiel`, `pays`, `ce_zone_geo`, `dept`, `zone_geo`, `lieudit`, `station`, `milieu`, `latitude`, `longitude`, `altitude`, `geodatum`, `geometry`, `localisation_precision`, `localisation_floutage`, `localisation_coherence`, `date_observation`, `programme`, `mots_cles_texte`, `commentaire`, `date_creation`, `date_modification`, `date_transmission`, `abondance`, `certitude`, `phenologie`, `spontaneite`, `observateur`, `observateur_structure`, `type_donnees`, `biblio`, `source`, `herbier`, `determinateur`, `url_identiplante`, `validation_identiplante`, `date_validation`, `score_identiplante`, `images`, `grade`) SELECT `guid`, `id_observation`, `ce_utilisateur`, `pseudo_utilisateur`, `courriel_utilisateur`, `nom_sel`, `nom_sel_nn`, `nom_ret`, `nom_ret_nn`, `famille`, `nom_referentiel`, `pays`, `ce_zone_geo`, `dept`, `zone_geo`, `lieudit`, `station`, `milieu`, `latitude`, `longitude`, `altitude`, `geodatum`, `geometry`, `localisation_precision`, `localisation_floutage`, `localisation_coherence`, `date_observation`, `programme`, `mots_cles_texte`, `commentaire`, `date_creation`, `date_modification`, `date_transmission`, `abondance`, `certitude`, `phenologie`, `spontaneite`, `observateur`, `observateur_structure`, `type_donnees`, `biblio`, `source`, `herbier`, `determinateur`, `url_identiplante`, `validation_identiplante`, `date_validation`, `score_identiplante`, `images`, `grade` FROM `cel_export_total` WHERE `donnees_standard` = 1

chprod
cd www/scripts/cel/
php cli.php nettoyage -a tout

php cli.php nettoyage -a analyserMotsCles

/usr/local/bin/php-cli-54 -d memory_limit=4000M cli.php nettoyage -a tout

CREATE DATABASE tb_new_cel;
USE tb_new_cel;
source chemin/tb_new_cel.sql

cd www/scripts/cel/
php cli.php migrat2019 -a