1

I have a road and I need to calculate the nearest restaurant from it, its distance, its name and which road it is on(the restaurant). I have done all the things above except for the last one. Does anyone have any ideas?

 SELECT a.name, b.name, ST_Distance(a.geom, b.geom) AS distance
    FROM roads a, restaurants b
    WHERE a.name='Roehampton'
    ORDER BY distance ASC
    LIMIT 1 
  • Sorry, is the question how to find which road a restaurant is on? Having the where clause with Roahampton Lane is a bit confusing. Also, please don't write things like any1, this is a professional forum, and text speak is not really appropriate :-) – John Powell Jul 31 at 7:15
2

There is a specific way to make use of index while searching for nearest neighbors, it's <->, and as maximilien said you should use the road table again. If the restaurant is not exactly on the road, try a nearest neighbors search again:

WITH restaurant AS (
   SELECT a.name, b.name, ST_Distance(a.geom, b.geom) AS distance, b.geom
   FROM aufgabe5.osm_roads a, aufgabe5.italian_restaurants b
   WHERE a.name='Roehampton Lane'
   ORDER BY a.geom <-> b.geom ASC
   LIMIT 1
)
SELECT res.*, roads.name as restaurant_road
FROM restaurant res, aufgabe5.osm_roads roads
ORDER BY res.geom <-> roads.geom ASC
LIMIT 1
  • Thank you alot. :) – sqlisql Jul 31 at 11:41
0

maybe you can try to add another time the road table and look for St_intersects with restaurant geom ...

something like this :

SELECT c.name, a.name, b.name, ST_Distance(a.geom, b.geom) AS distance
    FROM aufgabe5.osm_roads a, aufgabe5.italian_restaurants b, aufgabe5.osm_roads c
    WHERE a.name='Roehampton Lane'
    AND St_intersects(b.geom, c.geom)
    ORDER BY distance ASC
    LIMIT 1 

or you take the query you already wrote and you juste reverse your search as you have the restaurant name (CTE could be usefull here) ...

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.