locked
Using numeric or spatial data types? RRS feed

  • Question

  • Hello,

    I am not a professional database designer, nor a beginner. but never worked with spatial types.

    I am making an MVC website where the user can find other users around him within given radius, i don't need this search to be so accurate, but i want it fast. so i want to save the latitudes and longitudes as numeric in the database, so for example to find people within 1 kilometer i query for latitudes and longitudes who fall in the range (+/- 0.01).

    another option is to work with spatial data type like geography.

    so will the clustered indexes be faster than spatial index? or there are other benefits of spatial data types in my use case that i can't see?

    (remember i don't need accurate calculations).

    Alan-SY

    Saturday, October 6, 2012 4:15 PM

Answers

  • If that’s all you ever want to do then, from the feedback I’ve received, numeric would be faster. You’d be sacrificing linestrings and polygons and the collection types like multipoint, and other spatial methods on points. You can have both using a persisted computed column and the static spatial Point() method, so you could do your own speed/accuracy tests if you wanted. Or migrate in future.
     
    Hope this helps,
    Bob
    • Marked as answer by Alan-Sy Sunday, October 14, 2012 5:32 PM
    Saturday, October 6, 2012 5:35 PM
  • Nope - I agree ;)

    Subject to the conditions that...

    • you only want to consider point locations (not lines, areas, collections etc.)
    • you only want to consider the distance between those locations (and not, say, any more complicated spatial/topological relationships)
    • you are happy to tolerate a degree of inaccuracy in the calculation caused by treating lat/long as measured on a flat plane, or a perfect sphere (rather than operating on a more accurate ellipsoidal model)

    ....then using simple maths on numeric columns with clustered indexes will be significantly faster than using the geography datatype with a spatial index.


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Marked as answer by Alan-Sy Sunday, October 14, 2012 5:32 PM
    Wednesday, October 10, 2012 8:43 AM
    Answerer

All replies

  • If that’s all you ever want to do then, from the feedback I’ve received, numeric would be faster. You’d be sacrificing linestrings and polygons and the collection types like multipoint, and other spatial methods on points. You can have both using a persisted computed column and the static spatial Point() method, so you could do your own speed/accuracy tests if you wanted. Or migrate in future.
     
    Hope this helps,
    Bob
    • Marked as answer by Alan-Sy Sunday, October 14, 2012 5:32 PM
    Saturday, October 6, 2012 5:35 PM
  • Thanks for the reply Bob :). anybody has different opinion?

    Alan-SY

    Wednesday, October 10, 2012 7:14 AM
  • Nope - I agree ;)

    Subject to the conditions that...

    • you only want to consider point locations (not lines, areas, collections etc.)
    • you only want to consider the distance between those locations (and not, say, any more complicated spatial/topological relationships)
    • you are happy to tolerate a degree of inaccuracy in the calculation caused by treating lat/long as measured on a flat plane, or a perfect sphere (rather than operating on a more accurate ellipsoidal model)

    ....then using simple maths on numeric columns with clustered indexes will be significantly faster than using the geography datatype with a spatial index.


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    • Marked as answer by Alan-Sy Sunday, October 14, 2012 5:32 PM
    Wednesday, October 10, 2012 8:43 AM
    Answerer
  • Thank you guys :)

    Alan-SY

    Sunday, October 14, 2012 5:20 PM