0

i've been stuck on this problem for quite a while now and I can't figure out what could be the issue here.

I have a Postgres DB with postgis extension installed

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA 'testschema'

I set the search path to 'testschema' and create the following table:

class StationGroup(Base):
    __tablename__ = 'station_Group'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    center_lat = Column(Float)
    center_lon = Column(Float)
    radius = Column(Float)
    geom = Column(Geometry(geometry_type='POINTZ', dimension=3, srid=4326))

    def __init__(self, name: str, center: Coordinate):
        self.name = name
        self.center_lat, self.center_lon = center.lat(), center.lng()
        self.radius = 0.0
        self.geom = 'POINTZ(%f %f %f)' % (
            center.lng(), center.lat(), center.alt()
        )

Now I create the object and want to add it to the table with

session.add(item)

Which results in the following query:

2019-01-15 14:04:26,495 INFO sqlalchemy.engine.base.Engine INSERT INTO "testschema"."station_Group" (name, center_lat, center_lon, radius, geom) VALUES (%(name)s, %(center_lat)s, %(center_lon)s, %(radius)s, ST_GeomFromEWKT(%(geom)s)) RETURNING "testschema"."station_Group".id
2019-01-15 14:04:26,496 INFO sqlalchemy.engine.base.Engine {'name': 'name', 'center_lat': 38.3389025, 'center_lon': 15.23485, 'radius': 0.0, 'geom': 'POINT(15.23485 38.3389025 0.000000)'}

Now I get the following result:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) FEHLER: Function st_geomfromewkt(unknown) does not exist

BUT: When I execute the query manually with

INSERT INTO "testschema"."station_Group"  (name, center_lat, center_lon, radius, geom) VALUES ('name', 38.3389025, 15.23485, 0.0, st_geogfromtext ('POINT(15.23485 38.3389025 0.000000)')) RETURNING "testschema"."station_Group".id

it works perfectly!

I can't figure out what I'm doing wrong, any ideas?

  • Low skill SQL-user here, but are you sure it has to be "EWKT"? What does the "E" stand for? – Erik Jan 15 '19 at 13:22
  • Extended WKT, see postgis.refractions.net/docs/…. And I don't select this function, geoalchemy does that. – lama_van_bleu Jan 15 '19 at 13:43
  • You need to add a text string indicating the srid, if you use EWKT. Look at the docs again – John Powell Jan 16 '19 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.