I've been flummoxed by this query. I have a table of points, and a table of lines, and I am trying to return the point id and line id whenever the point is contained within a buffer of the line (i.e. a single point could be contained multiple times, and I would want multiple records returned).
I have been using the following query:
select 'points'.'id', 'lines'.'id' from points, lines where Contains(Buffer('lines'.'geometry',50),'points'.'geometry')
I do get a result set returned, and it does give multiple points where it is in more than one buffer. However, the result set is limited to the first record in 'points'. I have tested the buffer selection on its own and it appears to be creating multiple polygons, so I'm unsure where the error is here, assuming this is possible to achieve. I don't think this is a specific Spatialite issue and it's probably my SQL. Any pointers much appreciated.