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

Permission

Iterate over all vertexes, keeping track of the furthest left, furthest right, furthest up and furthest down xs and ys as you do. (Or do it when first creating, if it's too slow) – Evangelineevangelism

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));

Interdisciplinary

Is there a way to do this in SQL server 2008 ? UnionAggregate and EnvelopeAggregate don´t exist there yet.. – Imitative

@Imitative This is SQL server 2008. Maybe you mean 2005 or 2008 with another compatibility mode? – Interdisciplinary