Search code examples
sqlpostgresqlpostgis

PostGis find points from multiple tables in certain distance from line

I have three tables with geometry "lines", "points_a", "points_b". What I am trying to do is find all points from two tables that are in range of 1,5 m from "lines"

finding points from one table is not a problem:

SELECT * from lines l 
JOIN points_a a ON (ST_DWithin(l.geom, a.geom, 1.5)

but i cant figure how to add second table with points. when I add next Join i get: points which both are in the same line buffor, when only one of them is buffer i don't get them

Solution

  • The reason you only get them when they are in both buffers is that you are using inner joins to create this query - this will exclude rows where any of the ON clauses are false.

    You can aggregate the points in a CTE (or subquery), and JOIN on that:

    WITH points AS (
  SELECT geom FROM points_a
  UNION ALL
  SELECT geom FROM points_b
)
SELECT
  *
FROM
  lines l 
JOIN
  points p ON ST_DWithin(l.geom, p.geom, 1.5)