Language Filter:     Display content for

DISTANCE WIZARD API

Click for larger view
Click to see a larger representation
of Distance and Boundary Wizards.

(This is the same documentation which ships with the product.)

ZIP Code Download's Distance Wizard provides functionality for calculating the distance between two points on Earth's surface. Combined with a database of ZIP/postal code coordinates, Distance Wizard can help perform many practical tasks, such as locating nearby storefronts or presenting geo-targeted ads to visitors.

Standard Edition is faster than competitors' products and easy to use. Professional Edition boasts ultra-high performance and is bundled with smart boundary technology to greatly enhance the speed of your database queries.

For example, to find all the postal codes in North America (US, Canada, Mexico) within 10 miles of 58271 (Pembina, North Dakota, USA, located a few miles from the Canadian border), your application would query over a million rows of data and process several calculations for each row to find maybe a dozen postal codes that are within 10 miles.

The Professional Edition calculates smart boundaries and exponentially reduces the number of rows involved from millions to just dozens, making it dramatically faster. View the complete examples to see how it works with some sample data.

These tools comprise a small code library of two main parts:

  1. Distance Wizard - to calculate the distance between two coordinates.
  2. Boundary Wizard - to find geolocational boundaries of a certain radius from a coordinate of origin. (Professional Edition only)

The ZIP Code Download Distance Wizard can be utilized in 16 different languages and platforms including:

  • ASP
  • C
  • C#
  • C++
  • ColdFusion
  • Java
  • MS SQL 2000 (or later)
  • MySQL 5 (or later)
  • Oracle 9 (or later)
  • Perl
  • PHP
  • Python
  • Ruby
  • VB.NET
  • Visual Basic 6
  • VBA (Microsoft Access and Microsoft Excel)

This documentation explains how to use the language of your choice to work with the API. It also includes usable sample source code.

There is a language filter at the top to display content for your specific platform.

Feature Matrix

Below is an overall comparison of the Standard and Professional editions of Distance Wizard.

Feature Comparison

Distance Wizard
Standard Edition 		Distance Wizard
Professional Edition
Calculates distance between two coordinates on Earth YES YES
Integrates with ZIP Code Download's ZIP Code Databases YES YES
Integrates with your own table of location data YES YES
Enables faster database queries with smart boundary technology NO YES
Ultra-high performance NO YES
Recommended for high-volume websites and applications NO YES
Execution time ZCD's Professional Package is exponentially faster

Below is a technical comparison, showing which API features are available (classes, functions, etc) in each edition.

Code Package Comparison

Distance Wizard
Standard Edition 		Distance Wizard
Professional Edition
DistanceWizard class
To calculate distance 		YES YES
BoundaryWizard class
For fast database queries 		NO YES
Boundary class
To improve performance 		NO YES
Coordinate class
To represent location 		YES YES
Measurement enum
For miles/kilometers 		YES YES

Standard Edition Library

NOTICE: Standard Edition lacks the speed-enhancing smart boundaries. Running database queries to determine distances will require tens of thousands of queries and calculations. However, with the Professional Edition, only a few dozen queries are performed. For large-scale applications, consider upgrading to Distance Wizard Professional.

Distance Wizard Standard is comprised of three classes/concepts:

  1. DistanceWizard - The distance calculator service class. Finds distance between two points.
  2. Measurement - A support class (or enum) to represent different units of measure.
  3. Coordinate - A convenient way to manage points of latitude and longitude.

Since this library deals with coordinates on Earth's surface, the wizard make calculations based on the surface of a sphere.

Professional Edition Library

In general, there are two main portions of this API: one that calculates four boundaries around an original coordinate given a radius, and one that calculates the distance between two coordinates.

These portions are represented by the following two classes:

  1. BoundaryWizard class
  2. DistanceWizard class

Once constructed, these objects have methods to perform their calculations.

BoundaryWizard exists to make quick and efficient database queries by setting certain limitations on what should be queried. DistanceWizard performs calculations between two geolocational points.

Since this library deals with coordinates on Earth's surface, the wizards make calculations based on the surface of a sphere.

Coordinate

Overview

The Coordinate class eases grouping of a set of latitude and longitude values.

Latitude and longitude can be thought of as x,y values:

(latitude, longitude)

Remarks

When set, a coordinate's latitude and longitude are assumed to be in decimal degrees. Also, latitude must be within ±90° and longitude must be within ±180°. Any value out of these bounds will throw an exception or cause the code to fail at execution.

There is no Coordinate class or concept in this API for database query languages. Individual latitude/longitude values are passed in as arguments instead of relying on Coordinate objects like in most programming languages. For examples, see DistanceWizard and BoundaryWizard pages.

Constructor

ASP/VBA/VB6 Construction of a Coordinate Dim myCoordinate: Set myCoordinate = New Coordinate
myCoordinate.Initialize 37.332418, -122.030039
Using the Coordinate struct in C Coordinate coord;
coord.Latitude = 37.332418;
coord.Longitude = -122.030039;
Initializing a Coordinate component in ColdFusion <cfobject name="coord" component="cfcs.Coordinate">
<cfset coord.init(37.332418, -122.030039)>
Constructor Syntax Coordinate.new(latitude, longitude)
Constructor Syntax Coordinate(latitude, longitude)
Constructor Syntax new Coordinate(double LatitudeDegrees, double LongitudeDegrees)

Properties

double Latitude

The latitude (x-value) of the coordinate.

double Longitude

The longitude (y-value) of the coordinate.

Accessor Methods

double Latitude()

Gets the latitude (x-value) of the coordinate.

Returns

The latitude value of the coordinate in degrees as a double.
double Longitude()

Gets the longitude (y-value) of the coordinate.

Returns

The longitude value of the coordinate in degrees as a double.

Methods

boolean Equals(Coordinate coordinate)

Compares this coordinate object with the argument, based on whether its latitude and longitude values are the same.

Parameters

Type Name Description
Coordinate coordinate The other coordinate to compare this coordinate with.

Returns

Boolean TRUE if the coordinates have the same latitude and longitude. Returns boolean FALSE otherwise.

Methods

int Equals(Coordinate coordinate)

Compares this coordinate object with the argument, based on whether its latitude and longitude values are the same.

Parameters

Type Name Description
Coordinate coordinate The other coordinate to compare this coordinate with.

Returns

1, as true, if the coordinates have the same latitude and longitude. Returns 0, as false, otherwise.

Methods

equals(other_coordinate)

Compares this coordinate object with the argument, based on whether its latitude and longitude values are the same.

Parameters

Type Name Description
Coordinate other_coordinate The other coordinate to compare this coordinate with.

Returns

bool True if the coordinates have the same latitude and longitude. Returns bool False otherwise.

Accessor Methods

float latitude

The latitude (x-value) of the coordinate.

float longitude

The longitude (y-value) of the coordinate.

Methods

boolean EqualTo(Coordinate coordinate)

Compares this coordinate object with the argument, based on whether its latitude and longitude values are the same.

Parameters

Type Name Description
Coordinate coordinate The other coordinate to compare this coordinate with.

Returns

Boolean TRUE if the coordinates have the same latitude and longitude. Returns boolean FALSE otherwise.

Methods

int Equals(Coordinate coordinate1, Coordinate coordinate2)

Compares this coordinate object with the argument, based on whether its latitude and longitude values are the same.

Parameters

Type Name Description
Coordinate coordinate1 The first coordinate to compare.
Coordinate coordinate2 The other coordinate to compare.

Returns

1 if the coordinates have the same latitude and longitude. Returns 0 otherwise.
void Initialize [latitude], [longitude] This is a Sub routine and only exists in ASP, VBA, and VB6. It merely assigns values to the object.

Sets the values of latitude and longitude for this coordinate to represent. Since constructors in ASP, VBA, and VB6 cannot accept arguments, this is the preferred alternative. This is a Sub and should not have parenthasees or assignment operators.

Parameters

Type Name Description
double latitude The latitude value to represent. Must be between -180 and +180.
double longitude The longitude value to represent. Must be between -180 and +180.

Returns

void
void init(numeric latitude, numeric longitude) This initialization function exists only in ColdFusion and should be called just once to set its values.

Sets the values of latitude and longitude for this coordinate to represent. Since constructors in ColdFusion do not accept parameters, this is the conventional alternative.

Parameters

Type Name Description
numeric latitude The latitude value to represent. Must be between -180 and +180.
numeric longitude The longitude value to represent. Must be between -180 and +180.

Returns

void

Remarks

When set, a coordinate's latitude and longitude are assumed to be in decimal degrees. Also, latitude must be within ±90° and longitude must be within ±180°. Any value out of these bounds will throw an exception or cause the code to fail at execution.

Measurement

Overview

The Measurement enumeration is used to represent two different units of measure:

  1. Miles
  2. Kilometers

Usage Sample

ASP Dim Measure: Set Measure = New Measurement
Dim unit1: unit1 = Measure.Miles
Dim unit2: unit2 = Measure.Kilometers

Remarks

ASP does not support true enumerations, so a class with public read-only properties is used instead.
You will need to include the Measurement.asp file to use the Measurement class.
C Measurement unit1 = Miles;
Measurement unit2 = Kilometers;

Remarks

You will need to include the Measurement.h file to use the Measurement enum. If you include BoundaryWizard.h or DistanceWizard.h into your code, then it has already been included for you.
C# var unit1 = Measurement.Miles;
var unit2 = Measurement.Kilometers;
C++ Measurement unit1 = Measurement::Miles;
Measurement unit2 = Measurement::Kilometers;

Remarks

You will need to make sure Measurement.h is included. If you include BoundaryWizard.h or DistanceWizard.h into your code, then it has already been included for you.
ColdFusion <cfobject name="measure" component="cfcs.Measurement">
<cfset unit1 = measure.Miles>
<cfset unit2 = measure.Kilometers>
Java Measurement unit1 = Measurement.Miles;
Measurement unit2 = Measurement.Kilometers;

Remarks

You'll have to import the Measurement.java file. However, generally, a simple import wizards.*; line will suffice to import all the packaged classes.
MS SQL Server DECLARE @unit1 varchar(12), @unit2 varchar(12);
SELECT @unit1 = 'Miles';
SELECT @unit2 = 'Kilometers';

Remarks

If you're just passing a unit of measure as an argument statically, it's more efficient to just use the string literals 'Miles' and 'Kilometers'.
MySQL 5.0+ SET @unit1 = 'Miles';
SET @unit2 = 'Kilometers';

Remarks

If you're just passing a unit of measure as an argument statically, it's more efficient to just use the string literals 'Miles' and 'Kilometers' instead of setting variables like this. This is just an example on how use the Measurement constants.
Oracle 9+ DECLARE unit1 VARCHAR2(12) := 'Miles';
        unit2 VARCHAR2(12) := 'Kilometers';
BEGIN
   NULL;
END;
/

Remarks

If you're just passing a unit of measure as an argument statically, it's more efficient to just use the string literals 'Miles' and 'Kilometers' instead of setting variables like this. This is just an example on how use the Measurement constants.
Perl my $measure = new Measurement();
my $unit1 = $measure->{Miles};
my $unit2 = $measure->{Kilometers};

Remarks

Be sure to have a "use Measurement;" line somewhere at the beginning of your file or Perl will complain that it can't find the Measurement package.
Ruby unit_of_measure1 = Measurement::Miles
unit_of_measure2 = Measurement::Kilometers

Remarks

Ruby does support true enumerations, so a class with static fields is used instead. Make sure you 'require' the Measurement module to use it.
Python unit_of_measure1 = Measurement.MILES
unit_of_measure2 = Measurement.KILOMETERS

Remarks

Python does support true enumerations, so a class with static fields is used instead. Make sure you import the Measurement class from the measurement module in order to use it.
PHP $unit1 = Measurement::MILES;
$unit2 = Measurement::KILOMETERS;

Remarks

PHP does not support true enumerations, so a class with static fields is used instead. You'll probably want to use require_once() to be able to use the Measurement class.
VB.NET Dim unit1 As Measurement = Measurement.Miles;
Dim unit2 As Measurement = Measurement.Kilometers;
Visual Basic 6 Dim unit1 As Measurement: unit1 = Measurement.Miles
Dim unit2 As Measurement: unit2 = Measurement.Kilometers
VBA Dim unit1 As Measurement: unit1 = Measurement.Miles
Dim unit2 As Measurement: unit2 = Measurement.Kilometers

Remarks

Ensure that the Measure.cls file is listed as part of your macro.

Boundary

Overview

The Boundary class is a very simple value class which holds four geolocational lines: two lines of latitude, and two lines of longitude. They are figured by a BoundaryWizard object.

A Boundary looks like the representation below.

RadiusAssistant Visualization

Properties

double North

Gets the northern-most boundary line (a line of latitude).

double South

Gets the southern-most boundary line (a line of latitude).

double East

Gets the eastern-most boundary line (a line of longitude).

double West

Gets the western-most boundary line (a line of longitude).

Properties

north

Gets the northern-most boundary line (a line of latitude).

south

Gets the southern-most boundary line (a line of latitude).

east

Gets the eastern-most boundary line (a line of longitude).

west

Gets the western-most boundary line (a line of longitude).

Accessor Methods

double North()

Gets the northern-most boundary line (a line of latitude).

Returns

The northern-most boundary line.
double South()

Gets the southern-most boundary line (a line of latitude).

Returns

The southern-most boundary line.
double East()

Gets the eastern-most boundary line (a line of longitude).

Returns

The eastern-most boundary line.
double West()

Gets the western-most boundary line (a line of longitude).

Returns

The western-most boundary line.
In MS SQL Server and MySQL, a table containing a boundary's values is returned from the CalculateBoundary function in the BoundaryWizard.sql file.

Remarks

All lines of latitude and longitude are expressed in decimal degrees.

You shouldn't ever need to create a Boundary. Use the BoundaryWizard's method CalculateBoundary() [ Ruby, Python: calculate_boundary() ] to obtain a Boundary.

BoundaryWizard

Overview

