column data type help

[wallaceoc80]

I am designing a table that will have two columns for gps coordinates. One for longitude and one for latitude. I don't know which data type I should use for this information. I was thinking either real or float.

I don't want the database to change the figure if it has less than 5ish digits after the decimal point. If it has more than that then it can round it off.

Which data type should I use?

Thanks for your help,

Wallace

 

[r937]

decimal(8,5)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[wallaceoc80]

Thanks for the quick response!

 

[wallaceoc80]

Just one more quick question. Ocassionally, a value might be bigger than the specified size and the value in the database looks like this:
9.3127675E-2

Will this cause errors in the future or am I simply losing a small bit of accuracy?

 

[dbomrrsm]

I would suggest you are in for problems in the future at some point.

To make sure the data will load ok you could do a check to see what is the max length of the data to the left of the ddcimal point and to the right of the decimal point.

load data into varchar field then do

select max(len (substring('1232432.25362',1,patindex('%.%','1232432.25362')-1)))

and something similar using patindex and to start the substring to get the max after the decimal.

select max(len (substring('1232432.25362',patindex('%.%','1232432.25362')+1,60)))

obviously just replace my 123etc with your field name

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[r937]

i guess it all depends on how many decimal points you want to preserve

9.3127675E-2 is the same as 0.093127675

in terms of lat/long, i doubt that anything beyond the 5th decimal position is significant (although i hasten to admit that i am not a GPS expert), so you might as well just use 0.09313

hence my suggestion for decimal(8,5)

and of course it's clear why you only need 3 positions in front of the decimal, right?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[wallaceoc80]

Thanks, for all the suggestions people.

I will use the decimal data type because like you said only the first 5 decimal places are really significant.

On the point of only needing 3 positions in front of the decimal - I understand this. However, does a minus sign take up one of these places?

e.g. if I had -113.34233... would this cause problems?

Thanks again,
Wallace

 

[SQLSister]

I believe you should have 9 places avaliable. At least that's what I use in my lat/long fields.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[r937]

why 9?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[SQLSister]

Son of a gun, you are right,Rudy. I don't need the extra place to account for the negative sign. I always thought I did.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[r937]

i guess numeric/decimal fields must use 2s-complement just like integers

i thought you were gonna say you really needed 6 places after the decimal for better accuracy

i suppose i could google the answer eventually, but does anyone know what plus or minus 0.00001 degrees actually equates to in metres?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[vongrunt]

On equator (latitude 0, worst spread) 1 longitudinal arc minute equals 1 nautical mile (1852m). And 1 degree is approx. 1000/9km (easy to remember).

 

[r937]

1 degree = 1000/9 km = 111.111 km = 111111 m

so 0.00001 degree = 1.11111 m

hmm, i wonder if GPS equipment can actually be more precise than that...



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)