spa-l2-cairo-shop-knn

Rerun

import geopandas as gpd

# Load the layers
shops = gpd.read_file('/work/cairo_retail.gpkg', layer='shops')
anchors = gpd.read_file('/work/cairo_retail.gpkg', layer='anchors')

print("Shops columns:", shops.columns.tolist())
print("Anchors columns:", anchors.columns.tolist())
print("Shops sample:\n", shops.head())
print("Anchors sample:\n", anchors.head())

import geopandas as gpd
import pandas as pd
import json
from scipy.spatial import cKDTree
import numpy as np

def normalize_name(name):
    if not name or pd.isna(name):
        return ""
    return " ".join(str(name).strip().upper().split())

def solve():
    # Load data
    shops = gpd.read_file('/work/cairo_retail.gpkg', layer='shops')
    anchors = gpd.read_file('/work/cairo_retail.gpkg', layer='anchors')

    # Standardize names
    shops['normalised_name'] = shops['raw_name'].apply(normalize_name)
    anchors['anchor_name_normalised'] = anchors['anchor_name'].apply(normalize_name)

    # The prompt says: "the same shop_id always carries the same normalised_name, 
    # and all shops belonging to one chain share a single canonical spelling."
    # Since we don't have a 'chain_id' column, I'll assume the normalization 
    # of raw_name is sufficient to handle "inconsistent spellings" 
    # (e.g. "OSCAR " vs "Oscar").
    # Let's check if there's any other indicator of a chain. 
    # Based on the inspection, there isn't. I will treat the normalized raw_name as the canonical name.
    
    # Ensure geometry is in metric CRS for distance calculations
    # The sample coords look like UTM (6xx, 8xx)
    if shops.crs is None:
        # If no CRS is provided, assume it is already in a metric projection based on the numbers
        # But let's check if we should transform.
        pass
    else:
        shops = shops.to_crs(epsg=3857) # A common metric projection, but we should use the original if it's already metric
        anchors = anchors.to_crs(shops.crs)

    # For distance calculations, let's extract coordinates
    shop_coords = np.array(list(shops.geometry.apply(lambda g: (g.x, g.y))))
    anchor_coords = np.array(list(anchors.geometry.apply(lambda g: (g.x, g.y))))

    shop_tree = cKDTree(shop_coords)
    anchor_tree = cKDTree(anchor_coords)

    output = []

    for i, anchor in anchors.iterrows():
        a_id = anchor['anchor_id']
        a_name = anchor['anchor_name_normalised']
        a_coord = anchor.geometry.coords[0]

        # 1. Find 5 nearest shops
        # k=5, but we need to be careful if there are fewer than 5 shops total
        k_shops = min(5, len(shops))
        distances_shops, indices_shops = shop_tree.query(a_coord, k=k_shops)
        
        # Handle case where k=1 (distances_shops becomes a scalar)
        if k_shops == 1:
            distances_shops = np.array([distances_shops])
            indices_shops = np.array([indices_shops])

        knn_list = []
        for dist, idx in zip(distances_shops, indices_shops):
            shop = shops.iloc[idx]
            knn_list.append({
                "shop_id": str(shop['shop_id']),
                "normalised_name": str(shop['normalised_name']),
                "distance_m": float(dist),
                "within_1km": bool(dist <= 1000.0)
            })

        # 2. Find 3 nearest sibling anchors
        # A "sibling" anchor is an anchor other than itself.
        # We query k=4 (self + 3 siblings)
        k_anchors = min(4, len(anchors))
        distances_anchors, indices_anchors = anchor_tree.query(a_coord, k=k_anchors)

        if k_anchors == 1:
            distances_anchors = np.array([distances_anchors])
            indices_anchors = np.array([indices_anchors])
        
        # Filter out the anchor itself
        sibling_indices = []
        sibling_distances = []
        for d, idx in zip(distances_anchors, indices_anchors):
            if anchors.iloc[idx]['anchor_id'] != a_id:
                sibling_indices.append(idx)
                sibling_distances.append(d)
            if len(sibling_indices) == 3:
                break
        
        # If we couldn't find 3 siblings, we'll just use what we have
        # (But the prompt implies we need a 5x3 matrix, so we assume 3 exist)
        # Let's pad if necessary (though the prompt suggests 5x3 is expected)
        
        # 3. Construct the 5x3 distance matrix
        # Rows: 5 shops (knn order), Cols: 3 sibling anchors (distance-ascending order)
        matrix = []
        for shop_knn in knn_list:
            row = []
            shop_coord = shops.iloc[shop_tree.query(shop_coord_from_knn_logic_needed_here, k=1)[1]] # wait, I have indices
            # Let's use the indices from the knn_list loop
            pass # Re-writing the loop below for clarity

        # Let's fix the logic in the loop
        
        # RE-DOING the anchor loop logic inside the loop for accuracy
        
    # Actually, let's rewrite the loop entirely in the script to be cleaner.
    pass

