SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 best SQL datatype to store Longitude/Latitude
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/28/2006 :  19:06:06  Show Profile  Reply with Quote

What would be the best datatype to store Longitude/Latitude co-ordinates such as below? Is it decimal? I was hoping there was a better way, just making sure as I really want to keep this code as optimized as possible. I'm storing it as a double in the VB app code if that means anything.

Thanks again for any advice! :)
mike123


<longtitude>34.085508</longtitude>
<latitude>-118.405121</latitude>

<longtitude>28.305149</longtitude>
<latitude>-81.363864</latitude>

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/28/2006 :  19:30:49  Show Profile  Reply with Quote
It depends on what you want to do with them.

If you are planning on using them it great circle distance calculations using the Haversine distance formula or the circular law of cosines, you will have to convert the values to radians first, so it would make sense to convert them to float using the RADIANS() function, and store them that way.

I would probably store them both ways, as numeric latitude and longitude that are easy to read, and as float radians to eliminate the overhead of converting them on the fly when doing distance calculations.




CODO ERGO SUM

Edited by - Michael Valentine Jones on 11/28/2006 19:31:38
Go to Top of Page

mike123
Flowing Fount of Yak Knowledge

1462 Posts

Posted - 11/28/2006 :  21:15:06  Show Profile  Reply with Quote
All this mapping stuff is still new to me, so I'm not 100% sure what I will be doing with Radians, however I will keep your recommendations in mind.

As far as just storing the latitude/longitude values. When I store -118.405121 as a numeric, it rounds it to -118. Am I missing something?

Thanks again :)
mike123
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/28/2006 :  21:26:58  Show Profile  Reply with Quote
You're doing something wrong.
See example:

declare @t table (
lon numeric(10,6) ,
lat numeric(10,6) )

insert into @t
select	lon = 34.085508,
	lat = -118.405121

select * from @t

Results:

(1 row(s) affected)

lon          lat          
------------ ------------ 
34.085508    -118.405121

(1 row(s) affected)


CODO ERGO SUM
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 11/28/2006 :  21:30:29  Show Profile  Visit jezemine's Homepage  Reply with Quote
how did you define your numeric column? it works this way: numeric(x,y) where x is the total number of digits, and y is the number of digits after the decimal point. if your y=0, that would explain your rounding:

declare @t table (i numeric(9,0))
insert into @t select -118.405121
select * from @t

declare @tt table (i numeric(9,6))
insert into @tt select -118.405121
select * from @tt

EDIT: i didn't cheat and look at MVJ's example, promise. "simultaneous" posts. pretty similar though


SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

Edited by - jezemine on 11/28/2006 21:34:39
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/29/2006 :  01:36:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think the term is




Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 11/29/2006 :  01:46:34  Show Profile  Visit jezemine's Homepage  Reply with Quote
don't know how to make that fancy graphic :)


SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 11/29/2006 :  01:52:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
[SNIPED] with lowercase characters in the tag.
Most used when someone has earlier posted same solution as you.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 11/29/2006 01:54:07
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/29/2006 :  02:03:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by jezemine

don't know how to make that fancy graphic :)


SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org


Use reply with quote option in Peso's reply and you can know what is used there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/29/2006 :  07:50:03  Show Profile  Reply with Quote
You'd have to play with it and do some testing, but I've read people using CLR UDT's for geospacial data. I have yet to find a good use for CLR UDT's, though.

Jay White
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/29/2006 :  08:52:53  Show Profile  Reply with Quote
quote:
Originally posted by Page47

You'd have to play with it and do some testing, but I've read people using CLR UDT's for geospacial data. I have yet to find a good use for CLR UDT's, though.

Jay White




It would be interesting to compare performance of a CLR UDT against a regular SQL UDT for computing the great circle distance with the Haversine distance formula.

I was doing some testing recently with a Zip code table that had latitude and longitude to see how long it took to find all locations within a certain distance. It seemed to perform OK with for an occasional lookup, but I would want to optimize it as much as possible if there were a lot of lookups of this type.




CODO ERGO SUM
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000