Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

Thanks

Stephen

share|improve this question
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

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.