Author |
Topic |
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2006-11-28 : 19:06:06
|
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)
7020 Posts |
Posted - 2006-11-28 : 19:30:49
|
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 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2006-11-28 : 21:15:06
|
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 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-28 : 21:26:58
|
You're doing something wrong.See example:declare @t table (lon numeric(10,6) ,lat numeric(10,6) )insert into @tselect lon = 34.085508, lat = -118.405121select * from @t Results:(1 row(s) affected)lon lat ------------ ------------ 34.085508 -118.405121(1 row(s) affected) CODO ERGO SUM |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-28 : 21:30:29
|
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 @tdeclare @tt table (i numeric(9,6))insert into @tt select -118.405121 select * from @ttEDIT: 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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-29 : 01:36:42
|
I think the term isPeter LarssonHelsingborg, Sweden |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-29 : 01:46:34
|
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 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-29 : 01:52:58
|
[SNIPED] with lowercase characters in the tag.Most used when someone has earlier posted same solution as you.Peter LarssonHelsingborg, Sweden |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-11-29 : 02:03:09
|
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 thereMadhivananFailing to plan is Planning to fail |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-11-29 : 07:50:03
|
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 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-29 : 08:52:53
|
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 |
|
|
|