Spatial Data/Indexing performance issues

Question

Any advice on this?

http://www.sqlservercentral.com/Forums/Topic1292997-391-1.aspx

thanks,

Chris

Answer 1

Without trying to be obtuse, perhaps I can answer your question with another question: "What made you think that using the geography datatype would make this query faster than using the old skool method?"

The geography datatype allows you to describe and store complex shapes - LineStrings, Polygons, Curves (from SQL 2012), and collections of these types, each one potentially containing thousands or more points. These shapes are positioned on an ellipsoidal surface, using one of a recognised number of geodetic models that approximate the shape of the earth. It provides methods not only to derive the distance between points, but to perform complex tests such as whether (and where) two shapes intersect, whether two shapes cross, overlap, or are contained within one another, and to calculate the area contained within an abstract shape, for example.

So, the geography datatype is good for performing accurate spatial computations between complex geometries. The trade-off is that it is not necessarily as fast as "old-skool" methods for performing simple Point-to-Point distance calculations. If you're happy using a simple approximation of the shape of the earth as a sphere, and you're only dealing with locations that can be described by a single pair of latitude/longitude coordinates, and you only want to do simple calculations such as straight-line distance, you will almost always get better performance using simple numerical methods on two columns of the float datatype.

I covered this a little in my talk at SQLBits 5 - you can see the slide deck and video online:

http://sqlbits.com/Sessions/Event5/Creating_High_Performance_Spatial_Databases

---

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

Answer 2

Thats a very good question and the answer is that it was more of a presumption than anything else. I thought i'd give the spatial route a try to see if it could/would improve the search but from viewing you SQLBits presentation/slides I'm barking up the wrong tree :).

Thanks for the response and information, I'll look into other ways I can tune this process.