Pr. Actualització base de dades de municipis

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

Aquesta és l'estructura actual de la taula de municipis:

mysql> desc municipis;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id_mun     | smallint     | NO   | PRI | NULL    |       |
| id_prov    | smallint     | YES  | MUL | NULL    |       |
| municipi   | varchar(120) | YES  |     | NULL    |       |
| superficie | smallint     | YES  |     | NULL    |       |
| habitants  | smallint     | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

I com bé saps, no tenim dades als camps de superficie i habitants:

mysql> select distinct superficie, habitants from municipis;
Empty set (0,00 sec)

Anem a completar la informació que tenim de la base de dades a partir de les dades reals que podem trobar al INE. Concretament, podem trobar el cens (número d'habitants) dels municipis; i d'altra banda podem trobar el codi oficial del municipi.

Codi oficial del INE i superfície dels municipis

Fent una cerca per completar la informació dels municipis, trobem aquest recurs:

i, com es comenta, la informació al final bé del INE (Instituto Nacional de Estadística).

Podem descarregar dades més completes de les que tenim en aquest Excel:

Concretament podem aprofitar les dades de la superfície dels municipis, i també el codi oficial del municipi (el id_mun que tenim nosaltres no és cap codi oficial, és senzillament un identificador nostre).

Obrim amb el full de càlcul el fitxer list-mun-2012.xls.

I hem d'aconseguir fer els updates de la taula de municipis:

UPDATE municipis SET cod_ine='01001', superficie=20 WHERE municipi='Alegría-Dulantzi';
UPDATE municipis SET cod_ine='01002', superficie=96 WHERE municipi='Amurrio';
UPDATE municipis SET cod_ine='01003', superficie=73 WHERE municipi='Aramaio';
...

NOTA: fixa't que hem arrodonit les dades originals de la superfície, que estan en decimals, al seu valor enter arrodonit (a la base de dades tenim el tipus SMALLINT). Amb el LibreOffice Calc això ho pots fer creant una nova columna i fer servir la funció ARROD (arrodonir en català, mirar com es diu la funció en castellà), i així obtens el valor de la superfície sense decimals.

Per fer-ho, el primer que hem de fer és afegir a l'estructura de la taula el camp cod_ine. Aprofitem també per canviar el tipus de dades d'habitants, de smallint a mediumint:

ALTER TABLE municipis ADD COLUMN cod_ine CHAR(5) AFTER id_mun;
ALTER TABLE municipis MODIFY COLUMN habitants mediumint;
mysql> desc municipis;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id_mun     | smallint     | NO   | PRI | NULL    |       |
| cod_ine    | char(5)      | YES  |     | NULL    |       |
| id_prov    | smallint     | YES  |     | NULL    |       |
| municipi   | varchar(120) | YES  |     | NULL    |       |
| superficie | smallint     | YES  |     | NULL    |       |
| habitants  | mediumint     | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Nota: en una primera versió havia posat el camp com a UNIQUE, però curiosament el valor del cod_ine no és únic, es repeteix una vintena de vegades.


Per construir aquestes sentències udpate (més de 8000 registres), necessitem treballar amb una nova columna del Excel i construir la nostra sentència SQL. Ho farem de la següent manera:

=CONCATENA("UPDATE municipis SET cod_ine=";A2;", superficie=";L2;" WHERE municipi='";B2;"';")

I millor, tenint en compte que els municipis poden tenir apòstrofs:

=CONCATENA("UPDATE municipis SET cod_ine=";A2;", superficie=";L2;" WHERE municipi='";SUBSTITUEIX(B2;"'";"''");"';")

És important que dominis la instrucció CONCATENA del Excel-Calc, doncs s'utilitza sovint.

Creem el script update_cod_ine_sup.sql amb els més de 8000 updates, i l'executem, controlant els errors que es puguin produir:

$ mysql -u alumne -pkeiL2lai municipis -f < ./update_cod_ine_sup.sql > ./control_errors.log 2>&1

NOTA1: aquest script s'ha d'executar dins del docker, i això vol dir que prèviament has hagut d'enviar el script (que l'has creat localment), al docker. S'ha d'executar en el prompt de Linux, no en el prompt del mysql.

