Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
kml_to_sql.py
#!/usr/bin/env python2.7
# -*- coding: utf-8 -*-
import codecs
import re
import sys
from pykml import parser
sys.stdout = codecs.getwriter('utf8')(sys.stdout)
if len(sys.argv) <= 1:
print("Usage: %s <kml file>" % sys.argv[0])
sys.exit(0)
kml_str = ""
for l in open(sys.argv[1]):
kml_str = kml_str + l
root = parser.fromstring(kml_str)
table_name = "seichi"
"""
CREATE TABLE seichi (
sid serial primary key,
name text not null,
descr text,
img text,
lat float8 not null,
lon float8 not null
);
CREATE UNIQUE INDEX seichi_latlon_idx ON seichi(lat,lon);
"""
print "INSERT INTO %s (name,descr,img,lat,lon) VALUES" % table_name
count = 0
for f in root.Document.Folder:
for p in f.Placemark:
name = unicode(p.name).rstrip()
img = ""
try:
d = unicode(p.description)
desc = re.sub("<.*>", "", d)
m = re.search(r".*img src=\"(https:[^\"]+)\".*", d)
if m is not None:
img = m.group(1)
except AttributeError as e:
desc = u""
t = unicode(p.Point.coordinates).split(',')
lat = t[0]
lon = t[1]
if count > 0:
print " ,",
else:
print " ",
print "('" + name + "','" + desc + "','" + img + "'," + lat + "," + lon + ")"
count += 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment