I want to count the number of points from one layer around each point from a different points layer. The thing is that each point (from both layers) has an "entrance date", so I want to count only those points that have an entry date prior to the one relating to.

For example:

I have a point with an entry date equal to August 2016, and a second layer of 2 points with an entry date equal to July 2016, and 2 points with an entry date equal to December 2016. All of the points are within 100m of the first point. I want to be able to get the number of points, within 100m radius (in this example), that have an entry date prior to August 2016. In this example, the result would be 2.

  • What program are you using? Can you provide some sample attribute structure? – Losbaltica Mar 6 '17 at 13:54
  • 1
    This would be pretty easy to do in Postgres/Postgis, using a combination of date/time functions and spatial functions. – John Powell Mar 6 '17 at 14:19

An approach using Virtual Layer.


I prepared this example:

  • point_layer_a: id_a (integer) and entry_a(date) fields
  • point_layer_b: id_b (integer) and entry_b(date) fields

Green buffer (radius 200m) zones were drawn only for illustration.

enter image description here

Virtual Layer

( Layer | Add Layer | Add/Edit Virtual Layer )

enter image description here

Query is:

SELECT id_a, entry_a, count(*)
FROM point_layer_a JOIN point_layer_b
ON (st_distance(point_layer_a.geometry, point_layer_b.geometry) < 200) 
WHERE to_date(entry_a) > to_date(entry_b)
GROUP BY entry_a

The output (virtual layer):

enter image description here

Sorry I am offering an example of date-to-date comparison only, not month-to-month as you prefer.

|improve this answer|||||

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.