1

I have a shp point layer with a "USER_ID" column. There is about 19 thousands features. Some of "ID"'s enters the list only ones or twice, but some of them enters 50 or more than 100 times.

I need to find the users which most often meet in this column. By all means it is possible to categorize this layer, but I think that the result shouldn't be clear. Is there a way to categorize only 50 most frequent values of a column?

data example

Taras
  • 32,823
  • 4
  • 66
  • 137
Burtsev
  • 535
  • 2
  • 8

1 Answers1

1

Not the best solution according to the data amount, i.e. 19 thousands features. But perhaps you are working with a database.

In terms of QGIS I can suggest using a Virtual Layer through Layer > Add Layer > Add/Edit Virtual Layer...

With the following query, it is possible to achieve the result, i.e. to find the users which most often meet in this column. Only users that are met more than 50. 

SELECT p."USER_ID", COUNT() AS "count"
FROM "points_layer" AS p
GROUP BY p."USER_ID"
HAVING COUNT() > 50

Or if you want to categorize only 50 most frequent values of a column that use this query

SELECT p."USER_ID", COUNT() AS "count"
FROM "points_layer" AS p
GROUP BY p."USER_ID"
ORDER BY COUNT() DESC
LIMIT 50

Note: The result will not maintain the geometry of the original point layer.

If you wish to maintain geometry of your point layer it is important to understand whether your users with same id share the same location. Nevertheless, when the geometry has to be kept, please extend the query with p.geometry

SELECT p."USER_ID", COUNT() AS "count", p.geometry
FROM "points_layer" AS p
GROUP BY p."USER_ID"
HAVING COUNT() > 50
Taras
  • 32,823
  • 4
  • 66
  • 137
  • 1
    Thank you, @Taras! I got virtual layer with count for each user. The easiest way to proceed and get geometry is to join that two layers and to categorize by "COUNT" column? Am I right? – Burtsev Mar 31 '20 at 07:19
  • Please check the updates) – Taras Mar 31 '20 at 07:26