0

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 ... )

closest_distance_to_road

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

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

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

2
  • 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
1

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.

1
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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