se på sjutton vi har tydligen diskuterat det på WN tidigare så jag hade lite testkod i min WNtest-databas:
Kod:
CREATE TABLE [dbo].[geographic]
(
[GEOGRAPHIC_ID] [int] NOT NULL,
[LONG] [float] NOT NULL,
[LAT] [float] NOT NULL,
[location_geography] [geography] NULL
)
Kod:
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
Kod:
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
Kod:
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
Kod:
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