[Conny Westh]

se på sjutton vi har tydligen diskuterat det på WN tidigare så jag hade lite testkod i min WNtest-databas:

CREATE TABLE [dbo].[geographic]
(
	[GEOGRAPHIC_ID] [int] NOT NULL,
	[LONG] [float] NOT NULL,
	[LAT] [float] NOT NULL,
	[location_geography] [geography] NULL
)

CREATE PROCEDURE [dbo].[CreateRandomPositions]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @start INT 
	DECLARE @stop INT 
	DECLARE @count INT 
	DECLARE @seed float

	SET @count = 0
	SET @start = 1
	SET @seed = RAND( (DATEPART(mm, GETDATE()) * 100000 )
           + (DATEPART(ss, GETDATE()) * 1000 )
           + DATEPART(ms, GETDATE()) );
	
	set @stop=@start+400000
	WHILE (@count <= @stop) 
	BEGIN 
		INSERT INTO [dbo].[geographic] ([GEOGRAPHIC_ID],[LONG], [LAT]) VALUES ((SELECT ISnull(MAX([GEOGRAPHIC_ID]),1) as c from geographic)+1,RAND()*180.0-90.0, RAND()*180.0-90.0) 
		set @count = @count + 1
	END 
END

CREATE PROCEDURE [dbo].[spGetNearLocations]
    @latitude decimal(18,14),    
    @longtitude decimal(18,14),
    @pDist Float
AS
BEGIN    
    SET NOCOUNT ON;    
    -- @p1 is the point you want to calculate the distance from which is passed as parameters    
	declare @p1 geography = geography::Point(@latitude,@longtitude, 4326);     
    SELECT *
    FROM
    (
		SELECT *        
		,@p1.STDistance(geography::Point([LAT], [LONG], 4326)) as [DistanceInKilometers]    
		FROM [geographic]
    ) as geo
	WHERE [DistanceInKilometers] < @pDist
END

GO

CREATE PROCEDURE [dbo].[TestDistance]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @stop Datetime
	declare @start Datetime
	set @start = GetDate()
	EXEC GetDist -84.093177, 35.922934, 8.2;
	set @stop = GetDate()
	SELECT DateDiff(ms,@start,@stop) AS PerformanceTime
	
	
	set @start = GetDate()
	EXEC TestLatLonRadiusDistanceCalculation -84.093177, 35.922934, 8.2;
	set @stop = GetDate()
	SELECT DateDiff(ms,@start,@stop) AS PerformanceTime
	
	
	set @start = GetDate()
	EXEC [dbo].[spGetNearLocations] -84.093177, 35.922934, 8.2;
	set @stop = GetDate()
	SELECT DateDiff(ms,@start,@stop) AS PerformanceTime
	
END

CREATE PROCEDURE [dbo].[TestLatLonRadiusDistanceCalculation]
	-- Add the parameters for the stored procedure here
	@pLong float,
	@pLat float,
	@pDist float
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT *
	FROM
	(
		select *, dbo.LatLonRadiusDistance(long,lat,@pLong,@pLat) as distance
		FROM geographic
		--WHERE (LAT < (@x+1)) AND (LAT >(@x-1)) AND (LONG <(@y+1)) AND (LONG > (@y-1))
	) as dist
	WHERE distance < @pDist
	ORDER By distance
END