The BoundaryWizard class is used to find four geolocational lines around an origin given a certain radius.

RadiusAssistant Visualization

Constructor

General Constructor Syntax new BoundaryWizard

Constructor

Constructor BoundaryWizard.new()

Constructor

Constructor BoundaryWizard()

Functions

CalculateBoundary(@latitude, @longitude, @distance, @measure)

Finds the boundary around a coordinate or point of origin given a latitude, longitude, radius, and unit of measure.

Parameters

Type Name Description
decimal(18, 15) latitude The specific latitude value around which the boundary should be computed.
decimal(18, 15) longitude The specific longitude value around which the boundary should be computed.
decimal(18, 10) distance The distance, in the specified unit of measure, around which the boundary will be computed.
varchar(12) measure The unit of measure for distance provided using a Measurement value.

Returns

A table containing the four values of the boundary around the given origin: "north", "south", "east", "west". The north and south lines are the upper and lower lines of latitude in decimal degrees. The east and west lines are the left and right lines of longitude in decimal degrees.

Usage Example

Procedures

CalculateBoundary(latitude, longitude, distance, measure)

Finds the boundary around a coordinate or point of origin given a latitude, longitude, radius, and unit of measure.

Parameters

Type Name Description
decimal(18, 15) latitude The specific latitude value around which the boundary should be computed.
decimal(18, 15) longitude The specific longitude value around which the boundary should be computed.
decimal(18, 10) distance The distance, in the specified unit of measure, around which the boundary will be computed.
varchar(12) measure The unit of measure for distance provided using a Measurement value.

Returns

A table containing the four values of the boundary around the given origin: "north", "south", "east", "west". The north and south lines are the upper and lower lines of latitude in decimal degrees. The east and west lines are the left and right lines of longitude in decimal degrees.

Usage Example

Procedures

CalculateBoundary(latitude, longitude, distance, measure)

Finds the boundary around a coordinate or point of origin given a latitude, longitude, radius, and unit of measure.

Parameters

Type Name Description
NUMBER latitude The specific latitude value around which the boundary should be computed.
NUMBER longitude The specific longitude value around which the boundary should be computed.
NUMBER distance The distance, in the specified unit of measure, around which the boundary will be computed.
VARCHAR2(12) measure The unit of measure for distance provided using a Measurement value.

Returns

A Boundary type object that holds the four geolocational boundaries. See the documentation page for Boundary to view its member functions.

Usage Example

Methods / Functions

calculate_boundary(origin_coordinate, distance, unit_of_measure)

Finds the boundary around a coordinate or point of origin.

Parameters

Type Name Description
Coordinate origin_coordinate The specific coordinate or location around which the boundary should be computed.
float distance The distance, in the specified unit of measure, around which the boundary will be computed.
Measurement unit_of_measure The unit of measure for distance provided using a Measurement value.

Returns

The boundary around the origin coordinate.

Usage Example

Methods / Functions

Boundary CalculateBoundary(Coordinate origin, double distance, int measure)

Finds the boundary around a coordinate or point of origin.

Parameters

Type Name Description
Coordinate origin The specific coordinate or location around which the boundary should be computed.
double distance The distance, in the specified unit of measure, around which the boundary will be computed.
Measurement measure The unit of measure for distance provided using a Measurement value.

Returns

The boundary around the origin coordinate.

Usage Example

ASP Basic Usage <!--#include file="UnitConverter.asp" -->
<!--#include file="Boundary.asp" -->
<!--#include file="Measurement.asp" -->
<!--#include file="Coordinate.asp" -->

<!--#include file="BoundaryWizard.asp" -->
<%
' Set up our coordinates
Dim originCoord: Set originCoord = New Coordinate
originCoord.Initialize 37.332418, -122.030039

' Sample radius (boundary) calculation
Dim boundCalc: Set boundCalc = New BoundaryWizard
Dim Measure: Set Measure = New Measurement

Dim radius
Set radius = boundCalc.CalculateBoundary(originCoord, 25, Measure.Miles)

Response.Write("Northern boundary: " & radius.North & "<br />")
Response.Write("Southern boundary: " & radius.South & "<br />")
Response.Write("Eastern boundary: " & radius.East & "<br />")
Response.Write("Western boundary: " & radius.West & "<br />")
%>
C Basic Usage #include <stdlib.h>
#include "BoundaryWizard.h"

int main()
{
   // Set up; simulate data entry.
   Coordinate originCoord;
   originCoord.Latitude = 37.332418;
   originCoord.Longitude = -122.030039;
   
   // SAMPLE USAGE OF BOUNDARY CALCULATOR
   Boundary radius = CalculateBoundary(originCoord, 25, Miles);

   printf("Northern boundary: %f\n", radius.North);
   printf("Southern boundary: %f\n", radius.South);
   printf("Eastern boundary: %f\n", radius.East);
   printf("Western boundary: %f\n\n", radius.West);

   getch();
   return 0;
}
C# Basic Usage using System;
using ZipCodeDownload.Wizards;

public sealed class Program
{
   private static void Main(string[] args)
   {
      BoundaryWizard boundCalc = new BoundaryWizard();

      // Radius calculation
      Boundary radius = boundCalc.CalculateBoundary(new Coordinate(37.332418, -122.030039),
         25.0,
         Measurement.Miles);

      Console.WriteLine("Northern boundary: " + radius.North);
      Console.WriteLine("Southern boundary: " + radius.South);
      Console.WriteLine("Eastern boundary: " + radius.East);
      Console.WriteLine("Western boundary: " + radius.West);

      Console.ReadLine();
   }
}
C++ Basic Usage #include <iostream>
#include <stdlib.h>
#include "BoundaryWizard.h"

using namespace std;
using namespace ZipCodeDownload::Wizards;

int main()
{
   // Set up
   Coordinate originCoord = Coordinate(37.332418, -122.030039);
   
   // Actual usage of class
   BoundaryWizard boundCalc = BoundaryWizard();
   Boundary radius = boundCalc.CalculateBoundary(originCoord, 25, Measurement::Miles);

   // Example output
   cout << "Northern boundary: " << radius.North() << endl;
   cout << "Southern boundary: " << radius.South() << endl;
   cout << "Eastern boundary: " << radius.East() << endl;
   cout << "Western boundary: " << radius.West() << endl << endl;
   
   system("PAUSE");

   return 0;
}
ColdFusion Basic Usage <!-- Simulate data entry; establish our coordinate origin. -->
<cfobject name="coord" component="cfcs.Coordinate">
<cfset coord.init(37.332418, -122.030039)>

<!-- Create our service object. -->
<cfobject component="cfcs.BoundaryWizard" name="boundcalc">

<!-- Calculate the boundary. -->
<cfobject name="measure" component="cfcs.Measurement">
<cfset bounds = boundcalc.CalculateBoundary(coord, 25.0, measure.Miles)>

<cfoutput>
   Northern boundary: #bounds.North()#<br />
   Southern boundary: #bounds.South()#<br />
   Eastern boundary: #bounds.East()#<br />
   Western boundary: #bounds.West()#
</cfoutput>
Java Basic Usage import wizards.*;

public class Sample {
   public static void main(String[] args)
   {
      BoundaryWizard boundCalc = new BoundaryWizard();
     
      // Set up sample coordinate
      Coordinate originCoord = new Coordinate(37.332418, -122.030039);
     
      // Sample radius calculation
      Boundary radius = boundCalc.CalculateBoundary(originCoord, 25.0, Measurement.Miles);
     
      System.out.println("Northern boundary: " + radius.North());
      System.out.println("Southern boundary: " + radius.South());
      System.out.println("Eastern boundary: " + radius.East());
      System.out.println("Western boundary: " + radius.West());
    }
}
Basic Usage for MS SQL SELECT * FROM dbo.CalculateBoundary(37.332418, -122.030039, 25, 'Miles');
Basic Usage for MySQL 5.0+ CALL CalculateBoundary(37.332418, -122.030039, 25, 'Miles');
Oracle 9+ DECLARE bound Boundary;
BEGIN
    bound := CalculateBoundary(37.332418, -122.030039, 25.0, 'Miles');
    dbms_output.put_line('Northern boundary: ' || bound.North());
    dbms_output.put_line('Southern boundary: ' || bound.South());
    dbms_output.put_line('Eastern boundary: ' || bound.East());
    dbms_output.put_line('Western boundary: ' || bound.West());
END;
/
 
Perl Basic Usage #!/usr/bin/perl
package main;

use Coordinate;
use Measurement;
use BoundaryWizard;

# Simulate data entry by setting up coordinate
my $coord = new Coordinate(37.332418, -122.030039);

# Represent our units of measure
my $measure = new Measurement();

# Create the Boundary Wizard
my $boundWizard = new BoundaryWizard();

# Calculate the actual boundary
my $boundary = $boundWizard->CalculateBoundary($coord, 25.0, $measure->{Miles});

print "Northern boundary: ".$boundary->North()."\n";
print "Southern boundary: ".$boundary->South()."\n";
print "Eastern boundary: ".$boundary->East()."\n";
print "Western boundary: ".$boundary->West();

exit;
Ruby Basic Usage #!/usr/bin/env ruby

require "Coordinate"
require "Measurement"
require "BoundaryWizard"

def boundary_wizard_example()
 
  boundary_scope = 25.0
  coord = Coordinate.new(37.332418, -122.030039)
  unit_of_measure = Measurement::MILES

  bound_wizard = BoundaryWizard.new()
 
  boundary = bound_wizard.calculate_boundary(coord, boundary_scope, unit_of_measure)
 
  puts "Northern boundary: %f" % boundary::north
  puts "Southern boundary: %f" % boundary::south
  puts "Eastern boundary: %f" % boundary::east
  puts "Western boundary: %f" % boundary::west
 
end

boundary_wizard_example()
Python Basic Usage #!usr/bin/env python

from coordinate import Coordinate
from measurement import Measurement
from boundary_wizard import BoundaryWizard

def boundary_wizard_example():
   
   
    coord = Coordinate(37.332418, -122.030039)
    unit_of_measure = Measurement.MILES
    boundary_scope = 25.0
   
    bound_wizard = BoundaryWizard()
   
    boundary = bound_wizard.calculate_boundary(coord, boundary_scope, unit_of_measure)
   
    print "Northen boundary:", boundary.north
    print "Southern boundary:", boundary.south
    print "Eastern boundary:", boundary.east
    print "Western boundary:", boundary.west

if __name__ == "__main__":
    boundary_wizard_example()
PHP Basic Usage <?php
require_once("BoundaryWizard.php");

$boundCalc = new BoundaryWizard();

// Simulate data entry
$latitude = 37.332418;
$longitude = -122.030039;
// Desired radius (in miles)
$radius = 25.0;

$coord = new Coordinate($latitude, $longitude);

$boundary = $boundCalc->CalculateBoundary($coord, $radius, Measurement::MILES);

echo "Northern boundary: " . $boundary->North() . "<br />";
echo "Southern boundary: " . $boundary->South() . "<br />";
echo "Eastern boundary: " . $boundary->East() . "<br />";
echo "Western boundary: " . $boundary->West();
?>
VB.NET Basic Usage Imports System
Imports ZipCodeDownload.Wizards

Public NotInheritable Class Program
   Public Shared Sub Main()

      Dim boundCalc As New BoundaryWizard()

      ' Sample radius calculation
      Dim radius As Boundary = boundCalc.CalculateBoundary( _
         New Coordinate(37.332418, -122.030039), _
         25, _
         Measurement.Miles)

      ' Sample usage of results
      Console.WriteLine("Northern boundary: " & radius.North)
      Console.WriteLine("Southern boundary: " & radius.South)
      Console.WriteLine("Eastern boundary: " & radius.East)
      Console.WriteLine("Western boundary: " & radius.West)

      Console.ReadLine()

   End Sub
End Class
Usage in Visual Basic 6 Sub Sample()
    ' Set up our coordinate
    Dim originCoord As Coordinate
    Set originCoord = New Coordinate
    originCoord.Initialize 37.332418, -122.030039
   
    ' Sample radius (boundary) calculation
    Dim boundCalc: Set boundCalc = New BoundaryWizard
   
    Dim radius: Set radius = boundCalc.CalculateBoundary(originCoord, 25, Measurement.Miles)
   
    Debug.Print "Northern boundary: " & radius.north
    Debug.Print "Southern boundary: " & radius.south
    Debug.Print "Eastern boundary: " & radius.east
    Debug.Print "Western boundary: " & radius.west
End Sub
VBA Basic Usage Sub Program()
    ' Set up our coordinate
    Dim originCoord As Coordinate
    Set originCoord = New Coordinate
    originCoord.Initialize 37.332418, -122.030039
   
    ' Sample radius (boundary) calculation
    Dim boundCalc: Set boundCalc = New BoundaryWizard
   
    Dim radius: Set radius = boundCalc.CalculateBoundary(originCoord, 25, Measurement.Miles)
   
    Debug.Print "Northern boundary: " & radius.north
    Debug.Print "Southern boundary: " & radius.south
    Debug.Print "Eastern boundary: " & radius.east
    Debug.Print "Western boundary: " & radius.west
End Sub

Output

The example above should output these numeric values (exact formatting and precision varies per platform):

OUTPUT Northern boundary: 37.6938359147632
Southern boundary: 36.9710000852368
Eastern boundary: -121.575490873859
Western boundary: -122.484587126141

Remarks

A Boundary object is returned from the CalculateBoundary (Ruby, Python: calculate_boundary) method. It contains two lines of latitude and two lines of longitude. See the documentation for the Boundary class for more information.

DistanceWizard

Overview

The DistanceWizard performs a calculation to determine the distance in a desired unit of measure between two given Coordinates on a sphere.

RadiusAssistant Visualization

Constructor

Constructor Syntax new DistanceWizard

Constructor

Constructor Syntax DistanceWizard.new()

Constructor

Constructor Syntax DistanceWizard()

Functions

CalculateDistance(@latitude1, @longitude1, @latitude2, @longitude2, @measure)

