ValNamesGeo_ValNombresGeo.txt

--------------------------------------------------------------------------------
---Do not copy /No Copiar
--EN

SiB Colombia scripts for Biodiversity data validation and cleaning in OPEN REFINE
https://github.com/SIB-Colombia/data-quality-open-refine

Script Name: Gegraphic name's validation according to DIVIPOLA 
Language: GREL 'General Refine Expression Language'
Created:2018-03-21
Last Update:2021-07-16
Contributors: Camila Plata, Ricardo Ortiz 

This script:
1-Creates concatenated columns of geographic names to check geographic hierarchy
2-Match single and concatenated columns with DIVIPOLA names
3-Returns validation columns using a boolean descriptor (1,0)

			Conventions boolean descriptor
			0-The geographic name DOES NOT match with any name at DIVIPOLA
			1-The geographic name matches with DIVIPOLA

Conditions
Dataset with DwC elements 'stateProvince'(minimum),'county','municipality'
DIVIPOLA archive uploaded in openRefine with named 'DIVIPOLA_20200311', the latest version can be found at the GitHuB repository

New data will be stored in columns at the beginning of the dataset
Check the data when the validation columns are mark with '0', this data needs to be checked and fixed

Conventions
spc  = stateProvince+County
spcm = stateProvince+County+Municipality
spValidation    = boolean descriptor, 1 if the stateProvince matches Divipola, 0 if not 
spcValidation   = boolean descriptor, 1 if the combination of stateProvince+County matches Divipola, 0 if not 
spcmMValidation = boolean descriptor, 1 if the combination of stateProvince+County+Municipality matches Divipola, 0 if not 

Warnings
The first time before running the script, upload to open refine the dataset and the divipola archive and restart openRefine, then run the script


--ES

Rutinas del SiB Colombia para la validación y limpieza de datos primarios 
de Biodiversidad en OPEN REFINE
https://github.com/SIB-Colombia/data-quality-open-refine

Nombre rutina: Validación de entidades geográficas de acuerdo a la División político administrativa oficial de Colombia (DIVIPOLA)
Languaje: GREL 'General Refine Expression Language'
Creado:2018-03-21
Última Actualización:2021-07-16
Autores:Camila Plata, Ricardo Ortiz 

Esta rutina:
1-Crea columnas con las entidades geográficas concatenadas para validar la jerarquía geográfica
2-Compara las columnas creadas con los nombres en DIVIPOLA 
3-Genera columnas de validación con descriptores booleanos (1,0)

			Convenciones descriptores booleanos
			0-El nombre geográfico NO coincide con ningún nombre en DIVIPOLA
			1-El nombre geográfico coincide con DIVIPOLA

Requerimientos:
Conjunto de datos con los elementos DwC 'stateProvince'(mínimo),'county','municipality'
Archivo DIVIPOLA cargado en openRefine con nombre 'DIVIPOLA_20200311', la última versión esta disponible en el repositorio de GitHuB

Los nuevos datos seran guardados en columnas el inicio del conjunto de datos
Revise los datos cuando las columnas de validación este marcadas con '0', estos datos necesitan ser revisados y ajustados

Convenciones
spc  = stateProvince+County
spcm = stateProvince+County+Municipality
spValidation    = descriptor booleano , 1 si stateProvince (Departamento) coincide con Divipola, 0 si no coincide
spcValidation   = descriptor booleano , 1 si la combinación stateProvince+County (departamento+municipio) coincide con Divipola, 0 si no coincide
spcmMValidation = descriptor booleano , 1 si la combinación stateProvince+County+Municipality (departamento+municipio+centroPoblado) coincide con Divipola, 0 si no coincide

Advertencias
Si está usando el script por primera vez se recomienda cargar el conjunto de datos y el archivo de divipola, y reiniciar openRefine antes de correr el script

---Do not copy /No Copiar
--------------------------------------------------------------------------------


