Stuart Gordon

import json

# Path to the aircraft.json file
file_path = '/path/to/aircraft.json'

# Read data from aircraft.json
with open(file_path, 'r') as file:
    aircraft_data = json.load(file)
    

import pika
import ssl

# RabbitMQ server details
rabbitmq_server = 'your_rabbitmq_server'
exchange_name = 'your_exchange_name'
routing_key = 'your_routing_key'
username = 'your_username'
password = 'your_password'

# SSL context for AMQPS
context = ssl.create_default_context(ssl.Purpose.SERVER_AUTH)
context.verify_mode = ssl.CERT_REQUIRED

# Connect to RabbitMQ
credentials = pika.PlainCredentials(username, password)
parameters = pika.ConnectionParameters(host=rabbitmq_server,
                                       virtual_host='/',
                                       credentials=credentials,
                                       ssl_options=pika.SSLOptions(context),
                                       port=5671)  # Default port for AMQPS
connection = pika.BlockingConnection(parameters)
channel = connection.channel()

# Declare the exchange
channel.exchange_declare(exchange=exchange_name, exchange_type='topic', durable=True)
    

# Publish data to the exchange with the routing key
channel.basic_publish(exchange=exchange_name,
                      routing_key=routing_key,
                      body=json.dumps(aircraft_data),
                      properties=pika.BasicProperties(
                          delivery_mode=2,  # persistent message
                      ))

# Close the connection
connection.close()
    

import json
import base64
import boto3
import os
import uuid
import datetime

def is_json(myjson):
    try:
        json.loads(myjson)
    except ValueError as e:
        return False
    return True


S3_BUCKET=os.getenv('s3Bucket')
S3_BUCKET_FOLDER=os.getenv('s3BucketFolder')

s3 = boto3.client('s3')


def put_s3_object(content, queues, message_format):
    # print(content, queues, message_format)
    date_time = datetime.datetime.now()
    date_timestamp = date_time.strftime("%Y%m%d_%H%M%S_%f")
    file_name = '/'.join([f"{queues}", f"date={date_time.year}{date_time.month:02d}{date_time.day:02d}", f"{str(uuid.uuid4())}"])
    full_file_name = S3_BUCKET_FOLDER + "/" + file_name + '.' + message_format
    multiline_string = '\r\n'.join(content)
    s3.put_object(Body=multiline_string,Key=full_file_name,Bucket=S3_BUCKET)

def lambda_handler(event, context):
    # print(event)
    for queues in event['rmqMessagesByQueue']:
        print (f"Queue: {queues}")
        messages = []
        for message in event['rmqMessagesByQueue'][queues]:
            data = base64.b64decode(message['data']).decode('ascii')
            if is_json(data):
                messages.append(data)

        print (f"Decoded messages in {queues} - {len(messages)}")
        put_s3_object(messages, queues, "json")

import boto3
import time
import pydeck as pdk
import folium
import os
os.environ['USE_PYGEOS'] = '0'
import geopandas as gpd
import pandas as pd
from shapely.geometry import Point
from datetime import datetime
import h3
import matplotlib.pyplot as plt
import seaborn as sns
import branca.colormap as cm
import numpy as np

# Load your data into a DataFrame
flight_data = pd.read_csv('fr24-z.csv')

def custom_parse_geospatial_track(track_str):
    # Splitting the string into individual track points
    track_points = track_str.strip('[]').split('}, {')
    
    # Parsing each track point
    track = []
    for point in track_points:
        # Cleaning and splitting each point into lat, lon, and timestamp
        point_clean = point.strip('{}').split(', ')
        if len(point_clean) >= 2:
            lat, lon = point_clean[0], point_clean[1]
            try:
                # Attempt to convert lat and lon to float and add to track
                track.append((float(lat), float(lon)))
            except ValueError:
                # If conversion fails, skip this point
                continue

    return track

# Function to check if the file exists in S3
def check_file_exists(bucket_name, s3_file_key):
    s3 = boto3.client('s3')
    try:
        s3.head_object(Bucket=bucket_name, Key=s3_file_key)
        return True
    except s3.exceptions.ClientError:
        return False

# Function to download file from S3
def download_file_from_s3(bucket_name, s3_file_key, local_file_path):
    s3 = boto3.client('s3')
    s3.download_file(bucket_name, s3_file_key, local_file_path)

