Bug in SQL Server geography?

Question

Can someone please confirm if this is a bug or I'm doing something wrong please?

I've this stored procedure (SQL Server 2008)

ALTER PROCEDURE [dbo].[Distance]
@origin varchar(50),@destination varchar(50),@unit varchar(5)
as
   declare @or geography, @dest  geography
   SET @or = (select Location from [dbo].Promotion where Name=@origin )
   SET @dest = (select Location from [dbo].Promotion where Name=@destination )
IF @unit='miles'
 SELECT @or.STDistance(@dest)/1609.344
ELSE
 --Else show the distance in km
 SELECT @or.STDistance(@dest)/1000

Location is geography datatype in the database

And I've this addresses in the database

     Latitude     Longitude
1   -34.612654   -58.463586 
2   -34.592802   -58.454317 
3   -34.597889   -58.617949

Then running this:

execute dbo.Distance 'Number 1','Number 2','km'
returns 2653.49845233371 kms

execute dbo.Distance 'Number 1','Number 3','km'
returns 17.2155414117145 kms

If you go to Google Maps in the first case the are around 4 kms betweeen those coordinates and the second comparison seems to be OK.

Why the first one is so wrong? Is it a bug in SQL Server?

Thanks in advance. Guillermo.

First rule of using large application is use by millions of people - the bug is in your code. Second rule - The bug is in your code. Third rule.... you get the idea ;) — Jamiec, Nov 20 '12 at 12:58
I assume Location is a computed column, based on the Latitude and Longitude columns? If so, can you give it's definition? (It would be a lot easier if you could create a simple table, an `INSERT` to populate it with your sample data, and a simple query to produce results, so that others can reproduce this) — Damien_The_Unbeliever, Nov 20 '12 at 13:06

score 0 · Accepted Answer · answered Nov 20 '12 at 13:34

Ok, I've found what the problem is, and yes, of course it was my fault :)

I've inserted records in the table like this

INSERT INTO [dbo].[TestLocation]([Name],[Location],[Latitude],[Longitude]) VALUES('Location1', geography::STGeomFromText('POINT(-34.612654 -58.463586)', 4326),-34.612654,-58.463586);

Then I realized that, at first, latitude and longitude were inverted, so I've changed then in order, but just for the last two fields and forgot to change the order in the "geography::STGeomFromText". After running

SELECT
  Ubicacion.Lat,
  Ubicacion.Long
FROM
  Promotion

I realized what the error was. Doh!

Be aware of this as

SELECT
  Ubicacion.Lat,
  Ubicacion.Long
FROM
  Promotion

Be aware of this as geography datatype is saved as binary so it is not human readable, so, unless you run the above query you won't realize the source of the problem.

Thanks! Guillermo.

This would be a good reason to change your definition of `Location` from `Location geography not null` to `Location AS geography::Point(Latitude,Longitude,4326)`. Or to put it another way, don't *store* computed values. (Optionally, add `persisted not null` and it should even allow it to be indexed) — Damien_The_Unbeliever, Nov 20 '12 at 14:27

Bug in SQL Server geography?

1 Answers1