locked
Bounding Box .STEnvelope(). for a set of geometry types OPTIMIZED. RRS feed

  • 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 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.
    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 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.
    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.

    It's very useful, Now I have a more clear idea about it.
    Thursday, September 9, 2010 11:17 PM