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

Question

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 1

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