kml_to_sql.py

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