Count Distinct Values per Polygon in QGIS – Step-by-Step

countfields-attributespoint-in-polygonqgis

In QGIS I have a list of points, each with a town it's located in and a status, either 'new' or 'existing'. So something like:

ID	town	type
01	Alicetown	new
02	Alicetown	new
03	Alicetown	existing
04	Bobsburgh	existing
05	Bobsburgh	new

I can count how many points are in each town (Alicetown: 3, Bobsburgh: 2), but I'd like to count how many new and existing points are in each (Alicetown: New: 2, Existing: 1; Bobsburgh: New: 1, Existing: 1).

I could just make a column for existing and new but I figured there has to be an easier way.

Is there a way to achieve what I'm trying to do? I've tried SQL but I don't really know enough about it to get it to do what I want.

Best Answer

Perhaps the simplest way is to use the Statistics by category tool. It creates a new table, summarising your input.

Select the fields by which to categorise, and leave Field to calculate statistics on blank to get the count only:

Result

Related Solutions

R – Counting Points in Polygon by Time or Attribute Condition

If you want to go the R way I would suggest this workflow:

assign each polygon an unique id / such as the polyid in your example
perform sf::st_join(points, polygons) on your points and polygons objects, with - crucially - your points in the first place of the join; it will produce your points object, enriched by the data columns from the polygons object (such as the unique id / polyid).
at this step you can drop the geometry (= sf::st_drop_geometry()), and do simple aggregation over your time dimension and polygon id, as both are present in your points object; {dplyr} is your friend here as you are dealing with a regular data frame object

Since an example is worth 1000 of words :) consider this piece of code. It builds on the well known & much loved North Carolina shapefile that ships with {sf}.

It first produces five polygons of dissolved North Carolinas stacked on top of each other, with polygon id's from 1 to 5.

Then it produces three semi-random points, two with time character 1 and one with 3 (or whatever).

The third step is where the action happens: first the points are spatially joined to polygons (it will be a kind of a cross join, since all points are in NC) and then the result is filtered on the secondary criterion - I am using polygon id = time, which makes no sense but is there only for the sake of example. Then it is a question of group by and summarise / functionally equivalent to select polygon id, count(*) from yer data group by polygon id in SQL speak.

library(sf)
library(dplyr)

# a set of polygons in time
shape <- st_read(system.file("shape/nc.shp", package="sf")) %>%  # included with sf package
  summarise() %>% # single polygon over entire NC
  tidyr::expand_grid(poly_id = 1:5) %>% # sequence of ids from 1 to 5
  st_as_sf(crs = 4267) # this information was lost during the expand_grid (tidyr equivalent of cross join)

# a set of points in time
cities <- data.frame(name = c("Raleigh", "Greensboro", "Wilmington"),
                  x = c(-78.633333, -79.819444, -77.912222),
                  y = c(35.766667, 36.08, 34.223333),
                  time = c(1, 1, 3)) %>%  # time, to be matched with poly_id
  st_as_sf(coords = c("x", "y"), crs = 4326) %>% # CRS of the coordinates
  st_transform(st_crs(shape)) # transorm to CRS of the shape object

# this is the action!   
cities %>% 
  st_join(shape) %>% # spatial join only, without the second criterion (yet)
  st_drop_geometry() %>% # drop geometry (no longer necessary)
  filter(time == poly_id) %>%  # filter over the second condition
  group_by(poly_id) %>%  # group by
  tally(name = "point_totals") # summarise the result
# A tibble: 2 × 2
#  poly_id time_totals
#    <int>       <int>
#1       1           2
#2       3           1

Best Answer

Result

Related Solutions

R – Counting Points in Polygon by Time or Attribute Condition

Related Question