I have the following table in supabase where I have the postgis extension installed:

create table
  public.prueba (
    id smallint generated by default as identity not null,
    name text null,
    age smallint null,
    created_at timestamp without time zone null,
    punto geography null,
    constraint prueba_pkey primary key (id)
  ) tablespace pg_default;

which relates to this SQLalchemy class:

class Prueba(Base):
    __tablename__ = "prueba"

    id: Mapped[int] = mapped_column(
        sa.SmallInteger, sa.Identity(start=1), primary_key=True
    )
    name: Mapped[str] = mapped_column(sa.String(50), nullable=False)
    age: Mapped[int] = mapped_column(sa.Integer, nullable=False)
    created_at: Mapped[datetime] = mapped_column(sa.DateTime, default=datetime.now)
    punto: Mapped[WKBElement] = mapped_column(
        Geometry(geometry_type="POINT", srid=4326, spatial_index=True)
    )

I use geoalchemy2 as suggested by this question but everytime I try to add data to this table the code fails.

The code I use to add data is the following:

prueba = Prueba(
    name="Prueba_2",
    age=5,
    created_at=datetime.now(),
    punto="POINT(-1.0 1.0)",
)

with Session() as session:
    session.add(prueba)
    sessionmit()

I create add the data this way because I was following the geoalchemy2 orm tutorial but when I run I get this exception:

  File "c:...\.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_geomfromewkt(unknown) does not exist
LINE 1: ...a_2', 5, '2024-12-28T18:49:07.130429'::timestamp, ST_GeomFro...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[SQL: INSERT INTO prueba (name, age, created_at, punto) VALUES (%(name)s, %(age)s, %(created_at)s, ST_GeomFromEWKT(%(punto)s)) RETURNING prueba.id]
[parameters: {'name': 'Prueba_2', 'age': 5, 'created_at': datetime.datetime(2024, 12, 28, 18, 49, 7, 130429), 'punto': 'POINT(-1.0 1.0)'}]
(Background on this error at: )

I guess that the error is in the way I have defined the class because the same error appears when I leave the punto value empty.

Also I have tried using a similar approach as this tutorial and tring to add the data with this code:

punto=WKBElement("POINT(10 25)", srid=4326),

gives me a different error:

 File "c:\...\back\main.py", line 16, in main
    punto=WKBElement("POINT(10 25)", srid=4326),
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\...\.venv\Lib\site-packages\geoalchemy2\elements.py", line 201, in __init__
    header = binascii.unhexlify(data[:18])
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
binascii.Error: Non-hexadecimal digit found

I have the following table in supabase where I have the postgis extension installed:

create table
  public.prueba (
    id smallint generated by default as identity not null,
    name text null,
    age smallint null,
    created_at timestamp without time zone null,
    punto geography null,
    constraint prueba_pkey primary key (id)
  ) tablespace pg_default;

which relates to this SQLalchemy class:

class Prueba(Base):
    __tablename__ = "prueba"

    id: Mapped[int] = mapped_column(
        sa.SmallInteger, sa.Identity(start=1), primary_key=True
    )
    name: Mapped[str] = mapped_column(sa.String(50), nullable=False)
    age: Mapped[int] = mapped_column(sa.Integer, nullable=False)
    created_at: Mapped[datetime] = mapped_column(sa.DateTime, default=datetime.now)
    punto: Mapped[WKBElement] = mapped_column(
        Geometry(geometry_type="POINT", srid=4326, spatial_index=True)
    )

I use geoalchemy2 as suggested by this question but everytime I try to add data to this table the code fails.

The code I use to add data is the following:

prueba = Prueba(
    name="Prueba_2",
    age=5,
    created_at=datetime.now(),
    punto="POINT(-1.0 1.0)",
)

with Session() as session:
    session.add(prueba)
    session.commit()

I create add the data this way because I was following the geoalchemy2 orm tutorial but when I run I get this exception:

  File "c:...\.venv\Lib\site-packages\sqlalchemy\engine\default.py", line 941, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_geomfromewkt(unknown) does not exist
LINE 1: ...a_2', 5, '2024-12-28T18:49:07.130429'::timestamp, ST_GeomFro...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[SQL: INSERT INTO prueba (name, age, created_at, punto) VALUES (%(name)s, %(age)s, %(created_at)s, ST_GeomFromEWKT(%(punto)s)) RETURNING prueba.id]
[parameters: {'name': 'Prueba_2', 'age': 5, 'created_at': datetime.datetime(2024, 12, 28, 18, 49, 7, 130429), 'punto': 'POINT(-1.0 1.0)'}]
(Background on this error at: https://sqlalche.me/e/20/f405)

I guess that the error is in the way I have defined the class because the same error appears when I leave the punto value empty.

Also I have tried using a similar approach as this tutorial and tring to add the data with this code:

punto=WKBElement("POINT(10 25)", srid=4326),

gives me a different error:

 File "c:\...\back\main.py", line 16, in main
    punto=WKBElement("POINT(10 25)", srid=4326),
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\...\.venv\Lib\site-packages\geoalchemy2\elements.py", line 201, in __init__
    header = binascii.unhexlify(data[:18])
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
binascii.Error: Non-hexadecimal digit found

• python
• sqlalchemy
• orm
• supabase
• geoalchemy2

2 Answers 2

Ok after some digging I found a solution. I don't know if it's the best solution but it works.

In supabase I have the postgis extension installed on the gis schema so I use the sqlalchemy.sql.expression.funcwith the following code:

prueba = Prueba(
    name="Prueba_3",
    age=5,
    created_at=datetime.now(),
    punto=func.gis.st_geogfromtext("SRID=4326;POINT(-73.935242 40.730610)"),
)

with Session() as session:
    session.add_all([prueba])
    session.commit()

The raw SQL that SQLalchemy executes I can see that it calls directly the function st_geogfromtext from the gis schema.

The error you're getting suggests that the ST_GeomFromEWKT function is not available. This function is part of PostGIS, so it seems that either PostGIS is not properly installed or not enabled in your database.

1. Ensure PostGIS is properly installed and enabled in your database. You can check this by running the SQL command:

SELECT PostGIS_version();

If this returns a version number, PostGIS is installed and enabled.

2. Modify your SQLAlchemy class to use Geography instead of Geometry in python:

from geoalchemy2 import Geography

class Prueba(Base):
    # ... other columns ...
    punto = Column(Geography(geometry_type='POINT', srid=4326))

3. When inserting data, use the func.ST_GeogFromText function:

from sqlalchemy import func
    
prueba = Prueba(
    name="Prueba_2",
    age=5,
    created_at=datetime.now(),
    punto=func.ST_GeogFromText('POINT(-1.0 1.0)', 4326)
)

with Session() as session:
     session.add(prueba)
     session.commit()

This approach should work with your PostGIS-enabled table in Supabase. It uses the ST_GeogFromText function to convert the WKT string to a geography object directly in the database.