Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

I want to 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.

share|improve this question
1  
STIntersects is almost never what you want with points or linestrings. Nathan's response (using STContains) shows the operation you want. –  BradHards Dec 21 '12 at 6:21

1 Answer

up vote 6 down vote accepted

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

enter image description here

share|improve this answer
1  
Is it really meant to be counts.di? –  BradHards Dec 21 '12 at 6:21
 
opps fixed it now –  Nathan W Dec 21 '12 at 8:41
 
I suspect it is the "JOIN PC" bit. It should be "JOIN [nosde].[dbo].[SA2_2011_3857] PC" –  Nathan W Dec 22 '12 at 7:20
 
i.e. just copy the whole select query and put it inside the ( ) –  Nathan W Dec 22 '12 at 7:21
1  
This happens because the JOIN and SELECT inside the () get run first so it doesn't know anything about your FROM [nosde].[dbo].[SA2_2011_3857] PC in the UPDATE bit –  Nathan W Dec 22 '12 at 7:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.