3

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

2 Answers 2

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?

1
  • Hi notkilroy, thanks for your suggestion. I actually use the following to the order as you proposed. But how do I have to write to get the first row of each h_key? The SQL: SELECT h.h_key, b.kat, b.bhfname, St_distance_sphere(h.geom, b.geom) min FROM house h, at_bhf_classified b WHERE St_distance_sphere(h.geom, b.geom) < 25000 AND b.kat IN ( 1, 2 ) ORDER BY h.h_key, b.kat, min Thanks Carsten
    – hoge6b01
    Commented Feb 26, 2014 at 8:18
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.