solve()

import geopandas as gpd
import pandas as pd
import json
from scipy.spatial import cKDTree
import numpy as np

def normalize_name(name):
    if not name or pd.isna(name):
        return ""
    return " ".join(str(name).strip().upper().split())

def solve():
    # Load data
    shops = gpd.read_file('/work/cairo_retail.gpkg', layer='shops')
    anchors = gpd.read_file('/work/cairo_retail.gpkg', layer='anchors')

    # Standardize names
    shops['normalised_name'] = shops['raw_name'].apply(normalize_name)
    anchors['anchor_name_normalised'] = anchors['anchor_name'].apply(normalize_name)

    # Ensure geometry is in metric CRS
    # Checking if CRS is already metric. The sample coords (637478, 814957) suggest UTM.
    # Let's check the CRS first.
    if shops.crs is None:
        # If no CRS is provided, we must assume one or define it. 
        # However, geopandas usually reads it if it's in the GPKG.
        # Since the coords look like metric, let's assume they are.
        pass
    else:
        # If it's WGS84 (degrees), we MUST transform to a metric CRS.
        if abs(shops.geometry.x.mean()) < 180:
            shops = shops.to_crs(epsg=3857) # or a better local one like 32636 for Egypt
            anchors = anchors.to_crs(shops.crs)

    # Extract coordinates for KDTree
    shop_coords = np.array(list(shops.geometry.apply(lambda g: (g.x, g.y))))
    anchor_coords = np.array(list(anchors.geometry.apply(lambda g: (g.x, g.y))))

    shop_tree = cKDTree(shop_coords)
    anchor_tree = cKDTree(anchor_coords)

    output = []

    for i, anchor in anchors.iterrows():
        a_id = str(anchor['anchor_id'])
        a_name = str(anchor['anchor_name_normalised'])
        a_coord = (anchor.geometry.x, anchor.geometry.y)

        # 1. Find 5 nearest shops
        k_shops = min(5, len(shops))
        distances_shops, indices_shops = shop_tree.query(a_coord, k=k_shops)
        
        if k_shops == 1:
            distances_shops = np.array([distances_shops])
            indices_shops = np.array([indices_shops])

        knn_list = []
        for dist, idx in zip(distances_shops, indices_shops):
            shop = shops.iloc[idx]
            knn_list.append({
                "shop_id": str(shop['shop_id']),
                "normalised_name": str(shop['normalised_name']),
                "distance_m": float(dist),
                "within_1km": bool(dist <= 1000.0),
                "coord": (shop.geometry.x, shop.geometry.y) # temporary for matrix calc
            })

        # 2. Find 3 nearest sibling anchors
        # Query k=4 to account for self
        k_anchors_query = min(4, len(anchors))
        distances_anchors, indices_anchors = anchor_tree.query(a_coord, k=k_anchors_query)
        
        if k_anchors_query == 1:
            distances_anchors = np.array([distances_anchors])
            indices_anchors = np.array([indices_anchors])

        sibling_indices = []
        sibling_distances = []
        for d, idx in zip(distances_anchors, indices_anchors):
            if str(anchors.iloc[idx]['anchor_id']) != a_id:
                sibling_indices.append(idx)
                sibling_distances.append(d)
            if len(sibling_indices) == 3:
                break
        
        # 3. Construct the 5x3 distance matrix
        # Rows follow knn order, columns correspond to the sibling anchors in distance-ascending order
        matrix = []
        for shop_entry in knn_list:
            row = []
            s_coord = shop_entry['coord']
            for s_idx in sibling_indices:
                # Distance between shop and sibling anchor
                sib_coord = anchor_coords[s_idx]
                # Euclidean distance for the metric projection
                d_m = np.sqrt((s_coord[0] - sib_coord[0])**2 + (s_coord[1] - sib_coord[1])**2)
                row.append(float(d_m))
            matrix.append(row)

        # Clean up knn_list for JSON (remove temporary coord)
        final_knn = []
        for item in knn_list:
            cleaned = {k: v for k, v in item.items() if k != 'coord'}
            final_knn.append(cleaned)

        output.append({
            "anchor_id": a_id,
            "anchor_name_normalised": a_name,
            "knn": final_knn,
            "full_distance_matrix_m": matrix
        })

    with open('/work/market_neighbourhoods.json', 'w') as f:
        json.dump(output, f, indent=2)