def query_athena_and_download(query, database, s3_output, bucket):
    athena = boto3.client('athena')

    # Start the query execution
    response = athena.start_query_execution(
        QueryString=query,
        QueryExecutionContext={'Database': database},
        ResultConfiguration={'OutputLocation': s3_output}
    )
    query_execution_id = response['QueryExecutionId']

    # Wait for the query to complete
    while True:
        response = athena.get_query_execution(QueryExecutionId=query_execution_id)
        state = response['QueryExecution']['Status']['State']
        if state in ['SUCCEEDED', 'FAILED', 'CANCELLED']:
            break
        time.sleep(1)  # Wait before polling again

    if state == 'SUCCEEDED':
        # Correcting the result file key
        # Assuming s3_output is something like 's3://bucket_name/path/to/output/'
        result_file_key = s3_output.replace('s3://{}/'.format(bucket), '') + query_execution_id + '.csv'

        # Check if the file exists in S3 and wait if it doesn't
        while not check_file_exists(bucket, result_file_key):
            time.sleep(1)  # Wait and check again

        local_file_path = 'result.csv'  # Local file path for downloaded result

        # Download the result file from S3
        download_file_from_s3(bucket, result_file_key, local_file_path)

        # Load the result into a Pandas DataFrame
        df = pd.read_csv(local_file_path)

        return df
    else:
        raise Exception(f"Query failed: {state}")

# Example usage
athena_query = """
    SELECT * FROM "fr24"."aircraft_smmary"
    """
athena_database = "fr24"
s3_output_path = "s3://open-data-archive/applications/athena/"

# Extract bucket name and file key from s3_output_path
bucket_name = 'open-data-archive'
df = query_athena_and_download(athena_query, athena_database, s3_output_path, bucket_name)

def custom_parse_geospatial_track(track_str):
    track_points = track_str.strip('[]').split('}, {')
    track = []
    for point in track_points:
        point_clean = point.strip('{}').split(', ')
        if len(point_clean) >= 4:
            lat, lon, alt, timestamp = point_clean[0], point_clean[1], point_clean[2], point_clean[3]
            try:
                # Parse the timestamp
                timestamp = datetime.strptime(timestamp, '%Y-%m-%d %H:%M:%S.%f')
                # Create a tuple of Point and timestamp
                track.append((Point(float(lat), float(lon), float(alt)), timestamp))
            except ValueError:
                continue
    return track

track_data = []

for _, row in df.iterrows():
    track_points = custom_parse_geospatial_track(row['geospatial_track'])
    for point, timestamp in track_points:
        track_point_data = row.to_dict()
        track_point_data['geometry'] = point
        track_point_data['timestamp'] = timestamp
        track_point_data['id'] = str(track_point_data['flight']) + "_" + str(track_point_data['hex'])
        track_data.append(track_point_data)


track_df = pd.DataFrame(track_data)

geo_track_df = gpd.GeoDataFrame(track_df, geometry='geometry')

# Extracting longitude, latitude, and altitude from the geometry column
geo_track_df['longitude'] = geo_track_df['geometry'].x
geo_track_df['latitude'] = geo_track_df['geometry'].y
geo_track_df['altitude'] = geo_track_df['geometry'].z * 0.3048

def haversine(lat1, lon1, lat2, lon2):
    # Radius of the Earth in km
    R = 6371.0
    
    # Convert coordinates from degrees to radians
    lat1_rad = np.radians(lat1)
    lon1_rad = np.radians(lon1)
    lat2_rad = np.radians(lat2)
    lon2_rad = np.radians(lon2)

    # Difference in coordinates
    dlat = lat2_rad - lat1_rad
    dlon = lon2_rad - lon1_rad

    # Haversine formula
    a = np.sin(dlat / 2)**2 + np.cos(lat1_rad) * np.cos(lat2_rad) * np.sin(dlon / 2)**2
    c = 2 * np.arctan2(np.sqrt(a), np.sqrt(1 - a))

    # Distance in kilometers
    distance = R * c
    return distance

def calculate_bearing(lat1, lon1, lat2, lon2):
    # Convert coordinates from degrees to radians
    lat1_rad = np.radians(lat1)
    lon1_rad = np.radians(lon1)
    lat2_rad = np.radians(lat2)
    lon2_rad = np.radians(lon2)

    # Calculate the bearing
    dlon = lon2_rad - lon1_rad
    x = np.sin(dlon) * np.cos(lat2_rad)
    y = np.cos(lat1_rad) * np.sin(lat2_rad) - np.sin(lat1_rad) * np.cos(lat2_rad) * np.cos(dlon)
    initial_bearing = np.arctan2(x, y)

    # Convert bearing from radians to degrees
    initial_bearing = np.degrees(initial_bearing)
    compass_bearing = (initial_bearing + 360) % 360

    return compass_bearing

