Set-returning functions

[bekozi]

Is it possible to work with set-returning functions in SQLAlchemy
without using raw SQL? For example, the PostgreSQL/PostGIS function
ST_Dump (http://bit.ly/culek7) returns a “geometry_dump" set. Using
ST_Dump in raw SQL goes something like:

SELECT
ST_Dump(ST_Intersection(data_table1.geom,data_table2.geom)).geom AS
geom...

In SQLAlchemy an attempt to construct this unsurprisingly yields an
attribute error:


session.query(func.ST_Dump(functions.intersection(Data1.geom,Data2.geom).geom.label('geom'))
AttributeError: 'Function' object has no attribute 'geom'

Curious if anyone knows a solution! A search for using set-returning
functions in SQLAlchemy yielded no obvious solution...

Thanks in advance.

--
Ben Koziol
Michigan Tech Research Institute (MTRI)
Ann Arbor, MI

[Michael Bayer]

On Aug 16, 2010, at 11:21 AM, bekozi wrote:

> Is it possible to work with set-returning functions in SQLAlchemy
> without using raw SQL? For example, the PostgreSQL/PostGIS function
> ST_Dump (http://bit.ly/culek7) returns a “geometry_dump" set. Using
> ST_Dump in raw SQL goes something like:
>
> SELECT
> ST_Dump(ST_Intersection(data_table1.geom,data_table2.geom)).geom AS
> geom...
>
> In SQLAlchemy an attempt to construct this unsurprisingly yields an
> attribute error:
>
>
> session.query(func.ST_Dump(functions.intersection(Data1.geom,Data2.geom).geom.label('geom'))
> AttributeError: 'Function' object has no attribute 'geom'

any SQL you want can be made available with Python expressions using @compiles:

from sqlalchemy import *
from sqlalchemy.sql import ColumnElement, column

from sqlalchemy.ext.compiler import compiles

class geom(ColumnElement):
def __init__(self, base):
self.base = base

@compiles(geom)
def compile(expr, compiler, **kw):
return compiler.process(expr.base) + ".geom"

data1, data2 = column('data1'), column('data2')
print select([func.ST_Dump(
geom(func.intersection(geom(data1),geom(data2)))
).label('geom')])

> Curious if anyone knows a solution! A search for using set-returning
> functions in SQLAlchemy yielded no obvious solution...

any reason you aren't using GeoAlchemy ?

[bekozi]

Thanks! Will try to get this working.

I am using GeoAlchemy quite extensively but these more obscure
functions and types are not supported...

On Aug 16, 9:14 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Aug 16, 2010, at 11:21 AM, bekozi wrote:
>
> > Is it possible to work with set-returning functions in SQLAlchemy
> > without using raw SQL? For example, the PostgreSQL/PostGIS function
> > ST_Dump (http://bit.ly/culek7) returns a “geometry_dump" set. Using
> > ST_Dump in raw SQL goes something like:
>
> >    SELECT
> >      ST_Dump(ST_Intersection(data_table1.geom,data_table2.geom)).geom AS
> > geom...
>
> > In SQLAlchemy an attempt to construct this unsurprisingly yields an
> > attribute error:
>

> > session.query(func.ST_Dump(functions.intersection(Data1.geom,Data2.geom).ge om.label('geom'))