use bound_box = sa.text("'BOX3D(5 5, 25 25)'::box3d")
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
-~----------~----~----~----~------~----~------~--~---
