from geopy.distance import geodesic import mysql.connector import json import random import numpy as np from sklearn.cluster import DBSCAN from shapely.geometry import Point, LineString, MultiPoint, Polygon from shapely.ops import transform from pyproj import Proj, transform as pyproj_transform import numpy as np from scipy.spatial.distance import cdist def generate_random_color(): # """Generate a random color in HEX format.""" return "#{:02x}{:02x}{:02x}".format(random.randint(0, 255), random.randint(0, 255), random.randint(0, 255)) def haversine(lat1, lon1, lat2, lon2): """Vectorized haversine formula.""" R = 6371 # Radius of the Earth in km lat1, lon1, lat2, lon2 = map(np.radians, [lat1, lon1, lat2, lon2]) dlat = lat2 - lat1 dlon = lon2 - lon1 a = np.sin(dlat / 2) ** 2 + np.cos(lat1) * np.cos(lat2) * np.sin(dlon / 2) ** 2 c = 2 * np.arctan2(np.sqrt(a), np.sqrt(1 - a)) return R * c def haversine_distance_matrix(coordinates): """Compute the distance matrix using vectorization.""" coords = np.radians(coordinates) # Convert to radians for haversine lat = coords[:, 0][:, np.newaxis] lon = coords[:, 1][:, np.newaxis] return haversine(lat, lon, lat.T, lon.T) # def haversine_distance_matrix(coordinates): # # """Calculate the distance matrix for DBSCAN clustering using haversine formula.""" # distance_matrix = np.zeros((len(coordinates), len(coordinates))) # for i, coord1 in enumerate(coordinates): # for j, coord2 in enumerate(coordinates): # if i != j: # distance_matrix[i][j] = geodesic(coord1, coord2).km # return distance_matrix def group_clients_by_sales(min_chiffre, max_chiffre): # Connect to the database conn = mysql.connector.connect( host="51.77.140.160", user="bassem", password="Clediss1234++", database="dist_utic", port=3306 ) try: cursor = conn.cursor(dictionary=True) # Fetch client data query = """ SELECT c.id, c.code, c.nom, c.prenom, c.latitude, c.longitude, (SELECT SUM(e.net_a_payer) FROM entetecommercials as e WHERE e.client_code=c.code AND e.type IN ('bl', 'avoir')) AS chiffre_vente FROM clients as c WHERE c.isactif=1 AND c.latitude IS NOT NULL AND c.longitude IS NOT NULL """ cursor.execute(query) data = cursor.fetchall() # Preprocess data clients = [] coordinates = [] for client in data: chiffre_vente = client.get('chiffre_vente', 0) or 0 latitude = float(client['latitude']) if client['latitude'] else None longitude = float(client['longitude']) if client['longitude'] else None if latitude and longitude: clients.append({ "id": client["id"], "code": client["code"], "nom": client["nom"], "prenom": client["prenom"], "latitude": latitude, "longitude": longitude, "chiffre_vente": chiffre_vente }) coordinates.append((longitude , latitude)) # Use DBSCAN to cluster clients based on GPS proximity distance_matrix = haversine_distance_matrix(coordinates) dbscan = DBSCAN(eps=2, min_samples=1, metric="precomputed") labels = dbscan.fit_predict(distance_matrix) # Group clients by cluster clusters = {} for label, client in zip(labels, clients): if label not in clusters: clusters[label] = { "clients": [], "total_sales": 0, "color": generate_random_color(), "coordinates": [], "polygon_area": 0, # Initialize polygon_area key "layer_polygon_wkt": None, # Initialize WKT key "layer_polygon": [] # Initialize polygon coordinates } clusters[label]["clients"].append(client) clusters[label]["total_sales"] += client["chiffre_vente"] clusters[label]["coordinates"].append((client["longitude"], client["latitude"])) # Calculate additional geometric data for group in clusters.values(): coordinates = group['coordinates'] group['line'] = coordinates # Ordered list of GPS points # Calculate total line length if len(coordinates) >= 2: line = LineString(coordinates) group['line_length'] = line.length / 1000 # Convert from meters to kilometers else: group['line_length'] = 0 # No line possible with fewer than two points # Generate enclosing convex hull polygon if len(coordinates) >= 3: points = MultiPoint([Point(lat, lon) for lat, lon in coordinates]) polygon = points.convex_hull # Convex hull polygon if isinstance(polygon, Polygon): group['layer_polygon_wkt'] = polygon.wkt # WKT representation of the polygon # Calculate the area and length using a projected metric proj = Proj(init='epsg:4326') # WGS84 coordinate system transformer = Proj(init='epsg:3395') # Mercator projection polygon_metric = transform(transformer.transform, polygon) if polygon.is_empty: group['layer_polygon'] = [] group['polygon_area'] = 0 # m² to km² else: group['layer_polygon'] = list(map(list, polygon.exterior.coords)) group['polygon_area'] = polygon_metric.area / 1_000_000 # m² to km² else: group['layer_polygon_wkt'] = None group['layer_polygon'] = [] else: group['layer_polygon_wkt'] = None group['layer_polygon'] = [] # Filter clusters by sales range valid_groups = [ { "group_id": i + 1, "clients": group["clients"], "total_sales": group["total_sales"], "color": group["color"], "line": group["line"], "line_length": group["line_length"], "layer_polygon_wkt": group["layer_polygon_wkt"], "layer_polygon": group["layer_polygon"], "polygon_area": group["polygon_area"] } for i, group in enumerate(clusters.values()) # if min_chiffre <= group["total_sales"] <= max_chiffre ] # Output results as JSON response = json.dumps(valid_groups, indent=4, ensure_ascii=False) print(response) finally: conn.close() if __name__ == "__main__": import sys # Read min_chiffre and max_chiffre from arguments if len(sys.argv) != 3: print("Usage: python Tournees.py ") sys.exit(1) min_chiffre = float(sys.argv[1]) max_chiffre = float(sys.argv[2]) group_clients_by_sales(min_chiffre, max_chiffre)