Answered by:
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
RosemaryFriday, October 23, 2009 3:41 AM
Answers

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 Zcoordinate 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. Proposed as answer by Alex Feng (SQL) Monday, October 26, 2009 5:36 AM
 Marked as answer by Alex Feng (SQL) Tuesday, October 27, 2009 10:24 AM
Friday, October 23, 2009 5:40 AM 
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 Proposed as answer by Alex Feng (SQL) Monday, October 26, 2009 5:36 AM
 Marked as answer by Alex Feng (SQL) Tuesday, October 27, 2009 10:24 AM
Friday, October 23, 2009 7:53 AMAnswerer
All replies

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 Zcoordinate 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. Proposed as answer by Alex Feng (SQL) Monday, October 26, 2009 5:36 AM
 Marked as answer by Alex Feng (SQL) Tuesday, October 27, 2009 10:24 AM
Friday, October 23, 2009 5:40 AM 
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 Proposed as answer by Alex Feng (SQL) Monday, October 26, 2009 5:36 AM
 Marked as answer by Alex Feng (SQL) Tuesday, October 27, 2009 10:24 AM
Friday, October 23, 2009 7:53 AMAnswerer