Обсуждение: Rank and Partition

Hi -

  I have used rank() in the past, but returning to the subject.. 

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)

in the following example, 
I used a partition by school name, and an order function of distance for ranking,
WHERE reduces the results in three ways, since it is a national list.

--
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;
--

note that the DISTINCT ON() element(s) must match the ORDER BY initial items
due to some internal rule in the engine.. 

this query seems to work.. comments welcome

--
Brian M Hamlin
OSGeo California Chapter 
blog.light42.com

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.