Calculates the distance between two geolocational points given those points and a unit of measure.

Parameters

Type Name Description
decimal(18, 15) latitude1 The latitude value of the first/origin coordinate.
decimal(18, 15) longitude1 The longitude value of the first/origin coordinate.
decimal(18, 15) latitude2 The latitude value of the second/relative coordinate.
decimal(18, 15) longitude2 The longitude value of the second/relative coordinate.
varchar(12) measure The unit of measure for distance provided using a Measurement value.

Returns

A distance between the two coordinates in decimal degrees.

Usage Example

Functions

CalculateDistance(latitude1, longitude1, latitude2, longitude2, measure)

Calculates the distance between two geolocational points given those points and a unit of measure.

Parameters

Type Name Description
decimal(18, 15) latitude1 The latitude value of the first/origin coordinate.
decimal(18, 15) longitude1 The longitude value of the first/origin coordinate.
decimal(18, 15) latitude2 The latitude value of the second/relative coordinate.
decimal(18, 15) longitude2 The longitude value of the second/relative coordinate.
varchar(12) measure The unit of measure for distance provided using a Measurement value.

Returns

A distance between the two coordinates in decimal degrees.

Usage Example

Functions

CalculateDistance(latitude1, longitude1, latitude2, longitude2, measure)

Calculates the distance between two geolocational points given those points and a unit of measure.

Parameters

Type Name Description
NUMBER latitude1 The latitude value of the first/origin coordinate.
NUMBER longitude1 The longitude value of the first/origin coordinate.
NUMBER latitude2 The latitude value of the second/relative coordinate.
NUMBER longitude2 The longitude value of the second/relative coordinate.
VARCHAR2(12) measure The unit of measure for distance provided using a Measurement value.

Returns

A distance between the two coordinates in decimal degrees as a NUMBER.

Usage Example

Methods / Functions

calculate_distance(origin_coordinate, relative_coordinate, unit_of_measure)

Finds the distance on a sphere from the Coordinate origin_coordinate to the Coordinate relative_coordinate.

Parameters

Type Name Description
Coordinate origin_coordinate The first, or starting, coordinate from which to compute distance.
Coordinate relative_coordinate The second, or ending, coordinate to which to compute distance.
Measurement unit_of_measure The unit of measure for distance using a Measurement value.

Returns

The distance between the two coordinates on a sphere as a double (a numeric value).

Usage Example

Methods / Functions

double CalculateDistance(Coordinate origin, Coordinate relative, int measure)

Finds the distance on a sphere from the Coordinate origin to the Coordinate relative.

Parameters

Type Name Description
Coordinate origin The first, or starting, coordinate from which to compute distance.
Coordinate relative The second, or ending, coordinate to which to compute distance.
int measure The unit of measure for distance using a Measurement value.

Returns

The distance between the two coordinates on a sphere as a double (a numeric value).

Usage Example

ASP Basic Usage <!--#include file="UnitConverter.asp" -->
<!--#include file="Measurement.asp" -->
<!--#include file="Coordinate.asp" -->

<!--#include file="DistanceWizard.asp" -->
<%
' Set up our coordinates
Dim originCoord, relativeCoord
Set originCoord = New Coordinate
originCoord.Initialize 37.332418, -122.030039
Set relativeCoord = New Coordinate
relativeCoord.Initialize 30.41922, -97.656097

' Used to represent units of measure
Dim Measure: Set Measure = New Measurement

' Sample distance calculation
Dim distCalc: Set distCalc = New DistanceWizard

Dim distance
distance = distCalc.CalculateDistance(originCoord, relativeCoord, Measure.Miles)

Response.Write("<br /><br />Distance: " & distance)
%>
C Basic Usage #include <stdlib.h>
#include "DistanceWizard.h"

int main()
{
   // Set up; simulate data entry.
   Coordinate originCoord, relativeCoord;
   originCoord.Latitude = 37.332418;
   originCoord.Longitude = -122.030039;
   relativeCoord.Latitude = 30.41922;
   relativeCoord.Longitude = -97.656097;
   
   // SAMPLE USAGE OF DISTANCE CALCULATOR
   double distance = CalculateDistance(originCoord, relativeCoord, Miles);

   printf("Distance: %f", distance);

   getch();
   return 0;
}
C# Basic Usage using System;
using ZipCodeDownload.Wizards;

public sealed class Program
{
    private static void Main(string[] args)
    {      
        // Sample distance calculation
        DistanceWizard distCalc = new DistanceWizard();

        double distance = distCalc.CalculateDistance(
        new Coordinate(37.332418, -122.030039),
        new Coordinate(30.41922, -97.656097),
        Measurement.Miles);

        Console.WriteLine("Distance: " + distance);
    }
}
C++ Basic Usage #include <iostream>
#include <stdlib.h>
#include "DistanceWizard.h"

using namespace std;
using namespace ZipCodeDownload::Wizards;

int main()
{
   // Set up
   Coordinate originCoord = Coordinate(37.332418, -122.030039);
   Coordinate relativeCoord = Coordinate(30.41922, -97.656097);
   
   DistanceWizard distCalc = DistanceWizard();

   // Perform calculation
   double distance = distCalc.CalculateDistance(originCoord, relativeCoord, Measurement::Miles);

   cout << "Distance in Miles: " << distance << endl;

   system("PAUSE");
   return 0;
}
ColdFusion Basic Usage <!-- Set up our utility/service components. -->
<cfobject name="distcalc" component="cfcs.DistanceWizard">
<cfobject name="measure" component="cfcs.Measurement">

<!-- Create our coordinates. -->
<cfobject name="coord1" component="cfcs.Coordinate">
<cfobject name="coord2" component="cfcs.Coordinate">
<cfset coord1.init(37.332418, -122.030039)>
<cfset coord2.init(30.41922, -97.656097)>

<!-- Calculate the distance and output. -->
<cfoutput>
   Distance: #distcalc.CalculateDistance(coord1, coord2, measure.Miles)#
</cfoutput>
Java Basic Usage import wizards.*;

public class Sample {
   public static void main(String[] args)
   {     
      // Set up some sample coordinates
      Coordinate originCoord = new Coordinate(37.332418, -122.030039);
      Coordinate relativeCoord = new Coordinate(30.41922, -97.656097);
     
      // Sample distance calculation
      DistanceWizard distCalc = new DistanceWizard();
     
      double distance = distCalc.CalculateDistance(
          originCoord,
          relativeCoord,
          Measurement.Miles);
     
      System.out.println("Distance: " + distance);
    }
}
MS SQL Basic Usage PRINT dbo.CalculateDistance(37.332418, -122.030039, 30.41922, -97.656097, 'Miles');
MySQL 5.0+ Basic Usage SET @distance = CalculateDistance(37.332418, -122.030039, 30.41922, -97.656097, 'Miles');
SELECT @distance;
Oracle 9+ Basic Usage DECLARE distance NUMBER;
BEGIN
    distance := CalculateDistance(37.332418, -122.030039, 30.41922, -97.656097, 'Miles');
    dbms_output.put_line('Distance: ' || distance);
END;
/
Perl Basic Usage #!/usr/bin/perl
package main;

use Coordinate;
use Measurement;
use DistanceWizard;

# Simulate data entry by setting up coordinates
my $coord1 = new Coordinate(37.332418, -122.030039);
my $coord2 = new Coordinate(30.41922, -97.656097);

# Represent our units of measure
my $measure = new Measurement();

# SAMPLE DISTANCE CALCULATION
my $distCalc = new DistanceWizard();
my $distance = $distCalc->CalculateDistance($coord1, $coord2, $measure->{Miles});

print "Distance: ".$distance;

exit;
Ruby Basic Usage #!/usr/bin/env ruby

require "Coordinate"
require "Measurement"
require "DistanceWizard"

def distance_wizard_example()
 
  coord1 = Coordinate.new(37.332418, -122.030039)
  coord2 = Coordinate.new(30.41922, -97.656097)
 
  dist_calc = DistanceWizard.new()
 
  distance = dist_calc.calculate_distance(coord1, coord2, Measurement::MILES)
 
  puts "Distance: %f miles" % distance
 
end

distance_wizard_example()
PHP Basic Usage <?php
require_once("DistanceWizard.php");

// Simulate data entry
$latitude1 = 37.332418;
$longitude1 = -122.030039;
$latitude2 = 30.41922;
$longitude2 = -97.656097;

// Create coordinates
$originCoord = new Coordinate($latitude1, $longitude1);
$relativeCoord = new Coordinate($latitude2, $longitude2);

$distCalc = new DistanceWizard();

// Perform calculation
$dist = $distCalc->CalculateDistance($originCoord, $relativeCoord, Measurement::MILES);

// Use result in a way of your choosing
echo "- Distance: " . $dist;
?>
Python Basic Usage #!usr/bin/env python

from coordinate import Coordinate
from measurement import Measurement
from distance_wizard import DistanceWizard

def distance_wizard_example():

    coord1 = Coordinate(37.332418, -122.030039)
    coord2 = Coordinate(30.41922, -97.656097)

    dist_calc = DistanceWizard()

    distance = dist_calc.calculate_distance(coord1, coord2, Measurement.MILES)

    print "Distance:", distance

if __name__ == "__main__":
    distance_wizard_example()
VB.NET Basic Usage Imports System
Imports ZipCodeDownload.Wizards

Public NotInheritable Class Program
   Public Shared Sub Main()

      ' Construction
      Dim distCalc As New DistanceWizard()

      ' Perform calculation
      Dim distance As Double = distCalc.CalculateDistance( _
         New Coordinate(37.332418, -122.030039), _
         New Coordinate(30.41922, -97.656097), _
         Measurement.Miles)

      Console.WriteLine("Distance: " & distance)

      Console.ReadLine()
   End Sub
End Class
Usage in Visual Basic 6 Sub Sample()
    ' Set up our coordinates
    Dim originCoord As Coordinate, relativeCoord As Coordinate
    Set originCoord = New Coordinate
    originCoord.Initialize 37.332418, -122.030039
    Set relativeCoord = New Coordinate
    relativeCoord.Initialize 30.41922, -97.656097
   
    ' Sample distance calculation
    Dim distCalc As New DistanceWizard
   
    Dim distance As Double
    distance = distCalc.CalculateDistance(originCoord, relativeCoord, Measurement.Miles)
   
    Debug.Print "Distance: " & distance
End Sub
VBA Basic Usage Sub Program()
    ' Set up our coordinates
    Dim originCoord As Coordinate, relativeCoord As Coordinate
    Set originCoord = New Coordinate
    originCoord.Initialize 37.332418, -122.030039
    Set relativeCoord = New Coordinate
    relativeCoord.Initialize 30.41922, -97.656097
   
    ' Sample distance calculation
    Dim distCalc As New DistanceWizard
   
    Dim distance As Double
    distance = distCalc.CalculateDistance(originCoord, relativeCoord, Measurement.Miles)
   
    Debug.Print "Distance: " & distance
End Sub

Output

The example above should output the following numeric value (exact formatting and precision varies per platform):

OUTPUT Distance: 1474.15267074819

Remarks

It's important to remember that two coordinates of latitude and longitude are passed in along with a unit of measure, and that the distance returned is the distance between those two coordinates on a sphere in the specified unit of measure.

Complete Code Example - Professional Edition

These examples are fully functional with the following requirements:

  1. A database with ZIP or postal code coordinates, city names, etc.
  2. The minimum language version listed
  3. DistanceWizard Professional Edition

Choose a language to get complete, cut-and-paste sample code:

No example

We do not have an advanced example for this language. Please select a different one.

Complete examples are available in PHP 5+, C# (.NET 2.0+), Java, Ruby, Perl, Python, and Microsoft SQL Server.

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Copy+paste this script into a file and save it in the same directory as the PHP classes that came with your product.
  2. Create a table like the one shown above and fill in the sample data. (Or, configure your own table of data.)
  3. Fill out your MySQL database connection information in the script.
  4. Make sure you have a database with ZIP code information available, and customize the code and queries depending on your configuration.
  5. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

PHP Complete Example (Advanced Usage) <?php
/**
 * A practical example of retrieving some Wal-Mart store
 * locations nearest a given ZIP Code.
 *
 * We want to do it quickly and efficiently, without running
 * thousands of SELECT queries. BoundaryWizard helps
 * us limit these queries extensively.
 *
 * HOW TO USE: Modify the database connection info below and
 * remember to customize the SQL queries below to match your
 * database configuration.
 *
 * SECURITY WARNINGS: This script contains code that outputs
 * debugging information. Remove it if adapted to a production
 * project. Also, you'll want to verify data input to prevent
 * SQL injection.
 *
 * COMPATIBILITY: You'll need an associative database containing
 * at least: ZIP Codes and their coordinates. ZIP Code Download's
 * database schemas are already configured to work with this
 * script (Premium or Commercial Edition databases).
 * To get one, visit:
 * http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
 *
 * DOCUMENTATION: See the API reference. Also, view the
 * BoundaryWizard or DistanceWizard or Boundary class
 * help pages without specifying a language and you'll
 * see a visual representation of these functions.
 *  
 * @company ZIP Code Download, LLC - 2009
 */
 
// Bring in the two main classes we'll be using.
require_once("BoundaryWizard.php");
require_once("DistanceWizard.php");

// Simulate data entry. Normally, these two values
// will come from a user-submitted form.
$zip = "95014";               // our origin ZIP code (a string)
$radius = 25.0;                 // the boundary's radius
$unit = Measurement::MILES;   // our desired unit of measure
                              // "Measurement" is included through one of the wizard files
                              // we already included.

// Connect to our ZIP code database.
$dbhost = "localhost";     // domain name or IP address (usually 'localhost')
$dbuser = "";    // database user name
$dbpass = "";     // that user's password
$dbname = "";    // the database's actual name

// Attempt connection with those credentials.
$dbconn = @mysql_connect($dbhost, $dbuser, $dbpass)
    or die("<p><b>ERROR ></b> Unable to make connection to database server:</p><p>".mysql_error()."</p>");
