run-20260618-093425Z / 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:02

Bash ls -l /work/bangkok_contractor_delivery.gpkg

-rw-r--r-- 1 agent agent 2703360 Jun 18 10:26 /work/bangkok_contractor_delivery.gpkg

0:19

Write /work/solve.py

content · 101 lines · py

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

def detect_encoding(df):
    """
    Heuristic to check if text columns contain mojibake.
    We check if common UTF-8 characters are represented as multiple latin1 chars.
    However, a simpler way is to check if the data can be encoded to utf-8 
    and if it contains common mojibake patterns.
    For this task, we'll check if any string column contains characters that 
    look like latin1-mojibake (e.g., Ã, ©, etc. where they shouldn't be).
    Actually, the prompt asks to report 'latin1-mojibake' or 'utf-8'.
    """
    text_cols = df.select_dtypes(include=['object']).columns
    if len(text_cols) == 0:
        return 'utf-8'
    
    for col in text_cols:
        # Sample some values
        samples = df[col].dropna().astype(str).head(100).tolist()
        for s in samples:
            # A common sign of mojibake is the presence of characters like 'Ã' 
            # followed by other symbols that result from UTF-8 interpreted as latin1.
            # We'll check if we can encode it as utf-8 (it should always be possible for strings in Python)
            # and then see if it "looks" like it was incorrectly decoded.
            # A simple check: if it contains sequences like 'Ã©' instead of 'é'
            # This is a bit naive but works for many cases.
            try:
                # Try to see if it's valid utf-8
                s.encode('utf-8')
                
                # Heuristic: if we see "Ã" followed by non-ascii, it's often mojibake
                # (where a single utf-8 char was read as multiple latin1 chars)
                # We search for the pattern of common mojibake.
                import re
                if re.search(r'[ÃÂÊÎÒÛ][ÂÃÊÎÒÛ]', s):
                    return 'latin1-mojibake'
            except UnicodeEncodeError:
                continue
    return 'utf-8'

def audit_gpkg(file_path, output_csv):
    layers = fiona.listlayers(file_path)
    audit_results = []

    for layer in layers:
        gdf = gpd.read_file(file_path, layer=layer)
        
        # CRS
        crs = gdf.crs
        epsg_code = f"EPSG:{crs.to_epsg()}" if crs and crs.to_epsg() else "Unknown"
        
        # Geometry Type
        if not gdf.empty:
            geom_type = gdf.geometry.type.iloc[0]
        else:
            geom_type = "None"
            
        # Feature Count
        feature_count = len(gdf)
        
        # Sample X and Y
        sample_x = None
        sample_y = None
        if not gdf.empty:
            sample_geom = gdf.geometry.iloc[0]
            if sample_geom.geom_type == 'Point':
                sample_x = sample_geom.x
                sample_y = sample_geom.y
            elif sample_geom.geom_type in ['LineString', 'MultiLineString']:
                # Use centroid or start point
                centroid = sample_geom.centroid
                sample_x = centroid.x
                sample_y = centroid.y
            elif sample_geom.geom_type in ['Polygon', 'MultiPolygon']:
                centroid = sample_geom.centroid
                sample_x = centroid.x
                sample_y = centroid.y

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

    df_audit = pd.DataFrame(audit_results)
    df_audit.to_csv(output_csv, index=False)
    print(f"Audit completed. Results saved to {output_csv}")

if __name__ == "__main__":
    audit_gpkg('/work/bangkok_contractor_delivery.gpkg', '/work/crs_audit.csv')

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

0:20

Bash python /work/solve.py

Audit completed. Results saved to /work/crs_audit.csv

/work/solve.py:16: 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.
text_cols = df.select_dtypes(include=['object']).columns
/work/solve.py:16: 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.
text_cols = df.select_dtypes(include=['object']).columns
/work/solve.py:16: 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.
text_cols = df.select_dtypes(include=['object']).columns

0:22

Bash cat /work/crs_audit.csv

layer_name,declared_crs,geometry_type,feature_count,sample_x,sample_y,encoding_detected
parcels,EPSG:24047,Polygon,4000,657706.4571303247,1509274.702150785,utf-8
roads,EPSG:32647,LineString,5000,656996.3371533038,1509487.963830215,utf-8
markets,EPSG:4326,Point,1000,100.4533,13.655000000000001,utf-8