run-20260529-0109Z / dd-l2-bangkok-multicrs-audit

0:00

A contractor just shipped `bangkok_contractor_delivery.gpkg` and I need a defect cite-sheet before I sign off. Please walk every layer in the file and write one row per layer to `crs_audit.csv` with these columns: layer_name, declared_crs (formatted as `EPSG:NNNN`), geometry_type, feature_count, sample_x and sample_y for a sample coordinate taken from the layer as it sits on disk, and encoding_detected. For encoding_detected, look at the text attributes for anomalies and report either `latin1-mojibake` or `utf-8`.

0:00

System info

{
  "adapter": "openrouter",
  "model": "google/gemma-4-26b-a4b-it",
  "base_url": "https://openrouter.ai/api/v1"
}

0:05

Bash ls -lh bangkok_contractor_delivery.gpkg

-rw-r--r-- 1 agent agent 2.6M Jun 15 23:13 bangkok_contractor_delivery.gpkg

0:37

Write /work/solve.py

content · 122 lines · py

import geopandas as gpd
import pandas as pd
import fiona
from shapely.geometry import Point

def detect_encoding(df):
    """
    Heuristic to detect if a dataframe contains mojibake.
    We check string columns for common mojibake patterns or 
    characters that look like they were incorrectly decoded.
    """
    # Identify object (string) columns
    str_cols = df.select_dtypes(include=['object']).columns
    
    for col in str_cols:
        for val in df[col].dropna().unique():
            if isinstance(val, str):
                # A common sign of latin1-mojibake is the presence of 
                # characters like Ã, ©, etc., in places they shouldn't be,
                # or specific patterns. 
                # However, a more robust way is to try encoding to ascii and 
                # seeing if it fails, but that's not quite right for mojibake.
                # Let's check for common mojibake patterns (e.g., Ã followed by something).
                # Since we can't be 100% sure without a dictionary, we'll look 
                # for "broken" looking sequences.
                try:
                    # If it's valid utf-8 and contains weird sequences, it might be mojibake.
                    # In many cases, mojibake happens when latin1 is read as utf-8.
                    # Let's try a simple check: if we can re-encode it back to latin1
                    # and it produces a 'cleaner' looking string, it's mojibake.
                    # This is a heuristic.
                    
                    # Try to see if the string contains high-bit characters that 
                    # look like they are part of a multi-byte sequence being interpreted as latin1.
                    # Example: 'Ã©' instead of 'é'.
                    # We check for sequences like 'Ã' followed by another high-bit char.
                    if any(c in val for c in ['Ã', 'Â', 'Ê', 'Ì', 'Ñ', 'Ò', 'Ó', 'Ô', 'Õ', 'Ö']):
                         # This is a very naive check.
                         return 'latin1-mojibake'
                except:
                    pass
    return 'utf-8'

def audit_gpkg(file_path):
    audit_results = []
    
    # Get layer names using fiona
    layers = fiona.listlayers(file_path)
    
    for layer in layers:
        # Load layer
        gdf = gpd.read_file(file_path, layer=layer)
        
        # Layer metadata
        layer_name = layer
        
        # CRS
        crs = gdf.crs
        if crs is not None:
            try:
                epsg_code = f"EPSG:{crs.to_epsg()}"
            except:
                epsg_code = str(crs)
        else:
            epsg_code = "None"
            
        # Geometry Type
        # Note: Geopandas/Shapely might have mixed types, but usually it's one per layer.
        # We'll take the type of the first non-null geometry.
        geom_type = "None"
        sample_x = None
        sample_y = None
        
        if not gdf.empty:
            first_geom = gdf.geometry.dropna().iloc[0] if not gdf.geometry.dropna().empty else None
            if first_geom:
                geom_type = first_geom.geom_type
                # Get sample coordinates from the geometry as it sits
                if first_geom.geom_type in ['Point', 'MultiPoint']:
                    # For Point, use the coordinate. For MultiPoint, use the first point.
                    if first_geom.geom_type == 'Point':
                        sample_x, sample_y = first_geom.x, first_geom.y
                    else:
                        sample_x, sample_y = first_geom.geoms[0].x, first_geom.geoms[0].y
                elif first_geom.geom_type in ['LineString', 'MultiLineString']:
                    # Use the first coordinate of the line
                    if first_geom.geom_type == 'LineString':
                        sample_x, sample_y = first_geom.coords[0]
                    else:
                        sample_x, sample_y = first_geom.geoms[0].coords[0]
                elif first_geom.geom_type in ['Polygon', 'MultiPolygon']:
                    # Use the first coordinate of the exterior ring
                    if first_geom.geom_type == 'Polygon':
                        sample_x, sample_y = first_geom.exterior.coords[0]
                    else:
                        sample_x, sample_y = first_geom.geoms[0].exterior.coords[0]

        feature_count = len(gdf)
        
        # Encoding detection
        encoding = detect_encoding(gdf)
        
        audit_results.append({
            'layer_name': layer_name,
            'declared_crs': epsg_code,
            'geometry_type': geom_type,
            'feature_count': feature_count,
            'sample_x': sample_x,
            'sample_y': sample_y,
            'encoding_detected': encoding
        })
        
    return pd.DataFrame(audit_results)

