sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_asewkb(point) does not exist

Question

Postgres flexible server running on Azure. Postgis extension was installed to the dbo schema using

create extension postgis schema dbo;

which also contains the core data tables. Under extensions in pgadmin on the schema I see postgis, but the tables containing the functions are sitting in an ext schema.

In python I have a SQLAlchemy model and am also using geoalchemy for a point data type.

from sqlalchemy import Column, Integer, String, ForeignKey, Float,Date
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from geoalchemy2 import Geometry, WKTElement

class Building(Base):
    __tablename__ = 'building'
    __table_args__ = {'schema': 'dbo'}

    building_id = Column(Integer, primary_key=True)
    placekey = Column(String, unique=True)
    street_address = Column(String)
    city = Column(String)
    state = Column(String)
    county = Column(String)
    postal_code = Column(String)
    coordinates = Column(Geometry(geometry_type='POINT'))

When SQLAlchemy checks for the existence of a record matching my criteria, it wraps the coordinates column in the function st_asewkb(point). I can see this function exists in the ext schema but since the tables are in dbo where the engine is working, it throws the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function st_asewkb(point) does not exist
LINE 1: ...building.postal_code AS dbo_building_postal_code, ST_AsEWKB(...

I considered changing the search path on the database but am concerned about other unintended consequences. Really would like to understand why the extension went on to the ext schema (if that is normative or just a consequence of azure managed postgres) and if I can change it. Or get sqlalchemy to reference the function on the schema directly so that I don't have to move my tables in the event I can't move the postgis extension. Any other suggestions welcome. Thank you.

Answer 1

You cannot directly apply the PostGIS function st_asewkb() to the built-in data type point. There is a type cast from point to geometry:

\dC point
                    List of casts
 Source type │ Target type │ Function │   Implicit?   
═════════════╪═════════════╪══════════╪═══════════════
[...]
 point       │ geometry    │ geometry │ no
[...]
(7 rows)

But that cast is not implicit, so you have to use an explicit cast:

st_asewkb(CAST (some_point AS geometry))

The whole affair makes little sense to me: point is suitable only for points in the Euclidian plane, while EWKB includes a coordinate system.