Point in bounding box problem

• Question

•

Hi,

I have just got started with Katmai and have got stuck trying to think of the best way to test if a Point is inside a Bounding Box.

I know that I could use STContains or STIntersects but they both do not give me the result I am looking for, let me explain further.

If I had a grid like the one below, and each grid square I have given an index from 0..3.

0,0         1,0           2,0

 0 1 2 3

0,2        1,2           2,2

If I had a point at 1,1, which would be at the center of the grid, STContains() would fail, which is correct, but I want that point to belong to one of the grid squares even if the point happens to straddle a grid line.

I could say that:

if (point.x >= min.x && point.y >= min.y && point.x < max.x && point.y < max.y)

where min is the top left corner of a bounding box and max is the bottom right corner of a bounding box, this would ensure that a point would always end up in one of the grid squares.

I would be grateful if anyone could suggest how I can solve this issue by using the Spatial functions.

I am sorry my example may seem a bit back to front, if I was to explain it again I would use the center of my 4 grid squares as coordinates 0,0

Wednesday, July 23, 2008 9:04 PM

• STContains only returns features that are completely within. You other query also allows for features that intersect on the left and bottom side of the box.

You can still utilize the index though. Just add a spatial query to it to filter most points out and then filter the ones that are at the two edges:

SELECT Location.STX as X, Location.STY as Y FROM Place

WHERE @BoundingBox.STIntersects(Location) = 1 AND

Location.STX < @BoundingBox.STPointN(3).STX AND

Location.STY < @BoundingBox.STPointN(3).STY;

This way, the two last conditions will only be run on a limited set of features (however you might have to verify the execution plan to ensure that this is what it will do).

Thursday, July 24, 2008 6:14 AM

All replies

• Just want to explain this problem a bit further with some SQL.

I have 100 Points equally spaced 1 unit apart and this is the query that gets me the results I want

DECLARE @BoundingBox GEOMETRY

SET @BoundingBox = GEOMETRY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))',0)

SELECT Location.STX AS X, Location.STY AS Y FROM Place

WHERE

Location.STX >= @BoundingBox.STPointN(1).STX AND

Location.STY >= @BoundingBox.STPointN(1).STY AND

Location.STX < @BoundingBox.STPointN(3).STX AND

Location.STY < @BoundingBox.STPointN(3).STY

This gives:

X Y
0 0
0 1
1 0
1 1

A query using STContains

DECLARE @BoundingBox GEOMETRY

SET @BoundingBox = GEOMETRY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))',0)

SELECT Location.STX as X, Location.STY as Y FROM Place

WHERE @BoundingBox.STContains(Location) = 1

gives this:

X Y

1 1

I am afraid using the first method, which gives me the results I want will not take advantage of spatial indexing, then again I am no SQL guru so I may be overlooking something, any help appreciated

Wednesday, July 23, 2008 9:40 PM
• STContains only returns features that are completely within. You other query also allows for features that intersect on the left and bottom side of the box.

You can still utilize the index though. Just add a spatial query to it to filter most points out and then filter the ones that are at the two edges:

SELECT Location.STX as X, Location.STY as Y FROM Place

WHERE @BoundingBox.STIntersects(Location) = 1 AND

Location.STX < @BoundingBox.STPointN(3).STX AND

Location.STY < @BoundingBox.STPointN(3).STY;

This way, the two last conditions will only be run on a limited set of features (however you might have to verify the execution plan to ensure that this is what it will do).

Thursday, July 24, 2008 6:14 AM
•

Cool thanks, that does utilize the index and its still very fast, less than a second on a million records compared to the non spatial function way that takes 35 seconds!

Thursday, July 24, 2008 9:29 AM