CODEWITHSUNDEEP
sql-server-2008geospatial
Nate Zaugg
Nate Zaugg

Reputation: 4218

SQL Server 2008 approximating .STWithin() on a Geography data column

I am using SQL 2008 Geography datatype for a project that deals with mapping out some areas. I need to check to make sure a smaller area is fully within the bounds of a larger area. With the Geometry datatype I could simply use .STWithin().

I would simply make the polygon's geometry datatypes as well as geography, but I'm not sure if using a polygon that was mapped on geography coordinants will work as expected if used as a simple geometry type.

Is there any way to do something like .STWithin() on a geography datatype?

Upvotes: 0

Views: 1986

Answers (1)

psousa
psousa

Reputation: 6726

Well, this isn't an approach as clean as using STWithin, but you could do it using "STDifference". I've prepared a small example:

Basically there's a large rectangle which serves as the container. There's also two rectangles, "inside" and "outside", where the first one is fully contained on the "container" and the second one only partially.

The idea is just to check if the STDifference between the child and the parent has any points (the trick is in the direction of the call).

declare @container geography;
declare @inside geography;
declare @outside geography;

select @container = geography::STGeomFromText('POLYGON((-10 10, -10 0, 0 0, 0 10, -10 10))', 4326);  -- large rectangle (container)

select @inside = geography::STGeomFromText('POLYGON((-9 9, -9 1, -1 1, -1 9, -9 9))', 4326); -- smaller rectangle (fully contained)

select @outside = geography::STGeomFromText('POLYGON((-9 9, -9 1, 2 1, 2 9, -9 9))', 4326); -- smaller rectangle (partially contained)

select @inside.STDifference(@container).STNumPoints() as [Points Outside Container] -- returns 0

select @outside.STDifference(@container).STNumPoints() as [Points Outside Container] -- returns > 0

Upvotes: 1

Related Questions