Solved

In ASP classic I need to search for the nearest plumbers within a 20 mile radius, given a zip code.

Posted on 2011-02-20
15
600 Views
Last Modified: 2012-05-11
This is a pretty common feature on many websites - like dating or "find a contractor" websites.

You'll enter in your zip code, then you enter in how many miles to "spread out" from this zip code, then the site displays for you all items within a, say, 5 mile radius of your zip code.

So I need to implement this functionality on my website but i have no idea where to start.

I have a database of every zip code in America.

And I have a database of all my plumbers around America

See image:

http://img17.imageshack.us/img17/3686/plumbers2.jpg

So when someone goes to my website and types in:

ZIP: 00501 Radius: 5

I need to show them all the plumbers I got within a 5 mile radius of their zip code.

Anyone got any ideas on how I would code that?

Since I have the longitude and latitude of all the zip codes, perhaps i'd have to do some sort of geometric heuristic?

I'm sure this is a classic computer science problem.

But I was only a computer science minor...

:)

little help?

Thanks!
0
Comment
Question by:bobbysdog
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
15 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 34940403
What database are you using? For example PostgreSQL with the PostGIS geospatial extensions can do all that with the inbuilt function calls. Trying to do something your self would be tricky and most likely slow without a lot of effort at understanding things like how R-tree indexes work.
0
 

Author Comment

by:bobbysdog
ID: 34940506
wow really?

I'm just using MS Access and Classic ASP...
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34940520
I guess it depends on how big your site needs to be (users, data size, etc). You could code it up yourself fairly quickly with simple geometry functions and if there is only one user that might be acceptable. However, I'm used to catering for 1000s of users so haven't touched Access/ASP in a loooong time :) Just found that MySQL has spatial extensions too if you prefer that to postgresql, but I have never used them.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bobbysdog
ID: 34940633
ya i would hope the site would eventually have thousands of visits a day.

damn that is a good link you posted...
0
 
LVL 7

Expert Comment

by:MrNed
ID: 34940643
If you really want that much traffic, get off MS Access immediately :) It will NOT scale.
0
 
LVL 1

Expert Comment

by:axbernardi
ID: 34944824
Hi.
If you want a very rough procedure (but it might be enoght for your purpose) you can run a query that take into consideration the coordinate of the two Post Code.
Then you can compare them keeping in mind that 1 degree of latitude it's always 69.2 miles.
1 degree of longitude unfortunately can vary with the latitude, but for the whole USA the value is between 48 and 58 (it is basically cos(latidude)*69.2 ) miles.
A couple of comparisson and you are roughly there!
0
 

Author Comment

by:bobbysdog
ID: 34945772
>> "If you really want that much traffic, get off MS Access immediately :) It will NOT scale."

thanks - ya i know Access has its flaws. Unfortunately i'm not smart enough to use anything else. Hopefully, someone will buy my website for a million dollars and then they'll have to worry about optimizing it with a better database later :)

>> "If you want a very rough procedure (but it might be enoght for your purpose) you can run a query that take into consideration the coordinate of the two Post Code..."

hi

i think i follow your procedure. the thing is, there are 40,000 zip codes. So I think i'd need some sort of Spatial Database algo as described above - right?

0
 
LVL 1

Expert Comment

by:axbernardi
ID: 34945908
well, you said:

>> Since I have the longitude and latitude of all the zip codes, ...

so... there you go... that's your Spacial Database
0
 

Author Comment

by:bobbysdog
ID: 34946421
Right

so i have this grid of x y coords

but the point is, I have 40,000 of them.

so the point of this thread is to try to find an algo that will get the 10 nearest points to

33.405559, -86.95141

and to do this in a fraction of a second (so i dont keep the user waiting...) and to implement this in ASP classic and MS Access on a typical windows server

0
 
LVL 1

Accepted Solution

by:
axbernardi earned 500 total points
ID: 34947448
The initilal question was:
how to find the points at less than X miles from 33.405559, -86.95141.

Given the plumbers table done as
Name,ZIP,...

and the zips table done as
ZIP,lat,lon

the resulting query would be:

select plumbers.name,plumbers.ZIP from plumbers inner join zips on zips.ZIP=plumbers.ZIP
where abs(lat-33.405559)< X/55 and abs(lon- (-86.95141))<X/69.2

Open in new window


This query would actually not drow a circle but a square around the given point.

Generally speaking, the original point (the 33.405559, -86.95141) could be found with one first query at the beginning given the "search for" zip, with a simple
select lat,lon from zips where zip=TheGivenZip

Shouldn't take long to run this 2 query
0
 

Author Comment

by:bobbysdog
ID: 34957508
Oooohhh

I see what you're getting at.

Ya thats cool. My website does not need to be precise at all. In fact if it guesses with a wide radius thats probably better.

but in your code, it looks like its only getting one side of the point...no?

wouldnt i need something like:

WHERE abs(lat-33.405559) < X/55 AND abs(lat-33.405559) > X/55

AND

abs(lon- (-86.95141)) < X/69.2 AND abs(lon- (-86.95141)) > X/69.2
0
 
LVL 1

Expert Comment

by:axbernardi
ID: 34958893
No, you don't need that, that's the point of having an abs function!
so abs(33.41-33.45) = abs(-0.04) = 0.04
0
 

Author Comment

by:bobbysdog
ID: 34967519
hmm

doesnt the abs value of a number just delete the neg sign

so in this case:

abs(lat-33.405559) <  X/55

wouldnt that just give me all the data on top of 33.40

like everything above San Francisco or thereabouts?
0
 
LVL 1

Expert Comment

by:axbernardi
ID: 34968132
imagining X = 6 then X/55 = 0.11
33.5-33.4=0.1  --> abs= 0.1, included
33.3-33.4=-0.1 --> abs= 0.1 included
33.6-33.4=0.2  --> abs= 0.2, excluded
33.2-33.4=-0.2  --> abs= -0.2, excluded

so the formula will give you true for all values between 33.29 and 33.51
Ok??
0
 

Author Comment

by:bobbysdog
ID: 34994558
oh i think i got it

thanks so much for helping me!!!!!!!!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Nadia
Suppose you use Uber application as a rider and you request a ride to go from one place to another. Your driver just arrived at the parking lot of your place. The only thing you know about the ride is the license plate number. How do you find your U…
The greatest common divisor (gcd) of two positive integers is their largest common divisor. Let's consider two numbers 12 and 20. The divisors of 12 are 1, 2, 3, 4, 6, 12 The divisors of 20 are 1, 2, 4, 5, 10 20 The highest number among the c…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question