Cannot read boundary.geojson with DuckDB v0.9.2

[giorgiobasile]

When reading the boundary.geojson file using DuckDB v0.9.2, the following exception is thrown:

aoi = "https://github.com/vida-impact/open-earth-data/raw/main/tutorials/boundary.geojson"
duckdb.sql(f"CREATE TABLE aoi AS SELECT geom FROM ST_Read('{aoi}')")
duckdb.sql("SELECT * FROM aoi").show()

---------------------------------------------------------------------------
IOException                               Traceback (most recent call last)
Cell In[13], line 2
      1 aoi = "https://github.com/vida-impact/open-earth-data/raw/main/tutorials/boundary.geojson"
----> 2 duckdb.sql(f"CREATE TABLE aoi AS SELECT geom FROM ST_Read('{aoi}')")
      3 duckdb.sql("SELECT * FROM aoi").show()

IOException: IO Error: Unknown file type

This is very related to duckdb/duckdb_spatial#181, where it is suggested to force the installation of the latest DuckDB Spatial nightly build by running:

duckdb.sql("FORCE INSTALL spatial FROM 'http://nightly-extensions.duckdb.org';")

This unfortunately results in running into another exception, already described in the more recent and still open duckdb/duckdb_spatial#221:

IOException: IO Error: GDAL Error (1): GDALOpen() called on https://github.com/vida-impact/open-earth-data/raw/main/tutorials/boundary.geojson recursively

By now, a suitable workaround may be to load the boundary file from a local path, to avoid any HTTP-related issue.

[Maxxen]

Hi! Saw that this issue got linked and just checking in, AFAIK this is fixed on the latest spatial builds!

FORCE INSTALL spatial FROM 'http://nightly-extensions.duckdb.org';
LOAD spatial;
LOAD httpfs;
SELECT * FROM st_read('https://github.com/vida-impact/open-earth-data/raw/main/tutorials/boundary.geojson');


┌───────────────────────────────────────────────────────────────────────────────────────┐
│                                         geom                                          │
│                                       geometry                                        │
├───────────────────────────────────────────────────────────────────────────────────────┤
│ POLYGON ((27.492479555568025 -29.3373975037322, 27.492479555568025 -29.357824001576…  │
└───────────────────────────────────────────────────────────────────────────────────────┘

[giorgiobasile]

Hi @Maxxen great to see! Just out of curiosity, is it something that was fixed very recently? I tried a couple of days ago with the latest nightly but it didn't seem to work, that's why I created the issue and PR.
Nevertheless, I confirm it works now for me as well, thank you very much for checking in!

[Maxxen]

Well uh, not really, maybe you just needed to restart and reload the new spatial extension?
Im still trying to figure out the original invalid file type issue. Are you on Windows?

[giorgiobasile]

No, I'm on MacOS, and that is happening when using the stable release.

[giorgiobasile]

Well uh, not really, maybe you just needed to restart and reload the new spatial extension?

@Maxxen I can confirm that the problem with the GDALOpen() recursion was happening when installing and loading the stable spatial extension, and then installing and loading the nightly one, all in the same DuckDB session. If I then run the query, I end up with that issue.
Whereas, if I then open a new session and reload the previously installed nightly one, everything works as expected; thank you for pointing it out!

[Maxxen]

Great! Thanks for the followup!