Re: postgis 2

[Rick Otten]

I'm running into the same issue (SQLAlchemy 0.8.4, GeoAlchemy 0.2.2, PostGIS 2.1, PostgreSQL 9.3) - and also looking for a workaround.

select ST_AsBinary(some_point) doesn't work.   
    [sqlalchemy.exc.ProgrammingError: (ProgrammingError) function st_asbinary(point) does not exist
     HINT:  No function matches the given name and argument types. You might need to add explicit type casts.]

select ST_AsBinary(some_point::geometry) does work.  (when run by hand against the database)

I'm trying to select an entire (wide) row back from my table (which has a POINT column in it), and am getting the broken SQL.

   dbSession.query(my_table).filter("some criteria")

Is there a trick to this?  I've scoured the interwebs with Google, but nothing obvious has jumped out as a solution short of specifying each column individually (either initially, or as needed).



On Sunday, September 2, 2012 9:27:36 PM UTC-4, binadam wrote:

Assuming I have:

class MyGeom(Base):

    __tablename__ = 'my_geom'

    id = Column(Integer, autoincrement=True, primary_key=True)

    geom = Column(Point)

    extra = Column(String)

If I do a union, e,g.

a = m.DBSession.query( m.MyGeom ).filter(m.MyGeom.extra==None)

b = m.DBSession.query( m.MyGeom ).filter(m.MyGeom.extra!=None)

a.union(b)

 I get the following:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) function st_asbinary(bytea) is not unique

LINE 1: SELECT anon_1.my_geom_id AS anon_1_my_geom_id, ST_AsBinary(a...

                                                       ^

HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

and I see a ticket about this (http://trac.osgeo.org/postgis/ticket/1869)

So is there a workaround for this in geoalchemy (besides literal SQL)?

(get this with geoalchemy & geoalchemy2)

Thanks.

[Eric Lemoine]

On Thu, Jan 2, 2014 at 10:51 PM, Rick Otten <rottenw...@gmail.com> wrote:
> I'm running into the same issue (SQLAlchemy 0.8.4, GeoAlchemy 0.2.2, PostGIS
> 2.1, PostgreSQL 9.3) - and also looking for a workaround.
>
> select ST_AsBinary(some_point) doesn't work.
> [sqlalchemy.exc.ProgrammingError: (ProgrammingError) function
> st_asbinary(point) does not exist
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.]
>
> select ST_AsBinary(some_point::geometry) does work. (when run by hand
> against the database)
>
> I'm trying to select an entire (wide) row back from my table (which has a
> POINT column in it), and am getting the broken SQL.
>
> dbSession.query(my_table).filter("some criteria")
>
> Is there a trick to this? I've scoured the interwebs with Google, but
> nothing obvious has jumped out as a solution short of specifying each column
> individually (either initially, or as needed)

Hi

Could you please show more code? A complete test case would be ideal. Thanks.



--
Eric

[Rick Otten]

I've attached a short python script that illustrates the issue (missing a cast in the ST_AsBinary() call).  Hopefully Google will let me attach it.  If this doesn't work, I'll paste it in the body of the email, or send it to you separately.

 

 

 

[Eric Lemoine]

It looks like you don't use PostGIS at all here, but PostgreSQL's
geometric types instead.

--
Eric

[Rick Otten]

Aha!

Instead of defining the column:

   location   point

I should have defined it:

   location  geography

doh.  Thanks.  The query works now.