# How to speed up a query for nearest linestring to point data

I have processed a Strahler stream order layer with a lot of river linestrings (type: Multilinestring) and their corresponding strahler number (see information here). Besides I have a point layer.

I want to know which linestring is the nearest to the distinct points from the point layer to extract the strahler number for every point. My query here is very slow. For around 500 points and 2000 linestrings it takes over 6 minutes:

``````SELECT DISTINCT ON
(lname)
lnumber,
lname,
strahler,
min(ST_Length(geom)) AS distance
FROM (
SELECT
ST_MakeLine(ST_ClosestPoint(strahler_streams.geom, landslide.geom), landslide.geom) AS geom,
strahler,
lname,
lnumber
FROM landslide, strahler_streams) AS foo
GROUP BY strahler, lname, lnumber, geom
ORDER BY lname, distance;
``````

I know there is the Indexed Nearest Neighbour Search in PostGIS.

But when I try this...

``````SELECT
landslide.lnumber,
strahler_streams.strahler
FROM landslide a, strahler_streams b
WHERE lnumber=2114
ORDER BY b.geom <#> a.geom LIMIT 1
``````

... it only works when I select one single point (`WHERE lnumber=2114`)

How can I speed up the query for the nearest neighbor search (linestring, point)?

With the advice of Mapperz I've edited my query:

``````SELECT DISTINCT ON
(lnumber)
lnumber,
strahler,
min(ST_Distance(ST_ClosestPoint(strahler_streams.geom, landslide.geom), landslide.geom)) AS distance
FROM landslide, strahler_streams
WHERE ST_DWithin(strahler_streams.geom, landslide.geom, 2000.0)
GROUP BY lnumber, strahler
ORDER BY lnumber, distance
``````

I've approximated the distance value until all point features are included. Before I count them with `SELECT count(geom) FROM landslide`.

I've run the query with `EXPLAIN ANALYZE`. About 500 point features and 9000 linestrings features were are involved in this query and it takes about, depending on the distance value, 250ms (for a distance of 2000m (EPSG:31468)).

You don't need the ST_ClosestPoint function. ST_ClosestPoint only gives you the same point that ST_Distance calculates and uses internally. So what you are doing is running the same algorithm twice.

If I don't miss something or just think wrong about it you are also doing things twice when you are both grouping and using distinct in this way. Distinct should be enough here like:

``````SELECT DISTINCT ON
(lnumber)
lnumber,
strahler,
ST_Distance(strahler_streams.geom, landslide.geom) AS distance
FROM landslide, strahler_streams
WHERE ST_DWithin(strahler_streams.geom, landslide.geom, 2000.0)
ORDER BY lnumber, distance
``````
• You're right with ST_ClosestPoint function. I have to group `lnumber` and `strahler`, because of the aggregate function `min` for the distance! Mar 26, 2014 at 16:53