Ancora un utile widget, per gestire informazioni geografiche - parte 1

Su questo blog ho già parlato della creazione di un database sqlite per gestire le informazioni geografiche che sono utili al lavoro dell'astrologo. Riprendo il tema, ma stavolta associandolo alla creazione di un altro widget complesso, che possiamo utilizzare in vari contesti, sia autonomo sia all'interno di un frame complesso.

Partiamo dal sito geonames che ci fornisce il materiale di base. I file contenuti veongono aggiornati spesso, il che ci aiuta a cogliere eventuali differenze demografiche e di geografia politica. Nello specifico ho selezionato i seguenti:

1. allCountries.zip
2. admin1CodesASCII.txt
3. admin2Codes.txt

Il primo file, molto voluminoso (più di 10 milioni di righe), contiene le coordinate geografiche di ogni luogo abitato del mondo, nonché elevazione, popolazione, timezone e codici relativi alle suddivisioni amministrative di appartenenza, i cui nomi per esteso sono contenuti nei due file successivi.

Per l'uso pratico del database delle località ho scelto di selezionare solo quei luoghi per cui la popolazione censita è di almeno 1 abitante, il che riduce considerevolmente il numero di righe che andranno a costituire il database sqlite finale. Nel seguito allego i file python che consentono di generare delle tabelle testuali per selezioanre e organizzare le informazioni esistenti.

Iniziamo dalla prima tabella testuale (file crea_tab1.py):

#1/usr/bin/env python
file_in = open('allCountries.txt','r')
file_out = open('all_gen.txt','w')
elenco=file_in.readlines()
file_in.close()
count = 1
for i in elenco:
    a=i.split('\t')
    stringa ="%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s"
    # print a[1], a[2], a[4], a[5], a[8], a[10], a[14], a[15], a[16]
    if int(a[14])>100:
        # for j in (1, 2, 4, 5, 8, 10, 14, 15, 17, 18):
        file_out.write(stringa % (a[1], a[2], a[4], a[5], a[8], a[10],
                                  a[11], a[12], a[13], a[14],
                                  a[16], a[17], a[18]))
file_out.close()
file_in.close()

##00 geonameid         : integer id of record in geonames database
##01 name              : name of geographical point (utf8) varchar(200)
##02 asciiname         : name of geographical point in plain ascii characters, varchar(200)
##03 alternatenames    : alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000)
##04 latitude          : latitude in decimal degrees (wgs84)
##05 longitude         : longitude in decimal degrees (wgs84)
##06 feature class     : see http://www.geonames.org/export/codes.html, char(1)
##07 feature code      : see http://www.geonames.org/export/codes.html, varchar(10)
##08 country code      : ISO-3166 2-letter country code, 2 characters
##09 cc2               : alternate country codes, comma separated, ISO-3166 2-letter country code, 60 characters
##10 admin1 code       : fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)
##11 admin2 code       : code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) 
##12 admin3 code       : code for third level administrative division, varchar(20)
##13 admin4 code       : code for fourth level administrative division, varchar(20)
##14 population        : bigint (8 byte int) 
##15 elevation         : in meters, integer
##16 dem               : digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.
##17 timezone          : the timezone id (see file timeZone.txt) varchar(40)
##18 modification date : date of last modification in yyyy-MM-dd format

il file allNews.txt è un file testuale in cui ogni riga è relativa ad una località, e contiene il nome in caratteri unicode, in caratteri ascii, vari codici relativi ai paesi e alle maggiori suddivisioni amministrative, popolazione, elevazione e timezone.

Il secondo script python (crea_tab2.py) genera nomi e codici delle principali suddivisioni amministrative, rilevate dal file admin1CodesASCII.txt:

#1/usr/bin/env python
file_in = open('admin1CodesASCII.txt','r')
file_out = open('all_admin1.txt','w')
elenco=file_in.readlines()
file_in.close()

for i in elenco:
    a=i.split('\t')
    b = a[0].split('.')
    stringa = "{};{};{};{};{}"
    file_out.write(stringa.format(b[0],b[1],a[1],a[2],a[3]))

file_out.close()
file_in.close()

Analogamente il terzo script (crea_tab3.py) per le ulteriori suddivisioni amministrative:

#1/usr/bin/env python
file_in = open('admin2Codes.txt','r')
file_out = open('all_admin2.txt','w')
elenco=file_in.readlines()
file_in.close()

for i in elenco:
    a=i.split('\t')
    b = a[0].split('.')
    stringa = "{};{};{};{};{};{}"
    file_out.write(stringa.format(b[0],b[1],b[2],a[1],a[2],a[3]))

