Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
tsp_to_kml.py
#!/usr/bin/env python2.7
# -*- coding: utf-8 -*-
import codecs
import psycopg2
import re
import sys
sys.stdout = codecs.getwriter('utf8')(sys.stdout)
kml = u"""
<?xml version='1.0' encoding='UTF-8'?>
<kml xmlns='http://www.opengis.net/kml/2.2'>
<Document>
<Style id="line-DB4436-5-nodesc">
<LineStyle>
<color>ff0000ff</color>
<width>5</width>
</LineStyle>
</Style>
<name>Copy of 【君の名は。】聖地巡礼マップ【8/31現在】</name>
<description><![CDATA[]]></description>
<Folder>
<name>巡礼最短経路</name>
"""
query = """
WITH path AS (
SELECT
t.seq,
t.id2,
s.lat,
s.lon,
lag(s.lat) OVER (ORDER BY seq) lag_lat,
lag(s.lon) OVER (ORDER BY seq) lag_lon
FROM
pgr_tsp('SELECT sid id, lat x, lon y FROM seichi ORDER BY sid', 1) t
LEFT OUTER JOIN seichi s ON t.id2 = s.sid
)
SELECT
*
FROM
path
WHERE
lag_lat IS NOT NULL
"""
conn = psycopg2.connect("dbname=pgr")
cur = conn.cursor()
cur.execute(query)
line_str = ""
for r in cur.fetchall():
kml = kml + '''
<Placemark>
<name>Path %s</name>
<styleUrl>#line-DB4436-5-nodesc</styleUrl>
<LineString>
<coordinates>%s,%s,0 %s,%s,0</coordinates>
</LineString>
</Placemark>
''' % (r[0], r[2], r[3], r[4], r[5])
kml = kml + """
</Folder>
</Document>
</kml>
"""
print (kml)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment