CODEWITHSUNDEEP
sqlsql-server
Marcus Ohlsson
Marcus Ohlsson

Reputation: 267

Count all point in geo polygon (sql server 2014)

I have one table that holds alot of location that is mapped using geo polygons

And then I have another table with properties that has a location that is a "geography" property

If I use this code I get the number of properties in a specific location:

SELECT COUNT(id) FROM Property 
WHERE [Status] = 1 
AND Location.Filter(Geography::Parse('POLYGON ((8.476 40.2591,8.4618 40.241,8.4787 40.2403,8.4851 40.2539,8.476 40.2591))')) = 1

But the issue is that I what to know how many properties that is located in every location (the polygons used in my query is from the location table)

The only connection between the tables are the location point

I really needs some help here

EDIT:

Thanks to Ben I finally got it to work, here's the code that works for me :)

    SELECT location.Id, (SELECT COUNT(property.Id) FROM Property AS property WHERE property.Location.Filter(Geography::Parse('POLYGON ((' + location.GeoPolygon + '))')) = 1) AS PropertyCount 
FROM Location AS location

Upvotes: 0

Views: 184

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

Something like this should work:

SELECT l.LocationID, count(*)
FROM dbo.Location as l
JOIN dbo.Property as p
   on l.geoColumn.STContains(p.Location) = 1

I'm assuming a structure on your Location table of something like:

create table dbo.Location (
   LocationID int,
   geoColumn geography,
   …
)

Upvotes: 1

Related Questions