if ($dbname != "" && !@mysql_select_db($dbname))
    die("<p><b>ERROR ></b> The specified database is unavailable or it does not exist.");

// Set up our query to see if the ZIP exists.
$query = "SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode='$zip' LIMIT 1";

// Run the query.
$result = @mysql_query($query);

if (!$result)
{
    // Explain SQL error.
    echo "<p><b>ERROR ></b> There was an error executing the database query to find ZIP coordinates:</p><p>".mysql_error()."</p>";
    exit;
}
elseif (@mysql_num_rows($result) == 0)
{
    // ZIP code was not found in database.
    echo "<p><b>ERROR ></b> 0 results returned; does the ZIP code exist in the database?";
    exit;
}
else
{
    // ZIP code exists, now find the nearby zip codes.
    $row = mysql_fetch_array($result);
    $latitude = $row['Latitude'];
    $longitude = $row['Longitude'];
   
    // Construct the BoundaryWizard object.
    // It will perform the calculation to get our boundary.
    $boundaryWizard = new BoundaryWizard();
   
    // Create the coordinate of the origin ZIP code.
    $originCoord = new Coordinate($latitude, $longitude);
   
    // Calculate.
    $boundary = $boundaryWizard->CalculateBoundary($originCoord, $radius, $unit);
   
    // Retrieve our bounds.
    $northern = $boundary->North();
    $southern = $boundary->South();
    $eastern = $boundary->East();
    $western = $boundary->West();
   
    /*
    Prepare to find some of the nearby stores.
    This example uses only a partial collection of real
    store location data.
   
     NOTE:
    Regarding the BETWEEN clauses, the lower (smaller) value should be
    first, and the value after the AND should be higher (larger).
    Consequently, this query only works for the western part
    of the northern hemisphere. For other parts of the world,
    you'll need to order these properly in your query!
   
    This query is the meat and potatoes. Be sure to customize it
    depending on your own database configuration!
    */
    $queryNearestZips = "SELECT stores.*,
       zips.Latitude,
       zips.Longitude
  FROM StoreLocations AS stores
 INNER JOIN ZIPCodes AS zips
    ON stores.ZipCode = zips.ZIPCode
 WHERE Latitude BETWEEN $southern AND $northern
   AND Longitude BETWEEN $western AND $eastern
   AND zips.CityType='D'
   AND Latitude != 0
   AND Longitude != 0";
   
    // Now do it!
    $resultNearestZips = @mysql_query($queryNearestZips);
   
    if (!$resultNearestZips)
    {
        // Explain the error...
        echo "<p><b>ERROR ></b> There was an error executing the database query to find nearby stores by ZIP:</p><p>".mysql_error()."</p>";
        exit;
    }
    else
    {
        // Now let's go through each ZIP code and find out how
        // far they are from the origin coordinate and display it.
        $distanceWizard = new DistanceWizard();
       
        // Make output pretty and organized.
        echo "<h2>Some Wal-Mart Locations Nearest to ".$zip."</h2>\n\n<table width=\"500\">\n";
       
        // Our array is declared here for scope. (Read about this below.)
        $dataset;
       
        // Loop through each result
        $i = 0;
        while ($rowZIP = mysql_fetch_array($resultNearestZips))
        {  
            // Get the relative ZIP code's coordinate.
            $relativeCoord = new Coordinate($rowZIP['Latitude'], $rowZIP['Longitude']);
                       
            // Calculate the distance.
            $distance = $distanceWizard->CalculateDistance(
                $originCoord,
                $relativeCoord,
                $unit
            );
           
            // This next "if" statement is necessary because of the box/circle model.
            // (See the documentation.) A boundary is a square but distances are calculated
            // using a radius (on a circle). This circle fits within that square and so
            // there are 'corners' that aren't quite within the desired distance.
            if ($distance <= $radius)
            {
                // We want to sort this by distance ASC, so the closest cities are displayed
                // first. Let's put the City & Zip & Distance values in an array for this example.
                // Normally you will use whichever values you need and in the order that
                // you need them.
                $dataset[$i]['City'] = $rowZIP['City'];
                $dataset[$i]['State'] = $rowZIP['State'];
                $dataset[$i]['ZipCode'] = $rowZIP['ZipCode'];
                $dataset[$i]['Address'] = $rowZIP['Address'];
                $dataset[$i]['Distance'] = $distance;
                $i ++;
            }
        }
       
        // Sorts a 2D array given a key from the 2nd dimension
        // of the array in ascending order.
        function sort_2d_array_asc(&$array, $innerkey)
        {
            $dim = array();
            foreach ($array as $innerarray)
                $dim[] = $innerarray[$innerkey];
            array_multisort($dim, $array);
        }
       
        // Perform sort.
        sort_2d_array_asc($dataset, "Distance");
       
        // Display results.
        foreach ($dataset as $resultset)
        {
            echo "<tr>\n\t<td>" . $resultset['Address'] . "</td>\n";
            echo "\t<td>" . $resultset['City'] . "</td>\n";
            echo "\t<td>" . $resultset['State'] . "</td>\n";
            echo "\t<td>" . $resultset['ZipCode'] . "</td>\n";
            echo "\t<td><b>" . round($resultset['Distance'], 2) . " miles</b></td>\n</tr>\n";
        }
       
        echo "</table>";
    }
}

mysql_close($dbconn);
?>

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Create a project and add the class files that came with your product.
  2. Add the "using ZipCodeDownloads.Wizards" line to the top of your program file.
  3. This example uses MySQL and the MySQL Connector/NET classes available from mysql.com. You'll need to install it and add a reference to it.
  4. Create a table like the one shown above and fill in the sample data. (Or, configure your own table of data.)
  5. Configure your connection string in the script.
  6. Make sure you have a database with ZIP code information available, and customize the code and queries depending on your configuration.
  7. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

C# Complete Example (Advanced Usage) using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

// Don't forget to add the class files to your project!
using ZipCodeDownload.Wizards;

namespace FullCSExample
{
    public sealed class Program
    {
        public static void Main(string[] args)
        {
            // Simulate data entry
            string zip = "95014";
            double radius = 25.0;
            Measurement unit = Measurement.Miles;

            using (IDbConnection connection = DbProviderFactories.GetFactory("MySql.Data.MySqlClient").CreateConnection())
            {
                connection.ConnectionString = "Server=localhost;Database=db_name;Uid=username;Pwd=password;";
                connection.Open();

                Coordinate originCoord = null;

                using (IDbCommand centroidCommand = connection.CreateCommand())
                {
                    // WARNING: To prevent SQL injection you must verify the value of the "zip" variable.
                    centroidCommand.CommandText = string.Format("SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode = '{0}' AND CityType = 'D'", zip);

                    using (IDataReader reader = centroidCommand.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.Read())
                        {
                            originCoord = new Coordinate((double)reader["Latitude"], (double)reader["Longitude"]);
                        }
                    }
                }

                if (originCoord == null)
                {
                    Console.WriteLine("ERROR > That ZIP Code cannot be found in the database.");
                    Console.Read();
                    return;
                }

                using (IDbCommand proximityCommand = connection.CreateCommand())
                {
                    BoundaryWizard boundCalc = new BoundaryWizard();
                    Boundary bounds = boundCalc.CalculateBoundary(originCoord, radius, unit);
                   
                    proximityCommand.CommandText = string.Format(
                        "SELECT stores.*, "
                            + "           zips.Latitude, "
                            + "           zips.Longitude "
                            + "      FROM StoreLocations AS stores "
                            + "     INNER JOIN ZIPCodes AS zips "
                            + "        ON stores.ZipCode = zips.ZIPCode "
                            + "     WHERE Latitude BETWEEN {0} AND {1}"
                            + "       AND Longitude BETWEEN {2} AND {3}"
                            + "       AND zips.CityType='D'"
                            + "       AND Latitude != 0"
                            + "       AND Longitude != 0",
                        bounds.South,
                        bounds.North,
                        bounds.West,
                        bounds.East);

                    using (IDataReader reader = proximityCommand.ExecuteReader())
                    {
                        DistanceWizard distCalc = new DistanceWizard();

                        List<object[]> stores = new List<object[]>();

                        while (reader.Read())
                        {
                            Coordinate relativeCoord = new Coordinate((double)reader["Latitude"], (double)reader["Longitude"]);
                            double distance = distCalc.CalculateDistance(originCoord, relativeCoord, unit);

                            if (distance > radius)
                            {
                                continue; // outside of radius, don't include in results.
                            }

                            object[] store = new object[]
                            {
                                distance,
                                (string)reader["Address"],
                                (string)reader["City"],
                                (string)reader["State"],
                                (string)reader["ZipCode"]
                            };

                            // Insert element into list according to distance.
                            for (int i = 0; i <= stores.Count; i++)
                            {
                                if (i == stores.Count)
                                {
                                    stores.Add(store);
                                    break;
                                }
                                else if (distance < (double)stores[i][0])
                                {
                                    stores.Insert(i, store);
                                    break;
                                }
                            }
                        }

                        // Output results.
                        Console.WriteLine("Some Wal-Mart Locations Nearest to " + zip + "\n");

                        foreach (object[] currentStore in stores)
                        {
                            Console.Write(currentStore[1] + "\n");
                            Console.Write(currentStore[2] + ", ");
                            Console.Write(currentStore[3] + " ");
                            Console.Write(currentStore[4] + "\n");
                            Console.Write("Distance: " + Math.Round((double)currentStore[0], 2) + " miles\n\n");
                        }
                    }
                }
            }
            Console.Read();
        }
    }
}

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Create a Java project and add the "wizards" package to it.
  2. Add the "import wizards.*;" line to the top of your program file.
  3. This example uses MySQL and the MySQL Connector/J classes available from mysql.com. You'll need to install it and add a reference to it. However, you can use any JDBC driver for your own database platform.
  4. Create a table like the one shown above and fill in the sample data. (Or, configure your own table of data.)
  5. Configure your connection string in the script.
  6. Make sure you have a database with ZIP code information available, and customize the code and queries depending on your configuration.
  7. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

Java Complete Example (Advanced Usage) /**
 * A practical example of retrieving store locations
 * nearest a given ZIP Code. (some sample Wal-Marts in
 * this particular example)
 *
 * We want to do it quickly and efficiently, without running
 * thousands of SELECT queries. BoundaryWizard helps
 * us limit these queries extensively.
 *
 * HOW TO USE: Modify the database connection info below and
 * remember to customize the SQL queries below to match your
 * database server and configuration. This example uses
 * MySQL. Install the proper JDBC drivers for your server
 * platform in order to use it.
 *
 * COMPATIBILITY: You'll need an associative database containing
 * at least: ZIP Codes and their coordinates. ZIP Code Download's
 * database schemas are already configured to work with this
 * script (Premium or Commercial Edition databases).
 * To get one, visit:
 * http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
 *
 * DOCUMENTATION: See the API reference. Also, view the
 * BoundaryWizard or DistanceWizard or Boundary class
 * help pages without specifying a language and you'll
 * see a visual representation of these functions.
 *  
 * @company ZIP Code Download, LLC - 2009
 */

import java.sql.*;
import java.util.ArrayList;
import wizards.*;

public class FullJavaExample
{
    public static void main(String[] args) throws ClassNotFoundException, SQLException
    {
        // Simulate data entry.
        String ZIPCode = "95014";                // The ZIP code the user typed in
        double radius = 25.0;                    // How far out to search...
        Measurement unit = Measurement.Miles;    // ... in this unit of measure.
       
        // Discover coordinates of origin ZIP code first.
        String databaseQuery = "SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode = '" + ZIPCode + "'";
       
        // Load the JDBC database driver! You must have one installed.
        // Consult Sun's Java or your driver provider's documentation for help.
        // We have a couple for you to choose from to get you started, once
        // you have installed one:
       
        // Microsoft SQL Server.
        //Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
       
        // MySQL. This example uses MySQL.
        Class.forName("com.mysql.jdbc.Driver");
       
        // Here are some example connection strings for JDBC
        // and a couple database platforms.
        /*
         * "jdbc:odbc:dsn_name;UID=your_uid;PWD=your_pwd" - JDBC-ODBC Bridge Driver.
         * "jdbc:oracle:thin:@machine_name:port_number:instance_name" - Oracle Type 4 JDBC Driver.
         * "jdbc:mysql://host_name:port/dbname" - MySQL Connector/J JDBC Driver.
         * "jdbc:microsoft:sqlserver://machine_name:port;DatabaseName=db_name" - Microsoft SQL Server Driver.
         */
       
        // This line takes your connection string and user login info.
        Connection conn = DriverManager.getConnection("jdbc:mysql://server:port/db_name", "username", "password");
       
        Statement stmt = conn.createStatement();
        ResultSet resultset = stmt.executeQuery(databaseQuery);

        // Go to the first row in the results.
        resultset.next();

        // Create our origin coordinate!
        Coordinate originCoord = new Coordinate(
            resultset.getDouble("Latitude"),
            resultset.getDouble("Longitude"));

        // Helps restrict the number of queries, possibly by thousands.
        BoundaryWizard boundCalc = new BoundaryWizard();
       
        // Find our boundaries for the query.
        Boundary bounds = boundCalc.CalculateBoundary(originCoord, radius, unit);

        /*  Prepare to find some of the nearby stores.
         *  This example uses only a partial collection of real
         *  store location data. Customize this query for your
         *  own production use!
         *  
         *   NOTE:
         *  Regarding the BETWEEN clauses: the lower (smaller) value should be
         *  first, and the value after the AND should be higher (larger).
         *  Consequently, this query only works for the western part
         *  of the northern hemisphere. For other parts of the world,
         *  you'll need to order these properly in your query!
         *  
         *  This query is the meat and potatoes. Be sure to customize it
         *  depending on your own database configuration!
         */
        databaseQuery = "" +
              "    SELECT stores.*, "
            + "           zips.Latitude, "
            + "           zips.Longitude "
            + "      FROM StoreLocations AS stores "
            + "     INNER JOIN ZIPCodes AS zips "
            + "        ON stores.ZipCode = zips.ZIPCode "
            + "     WHERE Latitude BETWEEN " + bounds.South() + " AND " + bounds.North()
            + "       AND Longitude BETWEEN " + bounds.West() + " AND " + bounds.East()
            + "       AND zips.CityType='D'"
            + "       AND Latitude != 0"
            + "       AND Longitude != 0";
   
        resultset = stmt.executeQuery(databaseQuery);
       
        // Prepare to calculate distances.
        DistanceWizard distCalc = new DistanceWizard();
       
        // This holds all our possible results.
        ArrayList<String[]> results = new ArrayList<String[]>();
       
        // Only keep the ones within our desired distance.
        while (resultset.next())
        {
            // Create the coordinate of this candidate ZIP code.
            Coordinate relativeCoord = new Coordinate(
                    resultset.getDouble("Latitude"),
                    resultset.getDouble("Longitude")
            );
           
            // This array stores data from each row.
            String[] result = new String[5];
           
            // Calculate the distance between these two coordinates!
            double distance = distCalc.CalculateDistance(originCoord, relativeCoord, Measurement.Miles);
           
            // It's close enough:
            if (distance <= radius)
            {
                result[0] = Double.toString(distance);
                result[1] = resultset.getString("Address");
                result[2] = resultset.getString("City");
                result[3] = resultset.getString("State");
                result[4] = resultset.getString("ZIPCode");
               
                // Insert element into list according to distance ASC.
                for (int i = 0; i <= results.size(); i++)
                {
                    if (i == results.size())
                    {
                        results.add(result);
                        break;
                    }
                    else if (distance < Double.parseDouble(results.get(i)[0]))
                    {
                        results.add(i, result);
                        break;
                    }
                }
            }
        }
       
        // Display results.
        System.out.println("Some Locations of Wal-Marts nearest " + ZIPCode + "\n");
       
        for (String[] store : results)
        {
            System.out.println(store[1]);
            System.out.println(store[2] + ", " + store[3] + " " + store[4]);
            System.out.println("Distance: " + Double.parseDouble(store[0]) + " miles\n");
        }
    }
}

