4

I am trying to ask two questions in one query using SQL. I want to know:

a) which polygon (ward) a line (road) is in. and then

b) within that ward, which points (B&B's) are within 1 mile from the same line (road) within the selected polygon (ward)

So far, I have identified the B&B's within 1 mile from the road using:

SELECT DISTINCT a."bnb_id"
FROM public.airbnb a
INNER JOIN public.roads b 
ON ST_DWithin(a.geom,b.geom,1609.34)
WHERE b."roadname" = 'Reservoir Road'
GROUP BY a."bnb_id"

(Naturally, this returns all the points regardless of which polygon.)

And I've identified which ward the road is in using:

SELECT DISTINCT a."name"
FROM public.wards a
INNER JOIN public.roads b 
ON ST_DWithin(a.geom,b.geom,1)
WHERE b."roadname" = 'Reservoir Road'
GROUP BY a."name"
5
  • What is your final goal, to get a table of each road, with the ward it is in and the closest B&B in that ward?
    – BERA
    Dec 17 '21 at 12:39
  • Hi, To get a list of B&B's within a mile, and from the same ward, as the road. If I ask for a list of B&B's within a mile of the road, I get a list from all different wards. Dec 17 '21 at 14:32
  • What projection are you using? 1 could be 1 degree, so up to 70 miles away. Dec 17 '21 at 14:57
  • do you have an example of the data from the 3 tables that you could share? Dec 17 '21 at 15:12
  • Hi, Projection is British National Grid. SSRID all set correctly. 1=1m. I guess I could probably forget that from the second query. Dec 18 '21 at 16:08
2

With big requests, I encourage you to use CTE to be more lisible. Sometimes it can also be more efficient to use materialized CTE for some steps, do some tests and look at the explain if you have performance issues.

There is some good question about your data and which projection it uses, you should be carefull about that and precise this when you ask your question.

Here is an exemple of what I think would do the job if I understood your question correctly:

WITH roads AS (
    SELECT
        r.roadname, r.geom
        FROM public.roads r
        WHERE "roadname" = 'Reservoir Road'
), wards AS (
    SELECT 
        DISTINCT w.name, w.geom
        FROM public.wards w, roads r
        WHERE ST_Intersects(w.geom, r.geom)
)
SELECT
    DISTINCT b.bnb_id, w.name, r.roadname
    FROM public.airbnb b, wards w, roads r
    WHERE 
        ST_Intersects(b.geom, w.geom)
        AND ST_DWithin(r.geom, b.geom, 1609.34) -- In case your data is projected, otherwise use ST_DWithin(r.geom::geography, b.geom::geography, 1609.34)

(Edit: changed a.geom to r.geom in the last request as OP said)

1
  • Hi, works perfectly (once I swapped the a.geom for r.geom). Thank you sooo much. Essentially trying to find the b&b's within 1 mile of reservoir road, but that are in the same ward as reservoir road. I know there is only one, and it gave the right result. Super stuff. Dec 18 '21 at 16:10

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.