Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
How to rank the circles that contain the highest number of points below.
Problem is that I can't use the points in polygon tool because if a point is contained in multiple circle, then it shouldn't count. QGIS or Postgis.
Maybe the solution would require an iterative approach
First, PostGIS doesn't have a notion of a circle for the purposes of math. It's a polygon. You can store circles as
I assume you're storing them as centroids. If so, it's easy use ST_DWithin(). Assuming you're using geography, the radius is in meters this will find all the points that are uniquely in one circle (don't overlap).
SELECT points.id, count(*)
FROM poi
JOIN points
ON ST_DWithin( poi.geom::geog, points.geom::geog, radiusInMeters )
GROUP BY points.id
HAVING count(*) = 1;
This will find all circles that have the most non-overlapping points.. We wrap the above in EXISTS to find the ones that don't overlap.
SELECT poi.id, count(*)
FROM poi
JOIN points
ON ST_DWithin( poi.geom::geog, points.geom::geog, radiusInMeters )
GROUP BY poi.id
WHERE EXISTS (
SELECT 1
FROM poi
JOIN points AS p2
ON ST_DWithin( poi.geom::geog, p2.geom::geog, radiusInMeters )
GROUP BY p2.id
HAVING count(*) = 1
WHERE p2.id = points.id // points.id is the outter.
);
If you run 'Join Attributes by Location' in QGIS. The points would be the target and the circles would be the join layer. If you use 'Take Summary of Intersecting Points', the result will contain a count of how many circles each point touches. See the attached screen shot for details on the parameters. You would only need to keep matching records.
Once you have the joined layer you can remove from it all the records that have a count that is greater than 1. The remaining records can be used in the count points in polygon tool to get you the data you need.
