2020_08 Actualización a DIVIPOLA 2019

Describimos a nivel técnico como se realizó la actualización del DIVIPOLA 2018 al DIVIPOLA 2019 en el motor sip. Motor para Ruby on Rails que facilita el desarrollo de sistemas de información.

Puede consultar un resumen ejecutivo de los resultados y sin los detalles técnicos en
https://github.com/pasosdeJesus/sip/wiki/Resumen-ejecutivo-de-la-actualizaci%C3%B3n-a-DIVIPOLA-2019

La actualización del DIVIPOLA se concreta en dos migraciones que hacen las modificaciones a la base de datos: https://github.com/pasosdeJesus/sip/blob/master/db/migrate/20200722210144_actualiza_divipola_2019.rb y https://github.com/pasosdeJesus/sip/blob/master/db/migrate/20200723133542_deshabilita_algunos_tclase.rb.

A continuación se detalla como se hicieron esas migraciones.

1. Fuente de datos

Se descargó de http://geoportal.dane.gov.co/servicios/descarga-y-metadatos/descarga-historicos-de-codificacion-divipola/

Se hizo copia de lo descargado en: https://github.com/pasosdeJesus/sip/blob/datos2020/geografia/Listado_2019.xlsx

Se trata de 33 departamentos, 1121 municipios y 7978 centros poblados (39 menos que en el DIVIPOLA 2018).

Los centros poblados se organizan en columnas:

Código del departamento (2 dígitos puede empezar con 0)
Nombre del Departamento (mayúsculas)
Código del municipio (5 dígitos empezando con los 2 del código del departamento)
Nombre del municipio (mayúsculas)
Código del centro poblado (8 dígitos comenzando con los 5 del código del municipio)
Nombre del centro poblado (mayúsculas)
Sigla del tipo de centro poblado (CM, CM*, CP y CP* --CM* y CP* solo usada en 5 centros poblados de Barrancominas / Guianía que entraron en rigor desde el 1.Dic.2019 como explica el documento)

Notamos el cambio en el tipo de centro poblado pues en 2018 se usaban C, CAS, CP, IPD, y en la nota al pie de este dice CM (Cabecera Municipal), CP (Centro Poblado), ANM (Área no municipalizada) --aunque no hay dato con tipo ANM.

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.

De la hoja de centros poblados, las filas de centros poblados se convirtieron a CSV, se eliminaron filas sin datos y se renombraron las columnas a: departamento_codigo, departamento_nombre, municipio_codigo, municipio_nombre, centro_poblado_codigo, centro_poblado_nombre y tipo. Se eliminó la columna con * que marcaba sólo los centros poblados de Barracominas/Guanía y se cambio el CP* por CP y el CM* por CM de ese municipio. Notamos que la conversión (con LibreOffice 6.3) dejó como enteros (sin los ceros iniciales) las columnas con código.

2.1. Búsqueda de errores comunes

Buscamos errores análogos a los que reportamos el año anterior (ver http://bit.ly/2OwFi9M), pero evidenciamos que ya fueron corregidos:

Espacios faltantes o sobrantes antes y después de paréntesis. Sin problema.
Espacios faltantes o sobrantes antes y después de guion. Sin problema.
Consistencia plural. [^A-Z]EL [ A-Z]*S[^A-Z], [^A-Z]LA [^,]*S[^A-Z], [^A-Z]LAS [^,]*[^S], [^A-Z]LOS [^,]*[^S],.
Punto final sobrante. Sin problema

Sólo resultó extraño uno que también está así en el el DIVIPOLA 2018 y que no habíamos reportado el año pasado:

76109102, EL LIMONES en BUENAVENTURA / VALLE DEL CAUCA. Por el momento lo cambiamos a LOS LIMONES

El CSV resultante se ha dejado en https://github.com/pasosdeJesus/sip/blob/datos2020/geografia/DIVIPOLA_2019_cp_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_2019_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_2019_corregido FROM '/tmp/DIVIPOLA_2019_cp_corregido.csv' DELIMITER ',' CSV HEADER;

Que da una cuenta de 7978 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
  ORDER BY 2, 4, 6 );

Esta daba una cuenta de 8016 registros correspondientes a los vigentes en 2018.

4. Comparación de departamentos con los de sip

En ambos se contaron 33 departamentos:

SELECT COUNT(DISTINCT coddep) FROM divipola_oficial_2019_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_2019_corregido as d);

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

