1

I have a sqlAlchemy model that has one column of type geometry which is defined like this:

point_geom = Column(Geometry('POINT'), index=True)

I'm using geoalchemy2 module:

from geoalchemy2 import Geometry

Then I make my queries using sqlAlchemy ORM, and everything works fine. For example:

data = session.query(myModel).filter_by(...)

My problem is that when I need to get the sql statement of the query object, I use the following code:

sql = data.statement.compile(dialect=postgresql.dialect())

But the column of type geometry is converted to Byte[], so the resulting sql statement is this:

SELECT column_a, column_b, ST_AsBinary(point_geom) AS point_geom 
FROM tablename WHERE ...

What should be done to avoid the conversion of the geometry type to byte type?

Falcoa
  • 2,612
  • 1
  • 20
  • 30

2 Answers2

1

I had the same problem when was working with Flask-Sqlalchemy and Geoalchemy2 and solved this as follows.

You just need to create a new subclass of GEOMETRY type.

If you look at documentations, the arguments of "GEOMETRY" type are given:

  • ElementType - which is the type of returned element, by default it's 'WKBElement' (Well-known-binary-element)
  • as_binary - the function to use, by default it's 'ST_AsEWKB' which in makes a problem on your case
  • from_text - the geometry constructor used to create, insert and update elements, by default it is 'ST_GeomFromEWKT'

So what I did? I have just created new subclass with required function, element and constructor and used "Geometry" type on my db models as always do.

    from geoalchemy2 import Geometry as BaseGeometry
    from geoalchemy2.elements import WKTElement
    
    
    class Geometry(BaseGeometry):
        from_text = 'ST_GeomFromText'
        as_binary = 'ST_asText'
        ElementType = WKTElement

As you can see I have changed only these 3 arguments of a base class. This will return you a String with required column variables.

James Nur
  • 23
  • 4
0

It think you can specify that in your query. Something like this: from geoalchemy2.functions import ST_AsGeoJSON

query = session.query(ST_AsGeoJSON(YourModel.geom_column))

That should change your conversion. There are many conversion functions in the geoalchemy documentation.

Shei Pi
  • 51
  • 2
  • 4