Solved

SQL Server Spatial Data Types

Posted on 2014-10-31
5
140 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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