Se compararon nombres:

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

En sip faltaba tilde en QUINDÍO y faltaba coma en BOGOTÁ, D.C.

5. Comparación de municipios con los de sip

Se contaron municipios:

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

En sip había 1122 de 2018 y DIVIPOLA 2019 uno menos 1121:

Se compararon códigos:

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

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

Con lo que se encontró que el municipio de más en el DIVIPOLA 2018 era el 94663 MAPIRIPANA, con 2 centros poblados:

Se buscaron diferencias en nombres

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

Se encontraron diferencias en 10, se propuso nombre nuevo en sip teniendo en cuenta:

Privilegiar nombre nuevo de DIVIPOLA 2019 pero dejar nombre anterior de DIVIPOLA 2018 entre paréntesis para guiar usuarios
Nombres con esa convención del año anterior se dejan ahora sólo con nuevo nombre
Si hay un nombre en DIVIPOLA 2019 que parece erroneo, emplear el que consideramos correcto y consultar al DANE

codmun	sip 2019	divipola 2019	sip 2020
13468	SANTA CRUZ DE MOMPÓX (MOMPÓS)	SANTA CRUZ DE MOMPÓX	SANTA CRUZ DE MOMPÓX
13655	SAN JACINTO public.DEL CAUCA	SAN JACINTO DEL CAUCA	SAN JACINTO DEL CAUCA
19318	GUAPÍ	GUAPI	GUAPI
19760	SOTARA	SOTARÁ - PAISPAMBA	SOTARÁ - PAISPAMBA
52036	ANCUYÁ	ANCUYA	ANCUYA
52224	CUASPÚD	CUASPUD CARLOSAMA	CUASPUD CARLOSAMA
68235	EL CARMEN DE CHUCURÍ	EL CARMEN DE CHUCURI	EL CARMEN DE CHUCURÍ
70823	TOLÚ VIEJO	SAN JOSÉ DE TOLUVIEJO	SAN JOSÉ DE TOLUVIEJO (TOLÚ VIEJO)
73055	ARMERO (ARMERO GUAYABAL)	ARMERO	ARMERO
94343	BARRANCO MINAS	BARRANCOMINAS*	BARRANCOMINAS

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

En sip se cuentan 8014

SELECT COUNT(DISTINCT codcp) FROM divipola_sip;

Divipola cuenta 7978

SELECT COUNT(DISTINCT codcp) FROM divipola_oficial_2019_corregido ;

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

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

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

Dan 62 registros comenzando con 5129014 LA CORRALITA y terminando con 76130044 ZAINERA . Estos dos centros poblados se encuentean en el DIVIPOLA 2018.

Para actualizar información se realiza la consulta:

