big_warps.py

"""
Copyleft 2013  ISD LackOfFaith; Do whatever with this
"""

import csv, sqlite3, sys

conn = sqlite3.connect(sys.argv[1])
db = conn.cursor()

def distance(point1, point2):
    return (
        (point1[0]-point2[0])**2 +
        (point1[1]-point2[1])**2 +
        (point1[2]-point2[2])**2
        )**0.5

def m_to_au(m):
    return m/149597870700.

def gate_name(g_id):
    Q_GATE_NAME = """
        select mapsolarsystems.solarsystemname
        from mapsolarsystems
        inner join invitems, mapjumps on
            invitems.locationid=mapsolarsystems.solarsystemid and
            mapjumps.celestialid=invitems.itemid
        where mapjumps.stargateid=?"""
    results = db.execute(Q_GATE_NAME, [g_id])
    try:
        return results.next()[0]
    except StopIteration:
        return None

def system_name(s_id):
    Q_SYSTEM_NAME = """
        select mapsolarsystems.solarsystemname
        from mapsolarsystems
        where mapsolarsystems.solarsystemid=?"""
    results = db.execute(Q_SYSTEM_NAME, [s_id])
    if results:
        return results.next()[0]
    else:
        return None


Q_STARGATE = """
        select invitems.itemid, invitems.locationid
        from invitems
        inner join invtypes, invgroups on
              invitems.typeid=invtypes.typeid and
              invtypes.groupid=invgroups.groupid
        where invgroups.groupname="Stargate" """;


system_gates = {}

for gate_id, system_id in db.execute(Q_STARGATE):
    if system_id not in system_gates:
        system_gates[system_id] = []
    system_gates[system_id].append(gate_id)


Q_GATE_LOCATION = """
        select mapdenormalize.x, mapdenormalize.y, mapdenormalize.z
        from mapdenormalize
        inner join invitems on
            invitems.itemid=mapdenormalize.itemid
        where invitems.itemid=?
        """

tmpgates = system_gates[system_gates.keys()[200]]

global_max = 0
global_gate1 = None
global_gate2 = None
global_system = None

max_warps = []

for system_id in system_gates:
    gate_coords = {}

    for gate_id in system_gates[system_id]:
        gate_coords[gate_id] = db.execute(Q_GATE_LOCATION, [gate_id]).next()

    max_dist = 0
    max_gate1 = None
    max_gate2 = None

    print
    print system_name(system_id)
    print "-----------------------"
    for gate1 in gate_coords:
        for gate2 in gate_coords:
            dist = distance(gate_coords[gate1], gate_coords[gate2])

            print "   ", m_to_au(dist), gate_coords[gate1], gate_coords[gate2]
            if dist > max_dist:
                max_dist = dist
                max_gate1 = gate1
                max_gate2 = gate2

    if max_dist:
        print "MAX", system_id, m_to_au(max_dist), gate_name(max_gate1), gate_name(max_gate2)

    if max_dist > global_max:
        global_max = max_dist
        global_gate1 = max_gate1
        global_gate2 = max_gate2
        global_system = system_id

    max_warps.append((max_dist, system_id, max_gate1, max_gate2))

print "Maximum gate-gate warp was..."
print "\tin system %s" % system_name(global_system)
print "\ta distance of %.2f AU" % m_to_au(global_max)
print "\tfrom Stargate (%s) to Stargate (%s)" % (gate_name(global_gate1), gate_name(global_gate2))

max_warps.sort(cmp=lambda x,y: cmp(x[0],y[0]))
max_warps.reverse()

with open("maxdists.csv", "w") as of:
    writer = csv.writer(of)
    writer.writerow(["System","Gate 1","Gate2","Distance (AU)"])
    for max_dist, system_id, max_gate1, max_gate2 in max_warps:
        if not max_dist:
            continue
        writer.writerow([
                system_name(system_id),
                gate_name(max_gate1),
                gate_name(max_gate2),
                "%.2f" % m_to_au(max_dist)
                ])