#
# simple KML generator for iPhone locationd database
#
# David Schuetz
# Intrepidus Group
# April, 2011
#
# version 1.01
# * fixed the missing __main__ section
# * added average option
#
# Provide the consolidated.db filename with --file, and optionally a
# name for the device (in case you want to plot multiple devices)
#
# You may also provide a bounding box with --west, --south, --east, --north
#
# The --average option will average together all the WiFi and Cell locations
# for each data set (so all the wifi locations fetched at the same time
# are averaged to a single point).
#
# Note that if you don't filter and have a lot of data (like a years' worth
# of phone travel), Google Earth will probably slow to a crawl. :)
#
import sys, time
import sqlite3
from getopt import getopt
avgPoints = False
tables = ['cell', 'cellh', 'cellloc', 'wifi', 'wifih']
def main():
global conn, avgPoints
opts, args = getopt(sys.argv[1:],'',['average', 'file=', 'west=', 'east=', 'south=', 'north=', 'table=', 'device='])
filename = ''
west = 0
east = 0
south = 0
north = 0
table = ''
device = 'iOS'
if opts:
for opt, value in opts:
if opt == '--file':
filename = value
elif opt == '--west':
west = value
elif opt == '--east':
east = value
elif opt == '--south':
south = value
elif opt == '--north':
north = value
elif opt == '--table':
table = value
elif opt == '--device':
device = value
elif opt == '--average':
avgPoints = True
if filename == '':
print "Need to supply a filename with --file"
sys.exit(0)
else:
conn = sqlite3.connect(filename)
printKmlHeader(device)
if table != '':
doTable(table, west, east, south, north)
else:
for table in tables:
doTable(table, west, east, south, north)
print "\n"
def doTable(table, west, east, south, north):
if table == 'cell':
title = "Cell Location"
elif table == 'wifi':
title = 'WiFi Location'
elif table == 'cellloc':
title = 'Cell Location Local'
elif table == 'cellh':
title = 'Cell Location Harvest'
elif table == 'wifih':
title = 'WiFi Location Harvest'
print """
%s
0
0
""" % (table, title)
points = getPoints(table, west, east, south, north)
for point in points:
printPoint(point)
print "\n"
def printKmlHeader(device):
print """
%s Data Points
normal
#sn_placemark_circle
highlight
#sh_placemark_circle_highlight
""" % device
def getPoints(table, west, east, south, north):
global conn
offset = 978307200 # from online calc, little more accurate than 31 * 365.25 * 60 * 60 * 24
points = []
c = conn.cursor()
if table == 'wifi':
if avgPoints:
sql = "SELECT count(Timestamp) || ' WiFi Locations', Timestamp, avg(Longitude), avg(Latitude) from WifiLocation GROUP BY Timestamp"
else:
sql = "SELECT MAC, Timestamp, Longitude, Latitude from WifiLocation"
elif table == 'wifih':
sql = "SELECT MAC, Timestamp, Longitude, Latitude from WifiLocationHarvest"
elif table == 'cell':
if avgPoints:
sql = "SELECT count(Timestamp) || ' Cell Locations', Timestamp, Longitude, Latitude from CellLocation GROUP BY Timestamp"
else:
sql = "SELECT MCC || ', ' || MNC || ', '|| LAC || ', ' || CI , Timestamp, Longitude, Latitude from CellLocation"
elif table == 'cellh':
sql = "SELECT MCC || ', ' || MNC || ', '|| LAC || ', ' || CI , Timestamp, Longitude, Latitude from CellLocationHarvest"
elif table == 'cellloc':
sql = "SELECT MCC || ', ' || MNC || ', '|| LAC || ', ' || CI , Timestamp, Longitude, Latitude from CellLocationLocal"
parms = []
if west != 0 and east != 0:
filter = "Longitude > ? AND Longitude < ?"
parms.append(west)
parms.append(east)
if south != 0 and north != 0:
latFilter = "Latitude > ? AND Latitude < ?"
if filter != '':
filter = '%s AND %s' % (filter, latFilter)
else:
filter = latFilter
parms.append(south)
parms.append(north)
if len(parms) > 0:
sql += " WHERE %s" % filter
try:
c.execute(sql, parms)
for pt in c:
name = pt[0]
t = time.gmtime(float(pt[1]) + offset)
timeStr = time.strftime('%Y-%m-%d %H:%M:%S', t)
timeStamp = time.strftime('%Y-%m-%dT%H:%M:%SZ', t)
lon = float(pt[2])
lat = float(pt[3])
point = [name, timeStr, lon, lat, timeStamp]
points.append(point)
except:
points = ()
return points
def printPoint(point):
print """
%s %s
#msn_placemark_circle
%s,%s
%s
""" % (point[0], point[1], point[2], point[3], point[4])
if __name__ == "__main__":
main()