Answered by:
Bounding Box .STEnvelope(). for a set of geometry types OPTIMIZED.
Question

Hi, I'm working on a project that includes operations with huge vol of information and I am using SQL Server 2008 right now with the extenssion for spatial information of course. I've been looking for a way to obtain the bounding box of a set of geometry data types optimally. I though that maybe I'll be able to obtain that information from the spatial index itself 'cause by it's structure seemd natural but I've found up that is not. I can store for each element it's coord X Y Z ahhh and perform a one by one check but it'll be cost a lot, I can update global values in each insertion but again it'll cost a lot and I really need to make this as a query because it will be a dll at the end (I'm using C# but any .NET language will do). Is there anything that I can do to make it better?? I mean, I guess this is a common issue, I mean is oftenly needed right?
I kindda need in my own language...
Envelope (Union (foreach g in GeometrySet Envelope(g))) :o)
I'm new here so I don't know how it works, I'll log in latter but here is my email: liena@enet.cu. I'll be very very thankfull if someone can help me with this.Friday, March 12, 2010 2:33 PM
Answers

Hi,First of all, try to avoid unions when possible, the cost is very high . There is not really a good solution, but try this:For points:
with s1 as( select MIN(geom.STX) as X1 ,MIN(geom.STY) as Y1 ,MAX(geom.STX) as X2 ,MAX(geom.STY) as Y2 from TABLE) SELECT Geometry::Point(X1,Y1,0).STUnion(Geometry::Point(X2,Y2,0)).STEnvelope() FROM S1
and for other types of geometrieswith s1 as( select MIN(geom.STEnvelope().STPointN(1).STX) as X1 ,MIN(geom.STEnvelope().STPointN(1).STY) as Y1 ,MAX(geom.STEnvelope().STPointN(3).STX) as X2 ,MAX(geom.STEnvelope().STPointN(3).STY) as Y2 from TABLE) SELECT Geometry::Point(X1,Y1,0).STUnion(Geometry::Point(X2,Y2,0)).STEnvelope() FROM S1
Note that when creating points for the final box, you should use your srid instead of 0. Also, this solution only works for geometry type, since geography doesn't implement .STEnvelope()Hope this helps. Marked as answer by Alex Feng (SQL) Sunday, March 21, 2010 12:10 PM
Friday, March 12, 2010 11:24 PM
All replies

Hi,First of all, try to avoid unions when possible, the cost is very high . There is not really a good solution, but try this:For points:
with s1 as( select MIN(geom.STX) as X1 ,MIN(geom.STY) as Y1 ,MAX(geom.STX) as X2 ,MAX(geom.STY) as Y2 from TABLE) SELECT Geometry::Point(X1,Y1,0).STUnion(Geometry::Point(X2,Y2,0)).STEnvelope() FROM S1
and for other types of geometrieswith s1 as( select MIN(geom.STEnvelope().STPointN(1).STX) as X1 ,MIN(geom.STEnvelope().STPointN(1).STY) as Y1 ,MAX(geom.STEnvelope().STPointN(3).STX) as X2 ,MAX(geom.STEnvelope().STPointN(3).STY) as Y2 from TABLE) SELECT Geometry::Point(X1,Y1,0).STUnion(Geometry::Point(X2,Y2,0)).STEnvelope() FROM S1
Note that when creating points for the final box, you should use your srid instead of 0. Also, this solution only works for geometry type, since geography doesn't implement .STEnvelope()Hope this helps. Marked as answer by Alex Feng (SQL) Sunday, March 21, 2010 12:10 PM
Friday, March 12, 2010 11:24 PM 
What you want is available in the SQL Server Spatial Tools Project on CodePlex. Look for the GeometryEnvelopeAggregate.Thursday, March 18, 2010 3:05 AM

Hi,
First of all, try to avoid unions when possible, the cost is very high . There is not really a good solution, but try this:
</IV>For points:with s1 as( select MIN(geom.STX) as X1 ,MIN(geom.STY) as Y1 ,MAX(geom.STX) as X2 ,MAX(geom.STY) as Y2 from TABLE) SELECT Geometry::Point(X1,Y1,0).STUnion(Geometry::Point(X2,Y2,0)).STEnvelope() FROM S1
and for other types of geometries
with s1 as( select MIN(geom.STEnvelope().STPointN(1).STX) as X1 ,MIN(geom.STEnvelope().STPointN(1).STY) as Y1 ,MAX(geom.STEnvelope()STPointN(3).STX) as X2 ,MAX(geom.STEnvelope().STPointN(3).STY) as Y2 from TABLE) SELECT Geometry::Point(X1,Y1,0).STUnion(Geometry::Point(X2,Y2,0)).STEnvelope() FROM S1
Note that when creating points for the final box, you should use your srid instead of 0. Also, this solution only works for geometry type, since geography doesn't implement .STEnvelope()
Hope this helps. Marked As Answer byChunSong Feng MSFT<ABBR class=affil>MSFT</ABBR><ABBR class=affil>, Moderator</ABBR>Sunday, March 21, 2010 12:10 PM
It's very useful, Now I have a more clear idea about it.Thursday, September 9, 2010 11:17 PM