{
    "op": "core/column-addition",
    "description": "Create column spcm at index 1 based on column stateProvince using expression grel:(cells['stateProvince'].value+' '+cells['county'].value+' '+cells['municipality'].value).trim().replace(/\\\\s+/,' ')",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spcm",
    "columnInsertIndex": 1,
    "baseColumnName": "stateProvince",
    "expression": "grel:(cells['stateProvince'].value+' '+cells['county'].value+' '+cells['municipality'].value).trim().replace(/\\\\s+/,' ')",
    "onError": "set-to-blank"
  },
  {
    "op": "core/column-addition",
    "description": "Create column spcmMatch at index 1 based on column spcm using expression grel:cell.cross('DIVIPOLA_20210416','SPCM')[0].cells['SPCM'].value",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spcmSuggested",
    "columnInsertIndex": 1,
    "baseColumnName": "spcm",
    "expression": "grel:cell.cross('DIVIPOLA_20210416','SPCM')[0].cells['SPCM'].value",
    "onError": "set-to-blank"
  },
 {
    "op": "core/column-addition",
    "description": "Create column spcmValidation at index 1 based on column kingdom using expression grel:if(value==cells[\"spcmSuggested\"].value,1,0)\"",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spcmValidation",
    "columnInsertIndex": 1,
    "baseColumnName": "spcm",
    "expression": "grel:if(value==cells[\"spcmSuggested\"].value,1,0).toString()\"",
    "onError": "set-to-blank"
  },
  {
    "op": "core/column-move",
    "description": "Move column genus to position 1",
    "columnName": "spcm",
    "index": 1
  },
  {
    "op": "core/column-addition",
    "description": "Create column spc at index 1 based on column stateProvince using expression grel:(cells['stateProvince'].value+' '+cells['county'].value).trim().replace(/\\\\s+/,' ')",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spc",
    "columnInsertIndex": 1,
    "baseColumnName": "stateProvince",
    "expression": "grel:(cells['stateProvince'].value+' '+cells['county'].value).trim().replace(/\\\\s+/,' ')",
    "onError": "set-to-blank"
  },
 {
    "op": "core/column-addition",
    "description": "Create column spcSuggested at index 1 based on column spc using expression grel:cell.cross('DIVIPOLA_20210416','SPC')[0].cells['SPC'].value",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spcSuggested",
    "columnInsertIndex": 1,
    "baseColumnName": "spc",
    "expression": "grel:cell.cross('DIVIPOLA_20210416','SPC')[0].cells['SPC'].value",
    "onError": "set-to-blank"
  },
  {
    "op": "core/column-addition",
    "description": "Create column spcValidation at index 1 based on column kingdom using expression grel:if(value==cells[\"spcSuggested\"].value,1,0)\"",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spcValidation",
    "columnInsertIndex": 1,
    "baseColumnName": "spc",
    "expression": "grel:if(value==cells[\"spcSuggested\"].value,1,0).toString()\"",
    "onError": "set-to-blank"
  },
   {
    "op": "core/column-move",
    "description": "Move column to position 1",
    "columnName": "spc",
    "index": 1
  },
  {
    "op": "core/column-addition",
    "description": "Create column spMatch at index 1 based on column stateProvince using expression grel:cell.cross('DIVIPOLA_20210416','stateProvince')[0].cells['stateProvince'].value",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spSuggested",
    "columnInsertIndex": 1,
    "baseColumnName": "stateProvince",
    "expression": "grel:cell.cross('DIVIPOLA_20210416','stateProvince')[0].cells['stateProvince'].value",
    "onError": "set-to-blank"
  },
  {
    "op": "core/column-addition",
    "description": "Create column spValidation at index 1 based on column kingdom using expression grel:if(value==cells[\"spSuggested\"].value,1,0)\"",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "spValidation",
    "columnInsertIndex": 1,
    "baseColumnName": "stateProvince",
    "expression": "grel:if(value==cells[\"spSuggested\"].value,1,0).toString()\"",
    "onError": "set-to-blank"
  },
   {
    "op": "core/column-move",
    "description": "Move column municipality to position 1",
    "columnName": "municipality",
    "index": 1
  },
   {
    "op": "core/column-move",
    "description": "Move column county to position 1",
    "columnName": "county",
    "index": 1
  },
   {
    "op": "core/column-move",
    "description": "Move column stateProvince to position 1",
    "columnName": "stateProvince",
    "index": 1
  },
  {
    "op": "core/column-removal",
    "description": "Remove column spSuggested",
    "columnName": "spSuggested"
  },
  {
    "op": "core/column-removal",
    "description": "Remove column spcSuggested",
    "columnName": "spcSuggested"
  },
  {
    "op": "core/column-removal",
    "description": "Remove column spmSuggested",
    "columnName": "spcmSuggested"
  }