1

If you see the Microsoft SQL Server 2008 R2 documentation for the Point data type it expects a latitude and longitude to be given as floats.

However, this post on SO suggests that the better data type for storing long and lat is decimal(9,6).

So, why are Microsoft using floats?

Update: In the following example, where the lat/long are denormalized to improve performance, should I match the float data type on the point, or maintain the decimal?

CREATE TABLE [dbo].[GeoTest](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [Lat] [decimal](9, 6) NOT NULL,
    [Long] [decimal](9, 6) NOT NULL,
    [GeoPoint] [GEOGRAPHY] NOT NULL,
 CONSTRAINT [PK_GeoTest] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The schema above allows me to this:

SELECT Lat, Long FROM GeoTest WHERE GeoPoint.STDistance(GEOGRAPHY::Point(47.6475, -122.1393, 4326))/1609.344 <= 20

Rather than this:

SELECT GeoPoint.Lat, GeoPoint.Long FROM GeoTest WHERE GeoPoint.STDistance(GEOGRAPHY::Point(47.6475, -122.1393, 4326))/1609.344 <= 20

Which is slower. It struck me odd that the two data types were different.

Community
  • 1
  • 1
Rebecca
  • 13,914
  • 10
  • 95
  • 136
  • Are you really able to, accurately, determine your position to significant decimal places, such that either one will be the wrong decision? – Damien_The_Unbeliever Mar 15 '12 at 14:37
  • I think you're confusing the Point data type with the question, what "generic" data type should I use for lat/long if I'm not using geographical data types. These are two totally different things, so it makes no sense to compare them. – Pondlife Mar 15 '12 at 14:44
  • @Pondlife I guess this makes a bit more sense in combination with my other question: http://stackoverflow.com/questions/9723533/is-it-wise-to-denormalize-latitude-and-longitude-to-make-a-geospatial-query-fast – Rebecca Mar 15 '12 at 16:09
  • @Pondlife I'm considering denormalizing the lat/long as non-geographical data types in the same table as the point for performance benefits. It seemed odd that the denormalized data type was different to the Point input params. – Rebecca Mar 15 '12 at 16:11
  • @Junto The "denormalized" data type does not have to be decimal(9,6), it could still be float. The recommendation to use decimal in the other question is a simple statement, with no explanation at all. That doesn't mean it's wrong, but you need to decide for yourself what the best option is, taking into account all factors in your environment: performance, maintenance, ease of programming, compatibility with your application and tools etc. As a general comment, start by doing the easiest thing and only worry about performance optimization if you actually need to. – Pondlife Mar 16 '12 at 08:36
  • https://wiki.gis.com/wiki/index.php/Decimal_degrees#:~:text=A%20value%20in%20decimal%20degrees%20to%205%20decimal%20places%20is,1.11%20meter%20at%20the%20equator. details the distance a decimal place is at the equator (the largest distance for a decimal place). 6DP is 11.1 cm. Also, using the RDBMS's built in geography functions are better suited to using a POINT type rather than constantly converting from 2 floats to a point. There is also a WKT (Well Known Text) format conversion mechanism in some RDBMS to help this conversion. – Richard A Quadling May 23 '23 at 13:06

1 Answers1

1

Internally, SQL Server stores geometry and geography data using the format described in the document at http://download.microsoft.com/download/7/9/3/79326E29-1E2E-45EE-AA73-74043587B17D/%5BMS-SSCLRT%5D.pdf

Each coordinate value is serialised as a double-precision floating-point value that is 64 bits (8 bytes) long. Therefore it makes sense for the input parameters to the Point() method to match this - i.e. use the float datatype.

As @Pondlife points out, the other post to which you refer relates to a generic datatype for storing coordinate values, which is a totally different question.

Alastair Aitchison
  • 3,532
  • 1
  • 16
  • 15