# Distance calculation PostGIS

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

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?

• 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 Commented Feb 26, 2014 at 8:18

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