# shortest distance between road and lake with PostGIS

I have a country-wide dataset with lakes (polygons) and streets (lines). For each lake I want to know the shortest distance to the closest street. (I do not necessarily need the closest street itself ... )

I use this query for it:

``````SELECT
*,
(SELECT
ST_Distance(lake.geom, street.geom)
FROM
street
ORDER BY
lake.geom <-> street.geom
LIMIT
1
) AS distance_closest_street
FROM
lake
``````

This query works quite fast on regional level, but takes forever on country level. I have the usual "gist"-index on the geometry columns.

Do you have ideas how this query could become faster?

Edit: I use PostgreSQL 9.5.7 and PostGIS 2.3.2

• Could you add the output of `EXPLAIN ANALYZE SELECT [...]` – pLumo Jun 26 '17 at 10:37

You don't mention what version of PostgreSQL/PostGIS you are using. The KNN nearest neighbor operators have improved greatly over recent years https://postgis.net/docs/geometry_distance_knn.html

So I believe you are just looking for the nearest neighbor road to each lake.

``````SELECT
a.lake_id
,a.nearest_street_id
,ST_Distance(
ST_Transform(a.geom,4326)::geography
,ST_Transform(b.geom,4326)::geography
) / 1000 AS distance_km
FROM (
SELECT
a.lake_id
,a.geom
,(
SELECT
bb.street_id
FROM
street bb
ORDER BY
bb.geom <-> aa.geom
LIMIT 1
) AS nearest_street_id
FROM
lake aa
) a
JOIN
street b
ON
a.nearest_street_id = b.street_id;
``````

This will work with 9.5 and greater and PostGIS 2.2. You can do similar things with earlier KNN versions - using <#> after 9.1 but its more work to batch and then inspect the batch for the nearest.

Note in cases where the distance is exactly the same, you just get whichever one comes up first in the limit.

Edit: Note you can try the lateral trick as well though in my testing I get very similar performance results

``````SELECT
a.lake_id
,b.nearest_street_id
,ST_Distance(
ST_Transform(a.geom,4326)::geography
,ST_Transform(b.geom,4326)::geography
) / 1000 AS distance_km
FROM
lake a
CROSS JOIN LATERAL (
SELECT
bb.street_id AS nearest_street_id
,bb.geom
FROM
street bb
ORDER BY
bb.geom <-> a.geom
LIMIT 1
) b
``````

Cheers,

Paul

• Thanks for your elaborate answer. I am actually only looking for the shortest distance to closest road (not necessarily the road itself ... ). But maybe I can use your answer to solve my problem. I will try to share my solution once I have it. – coding_bird Jun 29 '17 at 6:34
• I added my Postgres and PostGIS version. – coding_bird Jun 29 '17 at 6:35

I'm not an expert in postgis, but I have an idea. You can create another field of lakes with st_buffer (10km, 20km, etc) and indexed. Then use st_intersects with streets, I believe the search will be faster. This way you'll be using the gist index indeed.

• Thanks for you answer. Filtering the lakes before is a good idea. I will try it and hope to share my solution soon. – coding_bird Jun 29 '17 at 6:36