On Dec 10, 2007 1:11 PM, Chris M <[EMAIL PROTECTED]> wrote:
>
> use bound_box = sa.text("'BOX3D(5 5, 25 25)'::box3d")
Thanks, that worked perfectly.
Now I am going to write a method that will build an SA clause element
for this given a bounding box and a column to test.
Thanks for the help.
-Allen
>
> On Dec 10, 12:12 pm, "Allen Bierbaum" <[EMAIL PROTECTED]> wrote:
>
> > On Dec 10, 2007 9:08 AM, Allen Bierbaum <[EMAIL PROTECTED]> wrote:
> >
> >
> >
> > > I am trying to figure out how to best use SA to create a GIS query.
> > > In my application I am actually using ORM objects and mappers, but to
> > > keep my question focused on clauses and python expressions, I am just
> > > trying to test this out without the ORM first.
> >
> > > The SQL query I would like to generate is this:
> >
> > > select AsText(the_geom), *
> > > from pt
> > > where SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326) && the_geom and
> > > contains(SetSRID('BOX3D(-95.0 28.5, -95.8 28.8)'::box3d,4326),
> > > the_geom)
> > > limit 100;
> >
> > > So far the best I have been able to come up with is this:
> >
> > > pt.select(
> > > sa.and_(
> > > pt.c.pos.op('&&')(func.SetSRID("'BOX3D(-95.0 28.5, -95.8
> > > 28.8)'::box3d",4326)),
> > > func.contains(func.SetSRID("'BOX3D(-95 28.5, -95.8
> > > 28.8)'::box3d",4326), pt.c.pos)
> > > )
> > > )
> >
> > > Not the most readable way to represent it, but it seems to work. I
> > > have a couple questions though.
> >
> > > - I reuse "func.SetSRID("'BOX3D(-95 28.5, -95.8 28.8)'::box3d",4326)"
> > > twice. Is there a way to split this out into something I can just
> > > reuse?
> >
> > > - Is there any way to write an extension "operator" or something that
> > > could generate this for me? If I had my way, I would want the query
> > > to look like this:
> >
> > > pt.select( smart_contains( ((-95 28.5, -95.8 28.82), 4326), pt.c.pos))
> >
> > > - Can anyone point out a better way I could construct this query? Is
> > > there anything I am missing?
> >
> > I apologize for replying to my own posting, but I have some additional
> > information/problems.
> >
> > I tried to convert this over to an ORM query like this:
> >
> > bound_box = "'BOX3D(5 5, 25 25)'::box3d"
> >
> > spatial_q = session.query(Entity).filter_by(sa.and_(
> > Entity.c.pos.op('&&')(sa.func.SetSRID(bound_box, 4326)),
> > sa.func.Contains(sa.func.SetSRID(bound_box, 4326), Entity.c.pos)
> > ))
> > recs = spatial_q.all()
> >
> > I get an error because the SQL generated has a WHERE clause like this:
> >
> > WHERE (pt.the_geom && SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326))
> > AND Contains(SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326),
> > pt.the_geom) ORDER BY pt.gid
> >
> > I am not sure if you can see it clearly in e-mail, but the BOX3D text
> > is surrounded by apostrophes.
> >
> > What it should be is:
> >
> > SetSRID('BOX3D(5 5, 25 25)'::box3d, 4326)
> >
> > But instead it is sent to postgres as:
> >
> > SetSRID('''BOX3D(5 5, 25 25)''::box3d', 4326)
> >
> > Can anyone tell me why this is happening and what to do to fix it? I
> > already tried sa.literal(..) and that has the same issue. I tried
> > using sa.cast(..), but this doesn't work because it doesn't know about
> > the 'box3d' type.
> >
> > Thanks,
> > Allen
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
