How to create a Virtual layer with point geometry and count repetitions

Question

From a table with the following fields:

CODE1	CODE2	XCOORD	YCOORD
162	6605548EG7180N0001SF	418596,65	4620206,74
196	6604802EG7180S0001SX	418477,91	4620364,53
196	6604802EG7180S0001SX	418477,91	4620364,53
196	6604802EG7180S0001SX	418477,91	4620364,53
271	6605571EG7180N0001MF	418505,91	4620339,07
347	6605569EG7180N0001OF	418513,25	4620328,71
415	6605566EG7180N0001TF	418523,92	4620312,81
663	6604801EG7180S0001EX	418473,32	4620369,35
713	6604832EG7180S0002KM	418593,37	4620192,22
713	6604832EG7180S0002KM	418593,37	4620192,22
1128	6605547EG7180N0009AX	418603,38	4620193,32
1626	6604806EG7180S0001WX	418491,55	4620344,03
1695	6604830EG7180S0001XX	418583,67	4620205,61
1757	6605551EG7180N0001SF	418572,92	4620238,87
1757	6605551EG7180N0001SF	418572,92	4620238,87
1757	6605551EG7180N0001SF	418572,92	4620238,87
1757	6605551EG7180N0001SF	418572,92	4620238,87
1757	6605551EG7180N0001SF	418572,92	4620238,87
1757	6605551EG7180N0001SF	418572,92	4620238,87

I would like to create a virtual layer that does the following:

1- Group by the field 'CODE1'

2- Draw a point geometry at coordinates 'XCOORD' and 'YCOORD'

3- Count the number of repetitions by 'CODE1'

For example, for the resulting layer with point geometry, the table structure would be:

JGH · Accepted Answer · 2024-04-02 11:25:47Z

5

You can use group by and count...

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query. Replace 1234 by your CRS ID.

SELECT code1, code2, count(*) as REPETITIONS, MakePoint(XCOORD,YCOORD,1234) as geometry
FROM myLayer
GROUP BY code1, code2

PS: Depending how the primary key is defined, you might have to use min(xcoord) and min(ycoord)

answered Apr 2, 2024 at 11:08

JGH

44.4k3 gold badges50 silver badges96 bronze badges

1 Answer 1