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
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;
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_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
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.
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():