hpaste

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
Create a SQLite database suited for astrological purposes,
based on geonames.org database dumps.

Needed files:
	http://download.geonames.org/export/dump/cities1000.zip
	http://download.geonames.org/export/dump/countryInfo.txt
	http://download.geonames.org/export/dump/timeZones.txt

Procedure:
	cd to a tmp directory containing this script and needed files,
	and do:
	
	# python -c "import atlasbuild; atlasbuild.createdb()"
	
	This results in 'atlas.db' sqlite3 database file, containing about
	90000 cities.

Remarks:
	This is just a quick written test script...
	The database structure could have more integrity constraints.


stnsls@gmail.com

"""

try:
	from sqlite3 import dbapi2 as sqlite
except ImportError:
	from pysqlite2 import dbapi2 as sqlite


def createdb():
	# Open db
	cnx = sqlite.connect('./atlas.db', isolation_level=None)
	cur = cnx.cursor()
	# Create tables
	sql = ["""
	CREATE TABLE Timezones (
		tzid integer primary key,
		tzname varchar
	);""", """
	CREATE TABLE Countries (
		ctyid integer primary key,
		ctyname varchar
	);""", """
	CREATE TABLE Cities (
		id integer primary key,
		name varchar,
		country integer,
		timezone integer,
		latitude float,
		longitude float,
		altitude float
	);
	"""]
	for s in sql:
		cur.execute(s)
	# Insert timezones
	f = open('./timeZones.txt')
	all = f.readlines()
	f.close()
	sql = """INSERT INTO Timezones (tzname) VALUES (?);"""
	global alltz
	alltz = [x.split('\t')[0] for x in all[1:]]
	alltz.sort()
	for tz in alltz:
		cur.execute(sql, (tz,))
	# Insert countries
	f = open('countryInfo.txt')
	all = f.readlines()
	f.close()
	sql = """INSERT INTO Countries (ctyname) VALUES (?);"""
	global allcty
	allcty = [(x.split('\t')[0], x.split('\t')[4]) for x in all[28:]]
	for a, b in allcty:
		cur.execute(sql, (b,))
	# Insert places
	f = open('./allCountries.txt')
	all = f.readlines()
	f.close()
	all = [City(x) for x in all]
	sql = """INSERT INTO Cities (name, country, timezone, latitude, longitude, altitude)
	VALUES (?, ?, ?, ?, ?, ?);"""
	for c in all:
		cur.execute(sql, (c.name, c.cty, c.tz, c.lat, c.lon, c.alt))
	#
	cur.execute('vacuum;')


class City():
	def __init__(self, line):
		line = line.split('\t')
		self.name = line[1]
		self.lat = line[4]
		self.lon = line[5]
		self.alt = line[15]
		for c in allcty:
			if c[0] == line[8]:
				self.cty = allcty.index(c)+1
				break
		for tz in alltz:
			if tz == line[17]:
				self.tz = alltz.index(tz)+1
				break


# End.