# How many query points fall within distance X to a set of LineString

I have a set of 10 query points and I want to know how many of them are with distance within at most 4 meters of any LineString in a given table.

Note that if a point is within 4 meter distance to multiple LineString, this should only be counted once.

I understand how I can use `ST_DWithin` to do the proximity checking of a query point to the LineStirng, but I am not sure as how to write an efficient SQL query to get the desired answer.

For now I can only think of 10 separate SQL statements, each either returning 0 or 1 but that is highly inefficient.

This is a clearer/cleaner version of my previous question: Preprocess OSM streets to see if query points fall "on" them

Similar to this answer, you want to use an `EXISTS` clause:

``````SELECT count(*)
FROM points
WHERE EXISTS
(SELECT 1 FROM lines WHERE ST_DWithin(points.geom, lines.geom, 4))
``````

The `EXISTS` expression just returns true if the subquery returns one or more rows.

If your points aren't stored in a table, you could supply them as query parameters using `VALUES`:

``````SELECT count(*)
FROM (VALUES (point1), (point2), ... , (point10)) points (geom)
WHERE EXISTS
(SELECT 1 FROM lines WHERE ST_DWithin(points.geom, lines.geom, 4))
``````

This assumes that the number of query points is constant, and you can make a prepared statement with a fixed number of parameters. If this isn't the case, you could restructure this to accept an array of points as a single parameter instead.

• very cool, thanks! I have one question though: Your answer assumes that the query points are provided as a table, but actually in my scenario they are part of the query input. And I don't quite see how I would be able to change your example to match that usecase. – user695652 Feb 19 '16 at 17:56
• @user695652 updated the answer to show this. – dbaston Feb 19 '16 at 19:08

To get all points in a simple query you can use this:

``````select count(distinct your_points.id) from your_points inner join
your_lines on st_dwithin(your_points.geom, your_lines.geom, 4.0);
``````

I supposed tabla names are your_points and you_lines, and the geometry columns are called geom. id is the key of the point table.

A less effective solution could be:

``````select count(distinct your_points.id) from your_points inner join
your_lines on st_intersects(st_buffer(your_points.geom, 4.0), your_lines.geom);
``````