sql - Updating field to give count of points in polygon using STIntersects?

I have a points layer (dbo.ptLayer)

• Around 1M points


• Spatial Geometry Type (dbo.ptLayer.geom)


• No spatial index just yet, but will create one once data gathering complete.

I have a polygon layer (dbo.polygonLayer)

• Around 500 polygons.


• Spatial Geometry Type (dbo.polygonLayer.geom)

Both have fields called ID.

How do I populate an empty integer field in the polygon layer, with a count of the total number of points within each polygon?

Although I have access to other software products, I am interested to learn what can be done purely within SQL and SQL Server.

I believe I should be making use of STIntersects but would like to know what is the best way of doing an update to populate this field.

Answer

This should do what you need:

A select query:

SELECT polygons.id, Count(*) 
FROM points
JOIN polygons
ON polygons.ogr_geometry.STContains(points.ogr_geometry) = 1
GROUP BY polygons.id

With an update:

UPDATE polygons
SET [countcolumn] = counts.pointcount

FROM polygons
JOIN
(
 SELECT polygons.id, Count(*) 
 FROM points
 JOIN polygons
 ON polygons.ogr_geometry.STContains(points.ogr_geometry) = 1
 GROUP BY polygons.id
) counts ON polygons.id = counts.id

This is the result of of me running that query on one of my datasets