CatalogException stating I should install and load the spatial extension after installing and loading the spatial extension

[robertsiipola]

I'm having an issue with loading the spatial extension. I'm using python 3.12.1 and duckdb 0.10.0 on a M -series Macbook.

After installing everything using pip I try to install and load the spatial extension:

import duckdb
db = duckdb.connect()
db.execute("""
INSTALL spatial;
INSTALL httpfs;
LOAD spatial;
LOAD httpfs;
""")

I can even see that the extension has been loaded, as running

db.execute("FROM duckdb_extensions();").fetchall()

returns a tuple where one the elements is this:

('spatial',
  True,
  True,
  '/Users/robertsiipola/.duckdb/extensions/v0.10.0/osx_arm64/spatial.duckdb_extension',
  'Geospatial extension that adds support for working with spatial data and functions',
  []),

Now when I try and run the following query, using the spatial function ST_GeomFromText:

db.execute("SELECT * FROM ST_GeomFromText('POLYGON((13.414, 52.532, 13.414, 52.532, 13.414, 52.532, 13.414, 52.532, 13.414, 52.532))')").fetchall()

I get an error stating that I should install and load the spatial extension:

---------------------------------------------------------------------------
CatalogException                          Traceback (most recent call last)
Cell In[11], [line 1](vscode-notebook-cell:?execution_count=11&line=1)
----> [1](vscode-notebook-cell:?execution_count=11&line=1) db.execute("SELECT * FROM ST_GeomFromText('POLYGON((13.414, 52.532, 13.414, 52.532, 13.414, 52.532, 13.414, 52.532, 13.414, 52.532))')").fetchall()

CatalogException: Catalog Error: Table Function with name "st_geomfromtext" is not in the catalog, but it exists in the spatial extension.

Please try installing and loading the spatial extension:
INSTALL spatial;
LOAD spatial;

What's going on?

I get the same error when using the CLI tool installed from brew.

[carlopi]

Hi! This was a known limitation when using functions somewhat wrongly, here st_geomfromtext is a ScalarFunction and so it should be invoked from SELECT (instead of doing SELECT * FROM st_geomfromtext(...) simply SELECT st_geomfromtext(...))

Error has been improved in the last weeks (I think PR from @Tishj) Using the nightly CLI this gives a better error message:

Catalog Error: Table Function with name "st_geomfromtext" is not in the catalog, a function by this name exists in the spatial extension, but it's of a different type, namely Scalar Function

While:

LOAD spatial;
SELECT ST_GeomFromText('POLYGON((13.414, 52.532, 13.414, 52.532, 13.414, 52.532, 13.414, 52.532, 13.414, 52.532))');
Invalid Input Error: WKT Parser: Expected double at position 15 near: 'POLYGON((13.414,'|<---

Unsure if this info is enough to unblock you, or some spatial specific help is needed.

[Maxxen]

Like Carlo mentioned the issue is caused by trying to use ST_FromFromText as if its a table function. Additionally, it seems like the WKT text you try to pass is malformed. Commas are used to separate coordinate pairs, not the individual axis values. E.g. for a point its POINT (1 2) and not POINT (1,2)

[robertsiipola]

Ah! Thank you. Passing my coordinates first to the shapely library and then to duckdb fixed my issues.