Solved

SQL Server Spatial Data Types

Posted on 2014-10-31
5
142 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
[X]
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
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 53

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

717 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