开发者

Database storage of longitude/latitude values in SQL Server: decimal(2, ?)

In the table definition I saw:

Latitude     ->    varchar(50)
Longitude    ->    nvarchar(50)

Immediately, obviously, I queried the thinking behind this - being positively sure these values are in fact numerical by nature. Long story short: I postulated that these will be numerical, decimal in fact, and we would discard the 'thinking-in-str开发者_如何学运维ings' philosophy.

Now for the horns of my dilemma, I just went ahead and typed:

Latitude    ->    decimal(2, 4)

But hold on a second, 4 ain't right, right? Right. So I thought I'd up the threshold before realising (in a split second might I add) that 6 or 8 might not not cut it either. So, first things first...

Am I right in insisting we even go about it this way? And if so...

To what precision ought these values be stored to ensure we can persist the entire value which is to be inserted? For example, is there anything predefined by specification?

I don't just want to use something like Latitude -> decimal(2, 16) simply for it to be just as flawed as decimal(2, 2) in principle. And a similar question arises for Longitude specifically but I'm assuming the answer to one will suffice for the other, i.e decimal(3, answer).

We are using MSSQL Server 2005.

It seems I am educating myself with SQL Server by manual experience and therefore rendering parts of this question irrelevant: I can only use decimal(x, max(x)) not decimal(x, y) anyway! Will leave the question as is for input.


Decimal(2, 4) means 2 total digits of precision and 4 behind the decimal. SQL Server won't let you do that, but I think it would means you can store values from -0.0099 to 0.0099.

I'd recommend decimal(9, 6). This will store with an accuracy down to about 1/6th of an inch at the equator. Using 9 or less as the precision requires 5 bytes of storage, using 10-19 requires 9 bytes.


The maximum precision of decimals in sql server is currently 38. The scale can be up to 38. At it's max, a decimal will take up 17 bites, where as a varchar takes up whatever the length is plus 2. So if you went with a varchar(38), at it's max you're taking up 40 bits of data. The flip side is that a varchar is not as limited in size as a decimal is. So what you really need to do is figure out how many decimal points you're going to allow and then figure out your data type for it.

Source Info

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