)
if __name__ == '__main__':
conn = db.get_conn()
with open('/Users/nanfang/places.csv', 'r') as f:
r = csv.reader(f)
header = True
for name,name_en,slug,parent_slug,type,latitude,longitude in r:
name = unicode(name,"utf-8")
name_en = unicode(name_en,"utf-8")
if header:
header = False
continue
if latitude and longitude:
db.execute_sql(conn,"""
INSERT INTO place(name,name_en,slug,type,location) values
(%%s,%%s,%%s,%%s,ST_GeomFromText('POINT(%s %s)', 4326))
""" % (longitude, latitude)
, (name,name_en,slug,types.get(type,0))
)
else:
db.execute_sql(conn,'INSERT INTO place(name,name_en,slug,type) values (%s,%s,%s,%s)',
(name,name_en,slug,types.get(type,0))
)
print('imported %s' % name)
conn.commit()
parent_id INT,
location GEOGRAPHY(POINT,4326),
created_at TIMESTAMP WITH TIME ZONE NOT NULL default NOW(),
);
CREATE UNIQUE INDEX place_slug_idx ON place(slug);
CREATE INDEX place_name_idx ON place(name);
CREATE INDEX place_location ON place USING GIST (location);
CREATE TABLE place_mfw(
id INT PRIMARY KEY,
p_id INT NOT NULL,
name VARCHAR(256) NOT NULL,
hot INT,
rating SMALLINT,
level SMALLINT NOT NULL,
location GEOGRAPHY(POINT,4326),
tags VARCHAR[],
created_at TIMESTAMP WITH TIME ZONE NOT NULL default NOW()
);
"""
# type: 0: unknown, 1:country, 2:city, 4:spot
# mfw level: in reference to PLACE_LEVEL
if __name__ == '__main__':
conn = db.get_conn()
db.execute_sql(conn,SQL)
conn.commit()