I am a little unsure of the best way of doing things. I am creating a dealer locator module. I have the database which contains all necessary info such as zipcode, latitude, longitude called ZipCode. I obviously have a Dealers table which has dealer info, including zipcode. My question for the community is if there is a better way to do this process, because we in the usa alone there is over 65k zipcodes, and large chains can have thousands of locations.
Currently, i grab the zipcode and the radius the want results on from a form. I then take the zipcode and query the database, returning the lat and long of the zip the user entered on the form.
I pass this lat and long to the next procedure which incorporates the radius taken in on the form. This calculates all the lats and longs i will use to "box" off my area to perform a query on areas located within the box.
I then query Zipcodes (select * zipcodes where...) using <= and >= for all sides of the "box"(high and low latitude, and high and low longitude) at this point an inner join is done by zipcode with the zipcode on the dealers table.
Lastly, I do a distance calculation on the returned data dropping all those not within the radius.
Besides anyone have a better way to do this, does anyone have a suggestion about this:
Do I use a inner join for the dealer locator, or should I just add the lat and long to the dealers table automatically upon dealer registration of a cite?
CREATE PROCEDURE dbo.sp_GetDistanceByZip
(
@.StartZip char(5),
@.EndZip char(5)
)
AS
SET NOCOUNT ON
--DECLARE @.StartZip char(5)
--DECLARE @.EndZip char(5)--SET @.StartZip = '92833'
--SET @.EndZip = '90005'
DECLARE @.LatA float
DECLARE @.LongA float
DECLARE @.LatB float
DECLARE @.LongB float
DECLARE @.Distance float
SET @.LatA = pi() * (Select Top 1 Latitude From zips Where ZipCode = @.startZip) / 180
SET @.LongA = pi() * (Select Top 1 Longitude From zips Where ZipCode = @.startZip) / 180
SET @.LatB = pi() * (Select Top 1 Latitude From zips Where ZipCode = @.endZip) / 180
SET @.LongB = pi() * (Select Top 1 Longitude From zips Where ZipCode = @.endZip) / 180
SET @.Distance = ACOS(
SIN
(
convert(Float,(@.LatA))
)
*SIN
(
convert(Float,(@.LATB))
)
+COS
(
convert(Float,(@.LatA))
)
*COS
(
convert(Float,(@.latB))
)
*COS
(
convert(Float,((@.longa) - (@.LongB)))
)
) * 3963.1
SELECT @.Distance, @.lata, @.longa, @.latb, @.longb
GO
My suggestion for you is to use many short selects rather than a huge join. It'll run much faster, and index it properly.|||Thank you for the reply.
Question:
If I have narrowed down the amount of possible zip codes by my "box"(only the zips, lats, and longs are going to be returned that fall within this "box"), and I then do the join, is this more taxing on the system than your suggestion?|||It is less. you should try to stay away from processing in the SQL part as much as possible.
I would also have gone with a "box" solution.|||Thanks for the input.
I would strongly suggest for anyone interested in this, to purchase the solution along with a subscription for the database. For me, this is more of a matter of being able to do it rather than saving money. If it were a matter of dollars and cents, this solution would cost me roughly forty dollars for a site, and I spent an entire day on it. I may not be the best programmer, but I am worth more than 40 a day.|||Honestly, I have the entire US/Canada, and I've run that procedure with 10 threads. Each returned in less than 2 seconds. I've condensed it to specific regions and such, but for the most part, I'm very much ok with it's performance.
Explain your "boxing" method. That interests me|||Draw a cricle, now draw a box around it, the square should touch the box at four points, all other points contained within the circle are also contained within the box. Why should we query the entire continent or world if we can calculate that box from the radius using latitude and longitude. This drastically limits the number of records that we have to due our distance calculation on. We just get the zipcode and radius from the user, pull the latitude and longitude from the database by doing a select statement of the row containing that zipcode input by the user, next we do some calculations that determine our box from the radius where the centerpoint is the zipcode. Then we select only those records whose latitude and longitude fall within our box. Then we calculate distance based on only those in the box instead of the entire country, continent, etc. Everything outside of our radius distance, gets dropped(these would have still fallen within the box). The results are returned to the user.
I hope I am clear and this explains the "box" technique. Any questions, just post back. There is a little more than what I said which is involved in this, i just tried to explain it as simple as I could. Much easier to draw something like this than use words.|||Hi, I was just wondering...did you ever happen to find a fast solution to your problem?
I am currently working on a project that involved US AND Canada zip codes.
Seeing as Canada has over 700,000 post codes I really need to find the fastest way to accomplish returning results.
The way my app will need to work is pass 1 postal code...and return all records within a 50 mile radius.
Can you please let me know how your project went? Can you please share with me how you were able to achieve this?|||This really sholud not be an issue. We have databases of 300 million records that can be parsed quickly. It is down to your indexing strategies.
For retrieving areas within a circle, use a bit of pythagoras to work out the distances. Job's done pretty easy :)|||i am also about to take on this type of project with the exact same concept...I need to retrieve a business located within a 50 mile radius from a zip code. So any input on this would be great. I am assuming I need to buy a zip code database so if anyone knows of one that will accomplish this please let me know.
if anyone is interested in building it or has the code let me know what you charge to do it.
contact me at rpanek90@.hotmail.com
