?
Solved

How do you search distance by zip codes?

Posted on 2009-04-16
59
Medium Priority
?
1,205 Views
Last Modified: 2012-05-06
I have an MS Access database that stores cities and their respective zip codes. I know how to pull up this information in an MS Flexgrid. What is the code to search a certain amount of zips for a given mile range?

In other words, if I put in "search 5 miles out" from zip code 98109, the program would pull up all the zips that were 5 miles in radius.

I also have Latitude and Longitude associated with the cities/zips.
0
Comment
Question by:Taylor814
  • 23
  • 15
  • 10
  • +2
56 Comments
 
LVL 5

Expert Comment

by:drakeshe
ID: 24163152
Is this in conjunction with a web service?
0
 

Author Comment

by:Taylor814
ID: 24163885
No
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24174339
There are many examples of how to calculate this if you do a google search, I have not used it but the VB example at http://www.zipcodeworld.com/developers.htm seems reasonably straight forward and free. Once you have the calculation I would suggest that you create a table that holds the 2 zip codes and the distance between them, populate this with all ZIP codes ensuring ZIP1 is less than ZIP2 (this can be done in a query). Then you can then search on this table. You can then create a function that always checks the 2 zips and checks the smaller value agaist the ZIP1 and the larger against ZIP2. This way the table will not be as large as you do not need to hold each direction, ZIP1 to ZIP2 and ZIP2 to ZIP1.

Hope this helps, Andrew
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 18

Expert Comment

by:mdougan
ID: 24175254
If you have the lat/lon of your starting zip, then you can calculate the distance to each other zipcode using a simple navigation formula.  It might be a bit computationally intensive, unless you ran the calculation once and stored the results in your database.

