Issue performing geometry operation in GeoJSON file "Error: Referenced column "geometry" not found in FROM clause"

[monasound]

Hi,

I am exploring Duckdb to work with spatial files.

I am trying to import GeoJSON files and perform ST_UNION. I can read the files without any issue, but when performing ST_UNION operation, it result in the following error:

An error occurred while performing ST_UNION: Binder Error: Referenced column "geometry" not found in FROM clause! Candidate bindings: "geo_data_0.geom", "geo_data_0.Reference_Geography", "geo_data_0.ObjectId" LINE 5: SELECT geometry FROM geo_data_0

1. Can someone help me explain the issue?
2. Looks like there is no compatibility in reading feature class of ESRI Geodatabase. Please confirm.

FYI: Somebody raised similar error a year ago. However, I have no idea whether it is updated or not. Please kindly share your comments

[Maxxen]

Hello!

1. What is the actual SQL query you're trying to run? As the error message explains, you're trying to pass a column named "geometry" to the ST_Union function, but there is no column with that name. Supposedly you got a table with a column named "geom" when you imported the geojson file.

2. Im not too familiar with ESRI Geodatabases, but what have you tried? I assume you've tried to pass a gdb file to st_read? Can you share the GDB file?

3. If there are other related issues it's generally a good idea to link them so that we know what you're talking about.

[monasound]

Below is the code I used.

1. Yes, there is a geometry column which I can see.
2. It is one of the geodatabase that I have downloaded and it opens correctly. What I encountered was error with respect to path. I am trying to fix this. If I have issues, I will share my code.

Thank you for your kind time.

` import duckdb import requests

geojson_urls = [ 'https://services-eu1.arcgis.com/BuS9rtTsYEV5C0xh/arcgis/rest/services/CensusHub2022_T1_2_LEA/FeatureServer/0/query?outFields=&where=1%3D1&f=geojson', 'https://services-eu1.arcgis.com/BuS9rtTsYEV5C0xh/ArcGIS/rest/services/grid_population_2022_view/FeatureServer/0/query?outFields=&where=1%3D1&f=geojson' # Replace with your second URL ]

con = duckdb.connect()

con.execute("INSTALL spatial;") con.execute("LOAD spatial;")

for index, geojson_url in enumerate(geojson_urls):

response = requests.get(geojson_url)
response.raise_for_status()  # Raise an error for bad responses


if 'application/json' not in response.headers.get('Content-Type', ''):
    raise ValueError(f"The URL {geojson_url} did not return a valid GeoJSON file.")


local_geojson_path = f'data_{index}.geojson'
with open(local_geojson_path, 'w', encoding='utf-8') as f:
    f.write(response.text)


try:
    query = f'''
    CREATE TABLE geo_data_{index} AS SELECT * FROM ST_Read('{local_geojson_path}');
    '''
    
    con.execute(query)

except Exception as e:
    print(f"An error occurred while reading the GeoJSON file from {local_geojson_path}: {e}")

try: union_query = ''' CREATE TABLE geo_data_union AS SELECT ST_Union(geometry) AS geometry FROM ( SELECT geometry FROM geo_data_0 UNION ALL SELECT geometry FROM geo_data_1 ); '''

con.execute(union_query)


union_result = con.execute("SELECT * FROM geo_data_union").fetchdf()


print("Union of data_0 and data_1:")
print(union_result)

except Exception as e: print(f"An error occurred while performing ST_UNION: {e}")

con.close()`

[Maxxen]

Geometry is the name of the type not the column. The geometry column you get from ST_Read is called "geom", not "geometry"

[monasound]

Thank you for your reply.

I have issue using that url of GeoJSON. I have downloaded two GeoJSON files for a country and tried to do the same operations. It works and creates output.

Columns in GeoJSON_data1: [('OBJECTID', 'INTEGER', 'YES', None, None, None), ('FCsubtype', 'INTEGER', 'YES', None, None, None), ('geom', 'GEOMETRY', 'YES', None, None, None)]

`import duckdb

Connect to DuckDB

conn = duckdb.connect()

Install and load the spatial extension

conn.execute("INSTALL spatial;") conn.execute("LOAD spatial;")

Load the first GeoJSON file

conn.execute(""" CREATE TABLE geo_data1 AS SELECT * FROM ST_Read('M:\R\DuckDb\gEOjson\Built_Up_Areas.geojson') """)

Load the second GeoJSON file

conn.execute(""" CREATE TABLE geo_data2 AS SELECT * FROM ST_Read('M:\R\DuckDb\gEOjson\Vegetation_Areas.geojson') """)

Check the column names in geo_data1

columns_geo_data1 = conn.execute("DESCRIBE geo_data1").fetchall() print("Columns in geo_data1:", columns_geo_data1)

Check the column names in geo_data2

columns_geo_data2 = conn.execute("DESCRIBE geo_data2").fetchall() print("Columns in geo_data2:", columns_geo_data2) for row in columns_geo_data2: print(row)

Example of performing a geometry operation: calculating the area of geometries in the second GeoJSON

area_result = conn.execute(""" SELECT ST_Area(geom) AS area FROM geo_data1 """).fetchall()

Print the area results

print("Areas of geometries in the second GeoJSON file:") for area in area_result: print(area)

Example of a spatial join or union between the two datasets

union_result = conn.execute(""" SELECT a.OBJECTID, b.OBJECTID, ST_Union(a.geom, b.geom) AS union_geom FROM geo_data1 AS a JOIN geo_data2 AS b ON ST_Intersects_Extent(a.geom, b.geom) """).fetchall()

Create a new table for the union results

conn.execute(""" CREATE TABLE union_table AS SELECT a.OBJECTID AS id_a, b.OBJECTID AS id_b, ST_AsText(ST_Union(a.geom, b.geom)) AS union_geom FROM geo_data1 AS a JOIN geo_data2 AS b ON ST_Intersects_Extent(a.geom, b.geom) """)

Export the union results to a CSV file

output_directory = 'M:\R\DuckDb\union_results.csv' conn.execute(f"COPY union_table TO '{output_directory}' (FORMAT CSV, HEADER)")

Close the connection

conn.close()

print(f"Union results exported to {output_directory}") `