Bounding Box for Polygon

Question

I have around 40,000 polygons stored as geometry in my table. Now I want to create spatial index on that geometry column. While creating spatial Index it is asking me for Bounding box values. Can you please help me on how to find my bounding. in order to get my bounding box i need to find my xmin,ymin,xmax,ymax.

Thanks

Answer 1

EDIT: You could use EnvelopeAggregate in place of the UnionAggregate and STEnvelope of course...

---

You can perform a UnionAggregate on all the polygons, put an STEnvelope around them and visually pick out the XMin, YMin, XMax, YMax values. Of course you can do some TSQL manipulation of the STAsText of the bounding box, but I'll leave that as a manual exercise.

That gives you a box that covers your existing polygons, but you should consider how much padding you need or what bounds are necessary for future data.

Sample:

use tempdb;
create table GeometryTest( id int identity primary key, geom Geometry );
insert GeometryTest values ( 'POLYGON((-130 54, -130 23, -60 23, -60 54, -130 54))' );
insert GeometryTest values ( 'POLYGON((1 0, 0 0, 0 1, 1 1, 3 5, 1 0))' );
insert GeometryTest values ( 'POLYGON((0 0, -100 5, 0 60, 70 70, 3 5, 0 0))' );

select geometry::UnionAggregate ( geom ).STEnvelope().STAsText()
from GeometryTest;

---------------------------------------------------------------
POLYGON ((-130 0, 70 0, 70 70, -130 70, -130 0))
---------------------------------------------------------------

create spatial index six_GeometryTest_geom on GeometryTest(geom)
    WITH (BOUNDING_BOX = (-130, 0, 70, 70));