How to rank the circles that contain the highest number of points below. enter image description here

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

  • What is your end goal with this analysis? – Stephen Ruhl May 9 '17 at 14:07
  • Just to clarify, you only want the number of points in a circle if the circle is on its own (i.e. not overlapping another circle)? – Joseph May 9 '17 at 14:08
  • 1
    What happens if you do a spatial join ?. – gisnside May 9 '17 at 14:19
  • 2
    That would mean using a genetic algorithm that assigns points to different (overlapping) polygons and try every (a lot of) possible combination. Setting a point to one or another polygon will make the polygon the one having (or not) the most point, affecting the point assignation to the other polygons touching it. It is not an easy task, you may want to refine the requirement – JGH May 9 '17 at 14:49
  • 1
    I think this requires a bit of python... The task would be to iteratively find the circle with max# of points, store it and remove both circle and points from the analysis, then do it over and over till either no circles or point are left. – Jochen Schwarze May 10 '17 at 10:47

First, PostGIS doesn't have a notion of a circle for the purposes of math. It's a polygon. You can store circles as

  1. circlestrings
  2. or centroids (points) with radius

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
  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.
  • 1
    what does group by 2 refer too? – ziggy May 9 '17 at 16:30
  • 1
    @ziggy fixed =( – Evan Carroll May 9 '17 at 16:31

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.enter image description here

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.

  • But this doesn't iteratively remove points and polygons from the analysis... – Jochen Schwarze May 10 '17 at 10:43
  • It tells you which features connect with only a single feature in the opposite layer. You can use that information to remove all the records that you are not interested in. The edits I described on the join layer will get your point layer down to the records you are interested in. If you join a unique ID from the polygon layer to the point layer in the process above you can also derive the polygons that only match with a single point. Any polygon ID that appears more than once would need to be removed. – Kingfisher May 10 '17 at 13:07
  • Since it identifies the records that appear more than once, it marks them for easy deletion by the user. – Kingfisher May 10 '17 at 16:45

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.