3

I have one table ('TABLE A') with points of Data divided by Latitude, Longitude & Hour. On the other hand I have another table ('TABLE B') with County names.

How do I count points (from 'TABLE A') within counties ('TABLE B') but grouping them or maintaining hour segmentations?

Improve this question
2
  • How do you want to store the result? A new field for each hour (so you have one feature but 24 fields)? a new row for each hour (so you have 24 duplicated polygons but only one field for each)? one field with string/array/dict values (so you have one feature and one field which may be more difficult to handle)? How are your hours formatted? Is it an integer field?
    – MrXsquared
    Commented Aug 3, 2021 at 18:50
  • 1
    Yes! We want 24 rows (hours) for each polygon (county) which includes the amount of counted data points within each polygon in each hour. We do have hours as integer fields.
    – Andres V
    Commented Aug 3, 2021 at 18:56

2 Answers 2

Reset to default
4

You can use a virtual layer or execute SQL from processing toolbox with this query:

select pol.*, 
       poi.h as point_hour, 
       count(poi.h) as point_count
from polygonlayer as pol, pointlayer as poi 
where intersects(pol.geometry, poi.geometry) 
group by poi.h

where h is the name of your hour field. Note that this will return no row for empty counts. So if there is no point with hour 3, you will get no row for this hour.

The result will be duplicated polygons with one row for each hour and count that exists at least once.

Improve this answer
3

Expression wise you could do something like this since 3.16:

To count one specific hour:

array_count(overlay_contains('name_of_pointlayer',"name_of_hourfield"),hour_you_want_to_count)

To create a sorted array of counts, where array position 0 is count of hour 0 and position 6 is count of hour 6:

array_foreach(generate_series(0,23),array_count(overlay_contains('pointlayer',"hour"),@element))

To create a sorted string of counts, where string position 0 is count of hour 0 and position 6 is count of hour 6. Delimiter is semicolon, change that to whatever you want:

array_to_string(array_foreach(generate_series(0,23),array_count(overlay_contains('pointlayer',"hour"),@element)),';')

To create a map/dictionary of hours as keys and counts as values (both as strings). Pretty similar would go for an hstore type:

hstore_to_map(array_to_string(array_foreach(generate_series(0,23),@element||'=>'||array_count(overlay_contains('pointlayer',"hour"),@element))))

to get a value from this map/dictionary as integer, e.g. the count of hour 2:

to_int(hstore_to_map(array_to_string(array_foreach(generate_series(0,23),@element||'=>'||array_count(overlay_contains('pointlayer',"hour"),@element))))['2'])

unfortunately there is no possibility with QGIS expressions to create a dictionary/map with integers as values... that would require a custom Python function.

Improve this answer
2

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.