Checking if a point intersects within a 3d polygon

Question

Hello,

I have read the "Beginning Spatial with SQL server 2008" and checked on the web, but I cannot find anyway how to do the following inside the DB. So I am not sure if it can even be done.

What I am trying to find a way that for a given point(x,y,z) does it intersect/contain within a 3D polygon. I know I can do it in 2D, which I have tried and it works fine.

So the actual problem I am trying to solve is -
I want to store in the DB polygons that reperesnt layers, so each layer will have a depth and then I can say for a xyz point it will be in this layer..
Is this possible?

Any help would be greatly appretiated

Thank you
Rosemary

Answer 1

Hi Rosemary,

One question: for the types of 3D polygons that you want to store - do all the points in the polygon have the same value for their Z-coordinate or does each point of the polygon have a different Z value? If the former is the case is very easy to use SQL Server Spatial to find if a point is in a polygon. However if not you are out of luck, since SQL Server 2008 does not yet support true 3D geometries.

Answer 2

Just to codify what Ivor has already said, if each layer has a constant depth, such as this:

DECLARE @Table TABLE (
  layerId int IDENTITY(1,1),
  layer geometry
);

INSERT INTO @Table(layer) VALUES
  (geometry::STPolyFromText('POLYGON((0 0 0, 5 0 0, 5 5 0, 0 5 0, 0 0 0))', 0)),
  (geometry::STPolyFromText('POLYGON((2 1 1, 8 1 1, 8 10 1, 2 10 1, 2 1 1))', 0)),
  (geometry::STPolyFromText('POLYGON((1 3 2, 6 3 2, 6 6 2, 1 6 2, 1 3 2))', 0));

Then you can define a point and test whether the Z value of that point equals the Z value of a point in the polygon layer, such as this:

-- Declare a point
DECLARE @Point geometry = geometry::STPointFromText('POINT(4 4 0)', 0);

-- This returns intersecting polygons in every layer

SELECT * FROM @Table WHERE layer.STIntersects(@Point) = 1;

-- This returns only the first polygon (which has z = 0)
SELECT *
FROM @Table
WHERE
  layer.STIntersects(@Point) = 1
  AND layer.STPointN(1).Z = @Point.Z;

---

Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290