def total_distance_and_bearing(row, target_lat, target_lon, target_alt):
    # Calculate geographic distance
    geo_dist = haversine(row['longitude'], row['latitude'], target_lat, target_lon)
    
    # Calculate bearing
    bearing = calculate_bearing(target_lat, target_lon, row['longitude'], row['latitude'])

    # Convert altitude from feet to meters, then to kilometers
    row_altitude_km = row['altitude'] / 1000
    target_alt_km = target_alt / 1000

    # Calculate altitude difference in kilometers
    alt_diff = abs(row_altitude_km - target_alt_km)

    # Total distance considering both geographic and altitude differences
    total_dist = np.sqrt(geo_dist**2 + alt_diff**2)
    
    return total_dist, bearing

# Target location coordinates and altitude in meters
target_lat, target_lon, target_alt = 56.2, -4.6, 10

# Apply the distance and bearing calculation for each row in your DataFrame
geo_track_df[['distance_to_target', 'bearing_to_target']] = geo_track_df.apply(
    lambda row: total_distance_and_bearing(row, target_lat, target_lon, target_alt), 
    axis=1, result_type='expand')

# Assuming geo_track_df is your GeoDataFrame
# Add a column for the H3 index at a specified resolution (e.g., resolution 7)
resolution = 6
geo_track_df['h3_index'] = geo_track_df.apply(lambda row: h3.geo_to_h3(row['geometry'].y, row['geometry'].x, resolution), axis=1)

# Aggregate data by H3 index
# You can adjust the aggregation functions as per your requirements
agg_data = geo_track_df.groupby('h3_index').agg({
    'timestamp': ['count', 'min', 'max'],
    'min_altitude': ['mean', 'min', 'max'],
    'max_altitude': ['mean', 'min', 'max'],
    # Add other fields as necessary
}).reset_index()

# Renaming columns for clarity
agg_data.columns = ['_'.join(col).strip() for col in agg_data.columns.values]
agg_data.rename(columns={'h3_index_': 'h3_index'}, inplace=True)

def custom_parse_geospatial_track_for_pydeck(track_str, min_altitude, max_altitude):
    track_points = track_str.strip('[]').split('}, {')
    track = []
    for point in track_points:
        point_clean = point.strip('{}').split(', ')
        if len(point_clean) >= 2:
            lat, lon = point_clean[0], point_clean[1]
            # Using the average of min and max altitude as a placeholder for altitude
            altitude = (min_altitude + max_altitude) / 2
            try:
                # Swapping lat and lon to correct the order
                track.append({'position': [float(lon), float(lat), float(altitude)]})
            except ValueError:
                continue
    return track

# Function to map altitude to a color on a red-to-green scale
def altitude_to_color(altitude, min_altitude, max_altitude):
    # Normalize altitude in the range 0 to 1
    normalized_altitude = (altitude - min_altitude) / (max_altitude - min_altitude)
    
    # Red to green color scale
    red = int((1 - normalized_altitude) * 255)
    green = int(normalized_altitude * 255)
    blue = 0  # No blue component

    return [red, green, blue, 200]  # RGBA color


# Finding the minimum and maximum altitudes in the dataset
min_altitude = df['min_altitude'].min()
max_altitude = df['max_altitude'].max()

# Preparing the data for pydeck visualization with altitude-based color
data_for_pydeck_with_color = []
for _, row in df.iterrows():
    track = custom_parse_geospatial_track_for_pydeck(row['geospatial_track'], row['min_altitude'], row['max_altitude'])
    if track:
        # Flatten the track into individual segments for pydeck LineLayer
        for i in range(len(track) - 1):
            # Determine the color based on the altitude
            color = altitude_to_color((track[i]['position'][2] + track[i + 1]['position'][2]) / 2, min_altitude, max_altitude)
            segment = {
                'source_position': track[i]['position'],
                'target_position': track[i + 1]['position'],
                'color': color,
                'flight': row['flight'],
                'hex': row['hex'],
            }
            data_for_pydeck_with_color.append(segment)