CODEWITHSUNDEEP
sql-servergeometrygeospatial
Unforgiven
Unforgiven

Reputation: 2049

SQL Server : Spatial query

Consider we have some areas and multiple points are stored in two tables in SQL Server :

CREATE TABLE [dbo].[Areas](
    [Id] [int] NOT NULL,
        [Location] [geometry] NOT NULL)


        CREATE TABLE [dbo].[Points](
    [Id] [int] NOT NULL,
        [Location] [geometry] NOT NULL)

I know the function STIntersects uses to check if a polygon contains a point or not .
Is there any way to find points in areas for each area using single query ?
I don't know how to find points in areas because there are multiple areas . should I use cursor or for iteration ? If yes how ?

Upvotes: 0

Views: 4324

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

You need a way to compare every point to every area. A cross join accomplishes that. So something like this:

select a.Id as [AreaId]
     , p.Id as [PointId]
from dbo.Areas as a
cross join dbo.Points as p
where a.Location.STContains(p.Location) = 1

Upvotes: 1

Nick
Nick

Reputation: 146

Interesting. I've never used the geometry data type. But here's my first guess.

You can use the geometry method STWithin of the Point geometry field and pass in the Area geometry object to see whether or not the point is "within" the area

declare @Areas table ( AreaID int identity(1,1), Description varchar(50), Area geometry)
declare @Points table ( PointID int identity(1,1), Description varchar(50), Point geometry )

insert @Areas ( Description, Area )
select 'Area 1', geometry::STGeomFromText('POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))', 0)

insert @Areas ( Description, Area )
select 'Area 2', geometry::STGeomFromText('POLYGON((10 10, 10 15, 15 15, 15 10, 10 10))', 0)

insert @Points ( Description, Point )
select 'Point 1', geometry::STPointFromText('POINT(2 2)', 0)

insert @Points ( Description, Point )
select 'Point 2', geometry::STPointFromText('POINT(4 4)', 0)

insert @Points ( Description, Point )
select 'Point 3', geometry::STPointFromText('POINT(12 13)', 0)


select a.Description, p.Description, case when p.Point.STWithin(a.Area) = 1 then 'Yes' else 'No' end as PointWithin
from @Areas a, @Points p

Results:

Description Description PointWithin
----------- ----------- -----------
Area 1      Point 1     Yes
Area 2      Point 1     No
Area 1      Point 2     No
Area 2      Point 2     No
Area 1      Point 3     No
Area 2      Point 3     Yes

Hope this helps

Upvotes: 2

Related Questions