if __name__ == "__main__":
    input_file = 'bangkok_contractor_delivery.gpkg'
    output_file = 'crs_audit.csv'
    
    df_audit = audit_gpkg(input_file)
    df_audit.to_csv(output_file, index=False)
    print(f"Audit complete. Results written to {output_file}")

Wrote /work/solve.py (5102 chars).

0:43

Bash python solve.py

Audit complete. Results written to crs_audit.csv

/work/solve.py:13: Pandas4Warning: For backward compatibility, 'str' dtypes are included by select_dtypes when 'object' dtype is specified. This behavior is deprecated and will be removed in a future version. Explicitly pass 'str' to `include` to select them, or to `exclude` to remove them and silence this warning.
See https://pandas.pydata.org/docs/user_guide/migration-3-strings.html#string-migration-select-dtypes for details on how to write code that works with pandas 2 and 3.
str_cols = df.select_dtypes(include=['object']).columns
/work/solve.py:13: Pandas4Warning: For backward compatibility, 'str' dtypes are included by select_dtypes when 'object' dtype is specified. This behavior is deprecated and will be removed in a future version. Explicitly pass 'str' to `include` to select them, or to `exclude` to remove them and silence this warning.
See https://pandas.pydata.org/docs/user_guide/migration-3-strings.html#string-migration-select-dtypes for details on how to write code that works with pandas 2 and 3.
str_cols = df.select_dtypes(include=['object']).columns
/work/solve.py:13: Pandas4Warning: For backward compatibility, 'str' dtypes are included by select_dtypes when 'object' dtype is specified. This behavior is deprecated and will be removed in a future version. Explicitly pass 'str' to `include` to select them, or to `exclude` to remove them and silence this warning.
See https://pandas.pydata.org/docs/user_guide/migration-3-strings.html#string-migration-select-dtypes for details on how to write code that works with pandas 2 and 3.
str_cols = df.select_dtypes(include=['object']).columns

0:47

Read crs_audit.csv

     1	layer_name,declared_crs,geometry_type,feature_count,sample_x,sample_y,encoding_detected
     2	parcels,EPSG:24047,Polygon,4000,657421.5158199039,1509162.3701859994,utf-8
     3	roads,EPSG:32647,LineString,5000,656835.6850029442,1509487.0031804747,utf-8
     4	markets,EPSG:4326,Point,1000,100.4533,13.655000000000001,utf-8

1:08

Write /work/solve.py

content · 78 lines · py

import geopandas as gpd
import pandas as pd
import fiona

def detect_encoding(df):
    """
    Heuristic to detect if a dataframe contains mojibake.
    """
    str_cols = df.select_dtypes(include=['object']).columns
    for col in str_cols:
        for val in df[col].dropna().unique():
            if isinstance(val, str):
                # Heuristic: many common mojibake characters resulting from
                # interpreting UTF-8 as Latin-1 (e.g., 'Ã')
                if any(c in val for c in ['Ã', 'Â', 'Ê', 'Ì', 'Ñ', 'Ò', 'Ó', 'Ô', 'Õ', 'Ö']):
                    return 'latin1-mojibake'
    return 'utf-8'

def audit_gpkg(file_path):
    audit_results = []
    layers = fiona.listlayers(file_path)
    
    for layer in layers:
        gdf = gpd.read_file(file_path, layer=layer)
        
        # CRS
        crs = gdf.crs
        if crs is not None:
            try:
                epsg_code = f"EPSG:{crs.to_epsg()}"
            except:
                epsg_code = str(crs)
        else:
            epsg_code = "None"
            
        # Geometry and Sample Coords
        geom_type = "None"
        sample_x = None
        sample_y = None
        
        if not gdf.empty:
            # Ensure we work with valid geometries
            valid_geoms = gdf.geometry.dropna()
            if not valid_geoms.empty:
                first_geom = valid_geoms.iloc[0]
                geom_type = first_geom.geom_type
                
                if first_geom.geom_type == 'Point':
                    sample_x, sample_y = first_geom.x, first_geom.y
                elif first_geom.geom_type == 'MultiPoint':
                    sample_x, sample_y = first_geom.geoms[0].x, first_geom.geoms[0].y
                elif first_geom.geom_type == 'LineString':
                    sample_x, sample_y = first_geom.coords[0]
                elif first_geom.geom_type == 'MultiLineString':
                    sample_x, sample_y = first_geom.geoms[0].coords[0]
                elif first_geom.geom_type == 'Polygon':
                    sample_x, sample_y = first_geom.exterior.coords[0]
                elif first_geom.geom_type == 'MultiPolygon':
                    sample_x, sample_y = first_geom.geoms[0].exterior.coords[0]

        audit_results.append({
            'layer_name': layer,
            'declared_crs': epsg_code,
            'geometry_type': geom_type,
            'feature_count': len(gdf),
            'sample_x': sample_x,
            'sample_y': sample_y,
            'encoding_detected': detect_encoding(gdf)
        })
        
    return pd.DataFrame(audit_results)

if __name__ == "__main__":
    input_file = 'bangkok_contractor_delivery.gpkg'
    output_file = 'crs_audit.csv'
    df_audit = audit_gpkg(input_file)
    df_audit.to_csv(output_file, index=False)

Wrote /work/solve.py (2800 chars).