Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 best SQL datatype to store Longitude/Latitude

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
Go to Top of Page

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
Go to Top of Page

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 @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
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 @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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 01:36:42
I think the term is




Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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 there

Madhivanan

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

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -