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