# refine nearest_node with radius-distance ( ST_Distance, ST_DWithin )

i am a hobby-IT-person and currently stuck with some simple problem, maybe somebody can help me:

This is my working POSTGIS-function, source by underdark:

``````ALTER TABLE distance_graz

CREATE TABLE temp AS
SELECT a.osm_id, b.id, min(a.dist)
FROM
(SELECT planet_osm_point.osm_id, min(ST_Distance(planet_osm_point.way, distance_graz.the_geom))  AS dist
FROM planet_osm_point, distance_graz
GROUP BY planet_osm_point.osm_id) AS a,

(SELECT planet_osm_point.osm_id, distance_graz.id,
ST_Distance(planet_osm_point.way, distance_graz.the_geom) AS dist
FROM planet_osm_point, distance_graz) AS b
WHERE a.dist = b. dist
AND a.osm_id = b.id
GROUP BY a.osm_id, b.id;

UPDATE distance_graz
SET nearest_node =
(SELECT id
FROM temp
WHERE temp.gid = distance_graz.gid);
``````

What it does is to get the node "a" with the minimal distance to another node "b" and write it in a new table

But now I want to refine this by only getting "nearest points "b"" if they are within a certain distance (5000m) to this point "a".

I think the best way to do this is by adding "ST_dwithin" to the quer:

``````SELECT planet_osm_point.osm_id, ST_DWithin(planet_osm_point.way,distance_graz.the_geom,5000) AS wit
``````

The Question now is how and where do i implement this?

• It looks like that last query is cut off... I can't read the end of it. But I can see that `ST_DWithin` is in the `SELECT` clause, when it should be in the `WHERE` clause. You probably want to use it as a filter, not return its value. – Rob Skelly Oct 22 '15 at 15:25
• Where should be cut off? My last line is: WHERE temp.gid = distance_graz.gid); I know added the within as WHERE and wait for the results. (huge database). Lets see... ... (SELECT planet_osm_point.osm_id, min(ST_Distance(planet_osm_point.way, distance_graz.the_geom)) AS dist FROM planet_osm_point, distance_graz WHERE ST_DWithin(planet_osm_point.way,distance_graz.the_geom,5000) GROUP BY planet_osm_point.osm_id) AS a, ... – mordondro Oct 22 '15 at 16:46
• I was referring to this one: `SELECT planet_osm_point.osm_id, ST_DWithin(planet_osm_point.way,distance_graz.the_geom,5000) AS wit` – Rob Skelly Oct 22 '15 at 16:50