locked
for calculations which datatype is faster? RRS feed

  • Question

  • Hello all,

    After a bit of reading up, my understanding (which might be wrong) is that if super accuracy is not of utmost importance... doing calculations such as STDistance on a geometry datatype might be faster than a geography datatype?

    Is this true? If I have one pair of lat long values for each zip9 value, and i dont need 20 FT accuracy (and so assuming that the difference in distance from a set of zipcodes within a 100 miles of each other due to the curvature of the earth as compared to a flat map calculation is negligible) do i tend to gain faster query performance by using a geometry datatype rather than a geography datatype when doing STDistance?

    The reason I ask is when reading up how sql server breaks down these 2 datatypes (shown here)
    http://msdn.microsoft.com/en-us/library/bb964712.aspx

    it seems that it does a bit more work when it comes to the geography type, in comparison to the geometry datatype.

    any thoughts? sorry...i'm a spatial newb and your input would really help my understanding of this.
    Friday, February 20, 2009 4:41 PM

All replies

  • Distance is the length of a straight line drawn between two points.  The definition of a "straight line" is quite different between Geometry and Geography types, and therefore, you will get vastly different results as the points get further and futher apart. 

    This is further complicated by the fact that result that Geometry will give you will be in the units that your points are defined in (degrees?), while Geography will give you the distance in the units that the spatial reference system uses (meters?).

    I plotted some straight lines using Virtual Earth in 2D and 3D modes, and posted them in this post so that people can visualize the differences between how distance is calculated by each spatial type.

    http://www.jasonfollas.com/blog/archive/2008/05/16/sql-server-2008-spatial-data-part-7.aspx



    (But all other things considered, it's going to be vastly faster for Geometry to calculate the distance between two points than Geography.  This is because Geometry only needs to calculate the hypotenuse of a triangle, which has two subtractions, two multiplications, an addition, and a square root operation.  Geography has to use trig functions like SIN and COS, maybe a ATAN2 or two thrown in there for fun, etc...  vastly more complicated, and therefore, slower to calculate overall).
    Friday, February 20, 2009 5:47 PM
  • The geometry datatype is faster than the geography datatype for any calculations that involve metrics (STArea(), STDistance(), STLength()) etc., for the reasons Jason explained - the curved model on which the geography datatype operates is more complex than the flat plane of the geometry datatype.
    For other methods (STGeometryType(), STDimension(), STNumPoints() etc.), they are both the same.

    Even though speed of performance does differ slightly, I wouldn't recommend that you use this as the deciding factor when choosing which type to use. Accuracy of results (especially over large areas) is a much more significant feature, and this is where geography data is significantly better than the geometry datatype, which suffers from distortions introduced from projection. There's other factors to consider as well - geometry datatype is OGC-compliant, for example.
    A lot of the time, if you're planning on importing existing data, your choice of datatype is governed for you based on the format in which that data is supplied. If you've got geographic coordinates (i.e. longitude and latitude) then use the geography datatype. If you've got projected coordinates (state plane system, national grid, or UTM, for example) then use geometry.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Friday, February 20, 2009 7:41 PM
    Answerer