locked
Best way of displaying multiple polygons together? RRS feed

  • Question

  • Help Please,

    I have a geometry data stored in Table W.  What is the best way of showing all  400 multi polygons in sql server 2008r2 by the use of a query instead of manually loading the coordinates and displaying the polygons one by one?  Thanks:

    I have a sample bellow: 

    PK

    Xmax

    Xmin

    Ymax

    Ymin

    Bottom_Left

    Upper_Left

    Upper_Right

    Lower_Right

    1

    134084

    132893

    388276

    386410

    POINT (132893 386410)

    POINT (132893 388276)

    POINT (134084 388276)

    POINT (134084 386410)

    2

    133943

    133364

    388200

    386746

    POINT (133364 386746)

    POINT (133364 388200)

    POINT (133943 388200)

    POINT (133943 386746)

    3

    133112

    133079

    386791

    386756

    POINT (133079 386756)

    POINT (133079 386791)

    POINT (133112 386791)

    POINT (133112 386756)

    Wednesday, April 4, 2012 5:43 AM

Answers

  • Or, if SQLCLR isn't an option, you can just use Union() to append each geometry together individually such as SELECT @g = @g.STUnion(geomCol) FROM TableW;

    Do you actually have geometry polygon data in this table? The example structure you've listed just seems to contain extents as points or individual coordinate values.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Abhijit- SQL2008 Wednesday, April 4, 2012 12:46 PM
    • Marked as answer by Peja Tao Tuesday, April 10, 2012 2:57 AM
    Wednesday, April 4, 2012 7:07 AM
    Answerer
  • So, does this give you what you want?

    SELECT Bottom_Left.STUnion(Upper_Right).STEnvelope()
    FROM TableW;


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Peja Tao Friday, April 6, 2012 1:42 AM
    • Marked as answer by Peja Tao Tuesday, April 10, 2012 2:57 AM
    Wednesday, April 4, 2012 6:11 PM
    Answerer

All replies

  • If you have SQL Server 2012, use geometry::UnionAggregate. If not, take a look at SQL Server Spatial Tools on Codeplex. http://sqlspatialtools.codeplex.com/ I believe there is a geometry union aggregate there.
     
    Cheers, Bob
    Wednesday, April 4, 2012 6:03 AM
  • Or, if SQLCLR isn't an option, you can just use Union() to append each geometry together individually such as SELECT @g = @g.STUnion(geomCol) FROM TableW;

    Do you actually have geometry polygon data in this table? The example structure you've listed just seems to contain extents as points or individual coordinate values.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Abhijit- SQL2008 Wednesday, April 4, 2012 12:46 PM
    • Marked as answer by Peja Tao Tuesday, April 10, 2012 2:57 AM
    Wednesday, April 4, 2012 7:07 AM
    Answerer
  • HI

    SQL SERVER 2008 Has Geom.STUnion(< Column Name >) But it is slower than Geometry::UnionAggrigate()

    if you Have SQL CLR Enabled then you can use this command

    Example : SELECT LocationTypes,Geometry::UnionAggregate(Geom) From Locations  Group BY  LocationTypes

    Wednesday, April 4, 2012 12:56 PM
  • Hello Tanoshimi. Thanks for your answer. Actually I have points or individual coordinate values and am trying to form a polygon from each row of cordinate values. For instance for  Primary Key 1, I want to form one polygon from these points. and then repeat it for all the remaining PK's. Thanks

    PK 1:  POINT (132893 386410)

    POINT (132893 388276)

    POINT (134084 388276)

    POINT (134084 386410)    

    POINT (132893 386410)

    Wednesday, April 4, 2012 5:31 PM
  • Thanks for your answer. Please I have only 2008r2 version.

    Wednesday, April 4, 2012 5:32 PM
  • So, does this give you what you want?

    SELECT Bottom_Left.STUnion(Upper_Right).STEnvelope()
    FROM TableW;


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by Peja Tao Friday, April 6, 2012 1:42 AM
    • Marked as answer by Peja Tao Tuesday, April 10, 2012 2:57 AM
    Wednesday, April 4, 2012 6:11 PM
    Answerer