(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:
The ZIP Code Download Distance Wizard can be utilized in 16 different languages and platforms including:
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.
Below is an overall comparison of the Standard and Professional editions of Distance Wizard.
Distance Wizard Standard Edition |
Distance Wizard Professional Edition |
|
Calculates distance between two coordinates on Earth | ||
Integrates with ZIP Code Download's ZIP Code Databases | ||
Integrates with your own table of location data | ||
Enables faster database queries with smart boundary technology | ||
Ultra-high performance | ||
Recommended for high-volume websites and applications | ||
Execution time |
Below is a technical comparison, showing which API features are available (classes, functions, etc) in each edition.
Distance Wizard Standard Edition |
Distance Wizard Professional Edition |
|
DistanceWizard class To calculate distance |
||
BoundaryWizard class For fast database queries |
||
Boundary class To improve performance |
||
Coordinate class To represent location |
||
Measurement enum For miles/kilometers |
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:
Since this library deals with coordinates on Earth's surface, the wizard make calculations based on the surface of a sphere.
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:
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.
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)
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.
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)
double LatitudeThe latitude (x-value) of the coordinate.
double LongitudeThe longitude (y-value) of the coordinate.
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.
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.
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.
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.
float latitudeThe latitude (x-value) of the coordinate.
float longitudeThe longitude (y-value) of the coordinate.
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.
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
The Measurement enumeration is used to represent two different units of measure:
ASP
Dim
Measure: Set Measure = New
Measurement
Dim unit1: unit1 =
Measure.Miles
Dim unit2: unit2 =
Measure.Kilometers
C Measurement unit1
=
Miles;
Measurement unit2 = Kilometers;
C# var unit1 =
Measurement.Miles;
var unit2 = Measurement.Kilometers;
C++ Measurement unit1
=
Measurement::Miles;
Measurement unit2 = Measurement::Kilometers;
ColdFusion
<cfobject
name="measure" component="cfcs.Measurement">
<cfset unit1 = measure.Miles>
<cfset unit2 = measure.Kilometers>
Java Measurement unit1
=
Measurement.Miles;
Measurement unit2 = Measurement.Kilometers;
MS SQL Server DECLARE @unit1 varchar(12), @unit2 varchar(12);
SELECT @unit1
=
'Miles';
SELECT @unit2
=
'Kilometers';
MySQL 5.0+
SET
@unit1 =
'Miles'
;
SET @unit2
=
'Kilometers';
Oracle 9+ DECLARE unit1 VARCHAR2(12)
:= 'Miles';
unit2 VARCHAR2(12)
:= 'Kilometers';
BEGIN
NULL;
END;
/
Perl my $measure = new Measurement();
my $unit1
= $measure->{Miles};
my $unit2
= $measure->{Kilometers};
Ruby unit_of_measure1 = Measurement::Miles
unit_of_measure2 = Measurement::Kilometers
Python unit_of_measure1 = Measurement.MILES
unit_of_measure2 = Measurement.KILOMETERS
PHP $unit1
= Measurement::MILES;
$unit2 =
Measurement::KILOMETERS;
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
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.
double NorthGets the northern-most boundary line (a line of latitude).
double SouthGets the southern-most boundary line (a line of latitude).
double EastGets the eastern-most boundary line (a line of longitude).
double WestGets the western-most boundary line (a line of longitude).
northGets the northern-most boundary line (a line of latitude).
southGets the southern-most boundary line (a line of latitude).
eastGets the eastern-most boundary line (a line of longitude).
westGets the western-most boundary line (a line of longitude).
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.
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.
The BoundaryWizard class is used to find four geolocational lines around an origin given a certain radius.
General Constructor Syntax
new
BoundaryWizard
Constructor BoundaryWizard.new()
Constructor BoundaryWizard()
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.
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.
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.
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.
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.
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
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
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.
The DistanceWizard performs a calculation to determine the distance in a desired unit of measure between two given Coordinates on a sphere.
Constructor Syntax
new
DistanceWizard
Constructor Syntax DistanceWizard.new()
Constructor Syntax DistanceWizard()
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.
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.
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.
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).
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).
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
The example above should output the following numeric value (exact formatting and precision varies per platform):
OUTPUT Distance: 1474.15267074819
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.
These examples are fully functional with the following requirements:
Choose a language to get complete, cut-and-paste sample code:
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
These examples are fully functional with the following requirements:
Choose a language to get complete, cut-and-paste sample code:
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.