| Author |
 Topic  |
|
|
visa123
Yak Posting Veteran
India
54 Posts |
 Posted - 10/07/2010 : 05:11:08
|
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
|
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. |
 |
|
|
visa123
Yak Posting Veteran
India
54 Posts |
Posted - 10/07/2010 : 09:13:13
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
visa123
Yak Posting Veteran
India
54 Posts |
Posted - 10/08/2010 : 03:46:22
|
Thanks a lot Michael.Its working fine for me.It comes around 2 to 3 seconds
Visa.G |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/08/2010 : 07:21:02
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 10/08/2010 : 10:20:21
|
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 |
|
|
|
visa123
Yak Posting Veteran
India
54 Posts |
Posted - 01/24/2011 : 05:32:00
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 01/24/2011 : 14:23:01
|
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 |
|
|
|
visa123
Yak Posting Veteran
India
54 Posts |
Posted - 01/25/2011 : 04:57:45
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 01/25/2011 : 09:29:09
|
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 |
|
|
|
visa123
Yak Posting Veteran
India
54 Posts |
Posted - 01/27/2011 : 07:43:08
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 01/27/2011 : 11:37:38
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/27/2011 : 12:57:25
|
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" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 01/27/2011 : 15:07:23
|
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 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 01/27/2011 : 18:59:12
|
you'd pay a larger wormhole tax for the 5000 mile trip.
elsasoft.org |
|
|
| |
Topic |
|
|
|
Distance Calc Taking longtime
