468,526 Members | 2,133 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,526 developers. It's quick & easy.

Radius, distance, latitude, longitude tool or code

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?
--
Sandy
Nov 21 '05 #1
9 3175
> 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"
Nov 21 '05 #2

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
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?
--
Sandy


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
Nov 21 '05 #3
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!
--
Sandy
"Gerald Hernandez" wrote:

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
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?
--
Sandy


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

Nov 21 '05 #4
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
"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:71**********************************@microsof t.com...
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!
--
Sandy
"Gerald Hernandez" wrote:

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
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?
--
Sandy


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

Nov 21 '05 #5
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?

--
Sandy
"Gerald Hernandez" wrote:
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
"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:71**********************************@microsof t.com...
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!
--
Sandy
"Gerald Hernandez" wrote:

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:F0**********************************@microsof t.com...
> 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?
> --
> Sandy

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


Nov 21 '05 #6
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/pr.../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

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:3A**********************************@microsof t.com...
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?

--
Sandy
"Gerald Hernandez" wrote:
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
"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:71**********************************@microsof t.com...
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!
--
Sandy
"Gerald Hernandez" wrote:

>
> "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> news:F0**********************************@microsof t.com...
> > 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?
> > --
> > Sandy
>
> 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
>
>
>


Nov 21 '05 #7
Gerald -

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

--
Sandy
"Gerald Hernandez" wrote:
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/pr.../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

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:3A**********************************@microsof t.com...
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?

--
Sandy
"Gerald Hernandez" wrote:
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
"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:71**********************************@microsof t.com...
> 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!
>
>
> --
> Sandy
>
>
> "Gerald Hernandez" wrote:
>
> >
> > "Sandy" <Sa***@discussions.microsoft.com> wrote in message
> > news:F0**********************************@microsof t.com...
> > > 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?
> > > --
> > > Sandy
> >
> > 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
> >
> >
> >


Nov 21 '05 #8
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

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:05**********************************@microsof t.com...
Gerald -

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

--
Sandy

<Snipped>...
Nov 21 '05 #9
Thanks!
--
Sandy
"Gerald Hernandez" wrote:
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

"Sandy" <Sa***@discussions.microsoft.com> wrote in message
news:05**********************************@microsof t.com...
Gerald -

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

is
it to hook up?

--
Sandy

<Snipped>...

Nov 21 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Aaron Collins | last post: by
1 post views Thread by R. Rajesh Jeba Anbiah | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.