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.
Best Answer
This should do what you need:
A select query:
With an update:
This is the result of of me running that query on one of my datasets