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.



  • 1
    Which CRS is your data in? You are only getting results for the first point in points table?
    – underdark
    Mar 8 '11 at 12:22
  • it's osgb (27700). I did have a quick look at the wkt to check that that wasn't an issue, but it looked OK (mental note, need to get the newer version of the GUI to check visually). Yes, only getting a result for the first point so I assumed it wasn't a projection issue
    – Stev_k
    Mar 8 '11 at 12:52
  • Have you tried it as an explicit join?
    – Sean
    Mar 8 '11 at 14:35

You could also try using Within() or Intersects() functions.

if it still does not work, you can try to create first your buffers into a new/temporary table then select points within them. It would be clearer to see where the error come from processing step by step.

  • Thanks for this - it was the projections I believe. Reccommend using the spatialite_osm_map command line program from spatialite rather than the export of osm data within QGIS as it seems more robust at the moment.
    – Stev_k
    Mar 10 '11 at 1:54

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.