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

Antonio Locandro
  • 580
  • 3
  • 13
  • 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 05 '13 at 09: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 05 '13 at 15:59

1 Answers1

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

Micha
  • 15,555
  • 23
  • 29