I'm using PostGIS and OpenStreetMap, I tried to assign some bars to the nearest building from themselves I based myself on an example, see: link You can see below picture that resulted from below script. Where its circled in red the location of each bar is incorrect. Its not showing what I need to see. The selected location of the bar is not in front of it.

SELECT DISTINCT ON (b.osm_id) b.osm_id as osm_bar, a.osm_id as osm_building, ST_Distance(a.way, b.way)  as distance
FROM buildings a, bar_not_assigned b  
WHERE ST_DWithin(a.way, b.way, 10) 
ORDER BY b.osm_id, a.osm_id, ST_Distance(a.way, b.way);

enter image description here

When i used the script in this post link I got this result:

enter image description here

select poly.osm_id
from (select osm_id, way from buildings) poly
cross join lateral
  (select osm_id, way
   from bar_not_assigned pt
   where st_dwithin(pt.way, poly.way, 10)
   order by pt.way <-> poly.way limit 1) pts;

I changed limit to 1 to have one bulding for each bar, but does not work.

  • 1
    Shouldn't you be ordering by distance first? You could also try using a lateral join and limit one. See this post and the linked blog from Paul Ramsey for how to do it. Apr 19 '17 at 12:25
  • Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. Please don't add more information as answers to your question, rather use the edit link to edit your question to add any additional info.
    – Midavalo
    Apr 19 '17 at 14:24
select temp.bar_non_assigne.*, closest.batiment_id
from temp.bar_non_assigne
cross join lateral
    (select source.batiment.osm_id as batiment_id, 
     temp.bar_non_assigne.osm_id as bar_id, 
     st_distance(source.batiment.way, temp.bar_non_assigne.way) as distance
     from source.batiment
     where st_dwithin(source.batiment.way, temp.bar_non_assigne.way, 10)
     order by source.batiment.way <-> temp.bar_non_assigne.way
     limit 1) as closest
 where temp.bar_non_assigne.osm_id = closest.bar_id;

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.