-
Notifications
You must be signed in to change notification settings - Fork 0
/
importGeonames.py
52 lines (45 loc) · 1.3 KB
/
importGeonames.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
from sys import stdin
import pg
con = pg.connect(dbname="kyst")
def s(s, comma = True):
r = "'"+pg.escape_string(s)+"'"
if comma:
r += ", "
return r
def i(s):
if not s:
return "NULL, "
return str(int(s)) + ", "
q = "DELETE FROM geonames";
con.query(q);
for line in stdin:
line = line.split("\t")
assert len(line) == 19
#print line
q = "INSERT INTO geonames (geonameid, name, asciiname, alternatenames, position, " + \
"featureclass, featurecode, countrycode, countrycode2, admin1, admin2, admin3, admin4, " + \
"population, elevation, gtopo30, timezone, modifydate) " + \
"VALUES(" + \
i(line[0]) + \
s(line[1]) + \
s(line[2]) + \
s(line[3]) + \
("GeomFromText('POINT(%f %f)', -1), " % (float(line[4]), float(line[5]))) + \
s(line[6]) + \
s(line[7]) + \
s(line[8]) + \
s(line[9]) + \
s(line[10]) + \
s(line[11]) + \
s(line[12]) + \
s(line[13]) + \
i(line[14]) + \
i(line[15]) + \
i(line[16]) + \
s(line[17]) + \
s(line[18], False) + ")"
#print q;
con.query(q)
#break
q = "UPDATE geonames SET elevation=0 WHERE elevation IS NULL AND featureclass='H' AND featurecode = 'FJD'"
con.query(q);