2022_07 Actualización a DIVIPOLA 2022 07

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 2021 al DIVIPOLA 2022-07 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 2021 a DIVIPOLA 2022-07 puede consultar un resumen ejecutivo

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

1. Dos migraciones que hacen las modificaciones a la base de datos de aplicaciones y motores existentes basados en sip:https://github.com/pasosdeJesus/sip/blob/main/db/migrate/20220714191555_actualiza_divipola202207.rb y https://github.com/pasosdeJesus/sip/blob/main/db/migrate/20220721170452_actualiza2_divipola202207.rb
2. Cambio a la semilla con datos básicos que puede verse en esta contribución.

La edición 2022-07 del DIVIPOLA respecto al 2021 tuvo varios cambios a nivel técnico:

• Ahora consta de 3 archivos CSV para Departamentos, Municipios y Centros Poblados.
• Ahora cada registro tiene latitud y longitud (al interior del área)
• Ahora los registros de municipios tienen un tipo que puede ser "Municipio," "Área no Municipalizada" o "Isla"

1. Fuente de datos

Empleamos los CSVs descargados el 13.Jul.2022 de https://geoportal.dane.gov.co/geovisores/territorio/consulta-divipola-division-politico-administrativa-de-colombia/ cambiando codificación de LATIN1 a UTF-8 y coma por punto en cifras para facilitar importación directamente en PostgreSQL que no soporta localización de números con decimales.

Cuenta con 33 departamentos (son 32 pero el DIVIPOLA incluye a Bogotá D.C. como departamento), 1121 municipios (aunque tienen tipo "Municipio" 1102, tipo "Área no municipalizada" 18 e "Isla" a 1 "San Andrés" --extrañamente no cuenta a Providencia como Isla sino como municipio), 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.

En el CSV se renombraron columnas a nombres que típicamente usamos.

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

3.1 Departamentos

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

CREATE TABLE divipola202207_dep (
        coddep integer,
        departamento VARCHAR(512) COLLATE es_co_utf_8,
        latitud DOUBLE precision,
        longitud DOUBLE precision
);

COPY divipola202207_dep FROM '/var/division-politica/Colombia/2022/DIVIPOLA_Departamentos-2022-07.csv' DELIMITER ';' CSV HEADER;

Que da una cuenta de 33 registros.

Se verifica que todo código de departamento está en sip:

SELECT coddep FROM divipola202207_dep 
  WHERE coddep NOT IN (SELECT DISTINCT id_deplocal 
    FROM sip_departamento AS sd 
      WHERE sd.fechadeshabilitacion IS NULL 
      AND sd.id_pais=170 
      AND sd.nombre<>'EXTERIOR'
  )
;

Y se buscan códigos de sip que no estén en DIVIPOLA

SELECT sd.id, id_deplocal, sd.nombre FROM sip_departamento AS sd
    WHERE sd.fechadeshabilitacion IS NULL 
    AND sd.id_pais=170 
    AND sd.nombre<>'EXTERIOR'
    AND id_deplocal NOT IN (SELECT coddep FROM divipola202207_dep)
;

Pero no se encuentran, así que coinciden códigos.

Se buscan departamentos con nombres diferentes, pero no se encuentran, los nombres de todos coinciden:

SELECT coddep, departamento, sd.nombre
  FROM divipola202207_dep AS d 
  JOIN sip_departamento AS sd 
    ON sd.fechadeshabilitacion IS NULL 
    AND sd.nombre<>'EXTERIOR'
    AND sd.id_pais=170
    AND d.coddep=sd.id_deplocal 
  WHERE d.departamento<>upper(sd.nombre)
;

Se miden distancias de la latitud y longitud que vienen ahora en DIVIPOLA con las que tenemos en sip:

SELECT coddep, departamento, sqrt((d.latitud-sd.latitud)^2+(d.longitud-sd.longitud)^2) as dist, d.latitud, d.longitud
  FROM divipola202207_dep AS d 
  JOIN sip_departamento AS sd 
    ON sd.fechadeshabilitacion IS NULL 
    AND sd.nombre<>'EXTERIOR'
    AND sd.id_pais=170
  WHERE d.coddep=sd.id_deplocal
;

La máxima es 1.99 y hay 5 superiores a 1 que se revisan en un mapa, resultando también ubicadas dentro del departamento.

Preferimos usar los puntos de latitud y longitud publicados por el DANE. Que se facilita con:

