[GIS] How to speed up a query for nearest linestring to point data

linestringnearest neighborpointpostgispostgresql

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.

enter image description here

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

Best Answer

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

Related Question