5

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

2

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

1

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! – Stefan Mar 26 '14 at 16:53

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.