Set-returning functions in SQLAlchemy/GeoAlchemy

[bekozi]

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

SELECT
ST_Dump(ST_Intersection(data1.geom,data2.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 nothing obvious...

Thanks in advance.

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

[Eric Lemoine]

On Sun, Aug 15, 2010 at 7:49 PM, bekozi <benk...@gmail.com> wrote:
> Is it possible to work with "geometry_dump" sets in SQLAlchemy/
> GeoAlchemy without using raw SQL? For example, the PostGIS function
> ST_Dump (http://bit.ly/culek7) returns a geometry_dump. Using ST_Dump
> in raw SQL goes something like:
>
>        SELECT
>          ST_Dump(ST_Intersection(data1.geom,data2.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 nothing obvious...

Indeed. It might be worth bringing the question of Postgresql SRF on
the SQLAlchemy mailing list. Please come back here if you have any
answer. Cheers,

--
Eric Lemoine

Camptocamp France SAS
Savoie Technolac, BP 352
73377 Le Bourget du Lac, Cedex

Tel : 00 33 4 79 44 44 96
Mail : eric.l...@camptocamp.com
http://www.camptocamp.com

[bekozi]

Posted there. Will let you know if I hear anything.

http://groups.google.com/group/sqlalchemy/browse_thread/thread/cc3b5a3103bebf98

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

On Aug 16, 4:46 am, Eric Lemoine <eric.lemo...@camptocamp.com> wrote:

> On Sun, Aug 15, 2010 at 7:49 PM, bekozi <benkoz...@gmail.com> wrote:
> > Is it possible to work with "geometry_dump" sets in SQLAlchemy/
> > GeoAlchemy without using raw SQL? For example, the PostGIS function
> > ST_Dump (http://bit.ly/culek7) returns a geometry_dump. Using ST_Dump
> > in raw SQL goes something like:
>
> >        SELECT
> >          ST_Dump(ST_Intersection(data1.geom,data2.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)).g eom.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 nothing obvious...
>
> Indeed. It might be worth bringing the question of Postgresql SRF on
> the SQLAlchemy mailing list. Please come back here if you have any
> answer. Cheers,
>
> --
> Eric Lemoine
>
> Camptocamp France SAS
> Savoie Technolac, BP 352
> 73377 Le Bourget du Lac, Cedex
>
> Tel : 00 33 4 79 44 44 96

> Mail : eric.lemo...@camptocamp.comhttp://www.camptocamp.com

[bekozi]

Received a response on the SQLAlchemy forum. Here is a simple project
I put together demonstrating using the @compiles decorator to create a
set-returning expression (thanks to Michael Bayer for providing the
@compiles usage example).

from sqlalchemy import create_engine
from sqlalchemy.schema import MetaData, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import sessionmaker
from geoalchemy.geometry import Polygon, GeometryColumn, GeometryDDL
from sqlalchemy.types import Integer
from sqlalchemy.sql.expression import func

## SET-UP PROJECT
==============================================================

connstr = 'sqlite:///:memory:'
engine = create_engine(connstr)
metadata = MetaData(bind=engine)
Base = declarative_base(metadata=metadata)
Session = sessionmaker(bind=engine)


class Data1(Base):
__tablename__ = 'data1'
id = Column(Integer,primary_key=True)
geom = GeometryColumn(Polygon(2))


class Data2(Base):
__tablename__ = 'data2'
id = Column(Integer,primary_key=True)
geom = GeometryColumn(Polygon(2))


GeometryDDL(Data1.__table__)
GeometryDDL(Data2.__table__)

## BUILD NEW EXPRESSION
========================================================

#any SQL you want can be made available with Python expressions using
@compiles:
from sqlalchemy.sql import ColumnElement
from sqlalchemy.ext.compiler import compiles

class set_return(ColumnElement):
def __init__(self,base,field):
self.base = base
self.field = field
self.type = None # throws an error unless declared...

@compiles(set_return)
def compile(expr, compiler, **kw):
return compiler.process(expr.base) + '.' + expr.field

## CONSTRUCT SOME QUERIES
======================================================

s = Session()

q = s.query(Data1.id.label('id_data1'),
Data2.id.label('id_data2'),

set_return(func.ST_Dump(func.ST_Intersection(Data1.geom,Data2.geom)),
'geom').label('geom'))
print q

## or...

base = func.ST_Dump(func.ST_Intersection(Data1.geom,Data2.geom))
field = 'geom'
q = s.query(Data1.id.label('id_data1'),
Data2.id.label('id_data2'),
set_return(base,field).label('geom'))
print q

s.close()

On Aug 16, 2:09 pm, bekozi <benkoz...@gmail.com> wrote:
> Posted there. Will let you know if I hear anything.
>

> http://groups.google.com/group/sqlalchemy/browse_thread/thread/cc3b5a...

[bekozi]

Small change in compiler found after testing...oops...

@compiles(set_return)
def compile(expr, compiler, **kw):

return '(' + compiler.process(expr.base) + ').' + expr.field