Untitled

import psycopg2
import networkx as nx
import time

# Database connection parameters
hostname = 'localhost'
database = 'GraphSQL'
username = 'qs2215'
password = '****'
port = '9090'

def fetch_road_network():
    start_time = time.time()
    try:
        # Connect to the PostgreSQL database
        conn = psycopg2.connect(
            dbname=database,
            user=username,
            password=password,
            host=hostname,
            port=port
        )
        cursor = conn.cursor()

        # Update the query to match the actual column names
        query = 'SELECT source_node_id, destination_node_id FROM "Road networks"."roadNet-TX";'
        cursor.execute(query)

        # Fetch all rows from the query result
        rows = cursor.fetchall()
        print("Data fetched successfully.")
        return rows

    except Exception as error:
        print("Error while connecting to PostgreSQL:", error)
        return None
    finally:
        if conn:
            cursor.close()
            conn.close()
        print("PostgreSQL connection is closed")
        print(f"Data fetching took {time.time() - start_time:.2f} seconds")

def create_graph(data):
    start_time = time.time()
    G = nx.Graph()  # Ensure your graph is created correctly

    # Each row in data is a tuple (source_node_id, destination_node_id)
    for source_node_id, destination_node_id in data:
        G.add_edge(source_node_id, destination_node_id, weight=1)  # Assuming weight = 1

    print(f"Graph creation took {time.time() - start_time:.2f} seconds")
    return G


def calculate_graph_metrics(G):
    # Nodes and Edges
    num_nodes = G.number_of_nodes()
    print(f"Nodes: {num_nodes}")

    num_edges = G.number_of_edges()
    print(f"Edges: {num_edges}")

    # Largest Connected Component
    largest_cc = max(nx.connected_components(G), key=len)
    print(f"Nodes in largest CC: {len(largest_cc)}")
    cc_graph = G.subgraph(largest_cc)
    print(f"Edges in largest CC: {len(cc_graph.edges())}")

    # Average Clustering Coefficient
    avg_clustering_coeff = nx.average_clustering(G)
    print(f"Average clustering coefficient: {avg_clustering_coeff}")

    # Number of Triangles
    triangles = sum(nx.triangles(G).values()) // 3
    print(f"Number of triangles: {triangles}")

    # Fraction of Closed Triangles
    triplets = sum(nx.triangles(G).values()) / 3
    closed_triangles = nx.transitivity(G)
    print(f"Fraction of closed triangles: {closed_triangles}")


    # Diameter (it can be very computationally expensive)
    diameter = nx.diameter(G.subgraph(largest_wcc))
    print(f"Diameter: {diameter}")

    # 90-percentile Effective Diameter
    effective_diameter = nx.effective_diameter(G, percentile=0.9)

    

    print(f"90-percentile effective diameter: {effective_diameter}")

def find_diameter(G):
    start_time = time.time()
    try:
        # Find all connected components
        components = nx.connected_components(G)
        # Calculate the diameter for each component
        diameters = [nx.diameter(G.subgraph(component)) for component in components]
        # Take the maximum diameter across all components
        max_diameter = max(diameters)
        print(f"The maximum diameter across all connected components is: {max_diameter}")
        print(f"Diameter calculation took {time.time() - start_time:.2f} seconds")
        return max_diameter
    except Exception as error:
        print("Failed to calculate the diameter:", error)

# Fetch road network data
start_time = time.time()
road_network_data = fetch_road_network()

# Ensure road_network_data is not None before proceeding
if road_network_data is not None:
    # Create a graph from the road network data
    G = create_graph(road_network_data)
    calculate_graph_metrics(G)
    # Calculate the maximum diameter across all connected components of the graph
    max_diameter = find_diameter(G)
else:
    print("Failed to fetch road network data.")

print(f"Total runtime: {time.time() - start_time:.2f} seconds")