How to count how many points intersect each polygon using SQL SERVER

Question

Hi so I have database of Polygons and Points with geometry data. I want to see how many intersect per polygon just showing the count of polygons with points in them.

This my script of finding the intersected points and polygons:

SELECT NEATCELL FROM 
[dbo].[POLYGON] as p,[PLACES6].[dbo].[Points] as h
WHERE P.NEATCELL.STIntersects(h.PointsGEOM) = 1  

Now I want to find how many points are there in each intersected polygon using the count function.So for this script I wanna do a count of how many points are there in neatcell. How do I go about doing that?

Answer 1

The following query should get you the result you want:

SELECT 
    NEATCELL, 
    COUNT(*) AS NumberOfIntersections 
FROM 
    [dbo].[POLYGON] as p,
    [PLACES6].[dbo].[Points] as h
WHERE 
    P.NEATCELL.STIntersects(h.PointsGEOM) = 1
GROUP BY 
    NEATCELL