fio-l1-nyc-csvwkt-addresses

Rerun

total 144
drwxr-xr-x 1 agent agent     34 Jun  8 10:37 .
drwxr-xr-x 1 root  root      14 Jun  8 10:37 ..
-rw-r--r-- 1 agent agent 143422 Jun  8 10:37 nyc_addresses.csv

python3 -c "
import pandas as pd
df = pd.read_csv('/work/nyc_addresses.csv', dtype=str)
print('recorded_at empty:', df['recorded_at'].isna().sum(), '/', len(df))
print('unit_count empty:', (df['unit_count'] == '').sum())
print('unit_count unique:', sorted(df['unit_count'].unique()))
print('recorded_at sample empties:', df['recorded_at'][df['recorded_at'] == ''].head())
"

recorded_at empty: 0 / 1056
unit_count empty: 0
unit_count unique: ['0', '1', '10', '11', '12', '2', '3', '4', '5', '6', '7', '8', '9']
recorded_at sample empties: Series([], Name: recorded_at, dtype: str)

addresses.geoparquet written successfully
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 && python3 -c "
import duckdb

con = duckdb.connect()
con.execute('INSTALL spatial; LOAD spatial;')

# Verify WHERE recorded_at > '2024-01-01' works without CAST
result = con.execute(\"""
    SELECT count(*) as cnt
    FROM 'addresses.geoparquet'
    WHERE recorded_at > '2024-01-01'::TIMESTAMP
\""").fetchone()
print(f'Count where recorded_at > 2024-01-01: {result[0]}')

# Verify SUM(unit_count) works without CAST
result = con.execute(\"""
    SELECT SUM(unit_count) as total_units
    FROM 'addresses.geoparquet'
\""").fetchone()
print(f'SUM(unit_count): {result[0]}')

# Verify combined
result = con.execute(\"""
    SELECT SUM(unit_count) as total_units
    FROM 'addresses.geoparquet'
    WHERE recorded_at > '2024-01-01'::TIMESTAMP
\""").fetchone()
print(f'SUM(unit_count) after filter: {result[0]}')

# Show schema
result = con.execute(\"""
    SELECT column_name, data_type
    FROM (DESCRIBE 'addresses.geoparquet')
\""").fetchall()
print('Schema:')
for row in result:
    print(f'  {row[0]}: {row[1]}')
"

  File "<string>", line 8
    result = con.execute("
                         ^
SyntaxError: unterminated string literal (detected at line 8)

Traceback (most recent call last):
  File "/work/verify.py", line 7, in <module>
    result = con.execute("""
             ~~~~~~~~~~~^^^^
        SELECT count(*) as cnt
        ^^^^^^^^^^^^^^^^^^^^^^
        FROM 'addresses.geoparquet'
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^
        WHERE recorded_at > '2024-01-01'::TIMESTAMP
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    """).fetchone()
    ^^^^
_duckdb.BinderException: Binder Error: No extension found that is capable of reading the file "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 && python3 -c "
import duckdb
con = duckdb.connect()
con.execute('INSTALL spatial; LOAD spatial;')
# Try using read_parquet
result = con.execute(\"\"\"
    SELECT count(*) FROM read_parquet('addresses.geoparquet')
\"\"\").fetchone()
print(result)
"

Traceback (most recent call last):
  File "/work/verify.py", line 30, in <module>
    result = con.execute("""
             ~~~~~~~~~~~^^^^
        SELECT column_name, data_type
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        FROM (DESCRIBE read_parquet('addresses.geoparquet'))
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    """).fetchall()
    ^^^^
_duckdb.ParserException: Parser Error: syntax error at or near "("

LINE 3:     FROM (DESCRIBE read_parquet('addresses.geoparquet'))
                                       ^

cd /work && python3 -c "
import duckdb
con = duckdb.connect()
con.execute('INSTALL spatial; LOAD spatial;')
print(con.execute(\"DESCRIBE read_parquet('addresses.geoparquet')\").fetchdf())
"

Traceback (most recent call last):
  File "<string>", line 5, in <module>
    print(con.execute("DESCRIBE read_parquet('addresses.geoparquet')").fetchdf())
          ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
_duckdb.ParserException: Parser Error: syntax error at or near "("

LINE 1: DESCRIBE read_parquet('addresses.geoparquet')
                             ^

cd /work && python3 -c "
import duckdb
con = duckdb.connect()
con.execute('INSTALL spatial; LOAD spatial;')
# Try DESCRIBE with subquery
print(con.execute('SELECT column_name, data_type FROM (DESCRIBE SELECT * FROM read_parquet(\\'addresses.geoparquet\\'))').fetchall())
"

Traceback (most recent call last):
  File "<string>", line 6, in <module>
    print(con.execute('SELECT column_name, data_type FROM (DESCRIBE SELECT * FROM read_parquet(\'addresses.geoparquet\'))').fetchall())
          ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
_duckdb.BinderException: Binder Error: Referenced column "data_type" not found in FROM clause!
Candidate bindings: "column_type", "default"

LINE 1: SELECT column_name, data_type FROM (DESCRIBE SELECT * FROM read_parquet('addresses...
                            ^

cd /work && python3 -c "
import pyarrow.parquet as pq
schema = pq.read_schema('addresses.geoparquet')
print(schema)
"

id: large_string
country: large_string
postcode: large_string
street: large_string
number: large_string
unit: large_string
postal_city: large_string
recorded_at: timestamp[us, tz=UTC]
unit_count: int32
geometry: binary
  -- field metadata --
  ARROW:extension:name: 'geoarrow.wkb'
  ARROW:extension:metadata: '{"crs": {"$schema": "https://proj.org/schema' + 1498
-- schema metadata --
pandas: '{"index_columns": [], "column_indexes": [], "columns": [{"name":' + 1232
geo: '{"primary_column": "geometry", "columns": {"geometry": {"encoding":' + 1373