1

I have two columns Northing and Easting which is in NZTM 2000 format. From these two columns I calculate my Geometry column using geometry:: Point(Easting,Northing,0). Next step in to create a bounding box to create Spatial Index on my geometry column. While creating spatial Index SQL Server 2008 R2 is asking me for Bound Box i.e. X Min ,Y Min, X Max, Y Max.

So How I need to calculate my bounding box to create spatial index on my geometry column. Can i find the min(Northing) = X Min, min(Easting), max(Northing), Max(Easting). Can some one help me if I am doing correct calculation to create bounding box for my spatial index?

The reason I want to create index is because I want to use STContains() to check where my point exists in that region or not.

Cheers,

ace_mccloud
  • 497
  • 1
  • 8
  • 25

2 Answers2

1

This would depend if you want your index to cover all your data or just parts of it. For a full coverage you would use:
XMIN: -180
YMIN: -90
XMAX: 180
YMAX: 90

Reference: Beginning Spatial with SQL server By Alastair Aitchison
(I can strongly recommend this book)

Tomas
  • 3,573
  • 2
  • 20
  • 25
  • Hi Tomas, Thanks for your reply. I can't put -180,180 because my points are not in geography they are in geometry. I think I can't use -180,180... as they will be used when my data is in geography. can you please correct me? – ace_mccloud Nov 26 '12 at 21:10
  • 2
    Hi, you cannot easily mix between geography and geometry. The first is a round earth system, while the other is a flat earth system. See http://stackoverflow.com/questions/1867905/sql-2008-geography-geometry-which-to-use if you need to convert between them then you need to reproject. Geography do not need/support any bounding box. – Tomas Nov 26 '12 at 21:54
1

I haven't used Eastings and Northings, but I believe the same logic should apply.

I create a ZIP Code bounding box from a geometry using the following code:

UPDATE BBox.dbo.ZIPs
SET bbox = geom.STEnvelope()

I also highly recommend getting the book that Tomas mentioned.

user1473461
  • 369
  • 2
  • 2