I am trying to use SQLAlchemy to find the closest road(line segment)
to a given point. I have found something that appears to work
correctly in SQL:
SELECT *, distance(the_geom, GeomFromText('POINT(-93.6493 41.9946)',
4326)) AS dist FROM "Streets" where the_geom &&
SetSRID('BOX3D(-93.6493 41.9946, -93.625 42.0104)'::box3d, 4326) ORDER
BY dist limit 1;
First, is there a better way to do a query like this? More importantly
though, how would I create the query in code using SQLAlchemy? I have
existing code that does the BBOX calculation correctly, but I can't
figure out how to do the "distance(the_geom, ...) AS dist" work
correctly.
engine = sa.create_engine(db_uri)
metadata = sa.MetaData()
metadata.connect(engine)
table_info = sa.Table("Streets", metadata, autoload = True)
s = table_info.select()
bound_box = sa.text("'BOX3D(%s %s, %s %s)'::box3d"% (ll[0], ll[1],
ur[0], ur[1]))
spatial_clause = table_info.c.the_geom.op('&&')
(sa.func.SetSRID(bound_box, srid))
s.append_whereclause(spatial_clause)
results = list(s.execute())
Thanks,
Aron
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
