Radius, distance, latitude, longitude tool or code

[Guest]

Hello -

I need either a cheap tool or code & DB that calculates, eg. within 50-mile
radius of a zip code.

Anyone have any suggestions?

 

[Mike Labosh]

I need either a cheap tool or code & DB that calculates, eg. within

50-mile
radius of a zip code.

I don't remember what the title of the thread was, but several months ago,
someone in microsoft.public.sqlserver.programming posted a SQL Server table
and a stored procedure that does exactly that. If you repost your question
there, you might get a couple excellent answers.
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS."
-- General Barringer, "War Games"

 

[Gerald Hernandez]

Hello -

I need either a cheap tool or code & DB that calculates, eg. within 50-mile
radius of a zip code.

Anyone have any suggestions?

That calculates what specifically?
Tell you whether a specific location is within 50 miles of a zip code?
Are we talking coordinates, addresses, what?
When you say within 50 miles of a zip code, you mean the actual boundary,
one of the centroids, the PO itself?
Sorry, I might be overthinking this as I do all kinds of stuff like this.
If you can clearly define what you have and what you need I might be able to
point you in the right direction.

Gerald

 

[Guest]

Gerald -

Thanks for your response. Specifically, I need to be able to have someone
enter a zip code OR city and state and be able to find out whether there is a
listing in my DB which has businesses listed with zip codes, cities and
states within a radius of 50 miles, 100 miles, etc. (The user can indicate
what radius they want.)

This is similar to store locators on websites where you can enter a zip code
and find a store within your vicinity. They have a dropdownlist indicating
whether you want "100 miles," "50 miles," "20 miles," etc.

Any help will be greatly appreciated!

 

[Gerald Hernandez]

Sandy,

One of the key elements to this is getting a known locations for both the
search point and the business locations. This is usually accomplished via a
geocode, find the location for a particular address. This is easy enough for
your business addresses as they are known. A little more difficult is for
your starting search point.

If only using ZIP code, it is not very complicated. In most cases, they just
use the centroid of the zip code, which can be defined in numerous ways but
usually the center of mass or the center of distribution/population. Then
compare that to the known location of the businesses. There is oftentimes
much pre-computation of certain parameters, such as assigning an average
value for the "radius" of a ZIP code. Plus, it's important to note that ZIP
code areas can be disjoint, meaning you can have multiple areas with the
same ZIP code with other ZIP codes in between.

City is a little more difficult. There are numerous Towns and/or Cities that
are not incorporated. As such, they don't usually have clearly defined or
maintained boundary information. For these they do a cross reference to
postal delivery names. Basically mapping "city" names to encompassing ZIP
codes, then use the above ZIP code method for location.

The above methods are quite inaccurate, but if you are just looking for
something like the store locator example, then it works well enough. If you
are needing something more complex, like routing, point to point directions,
or analysis that requires accuracy, then it is far from sufficient.

In a nutshell, you will need to determine the location of the businesses,
probably via a geocode and store that in your database. You will probably
then want to get your hands on ZIP code data and store the centroids,
bounding box area, and calculate an averaged radius size. You will also need
to cross reference the "city" names to postal delivery names and therefore
ZIP codes. There are a number of companies that can provide the needed data.
TIGER has much of it, and you might start there. It is usually free, but can
take a fair amount of work to get it into a usable form. Other companies
have already done much of the hard work for you and also reference other
datasets, but these can be quite expensive. My recommendation my be to get
your hands on MapPoint. It is relatively cheap and comes pre-packaged with
the data you need, and also provides methods to help you along.

Once you have all that information available, the query isn't terribly
difficult, well relatively speaking.
Based on your search criteria, you build a search Range / Bounding Box. Then
initially query out a subset of records using that. This will give you a
list of records that "could" meet your criteria.
Then from that dataset, you iterate through each record performing a
distance calculation to see if it actually falls within the desired radius;
keeping the ones you need and discarding the rest. If you choose to keep
your data in Lat/Lon instead of projecting coordinates to a Cartesian
coordinate system, then you will probably want to use a math library
designed to work with Lat/Lon to calculate distance. Again, MapPoint can do
this, of course there are numerous others as well. I haven't examined too
many of the "free" ones, so I can't offer a suggestion there other than to
be careful. Some of the free ones I've seen have flawed math and can give
you quite inaccurate results, especially in the northern half of the US.

I know that didn't really answer your question or provide you a solution,
but hopefully it will help you along.

Gerald

 

[Guest]

Thanks for your reply, Gerald. It seems I may have found a solution suitable
to my current needs from: http://www.aspfaq.com/show.asp?id=2527

I think I have the stored procedure working, although I still need to test
it some more. I also have to build in the capabilities to use city as a
parameter. Currently it only does zip codes. I tried to plug it into my
aspx page, though, and haven't been able to get it to work correctly yet . .
.. probably something stupid I'm doing.

I briefly glanced at MapPoint and to be perfectly honest, I got aggravated
with the fact that I couldn't find the price, so I gave up. What do you mean
by "relatively cheap," and where do they list a price?

 

[Gerald Hernandez]

That's an interesting article. It takes a very simplistic approach, but
looks like a good start for what you need.

Here is a starting link for pricing on MapPoint:
http://www.microsoft.com/mappoint/products/2004/purchase.mspx

I say "relatively", because compared to ArcView ($3000+) and others much
more expensive, the $300 for MapPoint is cheap. Getting quality data itself
can be expensive. ZIP code areas are changing constantly, and many data
providers provide quarterly updates, and some monthly. Assuming you need the
whole USA. Cheap data, is well cheap, sometimes free. Usually based on very
old TIGER files, often times 10 years or more out of date. Odds are to get a
decent City name to ZIP code delivery cross reference you will need to
purchase the data. Now when I say expensive, this is compared to free, you
are probably talking about less than $500.

Another thing to be aware of is that most data providers have specific
limitations on usage of the data. If it involves web pages and such, there
is an additional fee. I believe MapPoint has the same limitations,
considering they just license the data from the normal commercial providers.

Gerald

 

[Guest]

Gerald -

Thanks for the link to MapPoint. Have you ever used it? If so, how hard is
it to hook up?

 

[Gerald Hernandez]

Actually I have not used it. With half a dozen other enterprise level GIS
apps and a bunch of my own, MapPoint just doesn't cut it for me. MapPoint
seems quite limited, but I think it might be an excellent entry point for a
lot of people. From what I have read from others, and the impression I get
from the docs is that it is pretty easy to work with.

Gerald

Gerald -

Thanks for the link to MapPoint. Have you ever used it? If so, how hard is
it to hook up?

<Snipped>...

 

[Guest]

Thanks!
--
Sandy

Actually I have not used it. With half a dozen other enterprise level GIS
apps and a bunch of my own, MapPoint just doesn't cut it for me. MapPoint
seems quite limited, but I think it might be an excellent entry point for a
lot of people. From what I have read from others, and the impression I get
from the docs is that it is pretty easy to work with.

Gerald


<Snipped>...