Varying result of ST_ClosestPoint() depending on ST_DWithin() distance parameter

Question

I have a table containing a streetnetwork as MultiLineString and a table which contains buildings as points. Both in EPSG:31467.

Basically I want to match buildings onto the streetnetwork by taking the closest point to a building on the network as the match.

In a function I am iterating through all of the buildings. When the following block of code is reached, the geometry record of one building is in variable build_geom_rec.

i := i + 1;
EXECUTE 'SELECT ST_AsText(ST_ClosestPoint(streets.geom, $1)) FROM ' || streets_tbl || ' streets WHERE ST_DWithin(streets.geom, $1, 100)' INTO closest_point USING build_geom_rec;
build_geom_tex := ST_AsText(build_geom_rec);
RAISE NOTICE 'entry: % date: % area: % building: % closest_point: %', i, date_area.datum, date_area.bezirk, build_geom_tex, closest_point;

I use ST_ClosestPoint() to find the closest point of the current building on the streetnetwork and ST_DWithin() in order to take only those parts of the streetnetwork into account which lie within a certain distance. The determined closest point is stored as text in variable closest_point. At the end of the iteration I print out the coordinates of building and closest point.

Here is the problem: Depending on the distance parameter used in ST_DWithin I get different results as closest point on the street network for the same buildings.

Example outputs for one building using ST_ClosestPoint and ST_DWithin with distance parameters: 30, 100, 500

distance parameter 30: 
building: POINT(3395776 5885985) closest_point: POINT(3395770.95003767 5885967.03787269)
distance parameter 100:
building: POINT(3395776 5885985) closest_point: POINT(3395756.94892761 5885912.03597038)
distance parameter 500:
building: POINT(3395776 5885985) closest_point: POINT(3395701.1594 5885589.6837)

In the following visualization you can see that all of the determined closest points lie on the streetnetwork, they just lie further away from the building with increasing distance parameter.

Apparently only parameter 30 returns a result as I did expect it. But since there are also buildings which lie further away from the streets, I need to use a larger radius for ST_DWithin.

Question:

Is something wrong with my query? Do I use ST_DWithin() in a wrong way?

I hope it's just something simple that I am missing here.

Accepted Answer:

MickyT's answer explains what I was doing wrong and suggests a working solution.

user30184's solution is actually even more convenient because here EVERY point gets a match on the streetnetwork and besides it is faster than the solution using ST_Distance():

Answer 1

The problem you have is that the query you are using is returning multiple rows. One for each street geometry within the specified distance.

You will need to change the query slightly to return only the closest point out of all the results.

The following example hopefully shows what I mean.

CREATE TABLE sample_roads (
    geom Geometry
);

INSERT INTO sample_roads VALUES
    (ST_GeomFromText('LINESTRING(0 10, 20 10)',0))
    ,(ST_GeomFromText('LINESTRING(0 20, 20 20)',0))
    ,(ST_GeomFromText('LINESTRING(0 30, 20 30)',0))
    ,(ST_GeomFromText('LINESTRING(0 40, 20 40)',0))
    ,(ST_GeomFromText('LINESTRING(0 50, 20 50)',0))
    ,(ST_GeomFromText('LINESTRING(0 60, 20 60)',0));

-- Small distance that only hits a single road
SELECT ST_AsText(ST_ClosestPoint(streets.geom, ST_Point(5, 0)))
FROM sample_roads streets
WHERE ST_DWithin(streets.geom, ST_Point(5, 0), 15);

-- Large distance that hits each road and returns the closest point on each
SELECT ST_AsText(ST_ClosestPoint(streets.geom, ST_Point(5, 0)))
FROM sample_roads streets
WHERE ST_DWithin(streets.geom, ST_Point(5, 0), 100);

-- Same as above, but order and only return 1
SELECT ST_AsText(ST_ClosestPoint(streets.geom, ST_Point(5, 0)))
FROM sample_roads streets
WHERE ST_DWithin(streets.geom, ST_Point(5, 0), 100)
ORDER BY ST_Distance(ST_ClosestPoint(streets.geom, ST_Point(5, 0)), ST_Point(5, 0)) ASC
LIMIT 1;

Answer 2

Completing the answer by @MickyT, it should be faster to use the indexed KNN search https://gisforthought.com/projects/postgis_tutorial/knn.html instead of the "ST_Within...ORDER BY...LIMIT 1" query for selecting the closest streets.

A modified query that gives the same result as the example "-- Same as above, but order and only return 1" Extra benefit is that there is no need to use any fixed distance which would always be somehow sub-optimal. Either it selects unnecessary many candidates or then it does not find anything from areas where the data are sparse.

SELECT ST_AsText(ST_ClosestPoint(a.geom, ST_Point(5, 0)))
FROM 
(select geom from sample_roads
ORDER BY geom <-> st_makepoint(5,0)
LIMIT 1) a;