I'll leave it up to you to convert the meters to miles!
Private Function DistanceInMeters(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
' Takes lat/longs as expressions in Degrees
 
' approx radius of Earth in meters.  True radius varies from
' 6357km (polar) to 6378km (equatorial).
Const earth_radius = 6367000
Dim rLat1 As Double
Dim rLong1 As Double
Dim rLat2 As Double
Dim rLong2 As Double
 
    rLat1 = DegreesToRadians(lat1)
    rLong1 = DegreesToRadians(long1)
    
    rLat2 = DegreesToRadians(lat2)
    rLong2 = DegreesToRadians(long2)
    
' Using Radians
'   less subject to rounding errors for small distances
    DistanceInMeters = (2 * asin(Sqr((Sin((rLat1 - rLat2) / 2)) ^ 2 + Cos(rLat1) * Cos(rLat2) * (Sin((rLong1 - rLong2) / 2)) ^ 2))) * earth_radius
 
End Function
 
Private Function asin(X As Double) As Double
    asin = Atn(X / Sqr(-X * X + 1))
End Function
 
Private Function DegreesToRadians(X As Double) As Double
Const pi = 3.14159265358979
'To convert degrees to radians, multiply degrees by pi/180.
    DegreesToRadians = X * pi / 180
End Function

Open in new window

0
 

Author Comment

by:Taylor814
ID: 24175847
Since it is calculative intensive, it would be best then to use a web service. Mods, can this be changed to a PHP /MySQL category?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24176351
You need to know about this:
http://en.wikipedia.org/wiki/Haversine_formula

And you need to have your ZIP code file include a Latitude/Longitude pair for each ZIP code.  Usually this is the geographic center of the ZIP code area, but it may also be the location of the central post office for that ZIP code.  I have looked at a lot of ZIP code + Lat/Lon data bases and they are of varying quality - in the end you may want to try geocoding the ZIP codes yourself via Yahoo or Google (or both) in order to get the best accuracy.

ZIP codes are not really "geographic" locations - they are postal carriers routes, so you may find some error and overlap in ZIP codes, and of course they will be quite small in heavily populated areas and quite large in the US Western states.

Also, you need to be aware that the Lat/Lon pair data when expressed in a decimal number, as required for Haversine computations, has a different meaning in different latitudes.  As you get closer to the poles the numbers describe smaller distances.

Now with that as a background, I will post some code samples that may eb helpful.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24176404

// FUNCTION TO COMPUTE DISTANCE FROM ONE ZIP TO ANOTHER
function compute_zip_distance($from_zip, $to_zip, $source='Z') 
{
	global $db_connection;
	if ("$from_zip" == "$to_zip") 
	{
		return 0.0;
	}
 
	$dbt = 'RAY_ZIPCODES'; 
 
	$fsql	= "SELECT latitude, longitude from $dbt WHERE zip = \"$from_zip\" ";
	if (!$f	= mysql_query("$fsql", $db_connection)) { fatal_query_error($fsql); }
	$frow	= mysql_fetch_assoc($f);
	$f_lat	= $frow["latitude"];
	$f_lon	= $frow["longitude"];
 
	$tsql	= "SELECT latitude, longitude from $dbt WHERE zip = \"$to_zip\" ";
	if (!$t	= mysql_query("$tsql", $db_connection)) { fatal_query_error($tsql); }
	$trow	= mysql_fetch_assoc($t);
	$t_lat	= $trow["latitude"];
	$t_lon	= $trow["longitude"];
 
	if ( (!mysql_num_rows($f)) || (!mysql_num_rows($t)) ) return FALSE;
	return compute_distance($f_lat, $f_lon, $t_lat, $t_lon);
}
 
 
// FUNCTION TO COMPUTE DISTANCE FROM ONE LAT/LON PAIR TO ANOTHER
function compute_distance($from_lat, $from_lon, $to_lat, $to_lon) 
{
	if ( ($from_lat == $to_lat) && ($from_lon == $to_lon) ) 
	{
		return 0.0;
	}
 
	$from_lat	= floatval($from_lat);
	$from_lon	= floatval($from_lon);
	$to_lat		= floatval($to_lat);
	$to_lon		= floatval($to_lon);
 
	$dist	= acos( sin(deg2rad($from_lat))
              * sin(deg2rad($to_lat))
              + cos(deg2rad($from_lat))
              * cos(deg2rad($to_lat))
              * cos(deg2rad($from_lon - $to_lon)) );
 
	$dist	= rad2deg($dist);
	$miles	= (float) $dist * 69.0;
	return round($miles,1);
// To get kilometers, multiply miles by 1.61
// $km		= (float) $miles * 1.61;
// return round($km,2);
}

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24176463
Please look this over and post back here if you have any questions.  Best regards, ~Ray
// SOME ASSUMPTIONS
// INPUT ZIP CODE IS IN VAR $my_zip
// $my_zip LAT/LON FIELDS ARE IN VARS $my_lat AND $my_lon
// DATA BASE TABLE HAS z_zip ZIP CODE with z_lat AND z_lon FIELDS
// SOME LOCAL FUNCTIONS ARE USED IN CASE OF QUERY ERRORS
 
 
// CONSTANTS FOR SOME ROOM AROUND OUR ZIP CODE - GIVES SEVERAL MILES FOR PROXIMITY SEARCH
DEFINE("LATITUDE_OFFSET", 0.20000);
DEFINE("LONGITUDE_OFFSET", 0.20000);
 
 
 
// COMPUTE THE MIN AND MAX LAT/LON PAIRS
$minlat = $my_lat - LATITUDE_OFFSET;
$maxlat = $my_lat + LATITUDE_OFFSET;
$minlon	= $my_lon - LONGITUDE_OFFSET;
$maxlon	= $my_lon + LONGITUDE_OFFSET;
 
 
// FIND NEARBY ZIP CODES AND STORE IN A SMALL TEMPORARY TABLE
$tsql	= "CREATE TEMPORARY TABLE nearbyZipCodes ( ";
$tsql	.= "distance	decimal(6,1) ";
$tsql	.= " ) ENGINE=MEMORY SELECT * FROM ZipCodes WHERE ";
$tsql	.= "( z_lat BETWEEN $minlat AND $maxlat ) AND ( z_lon BETWEEN $minlon AND $maxlon ) ";
if (!$t	= mysql_query("$tsql", $db_connection)) { fatal_query_error($tsql); }
 
if (!$t	= mysql_query('SELECT COUNT(*) FROM nearbyZipCodes', $db_connection)) { fatal_query_error($tsql); }
$trow	= mysql_fetch_array($t, MYSQL_NUM);
$tcount	= $trow[0];
 
if ($tcount == 0) 
{
	die("Sorry, there is nothing close to that ZIP code");
} 
 
 
// COMPUTE THE DISTANCE FOR EACH NEARBY ZIP CODE AND UPDATE THE TEMPORARY TABLE
$dsql	= "SELECT * FROM nearbyZipCodes";
if (!$d	= mysql_query("$dsql", $db_connection)) { fatal_query_error($dsql); }
while ($drow = mysql_fetch_assoc($d)) 
{
	extract ($drow);
	$dist	= number_format(compute_distance( $my_lat, $my_lon, $z_lat, $z_lon ),1);
	$usql	= "UPDATE nearbyZipCodes SET distance = $dist WHERE key = $key LIMIT 1";
	if (!$u	= mysql_query("$usql", $db_connection)) { fatal_query_error($usql); }
}
 
 
// ITERATE OVER RESULTS SET FOR DISTANCES OF 5 MILES OR LESS
$rsql	= "SELECT * FROM nearbyZipCodes WHERE distance < 5.1 ORDER BY distance ASC";
if (!$r	= mysql_query("$rsql", $db_connection)) { fatal_query_error($rsql); }
while ($rrow = mysql_fetch_assoc($r))
{
	extract ($rrow);
	echo "<br/>$my_zip IS $distance MILES FROM $z_zip";
}

Open in new window

0
 
LVL 18

Expert Comment

by:mdougan
ID: 24176777
Well, switching from one environment to the other isn't going to make it any less computationally intensive.  The problem is that you start with one zip code, then you have to calculate the distance from that zipcode to all other zipcodes in your database before you can then select the ones within 5 miles.

My suggestion was that you go ahead and do this calculation now for every single combination of zipcodes, and store the result in a table with two keys, zipcode A and zipcode B, then, it would be a simple lookup to find the zipcodes within 5 miles of zipcode A.  No further calculation necessary.


But, if you want to do it the hard way, here is the same function as above in PHP
	    function DistanceInMeters($lat1, $long1, $lat2, $long2) {
	       // Takes lat/longs as expressions in Degrees
	       // approx radius of Earth in meters.  True radius varies from
	       // 6357km (polar) to 6378km (equatorial).
	       $earth_radius = 6367000;
	       $pi = 3.14159265358979;
	       $rLat1 = 0.0;
	       $rLong1 = 0.0;
	       $rLat2 = 0.0;
	       $rLong2 = 0.0;
	       $result = 0.0;
 
	       $rLat1 = deg2rad($lat1);
	       $rLong1 = deg2rad($long1);
 
	       $rLat2 = deg2rad($lat2);
	       $rLong2 = deg2rad($long2);
 
	       // Using Radians
	       // less subject to rounding errors for small distances
	       $result = (2 * asin(sqrt(pow(sin(($rLat1 - $rLat2) / 2), 2) + cos($rLat1) * cos($rLat2) * pow(sin(($rLong1 - $rLong2) / 2),2)))) * $earth_radius;
 
	       return($result);
	    }

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24176802
"then you have to calculate the distance from that zipcode to all other zipcodes"

Uhh, no.  That's why you down-select based on proximity as shown in the LATITUDE and LONGITUDE OFFSET fields given in my code example above.  It is this down-select that changes the process from computationally intensive to lightning fast.

As a practical matter there are only about 35,000 ZIP codes that matter - the other few thousand are APO addresses and "special" codes like the IRS data centers.  Another practical matter is the lat/lon offsets that we accept in the down-select.  IIRC, the 0.2000 values give ZIP codes in the 20+/- miles ranges for most of the US, so you might reduce the 0.2000 to 0.1000 and get fewer ZIP codes in the temporary table.  But that's really picking a small nit - the speed of the algorithm to compute distance is such that a few miles (and ZIP codes) more or less is not noticeable.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24176846
Agree that if you are going to re-calculate the distances every query, then pairing down the number based on lat/lon offsets is a good idea.

Still think it is a better idea to do a batch calculation up front, once, then never have to do the calculation again.  Then, the select can be done in the SQL without any calculation in the code.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24176940
It's a trade off between the processor and the storage, for sure.

You can experiment with the algorithm I posted here on my web site.  Just plug in a zip code.  Test 20005 and 89142 - there are noticeable differences!

http://www.carpool2camp.org/db_locale.php

Best to all, ~Ray
0
 

Author Comment

by:Taylor814
ID: 24180013
Ray - I'm very new to PHP. So, I'm supposed to put both of your code examples in one PHP file and upload it to my server. From there, I'm supposed to create a database with the table RAY_ZIPCODES and have 2 fields (z_lat and z_lon).

My question is, how do I go about connecting to the database on my server?
0
 

Author Comment

by:Taylor814
ID: 24180034
Also, I see the code seems to be coded for just 5 miles. I'm wanting the user to input any distance (5 miles, 10 miles, 25 miles, 50 miles, 100 miles, 250 miles, etc)
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24180356
This is not a simple problem for beginning PHP, so you might want to consider hiring a developer to get it all together for you.

The minimum "moving parts" of the data base table for the application are the ZIP code, the latitude and the longitude.  Lat/Lon pairs are called "geocodes" and are always expressed with latitude first.  If you add in altitude, you have the stuff of GPS location data, but altitude is not used in Haversine distance computations.

You can download or buy ZIP code geolocation data from a variety of sources.  I found lots of variability in the quality of the data, so you might want to scrub anything you get.

You can use PHP variables in MySQL queries so you can, for example, use the client input fields to set the distance ranges.  Be aware that if you're looking for all ZIP codes within 250 miles of a New York City ZIP code, you are going to get a huge results set.

For help on PHP and MySQL you might want to post questions in the "PHP and Databases" Zone here at EE.  I also recommend this book.  It will help you get a good foundation in these topics.
http://www.sitepoint.com/books/phpmysql1/

Best regards, ~Ray
0
 

Author Comment

by:Taylor814
ID: 24180411
I'm not going to hire a developer, I pay $$ to be on this site. I appreciate your responses/code, etc, but I'm looking for someone to give me a complete package so that I can implement this.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24180461
No disrespect meant, but I don't think development of this custom application is on the same cost scale as a membership in EE.  We are unpaid volunteers here, so if you're asking a question that has a pre-canned solution or a question that seeks guidance about how to solve a problem, you're likely to get a pretty good answer.  In the instant case it seems to me that you need custom code developed for your particular application and that's why I suggested that a paid solution might be the fastest way to achieve your goals.  

This is not a "hard" problem, per se -- it has been solved before for all kinds of retail store locator apps, but it has a lot of moving parts and will require integration with your server and other application components.  Any knowledgeable PHP developer will be able to take what I have posted above and get the job done.  There are also resources available on Google Apps that give examples and sample code.  Best of luck with it, ~Ray
0
 

Author Comment

by:Taylor814
ID: 24180493
I'll have to wait for someone else to come up with a solution then. It shouldn't be too hard to get a finished solution. I have a database containing lat/lon/zip/city, etc, a server and the ability to input info into mysql.

It really shouldn't be too hard to get it going. You even have an example on your site....
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24180895
Hi Taylor,

There is a lot of truth to what Ray is suggesting, but much has already been offered here that can get you very close to what you want, and yet you are not working with us to narrow down the solution for you.

First of all, what environment do you want the solution in?  VB/Access or PHP/MySQL?

Second... do you already have a database populated in Access/MySQL?  If so, which one?

Third, if you are writing in VB and reading from an Access database, then we can give you example code for that... but, if you are calling a PHP script which reads from a MySQL database, we need to know what you're calling it from, so we know what input/output you expect from any samples.

As Ray said, what you want to do is not all that difficult, however, for us to give you specific code, you have to be more specific with your requirements.

Cheers!
Mike
0
 

Author Comment

by:Taylor814
ID: 24187260
I thought I was being specific.

I have an MDB file containing zips/lat/lon. I can populate that in VB6. I'm trying to add a function that a user can type in a zip and either 5, 10, 25, 50, or 100 miles away, click a button, and they will get a list of zips from the specified range.

I was told this was too computationally intensive for VB6, sooooo.....since I have a webserver, I can use my webserver to interact with VB6 to get a list of zips.

I either need:
1. The code to display the zips in pure VB6 code...or
2. The code to display the zips in PHP/MySQL

0
 
LVL 28

Expert Comment

by:TextReport
ID: 24187438
Hi Taylor814, as an expert on this site i do not see my role as one to do your development work, that is my day job and I get paid for that, but I see it as to assist you to learn.

My posting, http:#a24174339, contains a link that gives you a specific function that calculates the distances. If you had come back to me then i would have ASSISTED you in the creation and population of your table and finally a function that received the zip and distance and returns the list of zips from the table.

Please note the memory intensive bit is the calculation for each zip to zip and is a one off for each pairing, once done then the table should be reasonably efficient. It would be a large table but should be ok with indexes.

Cheers, Andrew
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24187537
OK, perhaps it was my comment that wasn't clear.

What is too computationally intensive is calculating the distance between the zip that the user types in and every other zip code in the database in order to select those within the Mile Scope they chose.  If you only store the lat/lons in the database, then you will need to calculate the distances every single time the user makes a query.  

Ray suggested a technique that could limit the number of zipcodes that you calculate the distance for, from the one the user typed in, buy only selecting other zipcodes that are within .20 of a degree of lat and lon.  That's a good suggestion if you plan to calculate every time you run the query.

I suggested that you set up some code to calculate the distance between every zipcode in your database, then run that code once and update your database with a new table that would have zipcodeA, zipcodeB, Distance.  Then, when your users type in a zip, all you have to do is select from the new table where zipcodeA or zipCodeB = users zip and Distance <= whatever scope you're looking at... 5, 10, 25 miles etc.   In this way, you only have to do the distance calculations once, and from then on, the query will run really quickly because it is just a simple SQL database lookup.

Since your database is already in Access, and since you've already got VB code, I suggest leaving everything in the language it is in.  Since, moving it o PHP and MySQL wouldn't do anything to reduce how computationally intensive it is.

You said that you already knew how to retrieve the zip information from the database and populate a flexgrid with it, so, none of our code samples have included that code.  We've provided you with code that would calculate the distances between the zipcodes that you've returned.

So, if you were doing it the computationally intensive way, here is the pseudo-code for that process:

User types in a zip code, selects 5 miles, clicks a button.

Under the button_click code, you setup a query to the database to select the lat/lon of the zipcode the user selected. You retrieve that record back and store the lat/lon in some VB program variables.

You run another query to select every other zipcode besides the one the user selected.  While reading one by one through this recordset, store the lat/lons of the zipcodes into temp VB program variables, then call the DistanceInMeters function that I provided up in comment ID: 24175254, you convert the Meters to Miles, if the # miles is <= the Mile Scope selected by the user, then add that zipcode to the Flexgrid, continue processing until all zipcodes have been read.

To make it less computationally intensive, scope down the number of zipcodes to only select those, in this second query, that are within .20 degrees (you might have to change that number if they choose a Miles > 20) of the zipcode your user chose.

Or, as I said, do the calculation in some other utility program and populate your database with that info, then your program won't have to do that calculation when running the query.

I'm sure we can provide you with more detail, once you let us know which direction you think you want to proceed.

Cheers!
0
 

Author Comment

by:Taylor814
ID: 24187656
You're saying to store all the different ranges in the MDB file? Then what would be the code (not pseudo) to achieve such a listing? This seems like an unobtainable task.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24187984
Can you provide an Access MDB file with the table of zip codes, but limited to say 20 zip codes with the Long and Lat.
Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24188045
Oh and an indication of how many zip codes there are would also assist is sizing the table.
Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24188105
42903 records
0
 

Author Comment

by:Taylor814
ID: 24188129
Here's the zip.mdb...not sure why it's 6 megs.
zip.mdb
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24188165
I'm suggesting that you store the distance between each and every zip code in the database, not really ranges.

"What ... code ... to achive such a listing?"  Not sure if you are talking about the final list of zipcodes that are within the specified range, or the code to populate the database with the distances.

Let's say that you've created a new table in your database named zipcode_distances that has the following columns:
FromZipCode, ToZipCode, Distance.

To produce a listing of all zipcodes that are within the specified range you would run a select statement that would look something like:

SELECT ToZipCode as ZipCode FROM zipcode_distances WHERE FromZipCode = [Zipcode User Typed in] and Distance <= [Mile Scope User Selected]
UNION
SELECT FromZipCode as ZipCode FROM zipcode_distances WHERE ToZipCode = [Zipcode User Typed in] and Distance <= [Mile Scope User Selected]

The reason you'd be doing a UNION is that you only want to have the distance between two cities in the database in a single record... so:

CityA, CityB, 5 Miles

You could also have
CityB, CityA, 5 Miles

However, this second record is unnecessary, because the distance between the two cities is the same, regardless of which one you put first... so, when populating the distances, we just populate one record.  

That said, we don't know which city the user entered... if he is searching on CityB, then the first query in the UNION might not come back with any results... so, we're also checking the other column.
0
 

Author Comment

by:Taylor814
ID: 24188199
I was asking, what would the code be to achieve the distances between two zips to store in the database. How am I to get all the records that are needed to store these distances.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24188887
OK I have updated your database and attached it. The changes I have made are:-

1. Created Table ZipDistances with the fields ZIP1, ZIP2 and DISTANCE.
1a. Created PK of ZIP1 and ZIP2
1b. Created Index on ZIP1, ZIP2 and DISTANCE
1c. Created Validation Rule of [ZIP2]>[ZIP1] and ValidationRuleText of ZIP2 Must be Greater Than ZIP1
2. Created module basDistances from the code in the link I posted earlier
3. Created Append Query qappZipDistances, this adds all the ZIP codes to the ZipDistances table and runs the DISTANCE function in step 2 to calculate the distances.
4. Created module basDistances_GetNearest that contains the function ListZipsWithinXMiles. To run this you can do this from the immediate window by typing in ?ListZipsWithinXMiles(99501,10)

This is currently an Access VBA solution, the module requires a REFERENCE to the Microsoft DAO library, if this is not in the list of references then you will need to add it. From the Module window, Tools Menu,  References. You should be able to adapt the VBA function to VB using either DAO or ADO.

Cheers, Andrew

PS you need to Compact the database, this removes the rubbish leftover from repeated tasks.
zip.mdb
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24196360
To write the code to get all the possible combinations of zipcodes, you would issue a query such as this:

SELECT A.zipcode AS FromZipCode, A.lat as FromLat, A.lon as FromLon,
               B.zipcode as ToZipCode, B.lat as ToLat, B.lon as ToLon,
  FROM myzipcodes A, myzipcodes B
WHERE A.zipcode <> B.zipcode

Since this query does not JOIN the two instances of the myzipcodes table, it will create what is known as a Cartesian Product which is where every row is joined to every other row in the table.

Then, in your code, as you are reading records from the recordset, you pass the From and To Lat/Lons to the DistanceInMeters calculation that I posted above, convert the resulting Meters to Miles and put it in a local VB variable (I'm calling it lDistance below), then you would format an insert statement that would look something like this:

sSQL = "INSERT INTO zip_distances (from_zipcode, to_zipcode, distance) "
sSQL = sSQL &   "Select " & CSTR(myRecordset("FromZipCode")) & " AS FromZipCode."  
sSQL = sSQL &   CSTR(myRecordset("ToZipCode")) & " AS ToZipCode,"
sSQL = sSQL &   CSTR(lDistance) & " AS Distance "
sSQL = sSQL &   " WHERE NOT EXISTS ( "
sSQL = sSQL &   " SELECT * FROM zip_distances WHERE from_zipcode = " & CSTR(myRecordset("ToZipCode"))
sSQL = sSQL &   " AND to_zipcode = " & CSTR(myRecordset("FromZipCode")) & ")"

The reason  for the Not Exists clause is let's say you already have an entry in the zip_distances table for:

93011, 93117, 10

Then, if the pair that you're about to insert is this:

93117, 93011, 10

You don't want to insert that row.  That will reduce the size of your zip_distances table by half.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24196409
One other thing, if you know that the user will never choose a Mile Scope greater than some number, let's say 100 Miles, then, you could code that in your loop that if lDistance > 100 skip the insert.  That would keep the size of your zip_distances table to a manageable level.
0
 

Author Comment

by:Taylor814
ID: 24197993
TextReport: - Thanks for your help, but I haven't the slightest clue how to implement this on a VB6 form.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24198083
Do you have Access? If so give it a go first in there, once you have thge table populated then it is just a case of adapting the function but get the access code to work first.
Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24198145
I do have access and have the ability to see what you wrote. Not exactly sure what I'm supposed to do once I get in there.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24198266
Have you tested the function in the immediate window in access?

?ListZipsWithinXMiles(99501,10)

It is this function that needs to be created in VB6, not sure if you can use DAO though. Once you have a working function then you need to determine where you want to run it or perhaps adapt it to populate a listbox or combobox.

Cheers, Andrew
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24198414
Hi Taylor,

In your initial post, you indicated that you knew how to read zipcode data from your database and populate a Flexgrid, so, most of the comments I've posted, assume at least that level of knowledge of VB/Access DB coding.

Since there are many different libraries that are commonly used to read/write data to an Access database from VB, I have no clue which one you are using (DAO, ADO, ODBC etc), so, giving you example code using the library of my choice wouldn't do you much good.

If you can post a sample routine where you connect to your database and retrieve zipcode data for a flexgrid, then I could mock up a sample for you using the same sorts of objects and libraries.

As a person asking for the "expert's" advice, you should value the time the unpaid expert dedicates to helping you solve your problem, and provide them with as much of your existing code as possible, to save them time and encourage their participation. You're expected to do the grunt work.  

This site is dedicated to helping you overcome your technical hurdles, not provide you with programming services.  There are other sites such as RentACoder.com where you can hire a programmer if that's what you want.

I'm not trying to be snotty, just trying to clue you in so that you get better participation on your questions.

Cheers!
Mike
0
 

Author Comment

by:Taylor814
ID: 24206826
Mike - I'm using ADO. While I do appreciate you helping and doing it without pay, no one is MAKING you be here. If you think the work is too much, you're free to help in other threads.
0
 

Author Comment

by:Taylor814
ID: 24206895
Mike - Also, I disagree with your statement in that I should have included much more information in my original post. In fact, you are at fault for not asking the correct questions from me. It would ridiculous to think that, as a "non expert", I'm to know the questions to ask that would help an "expert" derive at a conclusion. For example, say your knee hurts and you go to the doctor. The doctor is supposed to ask you questions to derive that you have a torn meniscus. You're not expected to tell the doctor you believe you have a bucket handle tear. "Non experts" don't know what a bucket handle tear is. You're also not supposed to propose to the physician that you believe a uni-compartmental knee replacement would have a better outcome than a total knee replacement. You see where I'm going with this?
0
 

Author Comment

by:Taylor814
ID: 24206939
Text - Says "macros are disabled", so I'm currently trying to figure out how to enable them.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 24207498
Taylor, I was just trying to help you get more and better responses from the people who are trying to help you.

You're welcome to disagree with that, and if so, I wish you the best.

Cheers!
Mike
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24208201
What version of access are you using? You may need to go into the database properties and there may be a setting in there to say that you trust it.
Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24208341
access 2000 file format
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24222033
But what version of Access?
Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24233259
Andrew - I got the code to run, however, when running in Access 2007, I don't see where to input the search parameters. How am I to get this to run in my VB6 program?
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24233595
You need to create the ListZipsWithinXMiles within VB6, you will probably need to change the code so that is can retrieve the data from access as it is currently written to use DAO with CurrentDB and probably wont run straight in VB6, unfortunately I do not have VB6 to walk you through this.
Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24245124
Text - I imported my full zipcodes list into your zip.mdb file and tried to run the query to get all the results. It seems to hang up and not produce the results.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24245369
If you use the 42903zip codes the query will have to create 920,333,705 records so it is going to take a long long time. Try and break it into smaller chunks or just leave it.

Have you got your VB6 code to work?
Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24245694
I don't think storing 920 million records is a good idea. How do I implement something such as:
http://www.simplyzipcodes.com/zipcodesbydistance.php

Is it really this difficult?
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24245882
You already have the code that will do the calculation when you provide teh Longitude and Latitude, this could be ran in a query that you enter the 2 zips and cacluate on the fly or in VB you could lookup the long and lat for the zips and return the value. The function in the file I uploaded is taken from the link I provided in the very first posting and is called DISTANCE

The reason you wish to store all the possibles is that depending on how many time you calculate the distance between 2 zip codes it may become mnore efficient to look up the answer rather than have to calculate everytime. You did say you wanted to know all zips withing X miles fromt eh original rather than what is the distance between X and Y.

Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24246068
"You did say you wanted to know all zips withing X miles fromt eh original rather than what is the distance between X and Y."

Correct. The link I gave you does this.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24246131
So you have 2 choices, calculate every possible combination on the fly or store them in a database, I suspect the website does the latter. It may be worth the $10 to by the database and see if it is already filled in for you as it also gives you "Radius Formulas and Examples!"

Please note the website appears to limit the search to 100 miles.

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24246206
It looks as though you can restrict the search through the use of the LONG and LAT but it may still require calculating on the fly some of the values. Check out http://reviewapp.com/blog/2006/05/07/zip-code-radius-calculation-algorithm/
Cheers, Andrew
0
 

Author Comment

by:Taylor814
ID: 24251460
Text - I ran your query on the zipcodes table, but got an error stating that it would be larger than 2Gig and access won't allow it. I've found another zipcode mdb file that has an SQL query in it. How can I translate this SQL query to VB6?

To see the query, right click on it, choose Design View, then make sure you're viewing the SQL
ZipCodes.mdb
0
 
LVL 28

Accepted Solution

by:
TextReport earned 2000 total points
ID: 24272949
You should be able to use the SQL from the query in your VB application to query your access table. I have broken it up to variables that you can then build up the SQL string. You can set the strZIP and lng Distance from your user input screen and wrap this is the code you know how to run the query and populate the flex grid.
Cheers, Andrew
strZip = "98277"
lngDistance = 50
strCalculation = "((1.852*60*(((Atn(-(sin(zc1.RLat)*sin(zc2.RLat)+cos(zc1.RLat)*cos(zc2.RLat)*cos(abs((zc2.RLong)-(zc1.RLong))))/Sqr(-(sin(zc1.RLat)*sin(zc2.RLat)+cos(zc1.RLat)*cos(zc2.RLat)*cos(abs((zc2.RLong)-(zc1.RLong))))*(sin(zc1.RLat)*sin(zc2.RLat)+cos(zc1.RLat)*cos(zc2.RLat)*cos(abs((zc2.RLong)-(zc1.RLong))))+1))+2*Atn(1))/3.14159265358979)*180))/1.609344)"
 
strSQL = "SELECT DISTINCT zc2.ZIPCODE" & vbCrLf & _
         "     , " & strCalculation & " AS Distance" & vbCrLf & _
         "FROM ZipCodes AS zc1, ZipCodes AS zc2" & vbCrLf & _
         "WHERE zc1.ZIPCODE=" & Chr(39) & strZip & Chr(39) & vbCrLf & _
         "AND   zc2.ZIPCODE<>" & Chr(39) & strZip & Chr(39) & vbCrLf & _
         "AND   " & strCalculation  & "<=" & lngDistance & vbCrLf

Open in new window

0
 

Author Comment

by:Taylor814
ID: 24392783
Although I have it working and populating zipcodes in a listbox, the distances from the starting zip are not the correct mile range.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

839 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