2022_02 Actualización a DIVIPOLA 2021

Si ha desarrollado un motor o una aplicación sobre sip, para realizar esta actualización en la base de datos de su aplicación basta que actualice gemas y que corra migraciones:

bundle update; bundle
bin/rails db:migrate

En este documento, describimos a nivel técnico como se realizó la actualización del DIVIPOLA 2020 al DIVIPOLA 2021 en el motor sip --motor para Ruby on Rails que facilita el desarrollo de sistemas de información.

Si no está interesado en los detalles técnicos sino en los cambios del DIVIPOLA 2020 a DIVIPOLA 2021 puede consultar un resumen ejecutivo

En sip la actualización del DIVIPOLA se concreta en:

1. Una migración que hace las modificaciones a la base de datos de aplicaciones y motores existentes basados en sip:https://github.com/pasosdeJesus/sip/blob/main/db/migrate/20220213031520_divipola_2021.rb
2. Cambio a la semilla con datos básicos que puede verse en esta contribución.

La edición 2021 del DIVIPOLA respecto al 2020 tuvo pocos cambios e incluye un error.

1. Fuente de datos

Dado que el geoportal DANE ha estuvo fuera de servicio entre Nov.2021 y Ene.2022, comenzamos con un DIVIPOLA de Diciembre de 2021 suministrado por el DANE en respuesta a derecho de petición de Diciembre 2021 (ver https://gitlab.com/pasosdeJesus/division-politica/-/blob/main/Colombia/2020/correcciones/Derecho_de_peticion_correcciones_Veredal_2020.pdf).

Sin embargo este DIVIPOLA tiene un error en el código del municipio del centro poblado SAN JOSÉ de SAN JUANITO en META, porque dice 50683 (como el de SAN JOSÉ DE ARANA) y debía ser 50686.

Por eso como fuente de datos empleamos el DIVIPOLA 2021 suministrado pero con ese error corregido.

Cuenta con 33 departamentos (son 32 pero el DIVIPOLA incluye a Bogotá D.C. como departamento), 1121 municipios (son 1101 pero el DIVIPOLA cuenta como municipios 20 áreas no municipalizadas y la Isla de San Andrés), 7845 centros poblados (58 menos que en el DIVIPOLA 2020) de los cuales 1103 tienen tipo CM (Cabecera Municipal) y 6742 con tipo CP (Centro Poblado).

2. Preparación de datos

Ya el DANE nos aclaró, en respuesta a derecho de petición (ver https://github.com/pasosdeJesus/sip/blob/datos2020/geografia/respuesta_derecho_de_peticion_DIVIPOLA/20202400198471%20-%20CARLOS%20DURAN%20-%20Posibles%20errores%20en%20DIVIPOLA%20vigente%20y%202019.pdf) el uso de paréntesis y de guiónes en diversos nombres de centros poblados y municipios: los guiones suelen indicar nombres alternativos empleados por moradores y los paréntesis puede contener nombres antiguos o referencias para distinguir la ubicación de otras. Lo consignamos aquí porque no hemos visto una publicación del DANE donde se aclare esto.

La hoja de centros poblados se convirtió a CSV, se eliminaron filas sin datos y se renombraron las columnas a: cod_departamento, departamento, cod_municipio, municipio, cod_centropoblado, centropoblado y tipo_centropoblado. Notamos que la conversión (con LibreOffice 7) dejó como enteros (sin los ceros iniciales) las columnas con código.

Tras corregir el error mencionado, el CSV resultante se ha dejado en https://gitlab.com/pasosdeJesus/division-politica/-/blob/main/Colombia/2021/DIVIPOLA_DANE_2021_corregido.csv

3. Importación a base de datos de sip sobre PostgreSQL

El CSV se importó a una base de datos de sip así:

CREATE TABLE divipola_oficial_2021_corregido (
        coddep integer,
        departamento VARCHAR(512) COLLATE es_co_utf_8,
        codmun integer,
        municipio VARCHAR(512) COLLATE es_co_utf_8,
        codcp integer,
        centropoblado VARCHAR(512) COLLATE es_co_utf_8,
        tipocp VARCHAR(6)
);

COPY divipola_oficial_2021_corregido FROM '/tmp/DIVIPOLA_DANE_2021_corregido.csv' DELIMITER ',' CSV HEADER;

Que da una cuenta de 7845 registros.

Se hizo la vista:

CREATE OR REPLACE VIEW divipola_sip AS (SELECT
  sip_departamento.id_deplocal AS coddep,
  sip_departamento.nombre AS departamento,
  sip_departamento.id_deplocal*1000+sip_municipio.id_munlocal AS codmun,
  sip_municipio.nombre AS municipio,
  sip_departamento.id_deplocal*1000000 +
  sip_municipio.id_munlocal*1000+sip_clase.id_clalocal AS codcp,
  sip_clase.nombre AS  centropoblado,
  sip_clase.id_tclase AS tipocp,
  sip_clase.id AS sip_idcp
  FROM
  sip_departamento
  JOIN sip_municipio ON sip_municipio.id_departamento=sip_departamento.id
  JOIN sip_clase ON sip_clase.id_municipio=sip_municipio.id
  WHERE
  sip_departamento.id_pais=170
  AND sip_clase.fechadeshabilitacion IS NULL
  AND sip_clase.id < 100000
  ORDER BY 2, 4, 6 );

Esta daba una cuenta de 7884 registros correspondientes a los vigentes en 2020.

4. Comparación de departamentos con los de sip

En ambos se contaron 33 departamentos:

SELECT COUNT(DISTINCT coddep) FROM divipola_oficial_2021_corregido;
SELECT COUNT(DISTINCT coddep) FROM divipola_sip;

Se constató que los códigos eran iguales:

SELECT s.coddep FROM divipola_sip AS s 
  WHERE s.coddep NOT IN (SELECT DISTINCT coddep FROM divipola_oficial_2021_corregido as d);

SELECT d.coddep FROM divipola_oficial_2021_corregido AS d   
  WHERE d.coddep NOT IN (SELECT DISTINCT coddep FROM divipola_sip as s);

Se compararon nombres y resultaron todos iguales:

SELECT DISTINCT s.coddep,s.departamento,d.departamento FROM divipola_sip AS s 
  JOIN divipola_oficial_2021_corregido as d
  ON s.coddep=d.coddep
  WHERE s.departamento<>d.departamento
;

5. Comparación de municipios con los de sip

Se contaron 1121 municipios en ambos:

SELECT COUNT(DISTINCT codmun) FROM divipola_oficial_2021_corregido ;
SELECT COUNT(DISTINCT codmun) FROM divipola_sip;

Se compararon códigos sin encontrar diferencias:

SELECT DISTINCT s.codmun FROM divipola_sip AS s
  WHERE s.codmun NOT IN (
        SELECT DISTINCT codmun
        FROM divipola_oficial_2021_corregido as d
);

SELECT DISTINCT d.codmun FROM divipola_oficial_2021_corregido AS d
  WHERE d.codmun NOT IN (
        SELECT DISTINCT codmun
        FROM divipola_sip as s
);

Se buscaron diferencias en nombres, pero no hay

SELECT DISTINCT s.codmun,s.municipio,d.municipio FROM divipola_sip AS s
  JOIN divipola_oficial_2021_corregido as d
  ON s.codmun=d.codmun
  WHERE s.municipio<>d.municipio
;

6. Comparación de centros poblados con los de sip

• En sip se cuentan 7884

SELECT COUNT(DISTINCT codcp) FROM divipola_sip;

• Divipola 2021 cuenta 7845

SELECT COUNT(DISTINCT codcp) FROM divipola_oficial_2021_corregido ;

6.1 Centros poblados que se sacaron de DIVIPOLA 2021 respecto a los que tenía sip

Ubicamos los que están en sip pero no en DIVIPOLA 2021:

SELECT s.codcp, s.departamento, s.municipio, s.centropoblado FROM divipola_sip AS s
  WHERE s.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola_oficial_2021_corregido as d
) ORDER BY 1;

Dan 58 registros:

codcp	departamento	municipio	centropoblado
5001012	ANTIOQUIA	MEDELLÍN	LA LOMA
5040003	ANTIOQUIA	ANORÍ	MONTEFRÍO
5045007	ANTIOQUIA	APARTADÓ	LOS NARANJALES
5045008	ANTIOQUIA	APARTADÓ	VIJAGUAL
5154025	ANTIOQUIA	CAUCASIA	CAMPO ALEGRE
5543001	ANTIOQUIA	PEQUE	BARBACOAS
5656006	ANTIOQUIA	SAN JERÓNIMO	MESTIZAL
5656007	ANTIOQUIA	SAN JERÓNIMO	POLEAL
5656011	ANTIOQUIA	SAN JERÓNIMO	EL POMAR
5660006	ANTIOQUIA	SAN LUIS	EL PRODIGIO
5736004	ANTIOQUIA	SEGOVIA	EL CENIZO
5736005	ANTIOQUIA	SEGOVIA	EL CRISTO
5736007	ANTIOQUIA	SEGOVIA	LA CALIENTE
13160008	BOLÍVAR	CANTAGALLO	LA ESPERANZA
13160013	BOLÍVAR	CANTAGALLO	LEJANÍAS
13160020	BOLÍVAR	CANTAGALLO	YANACUÉ
19573005	CAUCA	PUERTO TEJADA	PERICO NEGRO
19701005	CAUCA	SANTA ROSA	SANTA MARTHA
19701015	CAUCA	SANTA ROSA	SECTOR MANDIYACO
20001033	CESAR	VALLEDUPAR	VERACRUZ
23417011	CÓRDOBA	LORICA	SAN ANTERITO
25307001	CUNDINAMARCA	GIRARDOT	SAN LORENZO
25785011	CUNDINAMARCA	TABIO	LOURDES
25839009	CUNDINAMARCA	UBALÁ	SOYA
27025015	CHOCÓ	ALTO BAUDÓ	DOCACINA
27050004	CHOCÓ	ATRATO	REAL DE TANANDÓ
27075001	CHOCÓ	BAHÍA SOLANO	CUPICA
27150008	CHOCÓ	CARMEN DEL DARIÉN	APARTADÓ BUENA VISTA
27413003	CHOCÓ	LLORÓ	LA VUELTA
27413010	CHOCÓ	LLORÓ	OGODÓ
41078010	HUILA	BARAYA	LA UNIÓN
41078011	HUILA	BARAYA	TURQUESTÁN
41396003	HUILA	LA PLATA	MOSCOPÁN
47555022	MAGDALENA	PLATO	LAS PLANADAS
50001028	META	VILLAVICENCIO	PARCELAS DEL PROGRESO
50001031	META	VILLAVICENCIO	SAN CARLOS
50245006	META	EL CALVARIO	SAN JOSE
50350002	META	LA MACARENA	LOS POZOS
50350011	META	LA MACARENA	LA TUNIA
50350016	META	LA MACARENA	VILLA CARDONA
50711001	META	VISTAHERMOSA	CAMPO ALEGRE
52405005	NARIÑO	LEIVA	NARIÑO
52405013	NARIÑO	LEIVA	FLORIDA BAJA
52560011	NARIÑO	POTOSÍ	CUASPUD NUCLEO
52573001	NARIÑO	PUERRES	EL PÁRAMO
52621013	NARIÑO	ROBERTO PAYÁN	PALOSECO
52683008	NARIÑO	SANDONÁ	BOHÓRQUEZ
52683019	NARIÑO	SANDONÁ	LA REGADERA
52835055	NARIÑO	SAN ANDRÉS DE TUMACO	BOCAS DE CURAY
52835206	NARIÑO	SAN ANDRÉS DE TUMACO	OLIVO CURAY
52835220	NARIÑO	SAN ANDRÉS DE TUMACO	SANDER CURAY
52835222	NARIÑO	SAN ANDRÉS DE TUMACO	SOLEDAD CURAY I
52835223	NARIÑO	SAN ANDRÉS DE TUMACO	SOLEDAD CURAY II
70523002	SUCRE	PALMITO	GUAIMARAL
76109094	VALLE DEL CAUCA	BUENAVENTURA	KATANGA
76823003	VALLE DEL CAUCA	TORO	LA PRADERA
85139006	CASANARE	MANÍ	SAN JOAQUÍN DE GARIBAY
95001011	GUAVIARE	SAN JOSÉ DEL GUAVIARE	PUERTO OSPINA

Para actualizar información se realiza la consulta:

SELECT 'UPDATE sip_clase SET observaciones=completa_obs(observaciones, ''No está en DIVIPOLA 2021.''),' || 
'  fechadeshabilitacion=''2022-02-13'' ' ||
'  WHERE id=''' || sip_idcp || '''; -- ' || s.codcp || ' ' || s.centropoblado 
FROM divipola_sip AS s
  WHERE s.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola_oficial_2021_corregido as d
) ORDER BY s.codcp;

Con \copy este resultado se envía a un archivo en el sistema de archivos para posteriormente agregarlo a la migración.

\COPY (SELECT ...) TO '/tmp/deshabilita.sql';

Note que se usa la función completa_obs cuya definición es:

CREATE OR REPLACE FUNCTION completa_obs(obs VARCHAR,
        nuevaobs VARCHAR) RETURNS VARCHAR AS $$
      BEGIN
        RETURN CASE WHEN obs IS NULL THEN nuevaobs
          WHEN obs='' THEN nuevaobs
          WHEN RIGHT(obs, 1)='.' THEN obs || ' ' || nuevaobs
          ELSE obs || '. ' || nuevaobs
        END;
      END; $$
      LANGUAGE PLPGSQL;

6.2 Centros poblados que se agregaron

Examinando los nuevos de DIVIPOLA 2021:

SELECT d.codcp, d.departamento, d.municipio, d.centropoblado FROM divipola_oficial_2021_corregido AS d
  WHERE d.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola_sip as s
) ORDER BY codcp;

Encontramos 19:

codcp	departamento	municipio	centropoblado
5659006	ANTIOQUIA	SAN JUAN DE URABÁ	BALSILLA
18753011	CAQUETÁ	SAN VICENTE DEL CAGUÁN	LOS POZOS
18753023	CAQUETÁ	SAN VICENTE DEL CAGUÁN	LA TUNIA
18753034	CAQUETÁ	SAN VICENTE DEL CAGUÁN	VILLA CARMONA
27077034	CHOCÓ	BAJO BAUDÓ	TOCASINA - DUBASA
27372009	CHOCÓ	JURADÓ	CUPICA
41357006	HUILA	ÍQUIRA	SAN MIGUEL
50573015	META	PUERTO LÓPEZ	PUEBLO NUEVO - GETSEMANÍ
50686003	META	SAN JUANITO	SAN JOSÉ
52520007	NARIÑO	FRANCISCO PIZARRO	BOCAS DE CURAY
52520015	NARIÑO	FRANCISCO PIZARRO	OLIVO CURAY
52520016	NARIÑO	FRANCISCO PIZARRO	SANDER CURAY
52520017	NARIÑO	FRANCISCO PIZARRO	SOLEDAD CURAY I
52520018	NARIÑO	FRANCISCO PIZARRO	SOLEDAD CURAY II
68575031	SANTANDER	PUERTO WILCHES	INVASIÓN LA INDEPENDENCIA
86571014	PUTUMAYO	PUERTO GUZMÁN	LOS GUADUALES
97001011	VAUPÉS	MITÚ	MARGEN IZQUIERDO
97001012	VAUPÉS	MITÚ	12 DE OCTUBRE
97001013	VAUPÉS	MITÚ	TAPURUCUARA

Buscamos entre los ya registrados en sip con el mismo código:

SELECT d.departamento, d.municipio, d.centropoblado,d.codcp, cla.nombre FROM divipola_oficial_2021_corregido AS d 
  JOIN sip_departamento AS dep ON dep.id_deplocal=d.coddep AND dep.id_pais=170 
  JOIN sip_municipio AS mun ON mun.id_departamento=dep.id AND mun.id_munlocal=d.codmun%1000 
  JOIN sip_clase AS cla ON cla.id_municipio=mun.id AND cla.id_clalocal=d.codcp%1000 
  WHERE d.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola_sip as s
  ) ORDER BY d.codcp;

Y encontramos 3, uno con nombre idéntico y los otros 2 con leve cambio en el nombre:

departamento	municipio	centro poblado	codcp	nombre anterior	observación
CAQUETÁ	SAN VICENTE DEL CAGUÁN	LOS POZOS	18753011	LOS POZOS	No está en DIVIPOLA 2018
CAQUETÁ	SAN VICENTE DEL CAGUÁN	LA TUNIA	18753023	LA TUNIA (CAQUETANIA)	No está en DIVIPOLA 2018
NARIÑO	FRANCISCO PIZARRO	BOCAS DE CURAY	52520007	BOCA DE CURAY	No está en DIVIPOLA 2018

También buscamos entre los ya registrados en sip con código diferente pero en el mismo municipio y con nombre similar pero no obtuvimos registros adicionales:

SELECT d.codcp, d.departamento, d.municipio, d.centropoblado, cla.nombre 
  FROM divipola_oficial_2021_corregido AS d 
  JOIN sip_departamento AS dep ON dep.id_pais=170 AND dep.id_deplocal=d.coddep 
  JOIN sip_municipio AS mun ON mun.id_departamento=dep.id AND mun.id_munlocal=d.codmun%1000 
  JOIN sip_clase AS cla ON cla.id_municipio=mun.id 
  WHERE d.codcp NOT IN ( 
        SELECT DISTINCT codcp
        FROM divipola_sip as s
  ) 
  AND (soundexesp(cla.nombre)=soundexesp(d.centropoblado) OR 
    cla.nombre LIKE ('%' || d.centropoblado || '%') OR
    d.centropoblado LIKE ('%' || cla.nombre || '%') ) 
  ORDER BY d.codcp;

Revisando listados históricos del DIVIPOLA encontramos que los 3 estaban presentes con el mismo código en los DIVIPOLAs del 2008 al 2017 y que los 3 fueron retirado en el DIVIPOLA 2018. Notamos también que hay 3 centro poblados con los mismos nombres pero en diferente municipio que eliminaron en el DIVIPOLA 2021. Por lo visto fueron movidos erradamente de municipio desde 2018 hasta mediados de 2020.

Esos 3 se actualizan y se vuelven a habilitar. Los demás se insertan con ayuda de:

SELECT 'INSERT INTO sip_clase (id, nombre, id_municipio, id_clalocal, observaciones) ' || 
  ' VALUES (, ''' || d.centropoblado || ''', ' || mun.id || ', ' || (d.codcp%1000)::varchar || ', ''Agregado en DIVIPOLA 2021'')'
  FROM divipola_oficial_2021 AS d 
  JOIN sip_departamento AS dep ON dep.id_deplocal=d.coddep AND dep.id_pais=170
  JOIN sip_municipio AS mun ON mun.id_departamento=dep.id AND mun.id_munlocal=d.codmun%1000 
  WHERE d.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola_sip as s
) ORDER BY codcp;

6.3 Diferencias en centro poblados con códigos comunes

Se encuentra 1 diferencia

SELECT s.sip_idcp, s.codcp, s.centropoblado, d.centropoblado FROM 
  divipola_sip AS s JOIN divipola_oficial_2021_corregido AS d 
  ON s.codcp=d.codcp
  WHERE s.centropoblado != d.centropoblado
  ORDER BY s.codcp
;

Código	sip2020	Divipola2021	sip2021
76828004	DOSQUEBRADAS	DOS QUEBRADAS	DOS QUEBRADAS

Se revisó y se actualizó en sip.

6.4 Cambio en tipo de centro poblado

Se encuentra uno introducido con DIVIPOLA 2020 pero que nos había hecho falta cambiar:

SELECT s.sip_idcp, s.codcp, s.departamento, s.municipio, s.centropoblado, s.tipocp, d.tipocp FROM 
  divipola_sip AS s JOIN divipola_oficial_2021_corregido AS d 
  ON s.codcp=d.codcp
  WHERE s.tipocp != UPPER(d.tipocp)
  ORDER BY s.codcp
;

sip_idcp	codcp	departamento	municipio	centropoblado	tipocp	tipocp
8117	52224001	NARIÑO	CUASPUD CARLOSAMA	MACAS	IPD	CP

7. Verificación de la migración

Se aplica la migración completa y se vuelven a ejecutar las comparaciones de este documento para asegurar (1) que la cantidad de departamentos, municipios y centros poblados es la misma, (2) que no hay cambio en los códigos y (3) que los cambios en nombres son intencionales, (4) que no hay diferencia en los tipos de centro poblados.

8. Cambio a la semilla con datos básicos

Después de ejecutar la migración se hizo con:

cd test/dummy
bin/rails sip:vuelcabasicas
cp db/datos-basicas.sql ../../db/