Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
visa123
Yak Posting Veteran
54 Posts |
Posted - 2010-10-07 : 05:11:08
|
HiI 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 = 50drop table #Resultcreate 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 postaldbWHERE replace(postalcode,'"','')=@zipcodeset @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 postaldbwhere (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_qryexec (@l_qry)Select * from #resultPreviously 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 |
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-10-07 : 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
54 Posts |
Posted - 2010-10-07 : 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)
7020 Posts |
|
visa123
Yak Posting Veteran
54 Posts |
Posted - 2010-10-08 : 03:46:22
|
Thanks a lot Michael.Its working fine for me.It comes around 2 to 3 secondsVisa.G |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 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" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-08 : 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
54 Posts |
Posted - 2011-01-24 : 05:32:00
|
Hi MichaelI got problem in mile search. its little bit urgentMy input is : zipcode : 45201 radius : 50 (miles)But its showing wrong result for me.Its showing above 50 miles also. ex : Xenia,OH,USAam using the following code for this :ALTER function [dbo].[F_GREAT_CIRCLE_DISTANCE] ( @Latitude1 float, @Longitude1 float, @Latitude2 float, @Longitude2 float )returns floatas/*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*/begindeclare @radius floatdeclare @lon1 floatdeclare @lon2 floatdeclare @lat1 floatdeclare @lat2 floatdeclare @a floatdeclare @distance float-- Sets average radius of Earth in Miles set @radius = 3956.0E-- Convert degrees to radiansset @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 @distanceend****GO/****** Object: UserDefinedFunction [dbo].[F_FIND_SEARCH_LIMITS] Script Date: 01/24/2011 16:14:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER 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_LIMITSFunction F_FIND_SEARCH_LIMITS is used to find the min/max limitsof Latitude and Longitude for the search radius passed inparameter @SearchRadius from the point on the earth defined byinput parameters @Latitude and @Longitude.The returned values can be used to setup a search of a tablecontaining Latitude and Longitude values for applicationsthat need to return values within a specified distance froma central point. This allows use of table indexes todo an efficient search for values than may be within the search radius. The result set can then be trimmed bycalculating the great circle distance to eliminatevalues outside the search radius.The calculation is refined in a loop to produce a result thatis accurate to less than 1 meter from the actual distance,and is slightly larger that the search radius to preventexcluding valid points.Parameter @SearchRadius is in kilometers.To use it with miles, multiply @SearchRadius by 1.609344EThis function uses function dbo.F_GREAT_CIRCLE_DISTANCEto calculate great circle distance. */begindeclare @Degrees floatdeclare @MaxLon floatdeclare @MinLon floatdeclare @MaxLat floatdeclare @MinLat floatdeclare @DegreePerKM floatdeclare @DistanceToPole floatdeclare @WorkDistance floatdeclare @SearchRadius1 floatdeclare @mess varchar(400)declare @cr varchar(2)declare @x intset @cr = char(13)+Char(10)set @mess = ''set @SearchRadius1= (@SearchRadius*1.609344E)-- Verify parameter @Latitude is validif 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 validif 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 kilometerif @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 endset @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 Earthif @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 equatorset @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-.0000000001Edeclare @count intset @count = 0while 1=1begin -- 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 radiusselect @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_Limitsselect @MaxLon, @MinLon, @MaxLat, @MinLatreturnError_Exit:-- Cause an conversion error to occur to send an error massageif @mess <> '' select @x= convert(int,@cr+@cr+@cr+replicate('*',90)+@cr+@mess+@cr+replicate('*',90)+@cr+@cr+@cr)returnend*******ALTER PROCEDURE [dbo].[USP_Calculate]@zipcode NVARCHAR(MAX),@distance NVARCHAR(MAX)ASdeclare @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 floatdeclare @Longitude floatdeclare @SearchRadius float-- Set Search Radius in milesif(@distance = '0')beginset @distance = '0.001'endelsebeginset @distance = @distanceendset @SearchRadius = @distance IF EXISTS(SELECT longitude,latitude FROM geodb1 WHERE postalcode=@zipcode)BEGINSELECT @Longitude=longitude,@Latitude=latitude FROM geodb1 WHERE postalcode=@zipcode ENDELSEBEGINPRint 'Hi'SET @Longitude = '1.00'SET @Latitude = '1.00'ENDPrint @LatitudePrint @LongitudePrint @SearchRadius-- Get Search limitsselect @MaxLongitude = a.MaxLongitude, @MinLongitude = a.MinLongitude, @MaxLatitude = a.MaxLatitude, @MinLatitude = a.MinLatitudefrom dbo.F_FIND_SEARCH_LIMITS(@Latitude,@Longitude,@SearchRadius) acreate table #Result( country nvarchar(max), state nvarchar(max), city nvarchar(max), zipcode nvarchar(max))-- Query Zip code Tableinsert into #Result (country,state,city,zipcode)select CountryCode As country,region1,place,postalcodefrom dbo.geodb1 awhere -- 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) <= @SearchRadiusorder 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 countryselect @l_states=COALESCE(@l_states + ',', '') + CONVERT(varchar,state) from #Result group by stateselect @l_city =COALESCE(@l_city + ',', '') + CONVERT(varchar,city) from #Result group by cityselect @l_zipcode=COALESCE(@l_zipcode + ',', '') + CONVERT(varchar,zipcode) from #Result group by zipcodedelete from #Resultset @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_countriesprint '@l_states'+ @l_statesprint '@l_city'+ @l_cityprint '@l_zipcode'+ @l_zipcodeinsert into #Result values(@l_countries,@l_states,@l_city,@l_zipcode)Select * from #Resultcan u pls suggest me for this? any mistake i did?Visa.G |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-01-24 : 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.htmlZip Code: 45201 (Cincinnati, Ohio)Latitude: +39.166759Longitude: -084.538220Zip Code: 45385 (Xenia, Ohio)Latitude: +39.684731Longitude: -083.908130CODO ERGO SUM |
|
|
visa123
Yak Posting Veteran
54 Posts |
Posted - 2011-01-25 : 04:57:45
|
HiI have checked in http://maps.google.com/.I think google map shows the standard one.In the Get Directions -> A,B Point given like belowFirst Input :A.Cincinnati, OH, United StatesB.Xenia, OH, United Statesresults showing : 54.9 mi,58.9 mi (2 way route)Second Input :A.45201B.45385results 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.50035Xenia Latitude : 39.69138 Longitude : -83.9514May be the Point variations is there.ThanksVisa.G |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-01-25 : 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.50035Xenia Latitude : 39.69138 Longitude : -83.9514CODO ERGO SUM |
|
|
visa123
Yak Posting Veteran
54 Posts |
Posted - 2011-01-27 : 07:43:08
|
HiYes. 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 miVisa.G |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-01-27 : 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
30421 Posts |
Posted - 2011-01-27 : 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)
7020 Posts |
Posted - 2011-01-27 : 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
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2011-01-27 : 18:59:12
|
you'd pay a larger wormhole tax for the 5000 mile trip. elsasoft.org |
|
|
|
|
|
|
|