fio-l1-nyc-csvwkt-addresses

Rerun

Done. Written to /work/addresses.geoparquet
Rows: 1056
Columns: ['id', 'country', 'postcode', 'street', 'number', 'unit', 'postal_city', 'recorded_at', 'unit_count', 'geometry']
CRS: EPSG:4326
dtypes:
id                             str
country                        str
postcode                       str
street                         str
number                         str
unit                           str
postal_city                    str
recorded_at    datetime64[us, UTC]
unit_count                   int32
geometry                  geometry
dtype: object

cd /work && python -c "
import geopandas as gpd
import duckdb

gdf = gpd.read_parquet('/work/addresses.geoparquet')

# Test WHERE recorded_at > '2024-01-01'
result = gdf[gdf['recorded_at'] > '2024-01-01']
print(f'WHERE recorded_at > 2024-01-01: {len(result)} rows')

# Test SUM(unit_count)
total = gdf['unit_count'].sum()
print(f'SUM(unit_count): {total}')

# Test via duckdb
conn = duckdb.connect()
conn.execute('INSTALL spatial; LOAD spatial;')
result2 = conn.execute(\"\"\"
    SELECT COUNT(*) as cnt, SUM(unit_count) as total_units
    FROM '/work/addresses.geoparquet'
    WHERE recorded_at > '2024-01-01'
\"\"\").fetchone()
print(f'DuckDB query: {result2[0]} rows, {result2[1]} total units')
"

Traceback (most recent call last):
  File "<string>", line 18, in <module>
    result2 = conn.execute("""
              ~~~~~~~~~~~~^^^^
        SELECT COUNT(*) as cnt, SUM(unit_count) as total_units
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        FROM '/work/addresses.geoparquet'
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        WHERE recorded_at > '2024-01-01'
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    """).fetchone()
    ^^^^
_duckdb.BinderException: Binder Error: No extension found that is capable of reading the file "/work/addresses.geoparquet"
* If this file is a supported file format you can explicitly use the reader functions, such as read_csv, read_json or read_parquet

cd /work && python -c "
import duckdb

conn = duckdb.connect()
conn.execute('INSTALL spatial; LOAD spatial;')
result = conn.execute(\"\"\"
    SELECT COUNT(*) as cnt, SUM(unit_count) as total_units
    FROM ST_Read('/work/addresses.geoparquet')
    WHERE recorded_at > '2024-01-01'
\"\"\").fetchone()
print(f'DuckDB query: {result[0]} rows, {result[1]} total units')
"

Traceback (most recent call last):
  File "<string>", line 6, in <module>
    result = conn.execute("""
             ~~~~~~~~~~~~^^^^
        SELECT COUNT(*) as cnt, SUM(unit_count) as total_units
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        FROM ST_Read('/work/addresses.geoparquet')
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        WHERE recorded_at > '2024-01-01'
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    """).fetchone()
    ^^^^
_duckdb.IOException: IO Error: Could not open GDAL dataset at: /work/addresses.geoparquet

LINE 3:     FROM ST_Read('/work/addresses.geoparquet')
                 ^

cd /work && python -c "
import duckdb

# Use read_parquet directly (not spatial)
conn = duckdb.connect()
result = conn.execute(\"\"\"
    SELECT COUNT(*) as cnt, SUM(unit_count) as total_units
    FROM read_parquet('/work/addresses.geoparquet')
    WHERE recorded_at > '2024-01-01'
\"\"\").fetchone()
print(f'DuckDB (read_parquet): {result[0]} rows, {result[1]} total units')

# Also verify the geometry column is present and has proper type
cols = conn.execute(\"\"\"
    DESCRIBE SELECT * FROM read_parquet('/work/addresses.geoparquet')
\"\"\").fetchall()
for c in cols:
    print(c)
"

DuckDB (read_parquet): 1055 rows, 1566 total units
('id', 'VARCHAR', 'YES', None, None, None)
('country', 'VARCHAR', 'YES', None, None, None)
('postcode', 'VARCHAR', 'YES', None, None, None)
('street', 'VARCHAR', 'YES', None, None, None)
('number', 'VARCHAR', 'YES', None, None, None)
('unit', 'VARCHAR', 'YES', None, None, None)
('postal_city', 'VARCHAR', 'YES', None, None, None)
('recorded_at', 'TIMESTAMP WITH TIME ZONE', 'YES', None, None, None)
('unit_count', 'INTEGER', 'YES', None, None, None)
('geometry', 'GEOMETRY(\'{"$schema":"https://proj.org/schemas/v0.7/projjson.schema.json","type":"GeographicCRS","name":"WGS 84","datum_ensemble":{"name":"World Geodetic System 1984 ensemble","members":[{"name":"World Geodetic System 1984 (Transit)"},{"name":"World Geodetic System 1984 (G730)"},{"name":"World Geodetic System 1984 (G873)"},{"name":"World Geodetic System 1984 (G1150)"},{"name":"World Geodetic System 1984 (G1674)"},{"name":"World Geodetic System 1984 (G1762)"},{"name":"World Geodetic System 1984 (G2139)"},{"name":"World Geodetic System 1984 (G2296)"}],"ellipsoid":{"name":"WGS 84","semi_major_axis":6378137,"inverse_flattening":298.257223563},"accuracy":"2.0","id":{"authority":"EPSG","code":6326}},"coordinate_system":{"subtype":"ellipsoidal","axis":[{"name":"Geodetic latitude","abbreviation":"Lat","direction":"north","unit":"degree"},{"name":"Geodetic longitude","abbreviation":"Lon","direction":"east","unit":"degree"}]},"scope":"Horizontal component of 3D system.","area":"World.","bbox":{"south_latitude":-90,"west_longitude":-180,"north_latitude":90,"east_longitude":180},"id":{"authority":"EPSG","code":4326}}\')', 'YES', None, None, None)