SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Distance Calc Taking longtime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

visa123
Yak Posting Veteran

India
54 Posts

Posted - 10/07/2010 :  05:11:08  Show Profile  Reply with Quote
Hi

I am querying the following.but its takes long time for execution.

can anyone help me.


Declare
@zipcode nvarchar(50),
@distnace numeric(18,2),
@longitude nvarchar(50),
@latitude nvarchar(50),
@l_qry nvarchar(max),
@l_larstr nvarchar(max),
@l_latstr nvarchar(max)

SET @zipcode='00610'
SET @distnace = 50

drop table #Result


create table #Result
(
country nvarchar(max),
state nvarchar(max),
city nvarchar(max),
zipcode nvarchar(max)
)



set @l_larstr='replace(Replace(longitude,'','',''.''),''"'','''')'
set @l_latstr='replace(Replace(latitude,'','',''.''),''"'','''')'

SELECT @longitude=cast(replace(Replace(longitude,',','.'),'"','') as decimal(18,10)),
@latitude=cast(replace(Replace(latitude,',','.'),'"','') as decimal(18,10)) FROM postaldb
WHERE replace(postalcode,'"','')=@zipcode


set @l_qry='insert into #Result (country,state,city,zipcode) select distinct replace(countrycode,''"'','''') as country,replace(region1,''"'','''') as state,
replace(place,''"'','''') as city,replace(postalcode,''"'','''') as zipcode
from postaldb
where (SQRT(POWER(((cast(replace(Replace('+@latitude+','','',''.''),''"'','''') as decimal(18,10))) -(cast(replace(Replace('+@l_latstr+','','',''.''),''"'','''') as decimal(18,10))))*110.7,2)
+POWER((cast(replace(Replace('+@longitude+','','',''.''),''"'','''') as decimal(18,10))-(cast(replace(Replace('+@l_larstr+','','',''.''),''"'','''') as decimal(18,10))))*75.6,2)))
<='+cast(@distnace as varchar)

print @l_qry
exec (@l_qry)

Select * from #result

Previously it takes 30 secs.So i have created the non-clustered Index.
and after that it takes 15 to 20 secs for execution.

But 15 to 20 secs is also too long.It takes time for only calculation.


In my postaldb total records is 6 laksh records.


can anyone help me how to rectify this issue?

Thanks
Visa.G

Edited by - visa123 on 10/07/2010 05:13:22

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 10/07/2010 :  05:23:57  Show Profile  Reply with Quote
Lot of calculations are getting involved in select as well as where.

One way to reduce time is to create an indexed view. Again it got its own pros and cons.
Go to Top of Page

visa123
Yak Posting Veteran

India
54 Posts

Posted - 10/07/2010 :  09:13:13  Show Profile  Reply with Quote
I have to pass parameter [ zipcode,distnace is my input ].

So its not possible in view to pass the parameter.

Anyother way is there?



Visa.G
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/07/2010 :  09:38:14  Show Profile  Reply with Quote
I don't think I understand why you are using dynamic SQL for this.

You may want to try doing the distance calculation with this function to see if it makes any distance in speed.
Great Circle Distance Function - Haversine Formula
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360

You should also look at this to see if you can cut down the number of rows you have to search.
Function F_FIND_SEARCH_LIMITS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112369







CODO ERGO SUM
Go to Top of Page

visa123
Yak Posting Veteran

India
54 Posts

Posted - 10/08/2010 :  03:46:22  Show Profile  Reply with Quote
Thanks a lot Michael.Its working fine for me.It comes around 2 to 3 seconds

Visa.G
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/08/2010 :  07:21:02  Show Profile  Reply with Quote
We calculate Lat/Long for a square which contains the max. radius we are interested in - say 50 miles radius - i.e. we calculate the top-left Lat/Long and the bottom-right Lat/Long of that square; some points will be outside the 50 mile radius limit, of course. Then we query records that are within that square. This is very fast - index based.

Then we calculate, for those records only, what the distance is using Great Circle formula - which has heavy-CPU Trigonometry implications.

We are also fortunate, in UK, to have resources that have locations in "miles E and N of South West point of the UK" and we can use these, rather than Lat/Long, for direct calculation of distance just using Pythagoras. I have no idea whether this works anyway in the world, or just because UK is a relatively small area, and not too near the Poles, and thus the inaccuracies are tolerable for the UK. But if it would work anywhere it would be worth converting Lat/Long for each record to a "miles-from" grid value, and then using that for calculations.

Sorry, I don't know the correct technical term for "miles-from grid position"

Edited by - Kristen on 10/08/2010 07:32:03
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/08/2010 :  10:20:21  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

We calculate Lat/Long for a square which contains the max. radius we are interested in - say 50 miles radius - i.e. we calculate the top-left Lat/Long and the bottom-right Lat/Long of that square; some points will be outside the 50 mile radius limit, of course. Then we query records that are within that square. This is very fast - index based.

Then we calculate, for those records only, what the distance is using Great Circle formula - which has heavy-CPU Trigonometry implications.

We are also fortunate, in UK, to have resources that have locations in "miles E and N of South West point of the UK" and we can use these, rather than Lat/Long, for direct calculation of distance just using Pythagoras. I have no idea whether this works anyway in the world, or just because UK is a relatively small area, and not too near the Poles, and thus the inaccuracies are tolerable for the UK. But if it would work anywhere it would be worth converting Lat/Long for each record to a "miles-from" grid value, and then using that for calculations.

Sorry, I don't know the correct technical term for "miles-from grid position"




I am not sure if the OP used it, but the F_FIND_SEARCH_LIMITS function that I posted a link for returns a result set with the North/South and East/West limits for the search.

If your lookup table has indexes on latitude and longitude, it should do a good job of limiting the search.





CODO ERGO SUM
Go to Top of Page

visa123
Yak Posting Veteran

India
54 Posts

Posted - 01/24/2011 :  05:32:00  Show Profile  Reply with Quote
Hi Michael

I got problem in mile search. its little bit urgent

My input is : zipcode : 45201 radius : 50 (miles)

But its showing wrong result for me.Its showing above 50 miles also. ex : Xenia,OH,USA

am using the following code for this :



ALTER function [dbo].[F_GREAT_CIRCLE_DISTANCE]
(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
returns float
as
/*
fUNCTION: F_GREAT_CIRCLE_DISTANCE

Computes the Great Circle distance in kilometers
between two points on the Earth using the
Haversine formula distance calculation.

Input Parameters:
@Longitude1 - Longitude in degrees of point 1
@Latitude1 - Latitude in degrees of point 1
@Longitude2 - Longitude in degrees of point 2
@Latitude2 - Latitude in degrees of point 2

*/
begin
declare @radius float

declare @lon1 float
declare @lon2 float
declare @lat1 float
declare @lat2 float

declare @a float
declare @distance float

-- Sets average radius of Earth in Miles
set @radius = 3956.0E

-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) +
(cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance =
@radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

return @distance

end

****


GO
/****** Object: UserDefinedFunction [dbo].[F_FIND_SEARCH_LIMITS] Script Date: 01/24/2011 16:14:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[F_FIND_SEARCH_LIMITS]
(
@Latitude float,
@Longitude float,
@SearchRadius float
)
returns @search_Limits table
(
MaxLongitude decimal(9,6) not null,
MinLongitude decimal(9,6) not null,
MaxLatitude decimal(9,6) not null,
MinLatitude decimal(9,6) not null
)
as
/*

Function: F_FIND_SEARCH_LIMITS

Function F_FIND_SEARCH_LIMITS is used to find the min/max limits
of Latitude and Longitude for the search radius passed in
parameter @SearchRadius from the point on the earth defined by
input parameters @Latitude and @Longitude.

The returned values can be used to setup a search of a table
containing Latitude and Longitude values for applications
that need to return values within a specified distance from
a central point. This allows use of table indexes to
do an efficient search for values than may be within the
search radius. The result set can then be trimmed by
calculating the great circle distance to eliminate
values outside the search radius.

The calculation is refined in a loop to produce a result that
is accurate to less than 1 meter from the actual distance,
and is slightly larger that the search radius to prevent
excluding valid points.

Parameter @SearchRadius is in kilometers.
To use it with miles, multiply @SearchRadius by 1.609344E

This function uses function dbo.F_GREAT_CIRCLE_DISTANCE
to calculate great circle distance.

*/

begin

declare @Degrees float
declare @MaxLon float
declare @MinLon float
declare @MaxLat float
declare @MinLat float
declare @DegreePerKM float
declare @DistanceToPole float
declare @WorkDistance float
declare @SearchRadius1 float

declare @mess varchar(400)
declare @cr varchar(2)
declare @x int

set @cr = char(13)+Char(10)
set @mess = ''

set @SearchRadius1= (@SearchRadius*1.609344E)


-- Verify parameter @Latitude is valid
if abs(@Latitude) >= 90.0E
begin
set @mess = 'Greater than maximum allowed Latitude, '+
', Latitude = '+
isnull(convert(varchar(20),convert(decimal(20,6),round(abs(@Latitude),6))),'NULL')
goto Error_Exit
end

-- Verify parameter @Longitude is valid
if abs(@Longitude) > 180.0E
begin
set @mess = 'Greater than maximum allowed Longitude, '+
', Longitude = '+
isnull(convert(varchar(20),convert(decimal(20,6),round(abs(@Longitude),6))),'NULL')
goto Error_Exit
end



-- Verify parameter @SearchRadius is greater than .001 kilometer
if @SearchRadius1 < .001E
begin
set @mess = 'Minimum search radius is .001'+
', Search Radius = '+
isnull(convert(varchar(20),convert(decimal(20,6),round(@SearchRadius1,6))),'NULL')
goto Error_Exit
end

set @DistanceToPole = dbo.F_GREAT_CIRCLE_DISTANCE(0E,abs(@Latitude),0E,90E)

-- Verify that the Search Radius is no closer than 100 kilometers from a pole of the Earth
if @SearchRadius1+100 > @DistanceToPole
begin
set @mess = 'Latitude too close to pole for search radius,'+@cr+
'Latitude = '+
isnull(convert(varchar(20),convert(decimal(20,6),round(@Latitude,6))),'NULL')+
', Search Radius = '+
isnull(convert(varchar(20),convert(decimal(20,6),round(@SearchRadius1,6))),'NULL')+
', Distance to Pole = '+
isnull(convert(varchar(20),convert(decimal(20,6),round(@DistanceToPole,6))),'NULL')
goto Error_Exit
end

-- Get number of degrees for a search at the equator
set @Degrees = @SearchRadius1/(dbo.F_GREAT_CIRCLE_DISTANCE(0E,0E,0E,90E)/90.000000000E)

-- Get great circle distance 90 degrees away on the same latitude
-- for initial estimate of degrees per kilometer.
set @DegreePerKM = (90E)/dbo.F_GREAT_CIRCLE_DISTANCE(@Latitude,0E,@Latitude,90E)

select
@MaxLon = @Longitude + (@DegreePerKM*@SearchRadius1),
@MinLon = @Longitude - (@DegreePerKM*@SearchRadius1),
@MaxLat = @Latitude + @Degrees+.0000000001E ,
@MinLat = @Latitude - @Degrees-.0000000001E

declare @count int
set @count = 0

while 1=1
begin
-- Calculate great circle distance along latitude line
set @WorkDistance = dbo.F_GREAT_CIRCLE_DISTANCE(@Latitude,@Longitude,@Latitude,@MaxLon)

-- Exit when calculation is good enough or loop count greater than 15
if @WorkDistance-@SearchRadius1 < 0.0000005E or
@count > 15
begin
break
end

set @count = @count+1

-- Refine estimate of degrees per kilometer
set @DegreePerKM = (@MaxLon-@Longitude)/@WorkDistance

-- Recalculate the min/max estimate with the refined degrees per kilometer
set @MaxLon = @Longitude+(@DegreePerKM*@SearchRadius1)
set @MinLon = @Longitude-(@DegreePerKM*@SearchRadius1)

end -- End of While loop

-- Add increment to ensure limits are slightly outside search radius
select
@MaxLon = round(@MaxLon+0.0000005E,6),
@MinLon = round(@MinLon-0.0000005E,6),
@MaxLat = round(@MaxLat+0.0000005E,6),
@MinLat = round(@MinLat-0.0000005E,6)

insert into @search_Limits
select
@MaxLon,
@MinLon,
@MaxLat,
@MinLat

return

Error_Exit:
-- Cause an conversion error to occur to send an error massage
if @mess <> ''
select @x=
convert(int,@cr+@cr+@cr+replicate('*',90)+@cr+@mess+@cr+replicate('*',90)+@cr+@cr+@cr)

return

end



*******

ALTER PROCEDURE [dbo].[USP_Calculate]
@zipcode NVARCHAR(MAX),
@distance NVARCHAR(MAX)
AS
declare @MaxLongitude decimal(9,6)
declare @MinLongitude decimal(9,6)
declare @MaxLatitude decimal(9,6)
declare @MinLatitude decimal(9,6)

declare @l_countries nvarchar(max)
declare @l_states nvarchar(max)
declare @l_city nvarchar(max)
declare @l_zipcode varchar(8000)

declare @Latitude float
declare @Longitude float
declare @SearchRadius float

-- Set Search Radius in miles

if(@distance = '0')
begin
set @distance = '0.001'
end
else
begin
set @distance = @distance
end
set @SearchRadius = @distance
IF EXISTS(SELECT longitude,latitude FROM geodb1 WHERE postalcode=@zipcode)
BEGIN
SELECT @Longitude=longitude,@Latitude=latitude FROM geodb1 WHERE postalcode=@zipcode
END
ELSE
BEGIN
PRint 'Hi'
SET @Longitude = '1.00'
SET @Latitude = '1.00'
END


Print @Latitude
Print @Longitude
Print @SearchRadius

-- Get Search limits
select
@MaxLongitude = a.MaxLongitude,
@MinLongitude = a.MinLongitude,
@MaxLatitude = a.MaxLatitude,
@MinLatitude = a.MinLatitude
from
dbo.F_FIND_SEARCH_LIMITS(@Latitude,@Longitude,@SearchRadius) a


create table #Result
(
country nvarchar(max),
state nvarchar(max),
city nvarchar(max),
zipcode nvarchar(max)
)


-- Query Zip code Table
insert into #Result (country,state,city,zipcode)
select
CountryCode As country,region1,place,postalcode
from
dbo.geodb1 a
where
-- Select only items within search limits
a.latitude between @MinLatitude and @MaxLatitude and
a.longitude between @MinLongitude and @MaxLongitude and
-- Select only items where distance is within search radius
dbo.F_GREAT_CIRCLE_DISTANCE( @Latitude, @Longitude, a.latitude, a.longitude) <= @SearchRadius
order by
-- Order closest to farthest
dbo.F_GREAT_CIRCLE_DISTANCE( @Latitude, @Longitude, a.latitude, a.longitude)

select @l_countries=COALESCE(@l_countries + ',', '') + CONVERT(varchar,country) from #Result group by country
select @l_states=COALESCE(@l_states + ',', '') + CONVERT(varchar,state) from #Result group by state
select @l_city =COALESCE(@l_city + ',', '') + CONVERT(varchar,city) from #Result group by city
select @l_zipcode=COALESCE(@l_zipcode + ',', '') + CONVERT(varchar,zipcode) from #Result group by zipcode
delete from #Result

set @l_countries=char(39)+replace(@l_countries,',',char(39)+','+char(39))+char(39)
set @l_states=char(39)+replace(@l_states,',',char(39)+','+char(39))+char(39)
set @l_city=char(39)+replace(replace(@l_city,'''',''),',',char(39)+','+char(39))+char(39)
set @l_zipcode=char(39)+replace(@l_zipcode,',',char(39)+','+char(39))+char(39)

print '@l_countries'+ @l_countries
print '@l_states'+ @l_states
print '@l_city'+ @l_city
print '@l_zipcode'+ @l_zipcode

insert into #Result values(@l_countries,@l_states,@l_city,@l_zipcode)


Select * from #Result


can u pls suggest me for this? any mistake i did?




Visa.G

Edited by - visa123 on 07/11/2011 06:31:14
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/24/2011 :  14:23:01  Show Profile  Reply with Quote
What are the latitude and longitude for the two points you think are in error?

How do you expect to get "urgent" help when you didn't even supply such basic information?


By my calculation, these two points are 49.07 miles apart.

http://www.brainyzip.com/state/zip_ohio.html
Zip Code: 45201 (Cincinnati, Ohio)
Latitude: +39.166759
Longitude: -084.538220

Zip Code: 45385 (Xenia, Ohio)
Latitude: +39.684731
Longitude: -083.908130






CODO ERGO SUM

Edited by - Michael Valentine Jones on 01/24/2011 14:29:24
Go to Top of Page

visa123
Yak Posting Veteran

India
54 Posts

Posted - 01/25/2011 :  04:57:45  Show Profile  Reply with Quote
Hi

I have checked in http://maps.google.com/.

I think google map shows the standard one.

In the Get Directions -> A,B Point given like below

First Input :

A.Cincinnati, OH, United States
B.Xenia, OH, United States

results showing : 54.9 mi,58.9 mi (2 way route)

Second Input :

A.45201
B.45385

results showing : 64.7 mi,67.6 mi,67.6 mi (3 routes)

My scenario :

We purchased geodb. So Cincinnati,Xenia Latitude,Longitude is different from your inputs.

Cincinnati Latitude : 39.10724 Longitude : -84.50035
Xenia Latitude : 39.69138 Longitude : -83.9514

May be the Point variations is there.

Thanks
Visa.G

Edited by - visa123 on 01/25/2011 04:58:26
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/25/2011 :  09:29:09  Show Profile  Reply with Quote
Did you even bother to test your code with that data?

The code you posted for F_GREAT_CIRCLE_DISTANCE returns a value of 49.844568465646141, which is below your 50 mile limit, for the two points below.

Cincinnati Latitude : 39.10724 Longitude : -84.50035
Xenia Latitude : 39.69138 Longitude : -83.9514





CODO ERGO SUM
Go to Top of Page

visa123
Yak Posting Veteran

India
54 Posts

Posted - 01/27/2011 :  07:43:08  Show Profile  Reply with Quote
Hi

Yes. I have tested. For DISTANCE Search function its come below 50 mile only.

But my testers checked in google map and compared my results

they informed google showing : results showing : 54.9 mi,58.9 mi (2 way route)

But my result showing : 49.8 mi






Visa.G
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/27/2011 :  11:37:38  Show Profile  Reply with Quote
So you are expecting the Google driving distance to match the great circle distance?

That would be impossible unless there was a road that ran directly from point 1 to point 2 along the great circle between the two points with no turns or curves.




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 01/27/2011 :  12:57:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Soon Michael, soon...
In the future everyone will driving DeLorean's powered by Mr Fusion.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 01/27/2011 :  15:07:23  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Soon Michael, soon...
In the future everyone will driving DeLorean's powered by Mr Fusion.



N 56°04'39.26"
E 12°55'05.63"




Once we have transporters, no one will care if it's 50 or 5,000 miles away.




CODO ERGO SUM
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 01/27/2011 :  18:59:12  Show Profile  Visit jezemine's Homepage  Reply with Quote
you'd pay a larger wormhole tax for the 5000 mile trip.


elsasoft.org
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000