georoutes.py

georoutes.py

def build_routes():
    """ return a geojson object with flight paths over last day """
    conn = database_connect()
    cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    output = cur.execute(
        """
        SELECT DISTINCT(tail)
        AS tail
        FROM planedemo_routes
        WHERE lat != ''
        AND tail != 'None'
        AND arrival_timestamp > (clock_timestamp() - '1 hour'::interval)
        """
    )
    results = cur.fetchall()
    geojson = []
    for i in results:
        sql = """SELECT lat,long FROM planedemo_routes
                 WHERE tail = '{}'
                 AND lat != ''
                 AND arrival_timestamp >
                 (clock_timestamp() - '1 hour'::interval)
                 """.format(i['tail'])
        coords = cur.execute(sql)
        nested_output = cur.fetchall()
        coordinates = []
        for ii in nested_output:
            latlong = [ii['long'], ii['lat']]
            coordinates.append(latlong)

        georow = {"type": "Feature",
                  "properties": {
                        "stroke": "#008e00",
                        "stroke-width": "2",
                        "stroke-opacity": ".2"
                   },
                  "geometry": {
                        "type": "LineString",
                        "coordinates":  coordinates
                   }
                  }

        geojson.append(georow)

    return json.dumps(geojson)