SELECT 'UPDATE sip_clase SET observaciones=completa_obs(observaciones, ''No está en DIVIPOLA 2019.''),' || 
'  fechadeshabilitacion=''2020-07-23'' ' ||
'  WHERE id=''' || sip_idcp || '''; -- ' || s.codcp || ' ' || s.centropoblado 
FROM divipola_sip AS s
  WHERE s.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola_oficial_2019_corregido as d
) ORDER BY s.codcp;

la cual comienza con:

UPDATE sip_clase SET observaciones=completa_obs(observaciones, 'No está en DIVIPOLA 2019.'),  fechadeshabilitacion='2020-07-23'   WHERE id='14023'; -- 5129014 LA CORRALITA

y termina con

UPDATE sip_clase SET observaciones=completa_obs(observaciones, 'No está en DIVIPOLA 2019.'),  fechadeshabilitacion='2020-07-23'   WHERE id='11879'; -- 76130044 ZAINERA

Con \copy este resultado se envía a un archivo en el sistema de archivo 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 respecto a los conocidos por sip

Examinando los nuevos de DIVIPOLA 2019:

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

Encontramos 26 y notamos cambios en código de unos centros poblados de DIVIPOLA 2018 (incluimos los 2 detectados con el cambio de BARRANCOMINAS):

Código anterior	Código Nuevo	Nombre del centro poblado
5129014	5129005	LA CORRALITA
5490017	5490018	VALE PAVA
94663000	94343001	MAPIRIPANA
94663001	94343005	PUERTO ZANCUDO

De estos identificamos 18 códigos que estaban deshabilitados en sip y volvieron a habilitarse en DIVIPOLA 2019:

SELECT d.codcp, d.centropoblado, cd.centropoblado FROM divipola_oficial_2019_corregido AS d
  JOIN (
    SELECT DISTINCT sip_departamento.id_deplocal*1000000+
      sip_municipio.id_munlocal*1000+  
      sip_clase.id_clalocal AS codcp,
      sip_clase.nombre AS centropoblado
    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 NOT NULL
  ) AS cd ON d.codcp=cd.codcp ORDER BY d.codcp;

De esos 18 tenían exactamente el mismo nombre 11:

SELECT d.codcp, d.centropoblado, cd.centropoblado FROM divipola_oficial_2019_corregido AS d
  JOIN (
    SELECT DISTINCT sip_departamento.id_deplocal*1000000+
      sip_municipio.id_munlocal*1000+  
      sip_clase.id_clalocal AS codcp,
      sip_clase.nombre AS centropoblado
    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 NOT NULL
  ) AS cd ON d.codcp=cd.codcp 
  WHERE d.centropoblado=cd.centropoblado
  ORDER BY d.codcp;

que son:

codcp	nombre
5284001	CARAUTA
15693002	EL IMPERIO
15757000	SOCHA
23660024	LOS BARRILES
25183001	EL SISGA
47745001	BUENAVISTA
47745002	NUEVA VENECIA
52001058	EL ROSARIO
52001087	LA CALDERA
76109044	CABECERA RÍO SAN JUAN
76890003	JIGUALES

que se preparan para habilitar con:

SELECT 'UPDATE sip_clase SET fechadeshabilitacion=NULL,' || 
  ' observaciones=completa_obs(observaciones, ''Rehabilitado por DIVIPOLA 2019 el 2020-07-23.'')' ||
  ' WHERE id=''' || cd.sip_idcp || '''; -- ' || d.codcp || ' ' || d.centropoblado 
FROM divipola_oficial_2019_corregido AS d
  JOIN (
    SELECT DISTINCT sip_departamento.id_deplocal*1000000+
      sip_municipio.id_munlocal*1000+  
      sip_clase.id_clalocal  AS codcp,
      sip_clase.nombre AS centropoblado,
      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 NOT NULL
  ) AS cd ON d.codcp=cd.codcp 
  WHERE d.centropoblado=cd.centropoblado
  ORDER BY d.codcp;

Los otros 7 se habilitan y se les actualiza nombre después de verificar:

SELECT 'UPDATE sip_clase SET fechadeshabilitacion=NULL, nombre=''' || d.centropoblado || ''', ' || 
  ' observaciones=completa_obs(observaciones, ''Rehabilitado por DIVIPOLA 2019 el 2020-07-23, nombre anterior: ' ||
  cd.centropoblado || '.'')' ||
  ' WHERE id=''' || cd.sip_idcp || '''; -- ' || d.codcp
FROM divipola_oficial_2019_corregido AS d
  JOIN (
    SELECT DISTINCT sip_departamento.id_deplocal*1000000+
      sip_municipio.id_munlocal*1000+  
      sip_clase.id_clalocal AS codcp,
      sip_clase.nombre AS centropoblado,
      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 NOT NULL
  ) AS cd ON d.codcp=cd.codcp
  WHERE d.centropoblado<>cd.centropoblado
  ORDER BY d.codcp;

Finalmente los 8 restantes deben insertarse:

SELECT 'INSERT INTO sip_clase (id, id_municipio, id_clalocal, ' || 
  'nombre, id_tclase, observaciones, ' || 
  'fechacreacion, created_at, updated_at) ' ||
  'VALUES (' || 15300+row_number() over (order by d.codcp) || ', ' || m.id::TEXT || ', ''' || RIGHT(d.codcp, 3) || ''', ''' ||
  d.centropoblado  || ''', ''' || UPPER(d.tipocp) || ''', ' ||  '''Aparece en DIVIPOLA 2019.'', ' ||
  '''2020-07-23'', ''2020-07-23'', ''2020-07-23'');' 
  FROM divipola_oficial_2019_corregido AS d 
  JOIN sip_departamento AS dep ON
    d.coddep = dep.id_deplocal
    AND id_pais = 170
  JOIN sip_municipio AS m ON
    dep.id = m.id_departamento AND (d.coddep*1000+m.id_munlocal) = d.codmun  
  WHERE d.codcp NOT IN (SELECT codcp FROM divipola_sip  UNION
  SELECT DISTINCT sip_departamento.id_deplocal*1000000+
      sip_municipio.id_munlocal*1000+  
      sip_clase.id_clalocal AS codcp
    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 NOT NULL
  )
  ORDER BY d.codcp;

6.3 Diferencias en centro poblados con códigos comunes

Se encuentran 57 diferencias que se revisaron y en general se actualizan.

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

La que no se aplicaron y se reportan al DANE son:

Código	Nombre DIVIPOLA 2019	SIP 2020
68235000	EL CARMEN DE CHUCURI	EL CARMEN DE CHUCURÍ
94343000	BARRANCOMINAS*	BARRANCOMINAS
13066	94343001	MAPIRIPANA
94343002	ARRECIFAL*	ARRECIFAL
94343004	MINITAS*	MINITAS

SELECT 'UPDATE sip_clase SET nombre=''' || d.centropoblado || ''', observaciones=''Nombre cambiado por DIVIPOLA 2019. Antes era ' || s.centropoblado || '.'' WHERE id=''' || s.sip_idcp || '''; -- ' ||  s.codcp FROM 
  divipola_sip AS s JOIN divipola_oficial_2019_corregido AS d 
  ON s.codcp=d.codcp
  JOIN sip_clase AS sc ON sc.id=s.sip_idcp WHERE s.centropoblado != d.centropoblado
  AND observaciones is null ORDER BY s.codcp
;

6.4 Cambio en tipo de centro poblado

Se encuentran 4703 diferencias:

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

Debidas al cambio en tipo de centro que ahora solo tiene valores CP (Centro Poblado) y CM (Cabecera Municipal).

Se actualizan en sip con:

    UPDATE sip_clase SET id_tclase='CP', observaciones='Tipo de centro cambiado por DIVIPOLA 2019. Antes era C.' WHERE id_tclase='C' AND fechadeshabilitacion IS
 NULL;
    UPDATE sip_clase SET id_tclase='CP', observaciones='Tipo de centro cambiado por DIVIPOLA 2019. Antes era CAS.' WHERE id_tclase='CAS' AND fechadeshabilitacio
n IS NULL;
    UPDATE sip_clase SET id_tclase='CP', observaciones='Tipo de centro cambiado por DIVIPOLA 2019. Antes era IPD.' WHERE id_tclase='IPD' AND fechadeshabilitacio
n IS NULL;
    UPDATE sip_clase SET id_tclase='CP', observaciones='Tipo de centro cambiado por DIVIPOLA 2019. Antes era IP.' WHERE id_tclase='IP' AND fechadeshabilitacion
IS NULL;
   UPDATE sip_clase SET id_tclase='CP', observaciones='Tipo de centro cambiado por DIVIPOLA 2019. Antes era IPM.' WHERE id_tclase='IPM' AND fechadeshabilitacio
n IS NULL;
    UPDATE sip_clase SET id_tclase='CP', observaciones='Tipo de centro cambiado por DIVIPOLA 2019. Antes era CD.' WHERE id_tclase='CD' AND fechadeshabilitacion 
IS NULL;
    UPDATE sip_clase SET id_tclase='CM' WHERE id=13064; --BARRANCOMINAS
;

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. Retroalimentación al DANE

Se preparó derecho de petición con correcciones:

https://github.com/pasosdeJesus/sip/raw/datos2020/geografia/Derecho_de_peticion_correcciones_DIVIPOLA_2019.pdf

Referencia al derecho de petición en Twitter:

https://twitter.com/pasosdeJesus/status/1286716754685841408

Respuesta del DANE del 10.Ago.2020:

Reconoció 3 de los errores que les informamos en el DIVIPOLA vigente hasta entonces: 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
Nuevo DIVIPOLA resultante: https://github.com/pasosdeJesus/sip/raw/datos2020/geografia/respuesta_derecho_de_peticion_DIVIPOLA/Listados_DIVIPOLA%20(6).xlsx

Agradecimiento por respuesta:

Por correo y por Twitter: https://twitter.com/pasosdeJesus/status/1293845244459585538

Desarrollado por Pasos de Jesús. Dominio público de acuerdo a legislación colombiana. Agradecemos financiación para personalizaciones de dominio público a diversas organizaciones, ver https://github.com/pasosdeJesus/sivel2/blob/master/CREDITOS.md

Provide feedback

Saved searches

Use saved searches to filter your results more quickly