1

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
   ADD COLUMN nearest_node integer;

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.