-1

I have a table A that has scattered points existing in space. Table B has a number of squared perimeters. I want an SQL code that will write a table of the number of points in table A that fall in the squared perimeters of table B.

I am writing this SQL in QGIS.

This is what the problem looks like:

Whereby: The dots of Table A are in Blue and the squared blocks are in Table B.

enter image description here

Output should be something like:

Claim Cell # | Count

1 = 9, 2 = 0, 3 = 0,

Etc...

So far I have:

select "TENURE_NUM", count(*) from Samples the Station_ID, but I don't know what to do next, I am trying to look at examples online but I have never really used SQL before.

Raboush2
  • 39
  • 5

1 Answers1

0

from what I understand maybe the following SQL request is a solution for your work :

-- Select the columns for output table
-- a = dots , b = square
SELECT
   b.number,
   count(a.*) as nbr_of_dots
FROM a, b

-- Condition for intersection and group for each square

WHERE st_intersects(a.geom,b.geom)
GROUP BY b.geom,b.id;
Pateaugruyere
  • 1