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 (
How can I speed up the query for the nearest neighbor search (linestring, point)?