file_out.close()
file_in.close()

Infine il quarto script (crea_tab4.py) genera una tabella con le sigle dei paesi e relativo nome per esteso, secondo le codifiche ISO 3166:

file_in = open("ISO 3166 Codes (Countries).csv")
                 
cnt = file_in.readlines()
lista = []
for i in cnt:
    lista.append(i.split(';'))
file_out = open('all_countries.txt', 'w')
for i in lista:
    file_out.write("{};{}\n".format(i[0].strip(),i[1].strip()))
file_out.close()
file_in.close()

L'elenco delle denominazioni ISO 3166 è facilmente reperibile sul web. Per ottenere un file sorgente pulito ho ricombinato il tutto in un file csv.

Con le quattro tabelle testuali appena create posso costruire finalmente il database sqlite localita.db. Allo scopo ho creato uno script che va lanciato all'atto della creazione del database con il comando in linea: sqlite3 localita.db <crea_db.txt. Il programma eseguibile sqlite3 non è preinstallato nella mia distribuzione ubuntu, ma si trova nel repository. Quindi, come di consueto, sarà sufficiente il comando sudo apt-get install sqlite3.

Allego il contenuto dello script crea_db.txt:

SELECT datetime('now')||'Luoghi';
DROP TABLE IF EXISTS luoghi;
CREATE TABLE luoghi (
   nome_unicode text, 
   nome_ascii text, 
   latitudine real, 
   longitudine real, 
   admin1 text, 
   admin2 text, 
   admin3 text, 
   admin4 text, 
   regione text, 
   popolazione int,
   elevazione int,
   timezone text,
   mod_date text);
SELECT datetime('now')||'a1';
DROP TABLE IF EXISTS a1;
CREATE TABLE a1 (
   admin1 text, 
   admin2 text,
   uniad1 text,
   ascad1 text,
   codice1 int);
SELECT datetime('now')||'a2';
DROP TABLE IF EXISTS a2; 
CREATE TABLE a2 (
   admin1 text,
   admin2 text,
   admin3 text,
    uniad2 text,
   ascad2 text,
   codice2 int);
SELECT datetime('now')||'a3';
DROP TABLE IF EXISTS a3; 
CREATE TABLE a3 (
   country text,
   admin1 text);
.separator ";"
.import all_gen.txt luoghi
.import all_admin1.txt a1
.import all_admin2.txt a2
.import all_countries.txt a3

SELECT datetime('now')||'loc1';
DROP TABLE IF EXISTS loc1;
CREATE TABLE loc1 AS SELECT * from luoghi as L
     LEFT JOIN a1 as A on L.admin1 = A.admin1 and  L.admin2 = A.admin2;

SELECT datetime('now')||'localita';
DROP TABLE IF EXISTS localita;
CREATE TABLE localita AS SELECT nome_ascii, nome_unicode, latitudine, longitudine, L.admin1, B.country, L.admin2, popolazione, elevazione, timezone, mod_date, uniad1, ascad1, uniad2, ascad2 from loc1 as L
   LEFT JOIN a2 as A on L.admin1 = A.admin1 and L.admin2 = A.admin2 and L.admin3 = A.admin3
   LEFT JOIN a3 as B on L.admin1 = B.admin1;
SELECT datetime('now');
   DROP TABLE luoghi;
   DROP TABLE a1;
   DROP TABLE a2;
   DROP TABLE a3;
   DROP TABLE loc1;

VACUUM;

Lo script di creazione del database procede attraverso la generazione dello schema di quattro tabelle, a partire dai file testuali precedentemente compilati, quindi vengono create in sequenza due tabelle ulteriori 'loc1' e 'localita' per estendere con procedure di JOIN la tabella delle località con i nomi per esteso delle suddivisioni amministrative. Al termine le tabelle di appoggio vengono cancellate e si effettua la compattazione del database sqlite (VACUUM). Nel seguito allego il trace della creazione del database:

2015-03-10 12:26:35Luoghi
2015-03-10 12:26:35a1
2015-03-10 12:26:35a2
2015-03-10 12:26:35a3
2015-03-10 12:26:38loc1
2015-03-10 12:26:40localita
2015-03-10 12:26:42

Come si vede, l'operazione è abbastanza veloce (7 secondi in tutto). Il database finale ha una dimensione di 53,7 megabyte, piuttosto voluminoso. La sua lettura da python risulta comunque molto rapida, come vedremo nel prossimo post. Per chi è interessato, trasferisco i file nel repository github nella sottocartella astrometry/atlas