Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Server Spatial Data Types

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

860 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