Re: Rank and Partition

On 20 Jul 2014, at 5:38, maplabs(at)light42(dot)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.