CREATE TABLE [dbo].[GEODATASOURCE_CITIES_GOLD](
	[REGION] [int] NULL,
	[SUB_REGION] [varchar](2) NULL,
	[UFI] [int] NULL,
	[UNI] [int] NULL,
	[DSG] [varchar](5) NULL,
	[CC_FIPS] [varchar](2) NULL,
	[CC_ISO] [varchar](2) NULL,
	[FULL_NAME] [varchar](200) NULL,
	[FULL_NAME_ND] [varchar](200) NULL,
	[SORT_NAME] [varchar](200) NULL,
	[ADM1_CODE] [varchar](2) NULL,
	[ADM1_FULL_NAME] [varchar](200) NULL,
	[ADM1_FULL_NAME_ND] [varchar](200) NULL,
	[ADM2_CODE] [varchar](3) NULL,
	[ADM2_FULL_NAME] [varchar](200) NULL,
	[ADM2_FULL_NAME_ND] [varchar](200) NULL,
	[LATITUDE] [float] NULL,
	[LONGITUDE] [float] NULL,
	[DMS_LATITUDE] [int] NULL,
	[DMS_LONGITUDE] [int] NULL,
	[UTM] [varchar](4) NULL,
	[JOG] [varchar](7) NULL
) ON [PRIMARY]

GO



create function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float) 
returns float

as

begin

declare @DegToRad as float
declare @Ans as float
declare @Miles as float

set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0

begin

return ( @Miles )

end

set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

set @Miles = CEILING(@Miles)

return ( @Miles )

end

SELECT TOP (@topRecords) dbo.Distance (@latitude,@longitude,LATITUDE,DMS_LONGITUDE),*  FROM [GEODATASOURCE_CITIES_GOLD] 
order by 1 desc

CREATE PROCEDURE [dbo].[select_closest_Cities]
--exec select_closest_Cities 100,10,20
(
@topRecords int,
@latitude float,
@longitude float
)
AS
SET NOCOUNT ON

declare @sql varchar(4000)

set @sql=
	'SELECT TOP '+convert(varchar,@topRecords)+' dbo.Distance ('+convert(varchar,@latitude)+','+convert(varchar,@longitude)+',LATITUDE,DMS_LONGITUDE),*  FROM [GEODATASOURCE_CITIES_GOLD] 
	order by 1 desc'
exec (@sql)
GO

CREATE PROCEDURE [dbo].[select_closest_Cities]
--exec select_closest_Cities 100,10,20
(
@topRecords int,
@latitude float,
@longitude float
)
AS
SET NOCOUNT ON

declare @sql varchar(4000)

set @sql=
	'SELECT TOP '+convert(varchar,@topRecords)+' dbo.Distance ('+convert(varchar,@latitude)+','+convert(varchar,@longitude)+',LATITUDE,DMS_LONGITUDE),*  FROM [GEODATASOURCE_CITIES_GOLD] 
	order by 1 /*desc*/  asc
exec (@sql)
GO

alter PROCEDURE [dbo].[select_closest_Cities]
--exec select_closest_Cities 100,10,20
(
@topRecords int,
@latitude float,
@longitude float
)
AS
SET NOCOUNT ON

declare @sql varchar(4000)

set @sql=
	'SELECT TOP '+convert(varchar,@topRecords)+' dbo.Distance ('+convert(varchar,@latitude)+','+convert(varchar,@longitude)+',LATITUDE,LONGITUDE),*  FROM [GEODATASOURCE_CITIES_GOLD] 
	order by 1 asc'
exec (@sql)

quote:

---

Originally posted by sakets_2000

Mike,

Whats the difference between DMS_LATITUDE & LATITUDE, and DMS_LONGITUDE and LONGITUDE in your table ? I think, Our case is just that we are considering the wrong columns for our calculations.
The formulae you are using looks good.

---

quote:

---

Originally posted by Michael Valentine Jones

You might want to look at the function on this link for the distance calculation:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

CODO ERGO SUM

---