Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

SQL Server Spatial Data Types

I have never used spatial data types.   If I am storing lat and lon as numeric like 48.413656  -108.351991

If I wanted to do distance calculations like find all rows within 5 miles, can I leave this as numeric or will converting to a spacial data type make more sense.  Or cast as spatial when running a query?
0
Scott Fell,  EE MVE
Asked:
Scott Fell,  EE MVE
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Depends on the accuracy you need, and whether you want to map the locations on a map in SSMS or SSRS.

If 5 miles is an approximation, and you are OK with (say) 5.1 or 4.9, then numeric will do. You just need to do a little maths based on Pythagoras Theorem to get your answer - not too difficult.
0
 
David KrollCommented:
I have my lat/lon fields as float.  Look up the Great Circle Distance TSQL formula to convert that to miles.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorAuthor Commented:
I understand the geometry part. I have always stored this as float, numeric, or as a string going way back.  

I think I may have misread what the geography data type is.  If I just need lat and lon to show on  a map then keeping it as is will be fine.  It looks like the geography type can handle polygons, points, curves etc.   If I stored rows of polygons and want to call up only polygons that intersect with another polygon, then that is what this field type looks like it is for.  Does that sound right?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's one of the things it's for.

Because the earth is not a perfectly round sphere, using simple maths for the distance between two points will get you roughly the right answer, but not precisely.

Doing a STD calculation of the distance between two points can make it more accurate - but in your case, I'm assuming such accuracy is not necessary.

Have a look at http://www.mssqltips.com/sqlservertip/2690/calculate-the-geographical-distance-between-two-cities-in-sql-server/ as an example of calculating using spatial means the difference between two points on the globe. Sounds like it would be an unnecessary complication for your use.
0
 
GaryCommented:
Use spatial geometry points, easier to index and a lot faster than comparing a lat/long columns, especially when you start getting into 10's of thousands of rows
Kinda the basis of why
http://www.experts-exchange.com/Q_28183404.html

The how
http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now