SELECT 'UPDATE sip_departamento SET latitud=''' || d.latitud || 
  ''', longitud=''' || d.longitud || ''' WHERE id=' || sd.id || '; -- ' || sd.nombre 
  FROM divipola202207_dep AS d JOIN sip_departamento AS sd 
    ON sd.fechadeshabilitacion IS NULL 
    AND sd.nombre<>'EXTERIOR'
    AND sd.id_pais=170
    AND d.coddep=sd.id_deplocal;

3.2 Municipios

El CSV de municipio se importó así:

CREATE TABLE divipola202207_mun (

        coddep integer,
        departamento VARCHAR(512) COLLATE es_co_utf_8,
        codmun integer,
        municipio VARCHAR(512) COLLATE es_co_utf_8,
        tipomun VARCHAR(512) COLLATE es_co_utf_8,
        latitud DOUBLE precision,
        longitud DOUBLE precision
);

COPY divipola202207_mun FROM '/var/division-politica/Colombia/2022/DIVIPOLA_Municipios-2022-07.csv' DELIMITER ';' CSV HEADER;

Que importa 1121 registros.

Se verifica consistencia en los 1121 municipios con departamentos con:

SELECT count(*) FROM divipola202207_mun AS m JOIN divipola202207_dep AS d ON d.coddep=m.coddep;

Y que todo departamento tenga municipios con:

SELECT d.coddep, COUNT(codmun) FROM divipola202207_mun AS m 
  JOIN divipola202207_dep AS d ON d.coddep=m.coddep 
  GROUP BY 1 
  HAVING COUNT(codmun)=0

Se verifica que todo código de municipio esté en sip:

SELECT codmun FROM divipola202207_mun 
  WHERE codmun NOT IN (SELECT DISTINCT id_deplocal*1000+id_munlocal 
    FROM sip_municipio AS sm 
    JOIN sip_departamento AS sd 
      ON sm.id_departamento=sd.id 
      AND sd.fechadeshabilitacion IS NULL 
      AND sd.id_pais=170 
      AND sd.nombre<>'EXTERIOR'
    WHERE sm.fechadeshabilitacion IS NULL);

Se determina que no hay códigos de municipio de sip que no están en DIVIPOLA con:

SELECT sm.id, id_deplocal*1000+id_munlocal, sm.nombre FROM sip_municipio AS sm
  JOIN sip_departamento AS sd ON sm.id_departamento=sd.id 
    AND sd.fechadeshabilitacion IS NULL 
    AND sd.id_pais=170 
    AND sd.nombre<>'EXTERIOR'
    AND sm.fechadeshabilitacion IS NULL 
  WHERE id_deplocal*1000+id_munlocal NOT IN (SELECT codmun FROM divipola202207_mun)
;

Se ubican nombres de municipios diferentes:

SELECT sm.id, id_deplocal*1000+id_munlocal AS codmun, upper(sm.nombre) AS sip, m.municipio AS div220207 
  FROM sip_municipio AS sm                                                       
  JOIN sip_departamento AS sd ON sm.id_departamento=sd.id 
    AND sd.fechadeshabilitacion IS NULL 
    AND sd.id_pais=170 
    AND sd.nombre<>'EXTERIOR'
    AND sm.fechadeshabilitacion IS NULL 
  JOIN divipola202207_mun AS m ON id_deplocal*1000+id_munlocal=codmun 
  WHERE upper(sm.nombre)<>municipio;
;

Que da un registro:

id	codmun	sip	div220207
1230	19760	SOTARÁ - PAISPAMBA	SOTARÁ PAISPAMBA

Que se actualiza en sip.

Se miden distancias de la latitud y longitud que vienen ahora en DIVIPOLA con las que tenemos en sip:

SELECT codmun, departamento, municipio, sqrt((m.latitud-sm.latitud)^2+(m.longitud-sm.longitud)^2) as dist, m.latitud, m.longitud
  FROM divipola202207_mun AS m 
  JOIN sip_departamento AS sd 
    ON sd.fechadeshabilitacion IS NULL 
    AND sd.nombre<>'EXTERIOR'
    AND sd.id_pais=170
    AND sd.id_deplocal=m.coddep
  JOIN sip_municipio AS sm ON sm.id_departamento=sd.id 
    AND sm.fechadeshabilitacion IS NULL 
    AND id_deplocal*1000+id_munlocal=codmun
;

La máxima distancia es 17.9 y 6 mayores que 2 fueron. Esto evidenció un problema con la ubicación en sip de 3 municipios que confirma decisíon de usar latitud y longitud de DIVIPOLA tras verificar las 6 de distancia superior a 2. Se facilita con:

SELECT 'UPDATE sip_municipio SET latitud=''' || m.latitud || 
  ''', longitud=''' || m.longitud || ''' WHERE id=' || sm.id || 
  '; -- ' || sm.nombre || ' / ' || sd.nombre 
  FROM divipola202207_mun AS m
  JOIN sip_departamento AS sd 
    ON sd.fechadeshabilitacion IS NULL 
    AND sd.nombre<>'EXTERIOR'
    AND sd.id_pais=170
    AND m.coddep=sd.id_deplocal
  JOIN sip_municipio AS sm 
    ON sm.id_departamento=sd.id 
    AND sm.fechadeshabilitacion IS NULL 
    AND m.codmun=id_deplocal*1000+id_munlocal
    ORDER BY sd.nombre, sm.nombre
;

Este DIVIPOLA 2022 incluye tipos de municipios con los siguientes valores y frecuencias:

tipomun	cuenta
Área no municipalizada	18
Municipio	1102
Isla	1

Se agrega esta información a sip en la tabla sip_municipio en un nuevo campo tipomun que inicialmente es NULL para paises diferentes a Colombia.

3.3 Centros poblados

El CSV de Centros poblados se importó así:

CREATE TABLE divipola202207_cp (
        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(16),
        latitud DOUBLE precision,
        longitud DOUBLE precision
);

COPY divipola202207_cp FROM '/var/division-politica/Colombia/2022/DIVIPOLA_CentrosPoblados-2022-07.csv' DELIMITER ';' CSV HEADER;

Que importa 7730 registros.

Se verifica consistencia con municipios con:

SELECT count(*) FROM divipola202207_cp AS c JOIN divipola202207_mun AS m ON c.codmun=m.codmun;

Y que todo municipio tenga centros poblados con:

SELECT m.codmun, COUNT(codcp) FROM divipola202207_cp AS c 
  JOIN divipola202207_mun AS m ON m.codmun=c.codmun 
  GROUP BY 1 
  HAVING COUNT(codcp)=0

Se crea vista que facilita comparaciones:

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

Esta daba una cuenta de 7845 registros correspondientes a los vigentes en 2021 (es decir el DIVIPOLA 2022 tiene 15 menos).

3.3.1 Centros poblados que se sacaron de DIVIPOLA 2022 respecto a los que tenía sip

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

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

Dan 136 registros:

codcp	departamento	municipio	centropoblado
11001016	BOGOTÁ, D.C.	BOGOTÁ, D.C.	SANTO DOMINGO
11001017	BOGOTÁ, D.C.	BOGOTÁ, D.C.	TIERRA NUEVA
13052007	BOLÍVAR	ARJONA	CONDOMINIO HACIENDA
13688019	BOLÍVAR	SANTA ROSA DEL SUR	SANTA ISABEL
20517007	CESAR	PAILITAS	MATA DE BARRO
23068016	CÓRDOBA	AYAPEL	SEHEVE
23300002	CÓRDOBA	COTORRA	LOS GÓMEZ
23555029	CÓRDOBA	PLANETA RICA	SAN JERÓNIMO (GOLERO)
23678017	CÓRDOBA	SAN CARLOS	CAROLINA
23807001	CÓRDOBA	TIERRALTA	CALLEJAS
25793002	CUNDINAMARCA	TAUSA	ROMA (TAUSA VIEJO)
27001036	CHOCÓ	QUIBDÓ	GITRADO
27001037	CHOCÓ	QUIBDÓ	MOJAUDO
27025010	CHOCÓ	ALTO BAUDÓ	YUCAL
27025012	CHOCÓ	ALTO BAUDÓ	BELLA VISTA
27025018	CHOCÓ	ALTO BAUDÓ	IRUTO
27025025	CHOCÓ	ALTO BAUDÓ	NUNCIDÓ
27025028	CHOCÓ	ALTO BAUDÓ	SANTA MARIA DE CONDOTO
27025032	CHOCÓ	ALTO BAUDÓ	GUINEO
27025034	CHOCÓ	ALTO BAUDÓ	PLAYITA
27025039	CHOCÓ	ALTO BAUDÓ	PUNTO CAIMINTO
27073003	CHOCÓ	BAGADÓ	DABAIBE
27073007	CHOCÓ	BAGADÓ	SAN MARINO
27077022	CHOCÓ	BAJO BAUDÓ	PUNTA DE IGUA
27150001	CHOCÓ	CARMEN DEL DARIÉN	BRISAS
27150017	CHOCÓ	CARMEN DEL DARIÉN	CHINTADO MEDIO
27160001	CHOCÓ	CÉRTEGUI	LA TOMA
27160003	CHOCÓ	CÉRTEGUI	PAREDES
27205006	CHOCÓ	CONDOTO	MANDINGA
27245009	CHOCÓ	EL CARMEN DE ATRATO	EL 21
27250004	CHOCÓ	EL LITORAL DEL SAN JUAN	CORRIENTE PALO
27250007	CHOCÓ	EL LITORAL DEL SAN JUAN	CHARAMBIRÁ
27250009	CHOCÓ	EL LITORAL DEL SAN JUAN	DESCOLGADERO
27250023	CHOCÓ	EL LITORAL DEL SAN JUAN	PAPAYO
27250025	CHOCÓ	EL LITORAL DEL SAN JUAN	CARRA
27250028	CHOCÓ	EL LITORAL DEL SAN JUAN	QUEBRADA DE PICHIMÁ
27250035	CHOCÓ	EL LITORAL DEL SAN JUAN	TROJITA
27250036	CHOCÓ	EL LITORAL DEL SAN JUAN	VENADO
27413004	CHOCÓ	LLORÓ	LAS HAMACAS
27413009	CHOCÓ	LLORÓ	NIPORDU
27413019	CHOCÓ	LLORÓ	SAN JORGE
27430004	CHOCÓ	MEDIO BAUDÓ	BELLA VISTA
27430005	CHOCÓ	MEDIO BAUDÓ	BERIGUADÓ
27430007	CHOCÓ	MEDIO BAUDÓ	PUERTO PLATANARES
27450002	CHOCÓ	MEDIO SAN JUAN	BOCA DE SURUCO
27450003	CHOCÓ	MEDIO SAN JUAN	CHIQUICHOQUI
27450004	CHOCÓ	MEDIO SAN JUAN	DIPURDÚ EL GUASIMO
27450005	CHOCÓ	MEDIO SAN JUAN	EL GUAMO
27450012	CHOCÓ	MEDIO SAN JUAN	SAN MIGUEL
27450014	CHOCÓ	MEDIO SAN JUAN	ISLA DE CRUZ
27450016	CHOCÓ	MEDIO SAN JUAN	MACEDONIA
27450018	CHOCÓ	MEDIO SAN JUAN	SAN JERÓNIMO
27450019	CHOCÓ	MEDIO SAN JUAN	UNIÓN WAUNAÁN
27491003	CHOCÓ	NÓVITA	IRABUBÚ
27491008	CHOCÓ	NÓVITA	URABARÁ
27491009	CHOCÓ	NÓVITA	CURUNDÓ
27491013	CHOCÓ	NÓVITA	CARMEN DE SURAMA
27491018	CHOCÓ	NÓVITA	TORRA
27580001	CHOCÓ	RÍO IRÓ	ALTO CHATO
27580007	CHOCÓ	RÍO IRÓ	CHARA
27580009	CHOCÓ	RÍO IRÓ	LA GUAMA
27580010	CHOCÓ	RÍO IRÓ	TODOSITICO
27580011	CHOCÓ	RÍO IRÓ	VIRO
27615012	CHOCÓ	RIOSUCIO	TRUANDÓ
27615019	CHOCÓ	RIOSUCIO	LA RAYA
27615024	CHOCÓ	RIOSUCIO	LA ISLETA
27745004	CHOCÓ	SIPÍ	TAPARAL
27745016	CHOCÓ	SIPÍ	BARRANCON
27745017	CHOCÓ	SIPÍ	BARRANCONCITO
27745018	CHOCÓ	SIPÍ	CHARCO HONDO
27745019	CHOCÓ	SIPÍ	CHARCO LARGO
27800012	CHOCÓ	UNGUÍA	EL ROTO
44430013	LA GUAJIRA	MAICAO	YOTOJOROY
44650015	LA GUAJIRA	SAN JUAN DEL CESAR	LA PEÑA DE LOS INDIOS
44650020	LA GUAJIRA	SAN JUAN DEL CESAR	POTRERITO
50001025	META	VILLAVICENCIO	CONDOMINIO SANTA BÁRBARA
50330004	META	MESETAS	MIRADOR
50711020	META	VISTAHERMOSA	EL TRIUNFO
52227002	NARIÑO	CUMBAL	MAYASQUER
52520014	NARIÑO	FRANCISCO PIZARRO	VUELTA DEL GALLO
52683024	NARIÑO	SANDONÁ	20 DE JULIO
52835189	NARIÑO	SAN ANDRÉS DE TUMACO	EL PROGRESO - SANTO DOMINGO
52835199	NARIÑO	SAN ANDRÉS DE TUMACO	LA BRAVA
5284012	ANTIOQUIA	FRONTINO	JENGAMECODA
54174008	NORTE DE SANTANDER	CHITAGÁ	CARRILLO
54206008	NORTE DE SANTANDER	CONVENCIÓN	SOLEDAD
54245003	NORTE DE SANTANDER	EL CARMEN	LA CULEBRITA
54250001	NORTE DE SANTANDER	EL TARRA	BELLA VISTA
54250006	NORTE DE SANTANDER	EL TARRA	LAS TORRES
54250008	NORTE DE SANTANDER	EL TARRA	LA MOTILANDIA
54810001	NORTE DE SANTANDER	TIBÚ	BARCO LA SILLA
5591010	ANTIOQUIA	PUERTO TRIUNFO	EL ALTO DEL POLLO
5604011	ANTIOQUIA	REMEDIOS	CHORRO DE LAGRIMAS
5631008	ANTIOQUIA	SABANETA	PAN DE AZÚCAR
5660015	ANTIOQUIA	SAN LUIS	MONTELORO
5893004	ANTIOQUIA	YONDÓ	CUATRO BOCAS
66170025	RISARALDA	DOSQUEBRADAS	GAITAN LA PLAYA
70001010	SUCRE	SINCELEJO	LAS HUERTAS
70001018	SUCRE	SINCELEJO	LAS PALMAS
70001028	SUCRE	SINCELEJO	SANTA CRUZ
70110002	SUCRE	BUENAVISTA	CALIFORNIA
70429030	SUCRE	MAJAGUAL	TOTUMAL
70702002	SUCRE	SAN JUAN DE BETULIA	HATO VIEJO
73555003	TOLIMA	PLANADAS	LA ESTRELLA
76109008	VALLE DEL CAUCA	BUENAVENTURA	CISNEROS
76109066	VALLE DEL CAUCA	BUENAVENTURA	LA DELFINA
76109117	VALLE DEL CAUCA	BUENAVENTURA	BETANIA
76109119	VALLE DEL CAUCA	BUENAVENTURA	EL CREDO
76109120	VALLE DEL CAUCA	BUENAVENTURA	EL EDEN
76109126	VALLE DEL CAUCA	BUENAVENTURA	PLAYA LARGA
76126003	VALLE DEL CAUCA	CALIMA	JIGUALES
76126013	VALLE DEL CAUCA	CALIMA	PUENTE TIERRA
76250005	VALLE DEL CAUCA	EL DOVIO	LA CABAÑA
76250012	VALLE DEL CAUCA	EL DOVIO	PLAYA RICA
81300004	ARAUCA	FORTUL	MATECAÑA
81736005	ARAUCA	SARAVENA	PUENTE DE BOJABÁ
81736019	ARAUCA	SARAVENA	TINAJAS
81794023	ARAUCA	TAME	LA ARENOSA
85125001	CASANARE	HATO COROZAL	CORRALITO
85125004	CASANARE	HATO COROZAL	MANARE
85125012	CASANARE	HATO COROZAL	SANTA BÁRBARA
85125014	CASANARE	HATO COROZAL	EL GUAFAL
85125015	CASANARE	HATO COROZAL	LAS CAMELIAS
8560007	ATLÁNTICO	PONEDERA	CASCAJAL
86573008	PUTUMAYO	PUERTO LEGUÍZAMO	LA VICTORIA
94343004	GUAINÍA	BARRANCOMINAS	MINITAS
94884001	GUAINÍA	PUERTO COLOMBIA	SEJAL (MAHIMACHI)
94887001	GUAINÍA	PANA PANA	BOCAS DE YARI
95015002	GUAVIARE	CALAMAR	LA UNION
95025007	GUAVIARE	EL RETORNO	MIROLINDO
95025008	GUAVIARE	EL RETORNO	LA CRISTALINA
95025009	GUAVIARE	EL RETORNO	LA NUEVA PRIMAVERA
97001007	VAUPÉS	MITÚ	MANDÍ
97666001	VAUPÉS	TARAIRA	COMUNIDAD DE CURUPIRA
97889001	VAUPÉS	YAVARATÉ	PAPURÍ
99773021	VICHADA	CUMARIBO	EL TUPARRO

Para actualizar información se realiza la consulta:

SELECT 'UPDATE sip_clase SET observaciones=''' || COALESCE(s.observaciones || '. ', '') || 'No está en DIVIPOLA 2022.'',' || 
'  fechadeshabilitacion=''2022-07-21'' ' ||
'  WHERE id=''' || sip_idcp || '''; -- ' || s.codcp || ' ' || s.centropoblado 
FROM divipola_sip AS s
  WHERE s.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola202207_cp 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';

Y para hacer el método down de la migración, se ejecuta el siguiente antes de aplicar la migración:

SELECT 'UPDATE sip_clase SET observaciones=' || COALESCE('''' || s.observaciones || '''', 'NULL') || ',' || 
'  fechadeshabilitacion=NULL ' ||
'  WHERE id=''' || sip_idcp || '''; -- ' || s.codcp || ' ' || s.centropoblado 
FROM divipola_sip AS s
  WHERE s.codcp NOT IN (
        SELECT DISTINCT codcp
        FROM divipola202207_cp as d
) ORDER BY s.codcp;

3.3.2 Centros poblados que se agregaron

Examinando los nuevos de DIVIPOLA 2022:

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

Encontramos 21:

codcp	departamento	municipio	centropoblado	corregido
5380020	ANTIOQUIA	LA ESTRELLA	PAN DE AZÚCAR
5756034	ANTIOQUIA	SONSÓN	EL ALTO DEL POLLO
13212017	BOLÍVAR	CÓRDOBA	CALIFORNIA
13836011	BOLÍVAR	TURBACO	CONDOMINIO HACIENDA
20787018	CESAR	TAMALAMEQUE	MATA DE BARRO
23001074	CÓRDOBA	MONTERÍA	SAN JERÓNIMO (GOLERO)
23162033	CÓRDOBA	CERETÉ	CAROLINA
23815035	CÓRDOBA	TUCHÍN	SANTA CRUZ
23855024	CÓRDOBA	VALENCIA	CALLEJAS
27077035	CHOCÓ	BAJO BAUDÓ	UNIÓN MISARA
27077036	CHOCÓ	BAJO BAUDÓ	BELLA VISTA
27160011	CHOCÓ	CÉRTEGUI	NIPORDU
63190035	QUINDÍO	CIRCASIA	HACIENDA HORIZONTES
70215025	SUCRE	COROZAL	LAS PALMAS
70215026	SUCRE	COROZAL	HATO VIEJO
70523020	SUCRE	PALMITO	LAS HUERTAS
70771053	SUCRE	SUCRE	TOTUMAL
73067022	TOLIMA	ATACO	LA ESTRELLA
76233049	VALLE DEL CAUCA	DAGUA	LA DELFINA
76233050	VALLE DEL CAUCA	DAGUA	EL EDEN	EL EDÉN
76233051	VALLE DEL CAUCA	DAGUA	PLAYA LARGA

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

SELECT d.departamento, d.municipio, d.centropoblado,d.codcp, cla.nombre FROM divipola202207_cp 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 1 con nombre idéntico:

departamento	municipio	centro poblado	codcp	nombre anterior	observación
ANTIOQUIA	LA ESTRELLA	PAN DE AZÚCAR	5380020	Pan De Azucar	Aparecía sin tilde y duplicado con uno tildado en el municipio SABANETA en DIVIPOLAS 2011-2015. Desapareció del municipio LA ESTRELLA en DIVIPOLAS 2016-2021 aunque se mantuvo el de SABANETA. En DIVIPOLA 2022 desaparece el de SABANETA y reaparece con tilde en LA ESTRELLA.

El cual se actualiza.

Los otros 20 se insertan con ayuda de:

SELECT 'INSERT INTO sip_clase (id, nombre, id_municipio, id_clalocal, observaciones, fechacreacion, created_at, updated_at) ' || 
  ' VALUES (, ''' || d.centropoblado || ''', ' || mun.id || ', ' || (d.codcp%1000)::varchar || 
  ', ''Agregado en DIVIPOLA 2021'', ''2022-07-21'', ''2022-07-21'', ''2022-07-21'');'
  FROM divipola202207_cp 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;

Comparando deshabilitados con los 20 insertados notamos 16 "movimientos" de municipio y 2 de departamento:

cod nuevo	departamento nuevo	municipio nuevo	centro poblado	cod. anterior	municipio anterior
5756034	ANTIOQUIA	SONSÓN	EL ALTO DEL POLLO	5591010	PUERTO TRIUNFO
13836011	BOLÍVAR	TURBACO	CONDOMINIO HACIENDA	13052007	ARJONA
20787018	CESAR	TAMALAMEQUE	MATA DE BARRO	20517007	PAILITAS
23001074	CÓRDOBA	MONTERÍA	SAN JERÓNIMO (GOLERO)	23555029	PLANETA RICA
23162033	CÓRDOBA	CERETÉ	CAROLINA	23678017	SAN CARLOS
23855024	CÓRDOBA	VALENCIA	CALLEJAS	23807001	TIERRALTA
27077036	CHOCÓ	BAJO BAUDÓ	BELLA VISTA	27025012	ALTO BAUDÓ
27160011	CHOCÓ	CÉRTEGUI	NIPORDU	27413009	LLORÓ
70215025	SUCRE	COROZAL	LAS PALMAS	70001018	SINCELEJO
70215026	SUCRE	COROZAL	HATO VIEJO	70702002	SAN JUAN DE BETULIA
70523020	SUCRE	PALMITO	LAS HUERTAS	70001010	SINCELEJO
70771053	SUCRE	SUCRE	TOTUMAL	70429030	MAJAGUAL
73067022	TOLIMA	ATACO	LA ESTRELLA	73555003	PLANADAS
76233049	VALLE DEL CAUCA	DAGUA	LA DELFINA	76109066	BUENAVENTURA
76233050	VALLE DEL CAUCA	DAGUA	EL EDEN	76109120	BUENAVENTURA
76233051	VALLE DEL CAUCA	DAGUA	PLAYA LARGA	76109126	BUENAVENTURA

Los que cambiaron de departamento fueron:

cod nuevo	departamento nuevo	municipio nuevo	centro poblado	cod. anterior	departamento anterior	municipio anterior
13212017	BOLÍVAR	CÓRDOBA	CALIFORNIA	70110002	SUCRE	BUENAVISTA
23815035	CÓRDOBA	TUCHÍN	SANTA CRUZ	70001028	SUCRE	SINCELEJO

3.3.3 Diferencias en centros poblados con códigos comunes

No encontramos diferencias

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

Bueno excepto tras actualizar porque agregamos tilde a EL EDÉN.

3.3.4 Latitud y Longitud

En sip no había latitud y longitud de centros poblados por lo que se adoptan del DIVIPOLA 2022:

SELECT 'UPDATE sip_clase SET latitud=' || coalesce('''' || c.latitud || '''', 'NULL') || 
  ', longitud=' || COALESCE('''' || c.longitud || '''', 'NULL') || ' WHERE id=' || sip_idcp || 
  '; -- ' || s.centropoblado || ' / ' || s.municipio || ' / ' || s.departamento
  FROM divipola202207_cp AS c
  JOIN divipola_sip AS s ON c.codcp=s.codcp
  ORDER BY s.departamento, s.municipio, s.centropoblado
;

En todo caso hay 190 centros poblados que no tienen latitud ni longitud:`

cód centro poblado	Departamento	Municipio	Centro poblado
91430000	AMAZONAS	LA VICTORIA	PACOA
91540002	AMAZONAS	PUERTO NARIÑO	BOYAHUAZÚ
5113001	ANTIOQUIA	BURITICÁ	EL NARANJO
5113002	ANTIOQUIA	BURITICÁ	GUARCO
5113005	ANTIOQUIA	BURITICÁ	LA ANGELINA
5113004	ANTIOQUIA	BURITICÁ	LLANOS DE URARCO
5001033	ANTIOQUIA	MEDELLÍN	URQUITA
5659006	ANTIOQUIA	SAN JUAN DE URABÁ	BALSILLA
5873002	ANTIOQUIA	VIGÍA DEL FUERTE	VEGAEZ
81300009	ARAUCA	FORTUL	SITIO NUEVO
13006035	BOLÍVAR	ACHÍ	SANTA LUCÍA
13440014	BOLÍVAR	MARGARITA	CAÑO MONO
13440017	BOLÍVAR	MARGARITA	LA MONTAÑA
13458003	BOLÍVAR	MONTECRISTO	PARAÍSO
13458008	BOLÍVAR	MONTECRISTO	SAN AGUSTÍN
13473015	BOLÍVAR	MORALES	EL CORCOVADO
13490001	BOLÍVAR	NOROSÍ	BUENA SEÑA
13490002	BOLÍVAR	NOROSÍ	CASA DE BARRO
13490003	BOLÍVAR	NOROSÍ	LAS NIEVES
13490004	BOLÍVAR	NOROSÍ	MINA BRISA
13650010	BOLÍVAR	SAN FERNANDO	EL CONTADERO
13650013	BOLÍVAR	SAN FERNANDO	LA GUADUA
13650014	BOLÍVAR	SAN FERNANDO	LAS CUEVAS
13810002	BOLÍVAR	TIQUISIO	BOCAS DE SOLIS
13810004	BOLÍVAR	TIQUISIO	DOS BOCAS
13810009	BOLÍVAR	TIQUISIO	QUEBRADA DEL MEDIO
15469007	BOYACÁ	MONIQUIRÁ	LOS CAYENOS
18094005	CAQUETÁ	BELÉN DE LOS ANDAQUÍES	PUEBLO NUEVO LOS ÁNGELES
18094009	CAQUETÁ	BELÉN DE LOS ANDAQUÍES	SAN ANTONIO DE PADUA
18150012	CAQUETÁ	CARTAGENA DEL CHAIRÁ	EL CAFÉ
18410013	CAQUETÁ	LA MONTAÑITA	PALMERAS
18592004	CAQUETÁ	PUERTO RICO	SANTANA RAMOS
18753023	CAQUETÁ	SAN VICENTE DEL CAGUÁN	LA TUNIA
18753011	CAQUETÁ	SAN VICENTE DEL CAGUÁN	LOS POZOS
18753034	CAQUETÁ	SAN VICENTE DEL CAGUÁN	VILLA CARMONA
18756006	CAQUETÁ	SOLANO	CUEMANI
85125005	CASANARE	HATO COROZAL	PUERTO COLOMBIA
19050005	CAUCA	ARGELIA	EL DIVISO
19050002	CAUCA	ARGELIA	LA BELLEZA
19050016	CAUCA	ARGELIA	PUERTO RICO
19050017	CAUCA	ARGELIA	SAN JUAN GUADUA
19075006	CAUCA	BALBOA	PURETO
19256012	CAUCA	EL TAMBO	HUISITÓ
19256016	CAUCA	EL TAMBO	LOS ANDES
19256036	CAUCA	EL TAMBO	PLAYA RICA
19517046	CAUCA	PÁEZ	SANTA ROSA
19532003	CAUCA	PATÍA	DON ALONSO
19533002	CAUCA	PIAMONTE	EL REMANSO
19533004	CAUCA	PIAMONTE	YAPURÁ
20175029	CESAR	CHIMICHAGUA	PIEDRAS BLANCAS
20570001	CESAR	PUEBLO BELLO	LA CAJA
20570004	CESAR	PUEBLO BELLO	NABUSIMAKE
27025030	CHOCÓ	ALTO BAUDÓ	AMPARRAIDA (SANTA RITA)
27025004	CHOCÓ	ALTO BAUDÓ	CHACHAJÓ
27025013	CHOCÓ	ALTO BAUDÓ	CHIGORODÓ
27025016	CHOCÓ	ALTO BAUDÓ	DOMINICO
27025014	CHOCÓ	ALTO BAUDÓ	EL SALTO (BELLA LUZ)
27025017	CHOCÓ	ALTO BAUDÓ	GEANDO
27025019	CHOCÓ	ALTO BAUDÓ	LA DIVISA
27025020	CHOCÓ	ALTO BAUDÓ	LA FELICIA
27025021	CHOCÓ	ALTO BAUDÓ	LA LOMA
27025033	CHOCÓ	ALTO BAUDÓ	MIACORA
27025024	CHOCÓ	ALTO BAUDÓ	MOJAUDÓ
27025022	CHOCÓ	ALTO BAUDÓ	PAVARANDÓ (PUREZA)
27025035	CHOCÓ	ALTO BAUDÓ	PUERTO ALEGRE
27025036	CHOCÓ	ALTO BAUDÓ	PUERTO CÓRDOBA URUDO
27025038	CHOCÓ	ALTO BAUDÓ	PUERTO LIBIA
27025027	CHOCÓ	ALTO BAUDÓ	PUESTO INDIO
27025007	CHOCÓ	ALTO BAUDÓ	SAN FRANCISCO DE CUGUCHO
27073012	CHOCÓ	BAGADÓ	PESCADITO
27073006	CHOCÓ	BAGADÓ	PIEDRA HONDA
27073011	CHOCÓ	BAGADÓ	VIVÍCORA
27077002	CHOCÓ	BAJO BAUDÓ	BELÉN DE DOCAMPODO
27077006	CHOCÓ	BAJO BAUDÓ	DOTENEDÓ
27077007	CHOCÓ	BAJO BAUDÓ	HIJUÁ
27077012	CHOCÓ	BAJO BAUDÓ	PLAYITA
27077032	CHOCÓ	BAJO BAUDÓ	PUERTO ABADÍA
27077014	CHOCÓ	BAJO BAUDÓ	PUNTA PURRICHA
27077034	CHOCÓ	BAJO BAUDÓ	TOCASINA - DUBASA
27150012	CHOCÓ	CARMEN DEL DARIÉN	CHICAO
27150014	CHOCÓ	CARMEN DEL DARIÉN	LA MADRE
27150004	CHOCÓ	CARMEN DEL DARIÉN	PUERTO LLERAS
27205016	CHOCÓ	CONDOTO	CONSUELO ANDRAPEDA
27205014	CHOCÓ	CONDOTO	LA PLANTA
27250021	CHOCÓ	EL LITORAL DEL SAN JUAN	BURUJÓN
27250041	CHOCÓ	EL LITORAL DEL SAN JUAN	CABECERA
27250026	CHOCÓ	EL LITORAL DEL SAN JUAN	PUERTO MURILLO
27372009	CHOCÓ	JURADÓ	CUPICA
27425005	CHOCÓ	MEDIO ATRATO	EL LLANO DE BEBARAMÁ
27425006	CHOCÓ	MEDIO ATRATO	SAN ANTONIO DEL BUEY (CAMPO SANTO)
27425008	CHOCÓ	MEDIO ATRATO	SAN ROQUE
27430013	CHOCÓ	MEDIO BAUDÓ	SAN MIGUEL BAUDOCITO
27430026	CHOCÓ	MEDIO BAUDÓ	UNIÓN MISARA
27450013	CHOCÓ	MEDIO SAN JUAN	FUJIADÓ
27450007	CHOCÓ	MEDIO SAN JUAN	LA RANCHA
27450015	CHOCÓ	MEDIO SAN JUAN	LA UNIÓN
27450008	CHOCÓ	MEDIO SAN JUAN	NOANAMÁ
27450017	CHOCÓ	MEDIO SAN JUAN	PUERTO MURILLO
27491004	CHOCÓ	NÓVITA	JUNTAS DE TAMANÁ
27491015	CHOCÓ	NÓVITA	LA PUENTE
27491016	CHOCÓ	NÓVITA	PINDAZA
27491017	CHOCÓ	NÓVITA	QUEBRADA LARGA
27001008	CHOCÓ	QUIBDÓ	BOCA DE TANANDÓ
27001013	CHOCÓ	QUIBDÓ	CAMPOBONITO
27001047	CHOCÓ	QUIBDÓ	EL FUERTE
27615022	CHOCÓ	RIOSUCIO	PERANCHITO
27745011	CHOCÓ	SIPÍ	CHAMBACÚ
23672012	CÓRDOBA	SAN ANTERO	EL NARANJO
23815032	CÓRDOBA	TUCHÍN	EL BARZAL
23815033	CÓRDOBA	TUCHÍN	LA GRANJA
23815034	CÓRDOBA	TUCHÍN	SAN MARTIN
25035001	CUNDINAMARCA	ANAPOIMA	LA PAZ
25099002	CUNDINAMARCA	BOJACÁ	SANTA BÁRBARA
25307004	CUNDINAMARCA	GIRARDOT	BERLÍN
25438005	CUNDINAMARCA	MEDINA	MESA DE LOS REYES
94343002	GUAINÍA	BARRANCOMINAS	ARRECIFAL
94343005	GUAINÍA	BARRANCOMINAS	PUERTO ZANCUDO
94887002	GUAINÍA	PANA PANA	VENADO ISANA
95025010	GUAVIARE	EL RETORNO	LA PAZ
95025004	GUAVIARE	EL RETORNO	MORICHAL VIEJO
95200010	GUAVIARE	MIRAFLORES	PUERTO SANTANDER
41357006	HUILA	ÍQUIRA	SAN MIGUEL
41503004	HUILA	OPORAPA	PARAGUAY
44650027	LA GUAJIRA	SAN JUAN DEL CESAR	VERACRUZ
50330007	META	MESETAS	LA ARGENTINA
50330009	META	MESETAS	PUERTO NARIÑO
50330010	META	MESETAS	SAN ISIDRO
50568008	META	PUERTO GAITÁN	ALTO TILLAVÁ
50573015	META	PUERTO LÓPEZ	PUEBLO NUEVO - GETSEMANÍ
50590007	META	PUERTO RICO	LA TIGRA
50001024	META	VILLAVICENCIO	CONDOMINIO DE LOS ODONTÓLOGOS
50711017	META	VISTAHERMOSA	TRES ESQUINAS
52224001	NARIÑO	CUASPUD CARLOSAMA	MACAS
52254001	NARIÑO	EL PEÑOL	LAS COCHAS
52520007	NARIÑO	FRANCISCO PIZARRO	BOCAS DE CURAY
52520015	NARIÑO	FRANCISCO PIZARRO	OLIVO CURAY
52520013	NARIÑO	FRANCISCO PIZARRO	SAN PEDRO DEL VINO
52520016	NARIÑO	FRANCISCO PIZARRO	SANDER CURAY
52520017	NARIÑO	FRANCISCO PIZARRO	SOLEDAD CURAY I
52520018	NARIÑO	FRANCISCO PIZARRO	SOLEDAD CURAY II
52473008	NARIÑO	MOSQUERA	PUEBLO NUEVO
52540003	NARIÑO	POLICARPA	SAN ROQUE (BUENAVISTA)
52540004	NARIÑO	POLICARPA	SÁNCHEZ
52540006	NARIÑO	POLICARPA	SANTA CRUZ
52560001	NARIÑO	POTOSÍ	CÁRDENAS
52835020	NARIÑO	SAN ANDRÉS DE TUMACO	BARRO COLORADO
52835011	NARIÑO	SAN ANDRÉS DE TUMACO	DESCOLGADERO
52835102	NARIÑO	SAN ANDRÉS DE TUMACO	EL COCO
52835114	NARIÑO	SAN ANDRÉS DE TUMACO	GUABAL
52835015	NARIÑO	SAN ANDRÉS DE TUMACO	LA CALETA
52835083	NARIÑO	SAN ANDRÉS DE TUMACO	LA SIRENA
52835085	NARIÑO	SAN ANDRÉS DE TUMACO	PALAY
52835047	NARIÑO	SAN ANDRÉS DE TUMACO	SALISVÍ
52683014	NARIÑO	SANDONÁ	ALTAMIRA CRUZ DE ARADA
52683016	NARIÑO	SANDONÁ	CHÁVEZ
52683009	NARIÑO	SANDONÁ	PARAGUAY
52683007	NARIÑO	SANDONÁ	ROMA CHÁVEZ
52683003	NARIÑO	SANDONÁ	SAN BERNARDO
52683022	NARIÑO	SANDONÁ	SAN FERNANDO
52683023	NARIÑO	SANDONÁ	SAN FRANCISCO ALTO
52683011	NARIÑO	SANDONÁ	SAN GABRIEL
54250005	NORTE DE SANTANDER	EL TARRA	EL PASO
54250007	NORTE DE SANTANDER	EL TARRA	LA CAMPANA
54720004	NORTE DE SANTANDER	SARDINATA	LAS MERCEDES
54720005	NORTE DE SANTANDER	SARDINATA	LUIS VERO
54800011	NORTE DE SANTANDER	TEORAMA	LA CECILIA
54810013	NORTE DE SANTANDER	TIBÚ	LA LLANA
86001009	PUTUMAYO	MOCOA	YUNGUILLO
86571014	PUTUMAYO	PUERTO GUZMÁN	LOS GUADUALES
86573005	PUTUMAYO	PUERTO LEGUÍZAMO	EL MECAYA
86573003	PUTUMAYO	PUERTO LEGUÍZAMO	SENSELLA
68575031	SANTANDER	PUERTO WILCHES	INVASIÓN LA INDEPENDENCIA
68745009	SANTANDER	SIMACOTA	LA ROCHELA
68755002	SANTANDER	SOCORRO	BERLÍN
73616006	TOLIMA	RIOBLANCO	MARACAIBO
76364004	VALLE DEL CAUCA	JAMUNDÍ	LA LIBERIA
76364018	VALLE DEL CAUCA	JAMUNDÍ	LA MESETA
76364019	VALLE DEL CAUCA	JAMUNDÍ	LA VENTURA
76377009	VALLE DEL CAUCA	LA CUMBRE	JIGUALES
76377002	VALLE DEL CAUCA	LA CUMBRE	LA MARÍA
76828004	VALLE DEL CAUCA	TRUJILLO	DOS QUEBRADAS
97001012	VAUPÉS	MITÚ	12 DE OCTUBRE
97001005	VAUPÉS	MITÚ	ACARICUARA
97001011	VAUPÉS	MITÚ	MARGEN IZQUIERDO
97001013	VAUPÉS	MITÚ	TAPURUCUARA
97001009	VAUPÉS	MITÚ	YAPÚ
99773004	VICHADA	CUMARIBO	AMANAVÉN
99773008	VICHADA	CUMARIBO	GUANAPE
99524007	VICHADA	LA PRIMAVERA	SAN TEODORO (LA PASCUA)
99001006	VICHADA	PUERTO CARREÑO	GUARIPA

4. 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

5. 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/