October 17, 2009 at 3:39 pm
the code below uses the same formula in two places (the SELECT and the JOIN) .. Will this hurt performance? I believe I could simplify it by making it a UDF, but am confused on how to do this when using a JOIN.. tx in advance, matt.
DECLARE @radians float
SELECT @radians = 57.295779513082323 --> 180/PI
SELECT t1.*, DistanceInMiles = 3963.0*acos(sin(t1.Latitude/(@radians)) * sin(t2.Latitude/(57.295779513082323)) + cos(t1.Latitude/(@radians)) * cos(t2.Latitude/(@radians)) * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)))
FROM dbo.ZipCodes t1
JOIN dbo.ZipCodes t2 ON (3963.0*acos(sin(t1.Latitude/(@radians)) * sin(t2.Latitude/(57.295779513082323)) + cos(t1.Latitude/(@radians)) * cos(t2.Latitude/(@radians)) * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)))< @radius)
WHERE t2.ZipCode='10023'
ORDER BY DistanceInMiles
October 19, 2009 at 1:50 am
I would wrap it in a Cte and then filter on the result of that.
However , what will hurt your performance more is unnecessary calculations.
In my Sarg i would also add where t1.Latitude Between t2.Latitude - @Radius and t2.Latitude+@Radius , same for Longitude. At least then it may be able to use an index.
Are you in a position to upgrade to 2008 ? SqlServer 2008 has native support for geospatial types
October 19, 2009 at 9:47 am
Dave Ballantyne (10/19/2009)
I would wrap it in a Cte and then filter on the result of that.However , what will hurt your performance more is unnecessary calculations.
In my Sarg i would also add where t1.Latitude Between t2.Latitude - @Radius and t2.Latitude+@Radius , same for Longitude. At least then it may be able to use an index.
Thanks Dave - but I still don't understand how to use a UDF here. and am confused about your suggestion of adding to "Sarg".. could you please give an example?
October 19, 2009 at 10:02 am
a sarg is a searchable argument. By making the Longitude and latitude searchable as below, this will quickly eliminate all those zipcodes outside of the squared off area. the more complex calculation will only be performed on those that are inside the area.
Additionally SQL Server may now use any indexes on those columns.
DECLARE @radians float
SELECT @radians = 57.295779513082323 --> 180/PI
with cteDistCalc
as(
SELECT t1.*,
DistanceInMiles = 3963.0*acos(sin(t1.Latitude/(@radians)) * sin(t2.Latitude/(57.295779513082323))
+ cos(t1.Latitude/(@radians)) * cos(t2.Latitude/(@radians)) * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)))
FROM dbo.ZipCodes t1
JOIN dbo.ZipCodes t2
on t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius
and t1.Longitude between t2.Longitude - @radius and t2.Longitude + @radius
where t1.ZipCode='10023'
)
select * from cteDistCalc < @radius
ORDER BY DistanceInMiles
October 19, 2009 at 10:12 am
Dave Ballantyne (10/19/2009)
a sarg is a searchable argument. By making the Longitude and latitude searchable as below, this will quickly eliminate all those zipcodes outside of the squared off area. the more complex calculation will only be performed on those that are inside the area.Additionally SQL Server may now use any indexes on those columns.
DECLARE @radians floatSELECT @radians = 57.295779513082323 --> 180/PI
with cteDistCalc
as(
SELECT t1.*,
DistanceInMiles = 3963.0*acos(sin(t1.Latitude/(@radians)) * sin(t2.Latitude/(57.295779513082323))
+ cos(t1.Latitude/(@radians)) * cos(t2.Latitude/(@radians)) * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)))
FROM dbo.ZipCodes t1
JOIN dbo.ZipCodes t2
on t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius
and t1.Longitude between t2.Longitude - @radius and t2.Longitude + @radius
where t1.ZipCode='10023'
)
select * from cteDistCalc < @radius
ORDER BY DistanceInMiles
I think your code is missing the declaration and set for @radius.
Also, there are a couple of rare but possible conditions that would invalidate that code:
1. If the search radius area crosses the international date line.
2. If the search radius area includes either the North or South pole.
Also, the longitude limits have to calculated taking into account that N radians is a shorter distance as you move from the equator towards the North or South pole. Also, taking into account that the shortest distance is along a great circle, and not directly along the lattitude line complicates the calculation for the limits on Longitude.
October 19, 2009 at 10:30 am
Michael Valentine Jones (10/19/2009)
Dave Ballantyne (10/19/2009)
a sarg is a searchable argument. By making the Longitude and latitude searchable as below, this will quickly eliminate all those zipcodes outside of the squared off area. the more complex calculation will only be performed on those that are inside the area.[/code]
--> @radius is in miles - I didn't think you could use it with Lat/Longitude calculations??? e.g. ( t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius )
October 19, 2009 at 10:38 am
matt6749 (10/19/2009)
--> @radius is in miles - I didn't think you could use it with Lat/Longitude calculations??? e.g. ( t1.Latitude between t2.Latitude - @radius and t2.Latitude + @radius )
Well, it shouldnt be to hard to find a formula to convert it , erroring on the side of caution and using the equator.
@michael-2 Valentine Jones
All very valid points. Not pretending its going to be bullet proof , taking into account all the valid points just demonstrating to the OP that performance can be improved by a much wider margin that executing the original calculation once rather than twice. The aim is to simply reduce the amount of points that have to use the complex calculation.
October 19, 2009 at 10:57 am
thank you.. But.. for my original post and question - how can I use a UDF to simplify? tx!
October 19, 2009 at 12:07 pm
I'm not sure what you mean by simplify, but here are two functions that you can use with each other to do the search you want. At the very least, you need to have a reliable method for calculating the limits of the search radius.
The first function calculates the distance. The second 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.
Row at a time function calls do have a performance penalty, but it may be simpler to use functions to calculate the distance than having to re-test every new query if your search volume is low.
Great Circle Distance Function - Haversine Formula
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360
Function F_FIND_SEARCH_LIMITS
October 19, 2009 at 12:53 pm
ok.. thanks!
October 30, 2009 at 11:34 am
I had to do something like this before SQL 2005 so CTE wasn't available. It was in a stored procedure where I'd get a zip code and number of miles for the radius and have to figure out the other zip codes that were within that radius. I had a table of zip codes with mean latitude and longitude for each.
I found that the fastest way to do this was determine the max latitude on both sides and max longitude on both sides based on the number of miles passed. Then select all zip codes falling within those boundaries (no math involved in this step - just between the max boundaries). This created a square more or less. Then use the trig functions to round off the corners.
This method was very fast. Since it was for marketing for car dealerships, I wasn't too concerned about north and south poles.
Todd Fifield
October 30, 2009 at 11:47 am
Thanks Todd - I have only used the "Max Latitude" limiter cause it was pretty simple. I haven't implemented the other part of the "square" - the max longitude cause it seemed too complex for me.. If you have a simple way - please forward!
thanks,
Matt, NYC
November 1, 2009 at 2:04 pm
Matt,
This was quite a while ago and I don't have the actual code I used at hand. Basically you divide the radius (in miles) you want by 111 (carried out to 6 decimal places). You add this value to latitude to get max and subtract to get min. Do the same for longitude (won't work near the international date line).
This gives min/max for both latitude and longitude. Everything between is included. Then use the trig functions to weed out what doesn't actually fit in the circle.
Todd Fifield
November 1, 2009 at 11:22 pm
If you can not use CTE you can replace the code by temp tables or table variables.:-)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply