GTOPO30 = 16
TIMEZONE = 17
MODIFICATION_DATE = 18
name_sql = """INSERT INTO place_name (place_id, lang_id, name, name_hash, is_official)
VALUES (%(place_id)s, %(lang_id)s, %(name)s, %(name_hash)s, %(is_official)s)"""
c.execute("SELECT nextval('place_id_seq')");
place_id = c.fetchone()[0]
i = 0
for iso2 in countries_map.keys():
sys.stdout.write("===> [%d%%] Processing %s data... downloading... " % ((i * (100.0/len(countries_map)), iso2)))
sys.stdout.flush()
i += 1
cn_path = imputils.zipex("http://download.geonames.org/export/dump/%s.zip" % iso2, "%s.txt" % iso2)
sys.stdout.write("collating... ")
sys.stdout.flush()
f = codecs.open(cn_path, "rt", "utf-8")
place_buf = []
name_buf = []
tmp_place_hndl, tmp_place_path = tempfile.mkstemp(dir="/tmp")
os.chmod(tmp_place_path, 0644)
tmp_place_name_hndl, tmp_place_name_path = tempfile.mkstemp(dir="/tmp")
os.chmod(tmp_place_name_path, 0644)
for l in f:
r = [x.strip() for x in l.split("\t")]
geonames_id = r[GEONAMEID]
main = sp[1].decode("latin-1").encode("utf-8")
lat = float(sp[3])
long = float(sp[2])
area_pp = sp[4].decode("latin-1").encode("utf-8")
c.execute("""INSERT INTO postcode (country_id, main, location, area_pp) VALUES
(%(de_id)s, %(main)s, ST_GeomFromText(%(loc)s, 4326), %(area_pp)s)""",
dict(de_id=de_id, main=main, loc='POINT(%(lat)s %(long)s)' % {'lat':lat, 'long':long}, area_pp=area_pp))
f.close()
db.commit()
# US zip codes
print "===> Downloading US zipcodes"
us_zip_path = imputils.zipex("http://mappinghacks.com/data/zipcode.zip", "zipcode.csv")
print "===> Importing US zipcodes"
us_id = get_country_id("US")
f = codecs.open(us_zip_path, "rt", "utf-8")
f.readline() # Skip the first line which contains the column names
for l in f:
if l.strip() == "":
continue
# Lines in the US zipcode file are quoted CSV e.g.:
# "00210","Portsmouth","NH","43.005895","-71.013202","-5","1"
sp = [x[1:-1] for x in l.strip().split(",")]
main = sp[0]
lat = float(sp[3])
long = float(sp[4])