This query uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Install the stored functions included in the product into your database.
  2. Create a table (perhaps called "StoreLocations") that stores the data shown above.
  3. You will need a table of ZIP code information. If you don't have one yet, get one at ZipCodeDownload.com.
  4. Run the query given below. Don't forget to customize the table and column names used depending on your schema!
  5. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

SQL Server Complete Example (Advanced Usage) /**
 * A practical example of retrieving store locations
 * nearest a given ZIP Code (some sample Wal-Marts in
 * this particular example).
 *
 * We want to do it quickly and efficiently, without running
 * thousands of SELECT queries. BoundaryWizard.sql helps
 * us limit these queries extensively.
 *
 * HOW TO USE: You'll obviously have to customize a lot of this
 * to work with your own database scheme. But, if you are using
 * one of our databases, the scheme should already match. Your
 * own data (e.g. store locations), though, is going to have
 * to be modified to fit in this query! In our example,
 * "StoreLocations" is the name of our table that holds the
 * sample locations of some Wal-Mart stores.
 *
 * COMPATIBILITY: You'll need an associative database containing
 * at least: ZIP Codes and their coordinates. ZIP Code Download's
 * database schemas are already configured to work with this
 * script (Premium or Commercial Edition databases).
 * To get one, visit:
 * http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
 *
 * DOCUMENTATION: See the API reference. Also, view the
 * BoundaryWizard or DistanceWizard or Boundary class
 * help pages without specifying a language and you'll
 * see a visual representation of these functions.
 *  
 * @company ZIP Code Download, LLC - 2009
*/

DECLARE @zip char(5),              -- ZIP code to search with
        @radius decimal(11, 6),    -- Max. distance away from ZIP
        @unit varchar(12),         -- 'Miles' or 'Kilometers' only.
        @originLat decimal(9, 6),  -- Will store the latitude of ZIP
        @originLon decimal(9, 6);  -- Will store the longitude of ZIP

-- Simulate data entry.
SELECT @zip = '95014';
SELECT @radius = 25;
SELECT @unit = 'Miles';

-- Get latitude, longitude of the ZIP above.
SELECT TOP 1
       @originLat = Latitude,
       @originLon = Longitude
  FROM [dbo].[zipcodes]
 WHERE [ZIPCode] = @zip AND CityType = 'D';

-- Get nearest store locations, ordered
-- by distance. Be sure to customize this
-- query based on your own schema!
-- In our example, our table of sample data
-- is "StoreLocations". Change this to
-- your own table name.
SELECT [stores].[Address],
       [zips].[CityName],
       [stores].[State],
       [zips].[ZIPCode],
       dbo.CalculateDistance(@originlat, @originLon, [zips].[Latitude], [zips].[Longitude], @unit) AS Distance
  FROM dbo.zipcodes AS [zips]
 INNER JOIN [dbo].[StoreLocations] AS [stores] ON [zips].[ZIPCode] = [stores].[ZIPCode]
 INNER JOIN [dbo].[Calculateboundary](@originLat, @originLon, @radius, @unit) AS [bounds]
    ON 1=1
 WHERE [zips].[Latitude] BETWEEN [bounds].[South] AND [bounds].[North]
   AND [zips].[Longitude] BETWEEN [bounds].[West] AND [bounds].[East]
   AND [zips].[CityType] = 'D' -- only one result per ZIP
   AND [zips].[Latitude] <> 0
   AND [zips].[Longitude] <> 0
   AND [dbo].[CalculateDistance](@originLat, @originLon, [zips].[Latitude], [zips].[Longitude], @unit) <= @radius
   AND [stores].[ZipCode] = [zips].[ZIPCode]
ORDER BY Distance, ZIPCode;

This code uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

This is only a sample, but you are free to modify it and use it.

Perl Complete Example (Advanced Usage) #!/usr/bin/perl -w

# A practical example of retrieving some Wal-Mart store
# locations nearest a given ZIP Code.
#
# We want to do it quickly and efficiently, without running
# thousands of SELECT queries. BoundaryWizard helps
# us limit these queries extensively.
#
# HOW TO USE: Modify the database connection info below and
# remember to customize the SQL queries below to match your
# database configuration.
#
# SECURITY WARNINGS: This script contains code that outputs
# debugging information. Remove it if adapted to a production
# project. Also, you'll want to verify data input to prevent
# SQL injection.
#
# COMPATIBILITY: You'll need an associative database containing
# at least: ZIP Codes and their coordinates. ZIP Code Download's
# database schemas are already configured to work with this
# script (Premium or Commercial Edition databases).
# To get one, visit:
# http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
#
# DOCUMENTATION: See the API reference. Also, view the
# BoundaryWizard or DistanceWizard or Boundary class
# help pages without specifying a language and you'll
# see a visual representation of these functions.
#  
# @company ZIP Code Download, LLC - 2009


package main;

use DBI;

# Distance Wizard Modules:
use Coordinate;
use BoundaryWizard;
use DistanceWizard;
use Measurement;

# Simulate data entry. Normally, these two values
# will come from a user-submitted form.

my $zip = 95014;
my $radius = 25.0;
my $measure = new Measurement();

# Connect to our ZIP Code Database - connection specific to our environment.
my $databaseName = "zipcodes.sqlite";
my $zipCodes = DBI->connect("DBI:SQLite:dbname=$databaseName", "", "")
        or die "Couldn't connect to database: " . DBI->errstr;

