Re: Rank and Partition

On 20 Jul 2014, at 5:38, maplabs@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
museumsof 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:
Youcould 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.