2

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

Improve this question
2
  • 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
    Commented Aug 5, 2013 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
    Commented Aug 5, 2013 at 15:59

1 Answer 1

Reset to default
4

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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