if __name__ == "__main__":
    solve()

import geopandas as gpd
import pandas as pd
import json
import numpy as np

def normalize_name(name):
    if not name or pd.isna(name):
        return ""
    return " ".join(str(name).strip().upper().split())

def solve():
    # Load data
    shops = gpd.read_file('/work/cairo_retail.gpkg', layer='shops')
    anchors = gpd.read_file('/work/cairo_retail.gpkg', layer='anchors')

    # Standardize names
    shops['normalised_name'] = shops['raw_name'].apply(normalize_name)
    anchors['anchor_name_normalised'] = anchors['anchor_name'].apply(normalize_name)

    # Ensure geometry is in metric CRS
    if shops.crs is None:
        pass
    else:
        if abs(shops.geometry.x.mean()) < 180:
            shops = shops.to_crs(epsg=3857)
            anchors = anchors.to_crs(shops.crs)

    # Extract coordinates for manual distance calc (since scipy is missing)
    shop_coords = np.array([(g.x, g.y) for g in shops.geometry])
    anchor_coords = np.array([(g.x, g.y) for g in anchors.geometry])

    output = []

    for i, anchor in anchors.iterrows():
        a_id = str(anchor['anchor_id'])
        a_name = str(anchor['anchor_name_normalised'])
        a_coord = (anchor.geometry.x, anchor.geometry.y)

        # 1. Find 5 nearest shops
        # Calculate all distances to shops
        shop_dists = np.linalg.norm(shop_coords - a_coord, axis=1)
        # Sort and get top k
        sort_idx = np.argsort(shop_dists)
        
        k_shops = min(5, len(shops))
        indices_shops = sort_idx[:k_shops]
        distances_shops = shop_dists[indices_shops]

        knn_list = []
        for dist, idx in zip(distances_shops, indices_shops):
            shop = shops.iloc[idx]
            knn_list.append({
                "shop_id": str(shop['shop_id']),
                "normalised_name": str(shop['normalised_name']),
                "distance_m": float(dist),
                "within_1km": bool(dist <= 1000.0),
                "coord": (shop.geometry.x, shop.geometry.y)
            })

        # 2. Find 3 nearest sibling anchors
        # Calculate all distances to anchors
        anchor_dists = np.linalg.norm(anchor_coords - a_coord, axis=1)
        sort_anchor_idx = np.argsort(anchor_dists)
        
        sibling_indices = []
        sibling_distances = []
        for idx in sort_anchor_idx:
            if str(anchors.iloc[idx]['anchor_id']) != a_id:
                sibling_indices.append(idx)
                sibling_distances.append(anchor_dists[idx])
            if len(sibling_indices) == 3:
                break
        
        # 3. Construct the 5x3 distance matrix
        matrix = []
        for shop_entry in knn_list:
            row = []
            s_coord = np.array(shop_entry['coord'])
            for s_idx in sibling_indices:
                sib_coord = anchor_coords[s_idx]
                d_m = np.linalg.norm(s_coord - sib_coord)
                row.append(float(d_m))
            matrix.append(row)

        # Clean up knn_list for JSON
        final_knn = []
        for item in knn_list:
            cleaned = {k: v for k, v in item.items() if k != 'coord'}
            final_knn.append(cleaned)

        output.append({
            "anchor_id": a_id,
            "anchor_name_normalised": a_name,
            "knn": final_knn,
            "full_distance_matrix_m": matrix
        })

    with open('/work/market_neighbourhoods.json', 'w') as f:
        json.dump(output, f, indent=2)

if __name__ == "__main__":
    solve()