NOTA2: com que aquest procés et pot semblar una mica llarg, recorda que pots fer servor el mysql-workbenck (File > Open SQL Script). (És convenient tenir marcada l'opció de tirar endavant encara que hi hagi errors).

Si el script triga una mica vol dir que va bé, són 8000 sentències. Quan acaba hem de mirar el log:

$ cat control_errors.log
mysql: [Warning] Using a password on the command line interface can be insecure.

OK, no hi ha errors

select count(*) from municipis where superficie is not null;
+----------+
| count(*) |
+----------+
|     7427 |
+----------+

Hem inserit la superfície en la majoria dels municipis. Què passa amb la resta de municipis? S'ha d'estudiar, segurament hi ha una discorància amb el nom del municipi (TBD).

select count(*) from municipis where cod_ine is not null;
+----------+
| count(*) |
+----------+
|     7427 |
+----------+

Com hem comentat, hi ha valors de cod_ine que són repetits. Els municipis amb cod_ine repetit són:

mysql> select cod_ine, count(*) as total from municipis group by cod_ine having total>=2;
+---------+-------+
| cod_ine | total |
+---------+-------+
|    NULL |   704 |
|   37279 |     2 |
|   28015 |     2 |
|   42151 |     2 |
|   17030 |     2 |
|   47221 |     2 |
|   31212 |     2 |
|   24165 |     2 |
|   31070 |     2 |
|   35013 |     2 |
|   33037 |     2 |
|   46244 |     2 |
|   39021 |     2 |
|   49241 |     2 |
|   49071 |     2 |
+---------+-------+

Què passa amb aquests 704 municipis amb valor NULL. No s'ha trobat el municipi perquè, per exemple, no és el mateix El Ejido que Ejido, El.

Què passa amb els cod_ine repetits? Són els municipis que tenen un altre municipi amb el mateix nom. Per exemple, el codi 33037 es correspon a Mieres, que existeix a Girona i a Astúries.

Tenint en compte que ja sabem quin és l'origen d'aquest problema, de moment no li donarem més importància (més endavant, si cal, ja ho arreglarem).

Habitants

La informació la podem trobar també al INE:

Anem a Detalle municipal, Descargar archivo comprimido.... Se'ns descarrega el fitxer pobmun.zip, que descomprimim, obrim el fitxrer pobmunmun20.xlsl, i veiem que tenim les dades de població actualitzades a 2020.

De manera similar al que hem fet abans, podem construir les instruccions update que ens permetran actualitzar el camp habitants:

=CONCATENA("UPDATE municipis SET habitants=";E3;" WHERE municipi='";H3;"';")

UPDATE municipis SET habitants=761 WHERE municipi='Abengibre';
UPDATE municipis SET habitants=506 WHERE municipi='Alatoz';
UPDATE municipis SET habitants=174336 WHERE municipi='Albacete';
UPDATE municipis SET habitants=679 WHERE municipi='Albatana';
...

I per tant creem el script update_cod_ine_hab.sql, i l'executem contra la base de dades com hem fet abans:

$ mysql -u alumne -pkeiL2lai municipis -f < ./update_hab.sql > ./control_errors.log 2>&1

$ cat control_errors.log

I ja tenim la informació dels habitants:

Municipi amb més habitants:
select municipi, id_prov, habitants from municipis where habitants=(select max(habitants) from municipis);
+----------+---------+-----------+
| municipi | id_prov | habitants |
+----------+---------+-----------+
| Madrid   |      28 |   3334730 |
+----------+---------+-----------+


Municipi amb menys habitants:
select municipi, id_prov, habitants from municipis where habitants=(select min(habitants) from municipis);
+-----------------+---------+-----------+
| municipi        | id_prov | habitants |
+-----------------+---------+-----------+
| Illán de Vacas  |      45 |         3 |
+-----------------+---------+-----------+


Número de municipis sense la informació dels habitants:
select count(*) from municipis where habitants is null;
+----------+
| count(*) |
+----------+
|      614 |
+----------+

Tasques a realitzar

1. Segueix pas a pas el que s'ha explicat a classe per tal d'introduir el cod_ine, superfície i habitants en la taula municipi:

  • 1a) afegir el camp cod_ine
  • 1b) update1
  • 1c) update2

2. Quants municipis encara no tenen el cod_ine, superficie i habitants actualitzats? Per què no s'ha fet l'actualització en aquests casos? Explica

3. Per què en següents municipis no ha funcionat bé l'actualització de les dades?

  • Ballestero, El
  • Gineta, La
  • Llocnou d'En Fenollet
  • Alboraia

4. Actualitza manualment el cod_ine, superficie i habitants dels següents municipis:

  • Ballestero, El
  • Gineta, La
  • Llocnou d'En Fenollet
  • Alboraia

5. Fes un informe de la superfície i número d'habitants de tots els municipis de Catalunya (inner joins).

6. Calcula la superfície total de Catalunya a partir de la suma de la superfície total dels seus municipis, amb aquesta comanda:

select sum(superficie) from municipis m
inner join provincies p ON m.id_prov=p.id_prov
inner join comunitats c ON p.id_com=c.id_com
and comunitat='Catalunya';

7. Compara aquest valor amb el de la Viquipèdia. Per què t'ha sortit més petit?

Entrega

Entregaràs al Classroom, dins del termini:

  • un fitxer de text (txt, document de google o pdf) on responguis totes les preguntes que se't fan en l'anterior apartat, amb comentaris. Posa alguna captura de pantalla, i alguna evidència de què has fet la pràctica en el teu ordinador.
  • Entregaràs el script amb els updates del cod_ine i superfície.
  • Entregaràs el script amb els updates dels habitants.

Solució

Aquests són els dos scripts que has de generar:

Si voleu executar aquests dos scripts en MySQL Workbench, com que són 8000x2 = 16000 instruccions update, és necessari desmarcar l'opció:

Edit > Preferences > SQL Editor

i desmarcar l'opció que hi ha abaix de tot, on posa:

Safe updates (reject...)

I aleshores has de reconnectar la base de dades (o be tancar i tornar a obrir el Mysql Workbench).


creat per Joan Quintana Compte, gener 2022