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
-~----------~----~----~----~------~----~------~--~---
