join on nearest object within a radius

[Rick Otten]

I can construct a valid PostgreSQL query like this:

select
  p.person_name,
  s.store_name,
  st_distance(p.location, s.location)
from
  persons p
  LEFT OUTER JOIN stores s ON s.store_name = (select sn.store_name from stores sn where ST_DWithin(p.location, sn.location, 10000) order by ST_Distance(sn.location, p.location) limit 1)

which joins all of the persons in my person table to the nearest store within 10km (if there is one).  Setting the query efficiencies question aside for a minute, I can't figure out to get SQL/GeoAlchemy to generate this query.

I'm using SQL Alchemy 8, on PostgreSQL 9.3.2, GeoAlchemy2-0.2.2, and PostGIS 2.1.1

I've tried using some of the join tricks outlined on the query page -- http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html -- but haven't gotten them to work yet.

aTdHvAaNnKcSe for any hints or help you guys can offer!

[Rick Otten]

I was able to get it to generate the query by putting the subquery directly in the main query.  I gave up on trying to get it to work with a select alias or a subquery as illustrated in the documentation.

 

I did need to disambiguate the table names by using 'aliased'.

from sqlalchemy.orm import aliased

outer_persons = aliased(persons)

inner_store = aliased(stores)

outer_store = aliased(stores)

myQuery = mySession.query(outer_persons)\

                                  .select_from(outer_persons)\

                                  .outerjoin(outer_stores, outer_stores.store_name == select([inner_stores.store_name])\

                                                                                                              .where(func.ST_DWithin(outer_persons.location, inner_stores.location, radiusMeters))\

                                                                                                              .order_by(func.ST_Distance(outer_persons.location, inner_stores.location))\

                                                                                                              .limit(1))\

                                 .filter(<stuff>)\

                                 .order_by(<rules>)\

                                 .limit(<number>)