On 20 Jul 2014, at 5:38, mapl...@light42.com wrote: > Assume I have a table of all schools in the US, and another with all museums, > and I want to see all museums that are within some distance of each school, > by school. > (yes this is spatial but the distance is just a function call - no mystery > there)
> -- > select > distinct on (s.name) s.name as school_name, > m.name as museum_name, m.admin2, > st_distance( s.geom::geography, m.geom::geography )::integer as dist, > rank() over ( partition by (s.name, s.admin2) > order by st_distance( s.geom::geography, m.geom::geography )) as rank > from museum m, school s > where > s.admin2 = 'Alameda County' AND > m.admin1 = 'California' AND > st_dwithin( m.geom::geography, s.geom::geography, 9000 ) > ORDER BY s.name, dist; > — > this query seems to work.. comments welcome Are you sure you want to restrict museums to s specific state? What if a school is near a state-border and there are museums of interest on the other side? What about schools or musea that have multiple locations (or a central administrative location)? If performance is an issue, neither schools nor museums tend to move around a lot and there aren’t too many of either: You could store those distances in a table linking schools and musea and update that table when convenient (a daily cron job, insert triggers, whatever suits you). Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