my $queryOrigin = $zipCodes->prepare(
    "SELECT Latitude, Longitude
       FROM ZIPCodes
      WHERE ZIPCode=\"$zip\"
      LIMIT 1") or die "Couldn't prepare sql statement: " . $zipCodes->errstr;

# Make sure our origin ZIP Code exists in the database.
$queryOrigin->execute()
        or die "Couldn't execute sql statement: " . $queryOrigin->errstr;

my @result = $queryOrigin->fetchrow_array;
if (not @result)
{
    die "There was an error executing the query to find ZIP Coordinates";
}
my $result_count = @result;
if ($result_count == 0)
{
    die "Zero results returned; does the ZIP Code exist in the database?";
}

# ZIP Code exists, now find the nearby ZIP Codes.
my $latitude = $result[0];
my $longitude = $result[1];

my $origin_coord = new Coordinate($latitude, $longitude);

# The boundary_wizard will calculate a boundary square, limitting the data to search.
my $boundary_wizard = new BoundaryWizard();
my $boundary = $boundary_wizard->CalculateBoundary($origin_coord, $radius, $measure->{Miles});

# Retrieve boundary border lines.
my $northern = $boundary->North();
my $southern = $boundary->South();
my $eastern = $boundary->East();
my $western = $boundary->West();

# Prepare to find some of the nearby stores.
# This example uses only a partial collection of real
# store location data.

# NOTE:
# Regarding the BETWEEN clauses, the lower (smaller) value should be
# first, and the value after the AND should be higher (larger).
# Consequently, this query only works for the western part
# of the northern hemisphere. For other parts of the world,
# you'll need to order these properly in your query!

# This query is the real meat and potatoes. Be sure to
# customize it depending on your own database configuration!

my $queryNearestZips = $zipCodes->prepare(
        "SELECT stores.*,
                zips.Latitude,
                zips.Longitude
           FROM StoreLocations AS stores
          INNER JOIN ZIPCodes AS zips
             ON stores.ZipCode = zips.ZIPCode
          WHERE Latitude BETWEEN $southern AND $northern
            AND Longitude BETWEEN $western AND $eastern
            AND zips.CityType='D'
            AND Latitude != 0
            AND Longitude != 0");

$queryNearestZips->execute()
        or die "Couldn't execute sql statement: " . $queryNearestZips->errstr;

my $distance_wizard = new DistanceWizard();

my @locations_within_radius = ();

# find store_locations within the scope of our search.
while (my @location = $queryNearestZips->fetchrow_array())
{
    # get the coodinate for each store location
    my $relative_coord = new Coordinate($location[4], $location[5]);
    # calculate the distance from the origin
    my $distance = $distance_wizard->CalculateDistance($origin_coord, $relative_coord, $measure->{Miles});
   
    if ($distance <= $radius) # store is within the scope of our search.
    {
        # don't include lat/lon data
        my @locationWithoutLatLon = @location[0..3];
        # include distance
        push (@locationWithoutLatLon, $distance);
        # store for later display.
        push (@locations_within_radius, [@locationWithoutLatLon]);
    }
}

# report the results:

print "Some Wal-Mart Locations Nearest to $zip:\n";

my @sortedStoreLocations = sort {$a->[4] <=> $b->[4]} @locations_within_radius;

foreach $store (@sortedStoreLocations)
{
    printf("%-20s\t%-15s\t%s\t%s\t%.2f miles\n",
           @$store[0], @$store[1], @$store[2], @$store[3], @$store[4]);
}

This code uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

This is only a sample, but you are free to modify it and use it.

Python Complete Example (Advanced Usage) #!/usr/bin/env python

# A practical example of retrieving some Wal-Mart store
# locations nearest a given ZIP Code.
#
# We want to do it quickly and efficiently, without running
# thousands of SELECT queries. BoundaryWizard helps
# us limit these queries extensively.
#
# HOW TO USE: Modify the database connection info below and
# remember to customize the SQL queries below to match your
# database configuration.
#
# SECURITY WARNINGS: This script contains code that outputs
# debugging information. Remove it if adapted to a production
# project. Also, you'll want to verify data input to prevent
# SQL injection.
#
# COMPATIBILITY: You'll need an associative database containing
# at least: ZIP Codes and their coordinates. ZIP Code Download's
# database schemas are already configured to work with this
# script (Premium or Commercial Edition databases).
# To get one, visit:
# http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
#
# DOCUMENTATION: See the API reference. Also, view the
# BoundaryWizard or DistanceWizard or Boundary class
# help pages without specifying a language and you'll
# see a visual representation of these functions.
#  
# @company ZIP Code Download, LLC - 2009

import operator
import sqlite3

# Distance Wizard Modules:
from coordinate import Coordinate
from boundary_wizard import BoundaryWizard
from distance_wizard import DistanceWizard
from measurement import Measurement
   
def professional_edition_example():
   
    # Simulate data entry. Normally, these two values
    # will come from a user-submitted form.

    zip = "95014"               # our origin ZIP Code
    radius = 25.0               # the scope of our search
    unit_of_measure = Measurement.MILES
   
    # Connect to our ZIP Code Database - connection specific to our environment.
    connection = sqlite3.connect('zipcodes.sqlite')
    cursor = connection.cursor()
   
    # Make sure our origin ZIP Code exists in the database.
    query = """SELECT Latitude, Longitude
                 FROM ZIPCodes
                WHERE ZIPCode='%s'
                LIMIT 1""" % (zip)
               
    cursor.execute(query)
    result = cursor.fetchall()
    if not result:
        print "There was an error executing the database query to find ZIP coordinates."
        return
    if len(result) == 0:
        print "Zero results returned; does the ZIP Code exist in the database?"
        return
   
    # ZIP Code exists, now find the nearby ZIP Codes.
    row = result[0]
    latitude = row[0]
    longitude = row[1]
    origin_coord = Coordinate(latitude, longitude)
   
    # The boundary_wizard will calculate a boundary square, limitting the data to search.
    boundary_wizard = BoundaryWizard()
    boundary = boundary_wizard.calculate_boundary(origin_coord, radius, unit_of_measure)
   
    # Retrieve boundary border lines.
    northern = boundary.north
    southern = boundary.south
    eastern = boundary.east
    western = boundary.west
   
    # Prepare to find some of the nearby stores.
    # This example uses only a partial collection of real
    # store location data.
   
    # NOTE:
    # Regarding the BETWEEN clauses, the lower (smaller) value should be
    # first, and the value after the AND should be higher (larger).
    # Consequently, this query only works for the western part
    # of the northern hemisphere. For other parts of the world,
    # you'll need to order these properly in your query!
   
    # This query is the real meat and potatoes. Be sure to
    # customize it depending on your own database configuration!
   
    query_zips_within_boundary = \
      """SELECT stores.*,
                zips.Latitude,
                zips.Longitude
           FROM StoreLocations AS stores
          INNER JOIN ZIPCodes AS zips
             ON stores.ZipCode = zips.ZIPCode
          WHERE Latitude BETWEEN %s AND %s
            AND Longitude BETWEEN %s AND %s
            AND zips.CityType='D'
            AND Latitude != 0
            AND Longitude != 0""" % (southern, northern, western, eastern)
 
    cursor.execute(query_zips_within_boundary)
    store_locations = cursor.fetchall()
   
    if not store_locations:
        print "There was an error executing the database query to find nearby stores by ZIP."
        return
   
    # Now let's go through each ZIP code and find out how
    # far they are from the origin coordinate and display it.
    distance_wizard = DistanceWizard()
   
    locations_within_radius = []
   
    # find store_locations within the scope of our search.
    for location in store_locations:
       
        location = list(location)   # so we can append the distance later.
        # get the coodinate for each store location
        relative_coord = Coordinate(location[-2], location[-1])
        # calculate the distance from the origin
        distance = distance_wizard.calculate_distance(origin_coord, relative_coord, unit_of_measure)
       
        if distance <= radius:  # store is within the scope of our search.
            location.append(distance)
            del location[4:6]   # remove unecesary latitude/longitude data for report
            locations_within_radius.append(location)
       
    # Sort the list in ascending order according to distance.
    distance_index = -1
    locations_within_radius = \
        sorted(locations_within_radius, key=operator.itemgetter(distance_index))
   
    # Results Report:
    print "Some Wal-Mart Locations Nearest to %s." % (zip)
    for location in locations_within_radius:
        print "%-20s\t%-15s\t%s\t%s\t%.2f miles" % \
                (location[0], location[1], location[2], location[3], location[4])
   
    cursor.close()
   
if __name__ == "__main__":

    professional_edition_example()

This code uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

This is only a sample, but you are free to modify it and use it.

Ruby Complete Example (Advanced Usage) #!/usr/bin/env ruby

# A practical example of retrieving some Wal-Mart store
# locations nearest a given ZIP Code.
#
# We want to do it quickly and efficiently, without running
# thousands of SELECT queries. BoundaryWizard helps
# us limit these queries extensively.
#
# HOW TO USE: Modify the database connection info below and
# remember to customize the SQL queries below to match your
# database configuration.
#
# SECURITY WARNINGS: This script contains code that outputs
# debugging information. Remove it if adapted to a production
# project. Also, you'll want to verify data input to prevent
# SQL injection.
#
# COMPATIBILITY: You'll need an associative database containing
# at least: ZIP Codes and their coordinates. ZIP Code Download's
# database schemas are already configured to work with this
# script (Premium or Commercial Edition databases).
# To get one, visit:
# http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
#
# DOCUMENTATION: See the API reference. Also, view the
# BoundaryWizard or DistanceWizard or Boundary class
# help pages without specifying a language and you'll
# see a visual representation of these functions.
#  
# @company ZIP Code Download, LLC - 2009

require "rubygems"
require "sqlite3"

# Distance Wizard Modules:
require "Coordinate"
require "BoundaryWizard"
require "DistanceWizard"
require "Measurement"

def professional_edition_example()
 
  # Simulate data entry. Normally, these two values
  # will come from a user-submitted form.
 
  zip = "95014"                  # our origin ZIP Code
  radius = 25.0                  # the scope of our search
  unit_of_measure = Measurement::MILES
 
  # Connect to our ZIP Code Database - connection specific to our environment
  connection = SQLite3::Database.new( "zipcodes.sqlite" )
 
  origin_zip_query = "SELECT Latitude, Longitude
                        FROM ZIPCodes
                       WHERE ZIPCode='%s'
                       LIMIT 1" % zip
 
  row = connection.execute(origin_zip_query)
 
  # Make sure our orgin ZIP Code exists in the database.
  if row == nil
    puts "There was an error executing the database query to find ZIP coordinates."
    return
  elsif row .length == 0
    puts "Zero results returned; does the ZIP Code exist in the database?"
    return
  end
 
  # ZIP Code exists, now calculate origin coordinate for distance search.
  latitude = row[0][0].to_f
  longitude = row[0][1].to_f
  origin_coord = Coordinate.new(latitude, longitude)
 
  # The boundary_wizard will calculate a boundary square, limitting the data to search.
  boundary_wizard = BoundaryWizard.new()
  boundary = boundary_wizard.calculate_boundary(origin_coord, radius, unit_of_measure)
   
  # Retrieve boundary border lines.
  northern = boundary::north
  southern = boundary::south
  eastern = boundary::east
  western = boundary::west
 
  # Prepare to find some of the nearby stores.
    # This example uses only a partial collection of real
    # store location data.
   
    # NOTE:
    # Regarding the BETWEEN clauses, the lower (smaller) value should be
    # first, and the value after the AND should be higher (larger).
    # Consequently, this query only works for the western part
    # of the northern hemisphere. For other parts of the world,
    # you'll need to order these properly in your query!
   
    # This query is the real meat and potatoes. Be sure to
    # customize it depending on your own database configuration!
   
    query_zips_within_boundary = \
      "SELECT stores.*,
              zips.Latitude,
              zips.Longitude
         FROM StoreLocations AS stores
        INNER JOIN ZIPCodes AS zips
           ON stores.ZipCode = zips.ZIPCode
        WHERE Latitude BETWEEN %s AND %s
          AND Longitude BETWEEN %s AND %s
          AND zips.CityType='D'
          AND Latitude != 0
          AND Longitude != 0" % [southern, northern, western, eastern]
   
  store_locations = connection.execute(query_zips_within_boundary)

  if not store_locations
    puts "There was an error executing the database query to find nearby stores by ZIP."
  end
 
  # Now let's go through each ZIP code and find out how
  # far they are from the origin coordinate and display it.
  distance_wizard = DistanceWizard.new()

  locations_within_radius = []

  # find store_locations within the scope of our search.
  for location in store_locations

    location = Array.new(location)
    # get the coodinate for each store location
    relative_coord = Coordinate.new(location[4].to_f, location[5].to_f)
    # calculate the distance from the origin
    distance = distance_wizard.calculate_distance(origin_coord, relative_coord, unit_of_measure)
   
    if distance <= radius  # store is within the scope of our search.
      # get rid of latitude and longitude fields but append the distance.
      location_to_append = location[0..3] << distance
      locations_within_radius << location_to_append
    end
  end
 
  # Sort the list in ascending order according to distance.
  distance_index = -1
  locations_within_radius.sort! {|a, b| a[distance_index] <=> b[distance_index]}
 
  # Display Report:
  for location in locations_within_radius
    puts "%-20s\t%-15s\t%s\t%s\t%.2f miles" % \
            [location[0], location[1], location[2], location[3], location[4]]
  end
 
  # Close database connection
  connection.close()
 
end

professional_edition_example

Example Results

Exact output layout may vary from platform to platform, but the values should be the same, given the data in your sample store location table is the same as ours (above).

OUTPUT 600 Showers Dr      Mountain View  CA   94040    5.03 miles
301 Ranch Dr        Milpitas       CA   95035    12.11 miles
40580 Albrae St     Fremont        CA   94538    15.34 miles
30600 Dyer Street   Union City     CA   94587    19.37 miles

Notice how the location in Oakland is not displayed. It is further than our specified radius of 25.0 miles. Experiment with changing the radius.

Complete Code Example - Standard Edition

These examples are fully functional with the following requirements:

  1. A database with ZIP or postal code coordinates, city names, etc.
  2. The minimum language version listed
  3. DistanceWizard Standard Edition

Choose a language to get complete, cut-and-paste sample code:

No example

We do not have an advanced example for this language. Please select a different one.

Complete examples are available in PHP 5+, C# (.NET 2.0+), Java, Ruby, Python, Perl, and Microsoft SQL Server.

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Copy+paste this script into a file and save it in the same directory as the PHP classes that came with your product.
  2. Create a table like the one shown above and fill in the sample data. (Or, configure your own table of data.)
  3. Fill out your MySQL database connection information in the script.
  4. Make sure you have a database with ZIP code information available, and customize the code and queries depending on your configuration.
  5. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

PHP Complete Example (Advanced Usage) <?php
/**
 * A practical example of retrieving some Wal-Mart store
 * locations nearest a given ZIP Code.
 *
 * The SQL has to look through all tens of thousands of rows
 * of the database and perform calculations. The Professional
 * Edition of this product has a speed boost feature called
 * BoundaryWizard that limits this number to just tens or hundreds
 * at most.
 *
 * HOW TO USE: Modify the database connection info below and
 * remember to customize the SQL queries below to match your
 * database configuration.
 *
 * SECURITY WARNINGS: This script contains code that outputs
 * debugging information. Remove it if adapted to a production
 * project. Also, you'll want to verify data input to prevent
 * SQL injection.
 *
 * COMPATIBILITY: You'll need an associative database containing
 * at least: ZIP Codes and their coordinates. ZIP Code Download's
 * database schemas are already configured to work with this
 * script (Premium or Commercial Edition databases).
 * To get one, visit:
 * http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
 *
 * DOCUMENTATION: See the API reference. Also, view the
 * DistanceWizard class help page without specifying a
 * language and you'll see a visual representation of
 * these functions.
 *  
 * @company ZIP Code Download, LLC - 2009
 */
 
// Bring in the DistanceWizard we'll be using.
require_once("DistanceWizard.php");

// Simulate data entry. Normally, these two values
// will come from a user-submitted form.
$zip = "95014";               // our origin ZIP code (a string)
$radius = 25.0;                 // the boundary's radius
$unit = Measurement::MILES;   // our desired unit of measure
                              // "Measurement" is included through one of the wizard files
                              // we already included.

// Connect to our ZIP code database.
$dbhost = "localhost";     // domain name or IP address (usually 'localhost')
$dbuser = "";    // database user name
$dbpass = "";     // that user's password
$dbname = "";    // the database's actual name

// Attempt connection with those credentials.
$dbconn = @mysql_connect($dbhost, $dbuser, $dbpass)
    or die("<p><b>ERROR ></b> Unable to make connection to database server:</p><p>".mysql_error()."</p>");
if ($dbname != "" && !@mysql_select_db($dbname))
    die("<p><b>ERROR ></b> The specified database is unavailable or it does not exist.");

// Set up our query to see if the ZIP exists.
$query = "SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode='$zip' LIMIT 1";

// Run the query.
$result = @mysql_query($query);

if (!$result)
{
    // Explain SQL error.
    echo "<p><b>ERROR ></b> There was an error executing the database query to find ZIP coordinates:</p><p>".mysql_error()."</p>";
    exit;
}
elseif (@mysql_num_rows($result) == 0)
{
    // ZIP code was not found in database.
    echo "<p><b>ERROR ></b> 0 results returned; does the ZIP code exist in the database?";
    exit;
}
else
{
    // ZIP code exists, now find the nearby zip codes.
    $row = mysql_fetch_array($result);
    $latitude = $row['Latitude'];
    $longitude = $row['Longitude'];

    // Create the coordinate of the origin ZIP code.
    $originCoord = new Coordinate($latitude, $longitude);
   
    /*
    Prepare to find some of the nearby stores.
    This example uses only a partial collection of real
    store location data.
   
    This query is the real meat and potatoes. Be sure to
   customize it depending on your own database configuration!
    */
    $queryNearestZips = "SELECT stores.*,
       zips.Latitude,
       zips.Longitude
  FROM StoreLocations AS stores
 INNER JOIN ZIPCodes AS zips
    ON stores.ZipCode = zips.ZIPCode
 WHERE zips.CityType='D'
   AND Latitude != 0
   AND Longitude != 0";
   
    // Now do it!
    $resultNearestZips = @mysql_query($queryNearestZips);
   
    if (!$resultNearestZips)
    {
        // Explain the error...
        echo "<p><b>ERROR ></b> There was an error executing the database query to find nearby stores by ZIP:</p><p>".mysql_error()."</p>";
        exit;
    }
    else
    {
        // Now let's go through each ZIP code and find out how
        // far they are from the origin coordinate and display it.
        $distanceWizard = new DistanceWizard();
       
        // Make output pretty and organized.
        echo "<h2>Some Wal-Mart Locations Nearest to ".$zip."</h2>\n\n<table width=\"500\">\n";
       
        // Our array is declared here for scope. (Read about this below.)
        $dataset;
       
        // Loop through each result
        $i = 0;
        while ($rowZIP = mysql_fetch_array($resultNearestZips))
        {  
            // Get the relative ZIP code's coordinate.
            $relativeCoord = new Coordinate($rowZIP['Latitude'], $rowZIP['Longitude']);
                       
            // Calculate the distance.
            $distance = $distanceWizard->CalculateDistance(
                $originCoord,
                $relativeCoord,
                $unit
            );
           
            if ($distance <= $radius)
            {
                // We want to sort this by distance ASC, so the closest cities are displayed
                // first. Let's put the City & Zip & Distance values in an array for this example.
                // Normally you will use whichever values you need and in the order that
                // you need them.
                $dataset[$i]['City'] = $rowZIP['City'];
                $dataset[$i]['State'] = $rowZIP['State'];
                $dataset[$i]['ZipCode'] = $rowZIP['ZipCode'];
                $dataset[$i]['Address'] = $rowZIP['Address'];
                $dataset[$i]['Distance'] = $distance;
                $i ++;
            }
        }
       
        // Sorts a 2D array given a key from the 2nd dimension
        // of the array in ascending order.
        function sort_2d_array_asc(&$array, $innerkey)
        {
            $dim = array();
            foreach ($array as $innerarray)
                $dim[] = $innerarray[$innerkey];
            array_multisort($dim, $array);
        }
       
        // Perform sort.
        sort_2d_array_asc($dataset, "Distance");
       
        // Display results.
        foreach ($dataset as $resultset)
        {
            echo "<tr>\n\t<td>" . $resultset['Address'] . "</td>\n";
            echo "\t<td>" . $resultset['City'] . "</td>\n";
            echo "\t<td>" . $resultset['State'] . "</td>\n";
            echo "\t<td>" . $resultset['ZipCode'] . "</td>\n";
            echo "\t<td><b>" . round($resultset['Distance'], 2) . " miles</b></td>\n</tr>\n";
        }
       
        echo "</table>";
    }
}

mysql_close($dbconn);
?>

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Create a project and add the class files that came with your product.
  2. Add the "using ZipCodeDownloads.Wizards" line to the top of your program file.
  3. This example uses MySQL and the MySQL Connector/NET classes available from mysql.com. You'll need to install it and add a reference to it.
  4. Create a table like the one shown above and fill in the sample data. (Or, configure your own table of data.)
  5. Configure your connection string in the script.
  6. Make sure you have a database with ZIP code information available, and customize the code and queries depending on your configuration.
  7. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

C# Complete Example (Advanced Usage) /** This Standard Edition of the Distance Wizard product
 * lacks the speed-enhancing capabilities of the Professional
 * Edition. This example has to go through all the rows (tens
 * or hundreds of thousands of them) in a ZIP code database
 * and perform calculations on each one.
 *
 * To improve speed, consider purchasing the Professional Edition.
 */
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

// Don't forget to add the class files to your project!
using ZipCodeDownload.Wizards;

namespace FullCSExample
{
    public sealed class Program
    {
        public static void Main(string[] args)
        {
            // Simulate data entry
            string zip = "95014";
            double radius = 25.0;
            Measurement unit = Measurement.Miles;

            using (IDbConnection connection = DbProviderFactories.GetFactory("MySql.Data.MySqlClient").CreateConnection())
            {
                connection.ConnectionString = "Server=localhost;Database=db_name;Uid=username;Pwd=password;";
                connection.Open();

                Coordinate originCoord = null;

                using (IDbCommand centroidCommand = connection.CreateCommand())
                {
                    // WARNING: To prevent SQL injection you must verify the value of the "zip" variable.
                    centroidCommand.CommandText = string.Format("SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode = '{0}' AND CityType = 'D'", zip);

                    using (IDataReader reader = centroidCommand.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.Read())
                        {
                            originCoord = new Coordinate((double)reader["Latitude"], (double)reader["Longitude"]);
                        }
                    }
                }

                if (originCoord == null)
                {
                    Console.WriteLine("ERROR > That ZIP Code cannot be found in the database.");
                    Console.Read();
                    return;
                }

                using (IDbCommand proximityCommand = connection.CreateCommand())
                {
                    proximityCommand.CommandText = string.Format(
                        "SELECT stores.*, "
                            + "           zips.Latitude, "
                            + "           zips.Longitude "
                            + "      FROM StoreLocations AS stores "
                            + "     INNER JOIN ZIPCodes AS zips "
                            + "        ON stores.ZipCode = zips.ZIPCode "
                            + "     WHERE zips.CityType='D'"
                            + "       AND Latitude != 0"
                            + "       AND Longitude != 0");

                    using (IDataReader reader = proximityCommand.ExecuteReader())
                    {
                        DistanceWizard distCalc = new DistanceWizard();

                        List<object[]> stores = new List<object[]>();

                        while (reader.Read())
                        {
                            Coordinate relativeCoord = new Coordinate((double)reader["Latitude"], (double)reader["Longitude"]);
                            double distance = distCalc.CalculateDistance(originCoord, relativeCoord, unit);

                            if (distance > radius)
                            {
                                continue; // outside of radius, don't include in results.
                            }

                            object[] store = new object[]
                            {
                                distance,
                                (string)reader["Address"],
                                (string)reader["City"],
                                (string)reader["State"],
                                (string)reader["ZipCode"]
                            };

                            // Insert element into list according to distance.
                            for (int i = 0; i <= stores.Count; i++)
                            {
                                if (i == stores.Count)
                                {
                                    stores.Add(store);
                                    break;
                                }
                                else if (distance < (double)stores[i][0])
                                {
                                    stores.Insert(i, store);
                                    break;
                                }
                            }
                        }

                        // Output results.
                        Console.WriteLine("Some Wal-Mart Locations Nearest to " + zip + "\n");

                        foreach (object[] currentStore in stores)
                        {
                            Console.Write(currentStore[1] + "\n");
                            Console.Write(currentStore[2] + ", ");
                            Console.Write(currentStore[3] + " ");
                            Console.Write(currentStore[4] + "\n");
                            Console.Write("Distance: " + Math.Round((double)currentStore[0], 2) + " miles\n\n");
                        }
                    }
                }
            }
            Console.Read();
        }
    }
}

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Create a Java project and add the "wizards" package to it.
  2. Add the "import wizards.*;" line to the top of your program file.
  3. This example uses MySQL and the MySQL Connector/J classes available from mysql.com. You'll need to install it and add a reference to it. However, you can use any JDBC driver for your own database platform.
  4. Create a table like the one shown above and fill in the sample data. (Or, configure your own table of data.)
  5. Configure your connection string in the script.
  6. Make sure you have a database with ZIP/postal code information available, and customize the code and queries depending on your configuration.
  7. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

Java Complete Example (Advanced Usage) /**
 * A practical example of retrieving store locations
 * nearest a given ZIP Code. (some sample Wal-Marts in
 * this particular example)
 *
 * This example is not efficient and the performance takes
 * a hit because the SQL has to look through every row in the
 * ZIP Code database (all tens or hundreds of thousands of
 * rows). Purchase the Professional Edition of Distance Wizard
 * from ZipCodeDownload to reduce the number of queries and
 * calculations to just tens or hundreds at most.
 *
 * HOW TO USE: Modify the database connection info below and
 * remember to customize the SQL queries below to match your
 * database server and configuration. This example uses
 * MySQL. Install the proper JDBC drivers for your server
 * platform in order to use it.
 *
 * COMPATIBILITY: You'll need an associative database containing
 * at least: ZIP/postal Codes and their coordinates. ZIP Code Download's
 * database schemas are already configured to work with this
 * script (Premium or Commercial Edition databases).
 * To get one, visit:
 * http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
 *
 * DOCUMENTATION: See the API reference. Also, view the
 * BoundaryWizard or DistanceWizard or Boundary class
 * help pages without specifying a language and you'll
 * see a visual representation of these functions.
 *  
 * @company ZIP Code Download, LLC - 2009
 */

import java.sql.*;
import java.util.ArrayList;
import wizards.*;

public class FullJavaExample
{
    public static void main(String[] args) throws ClassNotFoundException, SQLException
    {
        // Simulate data entry.
        String ZIPCode = "95014";                // The ZIP code the user typed in
        double radius = 25.0;                    // How far out to search...
        Measurement unit = Measurement.Miles;    // ... in this unit of measure.
       
        // Discover coordinates of origin ZIP code first.
        String databaseQuery = "SELECT Latitude, Longitude FROM ZIPCodes WHERE ZIPCode = '" + ZIPCode + "'";
       
        // Load the JDBC database driver! You must have one installed.
        // Consult Sun's Java or your driver provider's documentation for help.
        // We have a couple for you to choose from to get you started, once
        // you have installed one:
       
        // Microsoft SQL Server.
        //Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
       
        // MySQL. This example uses MySQL.
        Class.forName("com.mysql.jdbc.Driver");
       
        // Here are some example connection strings for JDBC
        // and a couple database platforms.
        /*
         * "jdbc:odbc:dsn_name;UID=your_uid;PWD=your_pwd" - JDBC-ODBC Bridge Driver.
         * "jdbc:oracle:thin:@machine_name:port_number:instance_name" - Oracle Type 4 JDBC Driver.
         * "jdbc:mysql://host_name:port/dbname" - MySQL Connector/J JDBC Driver.
         * "jdbc:microsoft:sqlserver://machine_name:port;DatabaseName=db_name" - Microsoft SQL Server Driver.
         */
       
        // This line takes your connection string and user login info.
        Connection conn = DriverManager.getConnection("jdbc:mysql://server:port/db_name", "username", "password");
       
        Statement stmt = conn.createStatement();
        ResultSet resultset = stmt.executeQuery(databaseQuery);

        // Go to the first row in the results.
        resultset.next();

        // Create our origin coordinate!
        Coordinate originCoord = new Coordinate(
            resultset.getDouble("Latitude"),
            resultset.getDouble("Longitude"));

        /*  Prepare to find some of the nearby stores.
         *  This example uses only a partial collection of real
         *  store location data. Customize this query for your
         *  own production use!
         *  
         *  This query is the meat and potatoes. Be sure to customize it
         *  depending on your own database configuration!
         */
        databaseQuery = "" +
              "    SELECT stores.*, "
            + "           zips.Latitude, "
            + "           zips.Longitude "
            + "      FROM StoreLocations AS stores "
            + "     INNER JOIN ZIPCodes AS zips "
            + "        ON stores.ZipCode = zips.ZIPCode "
            + "     WHERE zips.CityType='D'"
            + "       AND Latitude != 0"
            + "       AND Longitude != 0";
   
        resultset = stmt.executeQuery(databaseQuery);
       
        // Prepare to calculate distances.
        DistanceWizard distCalc = new DistanceWizard();
       
        // This holds all our possible results.
        ArrayList<String[]> results = new ArrayList<String[]>();
       
        // Only keep the ones within our desired distance.
        while (resultset.next())
        {
            // Create the coordinate of this candidate ZIP code.
            Coordinate relativeCoord = new Coordinate(
                    resultset.getDouble("Latitude"),
                    resultset.getDouble("Longitude")
            );
           
            // This array stores data from each row.
            String[] result = new String[5];
           
            // Calculate the distance between these two coordinates!
            double distance = distCalc.CalculateDistance(originCoord, relativeCoord, Measurement.Miles);
           
            // It's close enough:
            if (distance <= radius)
            {
                result[0] = Double.toString(distance);
                result[1] = resultset.getString("Address");
                result[2] = resultset.getString("City");
                result[3] = resultset.getString("State");
                result[4] = resultset.getString("ZIPCode");
               
                // Insert element into list according to distance ASC.
                for (int i = 0; i <= results.size(); i++)
                {
                    if (i == results.size())
                    {
                        results.add(result);
                        break;
                    }
                    else if (distance < Double.parseDouble(results.get(i)[0]))
                    {
                        results.add(i, result);
                        break;
                    }
                }
            }
        }
       
        // Display results.
        System.out.println("Some Locations of Wal-Marts nearest " + ZIPCode + "\n");
       
        for (String[] store : results)
        {
            System.out.println(store[1]);
            System.out.println(store[2] + ", " + store[3] + " " + store[4]);
            System.out.println("Distance: " + Double.parseDouble(store[0]) + " miles\n");
        }
    }
}

This query uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

  1. Install the stored functions included in the product into your database.
  2. Create a table (perhaps called "StoreLocations") that stores the data shown above.
  3. You will need a table of ZIP/postal code information. If you don't have one yet, you can get one at ZIPCodeDownload.com.
  4. Run the query given below. Don't forget to customize the table and column names used depending on your schema!
  5. Read the comments for clarification on any part of the code.

This is only a sample, but you are free to modify it and use it.

SQL Server Complete Example (Advanced Usage) /**
 * A practical example of retrieving store locations
 * nearest a given ZIP Code (some sample Wal-Marts in
 * this particular example).
 *
 * This SQL has to look through all tens or hundreds of
 * thousands of rows and perform calculations. To improve
 * speed, consider buying the Professional Edition of this
 * product. It limits the number of searches to just tens or
 * hundreds at most.
 *
 * HOW TO USE: You'll obviously have to customize a lot of this
 * to work with your own database scheme. But, if you are using
 * one of our databases, the scheme should already match. Your
 * own data (e.g. store locations), though, is going to have
 * to be modified to fit in this query! In our example,
 * "StoreLocations" is the name of our table that holds the
 * sample locations of some Wal-Mart stores.
 *
 * COMPATIBILITY: You'll need an associative database containing
 * at least: ZIP Codes and their coordinates. ZIP Code Download's
 * database schemas are already configured to work with this
 * script (Premium or Commercial Edition databases).
 * To get one, visit:
 * http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
 *
 * DOCUMENTATION: See the API reference. Also, view the
 * BoundaryWizard or DistanceWizard or Boundary class
 * help pages without specifying a language and you'll
 * see a visual representation of these functions.
 *  
 * @company ZIP Code Download, LLC - 2009
*/

DECLARE @zip char(5),              -- ZIP code to search with
        @radius decimal(11, 6),    -- Max. distance away from ZIP
        @unit varchar(12),         -- 'Miles' or 'Kilometers' only.
        @originLat decimal(9, 6),  -- Will store the latitude of ZIP
        @originLon decimal(9, 6);  -- Will store the longitude of ZIP

-- Simulate data entry.
SELECT @zip = '95014';
SELECT @radius = 25;
SELECT @unit = 'Miles';

-- Get latitude, longitude of the ZIP above.
SELECT TOP 1
       @originLat = Latitude,
       @originLon = Longitude
  FROM [dbo].[zipcodes]
 WHERE [ZIPCode] = @zip AND CityType = 'D';

-- Get nearest store locations, ordered
-- by distance. Be sure to customize this
-- query based on your own schema!
-- In our example, our table of sample data
-- is "StoreLocations". Change this to
-- your own table name.
SELECT [stores].[Address],
       [zips].[CityName],
       [stores].[State],
       [zips].[ZIPCode],
       dbo.CalculateDistance(@originlat, @originLon, [zips].[Latitude], [zips].[Longitude], @unit) AS Distance
  FROM dbo.zipcodes AS [zips]
 INNER JOIN [dbo].[StoreLocations] AS [stores]
    ON [zips].[ZIPCode] = [stores].[ZIPCode]
 WHERE [zips].[CityType] = 'D' -- only one result per ZIP
   AND [zips].[Latitude] <> 0
   AND [zips].[Longitude] <> 0
   AND [dbo].[CalculateDistance](@originLat, @originLon, [zips].[Latitude], [zips].[Longitude], @unit) <= @radius
   AND [stores].[ZipCode] = [zips].[ZIPCode]
ORDER BY Distance, ZIPCode;

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

This is only a sample, but you are free to modify it and use it.

Perl Complete Example (Advanced Usage) #!/usr/bin/perl -w

# A practical example of retrieving some Wal-Mart store
# locations nearest a given ZIP Code.
#
# The SQL has to look through all tens of thousands of rows
# of the database and perform calculations. The Professional
# Edition of this product has a speed boost feature called
# BoundaryWizard that limits this number to just tens or hundreds
# at most.
#
# HOW TO USE: Modify the database connection info below and
# remember to customize the SQL queries below to match your
# database configuration.
#
# SECURITY WARNINGS: This script contains code that outputs
# debugging information. Remove it if adapted to a production
# project. Also, you'll want to verify data input to prevent
# SQL injection.
#
# COMPATIBILITY: You'll need an associative database containing
# at least: ZIP Codes and their coordinates. ZIP Code Download's
# database schemas are already configured to work with this
# script (Premium or Commercial Edition databases).
# To get one, visit:
# http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
#
# DOCUMENTATION: See the API reference. Also, view the
# DistanceWizard class help page without specifying a
# language and you'll see a visual representation of
# these functions.
#  
# @company ZIP Code Download, LLC - 2009

package main;

use DBI;

# Distance Wizard Modules:
use Coordinate;
use DistanceWizard;
use Measurement;

# Simulate data entry. Normally, these two values
# will come from a user-submitted form.

my $zip = 95014;
my $radius = 25.0;
my $measure = new Measurement();

# Connect to our ZIP Code Database - connection specific to our environment.
my $databaseName = "zipcodes.sqlite";
my $zipCodes = DBI->connect("DBI:SQLite:dbname=$databaseName", "", "")
        or die "Couldn't connect to database: " . DBI->errstr;

my $queryOrigin = $zipCodes->prepare(
    "SELECT Latitude, Longitude
       FROM ZIPCodes
      WHERE ZIPCode=\"$zip\"
      LIMIT 1") or die "Couldn't prepare sql statement: " . $zipCodes->errstr;

# Make sure our origin ZIP Code exists in the database.
$queryOrigin->execute()
        or die "Couldn't execute sql statement: " . $queryOrigin->errstr;

my @result = $queryOrigin->fetchrow_array;
if (not @result)
{
    die "There was an error executing the query to find ZIP Coordinates";
}
my $result_count = @result;
if ($result_count == 0)
{
    die "Zero results returned; does the ZIP Code exist in the database?";
}

# ZIP Code exists, now find the nearby ZIP Codes.
my $latitude = $result[0];
my $longitude = $result[1];

my $origin_coord = new Coordinate($latitude, $longitude);

# Prepare to find some of the nearby stores.
# This example uses only a partial collection of real
# store location data.

# This query is the real meat and potatoes. Be sure to
# customize it depending on your own database configuration!
my $queryNearestZips = $zipCodes->prepare(
                       "SELECT stores.*,
                               zips.Latitude,
                               zips.Longitude
                          FROM StoreLocations AS stores
                         INNER JOIN ZIPCodes AS zips
                            ON stores.ZipCode = zips.ZIPCode
                         WHERE zips.CityType='D'
                           AND Latitude != 0
                           AND Longitude != 0");

$queryNearestZips->execute()
        or die "Couldn't execute sql statement: " . $queryNearestZips->errstr;

my $distance_wizard = new DistanceWizard();

my @locations_within_radius = ();

# find store_locations within the scope of our search.
while (my @location = $queryNearestZips->fetchrow_array())
{
    # get the coodinate for each store location
    my $relative_coord = new Coordinate($location[4], $location[5]);
    # calculate the distance from the origin
    my $distance = $distance_wizard->CalculateDistance($origin_coord, $relative_coord, $measure->{Miles});
   
    if ($distance <= $radius) # store is within the scope of our search.
    {
        # don't include lat/lon data
        my @locationWithoutLatLon = @location[0..3];
        # include distance
        push (@locationWithoutLatLon, $distance);
        # store for later display.
        push (@locations_within_radius, [@locationWithoutLatLon]);
    }
}

# report the results:

print "Some Wal-Mart Locations Nearest to $zip:\n";

my @sortedStoreLocations = sort {$a->[4] <=> $b->[4]} @locations_within_radius;

foreach $store (@sortedStoreLocations)
{
    printf("%-20s\t%-15s\t%s\t%s\t%.2f miles\n",
           @$store[0], @$store[1], @$store[2], @$store[3], @$store[4]);
}

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

This is only a sample, but you are free to modify it and use it.

Python Complete Example (Advanced Usage) #!/usr/bin/env python

# A practical example of retrieving some Wal-Mart store
# locations nearest a given ZIP Code.
#
# The SQL has to look through all tens of thousands of rows
# of the database and perform calculations. The Professional
# Edition of this product has a speed boost feature called
# BoundaryWizard that limits this number to just tens or hundreds
# at most.
#
# HOW TO USE: Modify the database connection info below and
# remember to customize the SQL queries below to match your
# database configuration.
#
# SECURITY WARNINGS: This script contains code that outputs
# debugging information. Remove it if adapted to a production
# project. Also, you'll want to verify data input to prevent
# SQL injection.
#
# COMPATIBILITY: You'll need an associative database containing
# at least: ZIP Codes and their coordinates. ZIP Code Download's
# database schemas are already configured to work with this
# script (Premium or Commercial Edition databases).
# To get one, visit:
# http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
#
# DOCUMENTATION: See the API reference. Also, view the
# DistanceWizard class help page without specifying a
# language and you'll see a visual representation of
# these functions.
#  
# @company ZIP Code Download, LLC - 2009

import operator
import sqlite3

# Distance Wizard Modules:
from coordinate import Coordinate
from distance_wizard import DistanceWizard
from measurement import Measurement
   
def standard_edition_example():
   
    # Simulate data entry. Normally, these two values
    # will come from a user-submitted form.

    zip = "95014"               # our origin ZIP Code
    radius = 25.0               # the scope of our search
    unit_of_measure = Measurement.MILES
   
    # Connect to our ZIP Code Database - connection specific to our environment.
    connection = sqlite3.connect('zipcodes.sqlite')
    cursor = connection.cursor()
   
    # Make sure our origin ZIP Code exists in the database.
    query = """SELECT Latitude, Longitude
                 FROM ZIPCodes
                WHERE ZIPCode='%s'
                LIMIT 1""" % (zip)
               
    cursor.execute(query)
    result = cursor.fetchall()
    if not result:
        print "There was an error executing the database query to find ZIP coordinates."
        return
    if len(result) == 0:
        print "Zero results returned; does the ZIP Code exist in the database?"
        return
   
    # ZIP Code exists, now find the nearby ZIP Codes.
    row = result[0]
    latitude = row[0]
    longitude = row[1]
   
    origin_coord = Coordinate(latitude, longitude)
   
    # Prepare to find some of the nearby stores.
    # This example uses only a partial collection of real
    # store location data.
   
    # This query is the real meat and potatoes. Be sure to
    # customize it depending on your own database configuration!
   
    queryNearestZips = """SELECT stores.*,
                                 zips.Latitude,
                                 zips.Longitude
                            FROM StoreLocations AS stores
                           INNER JOIN ZIPCodes AS zips
                              ON stores.ZipCode = zips.ZIPCode
                           WHERE zips.CityType='D'
                             AND Latitude != 0
                             AND Longitude != 0"""
                             
    cursor.execute(queryNearestZips)
    store_locations = cursor.fetchall()
   
    if not store_locations:
        print "There was an error executing the database query to find nearby stores by ZIP."
        return
   
    distance_wizard = DistanceWizard()
   
    locations_within_radius = []
   
    # find store_locations within the scope of our search.
    for location in store_locations:
       
        location = list(location)   # so we can append the distance later.
        # get the coodinate for each store location
        relative_coord = Coordinate(location[-2], location[-1])
        # calculate the distance from the origin
        distance = distance_wizard.calculate_distance(origin_coord, relative_coord, unit_of_measure)
       
        if distance <= radius:  # store is within the scope of our search.
            location.append(distance)
            del location[4:6]   # remove unecesary latitude/longitude data for report
            locations_within_radius.append(location)
       
    # Sort the list in ascending order according to distance.
    distance_index = -1
    locations_within_radius = \
        sorted(locations_within_radius, key=operator.itemgetter(distance_index))
   
    # Results Report:
    print "Some Wal-Mart Locations Nearest to %s." % (zip)
    for location in locations_within_radius:
        print "%-20s\t%-15s\t%s\t%s\t%.2f miles" % \
                (location[0], location[1], location[2], location[3], location[4])
   
    cursor.close()

if __name__ == "__main__":

    standard_edition_example()

This script uses some sample data (shown in the table below) and finds some of the Wal-Mart stores near the zip code 95014.

StoreLocations table
Sample Wal-Mart location data
Notice how the location in Oakland will not be displayed in the output of this example (below),
because it is further than 25.0 miles from our origin coordinate.

Instructions

This is only a sample, but you are free to modify it and use it.

Ruby Complete Example (Advanced Usage) #!/usr/bin/env ruby

# A practical example of retrieving some Wal-Mart store
# locations nearest a given ZIP Code.
#
# The SQL has to look through all tens of thousands of rows
# of the database and perform calculations. The Professional
# Edition of this product has a speed boost feature called
# BoundaryWizard that limits this number to just tens or hundreds
# at most.
#
# HOW TO USE: Modify the database connection info below and
# remember to customize the SQL queries below to match your
# database configuration.
#
# SECURITY WARNINGS: This script contains code that outputs
# debugging information. Remove it if adapted to a production
# project. Also, you'll want to verify data input to prevent
# SQL injection.
#
# COMPATIBILITY: You'll need an associative database containing
# at least: ZIP Codes and their coordinates. ZIP Code Download's
# database schemas are already configured to work with this
# script (Premium or Commercial Edition databases).
# To get one, visit:
# http://www.zipcodedownload.com/Products/Product/Z5Commercial/Standard/Overview/
#
# DOCUMENTATION: See the API reference. Also, view the
# DistanceWizard class help page without specifying a
# language and you'll see a visual representation of
# these functions.
#  
# @company ZIP Code Download, LLC - 2009

require "rubygems"
require "sqlite3"

# Distance Wizard Modules:
require "Coordinate"
require "DistanceWizard"
require "Measurement"

def standard_edition_example()
 
  # Simulate data entry. Normally, these two values
  # will come from a user-submitted form.
 
  zip = "95014"
  radius = 25.0
  unit_of_measure = Measurement::MILES
 
  # Connect to our ZIP Code Database - connection specific to our environment.
  connection = SQLite3::Database.new( "zipcodes.sqlite" )
 
  origin_zip_query = "SELECT Latitude, Longitude
                        FROM ZIPCodes
                       WHERE ZIPCode ='%s'
                       LIMIT 1" % zip
 
  row = connection.execute(origin_zip_query)
 
  # Make sure our orgin ZIP Code exists in the database.
  if row == nil
    puts "There was an error executing the database query to find ZIP coordinates."
    return
  elsif row .length == 0
    puts "Zero results returned; does the ZIP Code exist in the database?"
    return
  end
 
  # ZIP Code exists, now calculate origin coordinate for distance search.
  latitude = row[0][0].to_f
  longitude = row[0][1].to_f
  origin_coord = Coordinate.new(latitude, longitude)
 
  # Prepare to find some of the nearby stores.
  # This example uses only a partial collection of real
  # store location data.
 
  # This query is the real meat and potatoes. Be sure to
  # customize it depending on your own database configuration!

  queryNearestZips = "SELECT stores.*,
                             zips.Latitude,
                             zips.Longitude
                        FROM StoreLocations AS stores
                       INNER JOIN ZIPCodes AS zips
                          ON stores.ZipCode = zips.ZIPCode
                       WHERE zips.CityType='D'
                         AND Latitude != 0
                         AND Longitude != 0"
 
  store_locations = connection.execute(queryNearestZips)

  if not store_locations
    puts "There was an error executing the database query to find nearby stores by ZIP."
  end
 
  # Now let's go through each ZIP code and find out how
  # far they are from the origin coordinate and display it.
  distance_wizard = DistanceWizard.new()

  locations_within_radius = []

  # find store_locations within the scope of our search.
  for location in store_locations

    location = Array.new(location)
    # get the coodinate for each store location
    relative_coord = Coordinate.new(location[4].to_f, location[5].to_f)
    # calculate the distance from the origin
    distance = distance_wizard.calculate_distance(origin_coord, relative_coord, unit_of_measure)
   
    if distance <= radius  # store is within the scope of our search.
      # get rid of latitude and longitude fields but append the distance.
      location_to_append = location[0..3] << distance
      locations_within_radius << location_to_append
    end
  end
  # Sort the list in ascending order according to distance.
  distance_index = -1
  locations_within_radius.sort! {|a, b| a[distance_index] <=> b[distance_index]}
 
  # Display Report:
  for location in locations_within_radius
    puts "%-20s\t%-15s\t%s\t%s\t%.2f miles" % \
            [location[0], location[1], location[2], location[3], location[4]]
  end
 
  # Close database connection
  connection.close()

end

standard_edition_example

Example Results

Exact output layout may vary from platform to platform, but the values should be the same, given the data in your sample store location table is the same as ours (above).

OUTPUT 600 Showers Dr      Mountain View  CA   94040    5.03 miles
301 Ranch Dr        Milpitas       CA   95035    12.11 miles
40580 Albrae St     Fremont        CA   94538    15.34 miles
30600 Dyer Street   Union City     CA   94587    19.37 miles

Notice how the location in Oakland is not displayed. It is further than our specified radius of 25.0 miles. Experiment with changing the radius.