3

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:

CODE1 CODE2 REPETITIONS
162 6605548EG7180N0001SF 1
196 6604802EG7180S0001SX 3
271 6605571EG7180N0001MF 1
347 6605569EG7180N0001OF 1
415 6605566EG7180N0001TF 1
663 6604801EG7180S0001EX 1
713 6604832EG7180S0002KM 2
1128 6605547EG7180N0009AX 1
1626 6604806EG7180S0001WX 1
1695 6604830EG7180S0001XX 1
1757 6605551EG7180N0001SF 6

1 Answer 1

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)

3
  • Could you give more details in which conditions it is recommended to use the functions min(xcoord) and min(ycoord), and could you add an example of the virtual layer query?
    – Acperience
    Commented Apr 2 at 12:54
  • 1
    sqlite.org/…
    – JGH
    Commented Apr 2 at 15:06
  • Very useful, thank you
    – Acperience
    Commented Apr 4 at 11:32

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.