Distance calculation PostGIS

Question

I've got the following task to solve:

I've got two tables; one contains buildings (houses) as points and the other contains train-stations (at_bhf_classified) as points. I need to find out the distance from each house to the next trainstation. Well, this is more or less easy. But now the issue: The trainstation are categorized. That means: Give the distance to the next station of category 1 if within a 25km radius. If no category 1 station is found within this distance, give me the distance to cat 2 station within the 25 km radius.

So far I use the following statement to calculate the nearest category 1 station:

WITH dist_min 
 AS (SELECT h.h_key, 
            Min(St_distance_sphere(h.geom, b.geom)) dist_min 
     FROM   house h, 
            at_bhf_classified b 
     WHERE  St_distance_sphere(h.geom, b.geom) < 25000 and b.kat=1
     GROUP  BY h.h_key) 
SELECT h.h_key, 
   b.bhfname,
   b.kat,
   b.bhfnummer,
   round(cast(d.dist_min as numeric),2)
FROM   dist_min d, 
   house h, 
   at_bhf_classified b 
WHERE  St_distance_sphere(h.geom, b.geom) = dist_min 
   AND St_distance_sphere(h.geom, b.geom) < 25000
GROUP BY h.h_key, b.bhfname, b.kat, b.bhfnummer, d.dist_min
ORDER  BY h.h_key 

Do you know how to include the second condition explained further up?

Thanks for you help! Carsten

Answer 1

What if you calculated the distance each station has from the point, filtering out any station that is too far away, order by category, and return only the first value?

Answer 2

Okay, I found a solution:

WITH summary 
 AS (SELECT h.h_key, 
            b.kat, 
            b.bhfname, 
            Round(Cast(St_distance_sphere(h.geom, b.geom) / 1000 AS NUMERIC) 
            , 2) 
            AS 
               dist, 
            Row_number() 
              OVER( 
                partition BY h.h_key 
                ORDER BY h_key, kat, St_distance_sphere(h.geom, b.geom) DESC 
              ) 
            AS 
               rk 
     FROM   house h, 
            at_bhf_classified b 
     WHERE  St_distance_sphere(h.geom, b.geom) < 25000 
            AND b.kat IN ( 1, 2, 3 )) 
SELECT s.* 
FROM   summary s 
WHERE  s.rk = 1