Solved

SQL Server Spatial Data Types

Posted on 2014-10-31
5
131 Views
Last Modified: 2015-03-18
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
Comment
Question by:Scott Fell,  EE MVE
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40416475
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
 
LVL 11

Expert Comment

by:David Kroll
ID: 40416484
I have my lat/lon fields as float.  Look up the Great Circle Distance TSQL formula to convert that to miles.
0
 
LVL 52

Author Comment

by:Scott Fell, EE MVE
ID: 40416510
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40416560
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
 
LVL 58

Assisted Solution

by:Gary
Gary earned 250 total points
ID: 40423072
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now