2

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

3

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
1

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);

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.