2

I have two tables that I am storing geometries in.

CREATE TABLE config.region
(
    id int identity(1,1) PRIMARY KEY,
    polygon GEOMETRY NOT NULL
)

CREATE TABLE config.location
(
    id int identity(1,1) PRIMARY KEY,
    position GEOMETRY
)

The region table will hold rectangular polygons. The location table will hold only points.

What I want to do is select all the regions and a COUNT of how many points are actually within each polygon.

I've come up with this query, that shows the polygon id, the location id and whether or not the location is in the polygon. 

SELECT 
    polygon.id as pid, 
    config.location.id as lid, 
    polygon,
    polygon.STContains(config.location.position) as within 
FROM 
    config.polygon, config.location

How can I modify this to give counts instead of just a list of whether or not they are within each other?

chance
  • 6,307
  • 13
  • 46
  • 70
brianestey
  • 8,202
  • 5
  • 33
  • 48

1 Answers1

3

I don't have SQL Server 2008 so I can't test it now. You can try this:

select r.id, count(*) as qty
from config.region r
join config.location l on r.polygon.STContains(l.position) = 1
group by r.id

All polygons:

select p.*, isnull(t.qty, 0) as points_within_polygon
from config.region p
left join (
    select r.id, count(*) as qty
    from config.region r
    join config.location l on r.polygon.STContains(l.position) = 1
    group by r.id
) t on t.id = p.id

Added: Following code as subqery should work fine as well. You can try which is faster for you.

select r.id, sum(cast(r.polygon.STContains(l.position) as int)) as qty
from config.region r
cross join config.location l
group by r.id

Update: Casting bit to int added.

brianestey
  • 8,202
  • 5
  • 33
  • 48
Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • Thanks a lot! The first two queries work great! The last gives the error 'Operand data type bit is invalid for sum operator.' – brianestey Dec 13 '11 at 02:53
  • @brianestey, I've corrected second query (casting added) now this should work as subquery as well. – Michał Powaga Dec 13 '11 at 06:48
  • I got the last query to work but needed to add a group by statement - I edited the answer to include it. Thanks again for the help! – brianestey Dec 14 '11 at 01:52