I got about 2 million points in a spatialite database would like to get which points are withina radius of 30 km from another point. All data is in WGS84 but I need distance in km. Also it would be handy to summarize by some field

I tried spatial query in QGIS but crashed due to the size

  • Checking the distance from each of the 2M points to all the other 2M points means 4 trillion checks. Your crash "...due to size" is an understatement! I think you'll have to: 1-Transform the points to a metric CRS then 2-Create a polygon layer of circles of radius 30km around each point, and 3-Do a count of points in each polygon to find those with >1 point. This 3rd step will require a deploying a spatial index on the poly layer. – Micha Aug 5 '13 at 9:53
  • let me rephrase, I got 2 million points and I got one center(x,y) with 30 km radius, what I want to do is select all points that fall within a circle of 30 km radius from only one defined point. – Antonio Locandro Aug 5 '13 at 15:59

Ah, that's different:

SELECT count(*) AS "Number of Points",
  SUM(some_field) AS "Sum of Some Field"
FROM points 
WHERE (PtDistWithin(points.geometry, MakePoint(x,y, 4326), 30000)=TRUE);

Read the documentation about the PtDistWithin function to understand the spheroid option. (For a 30 km radius, I think it won't make any significant difference.)

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.