|
| |
This source code is in Visual FoxPro; but should be easily adapted to other
languages that support SQL statements.
NOTE: Due to web page formatting, the code may not be
formatted correctly (ie: comment lines may span more than one line, feel free to
download the code as well)
 Thanks to Carl Karsten for pointing out
a missing Having clause, which is needed for correctness -- the select clause
actually gathered a square (Rushmore optimizable) then the HAVING clause cleans
out the stragglers. Carl has his own version of the code --
check out whatCarl has
done with the latitude & Longitude data with zipcodes -- VERY cool -- done in
Visual FoxPro.
| Select
Zipcodes that are w/in X miles of a given zipcode |
#DEFINE cdbl
#DEFINE _PI 3.1415926535893
#DEFINE _StatuteMile 5280 &&' 5280 feet = 1 statute mile
#DEFINE _NauticalMile 6076.11549 &&' 6076.11549 feet = 1 nautical mile
#DEFINE _Seconds 60 && ' 60 seconds = 1 nautical mile
FUNCTION ZipRadius( tcZipcode, tnRadius )
LOCAL lnResults, lnSeconds, ;
lnLatRange, lnLonRange,;
lnLowLat,
lnLowLon, ;
lnHighLat, lnHighLon
* USALL is the table that holds the
latitude/longitude data
* ZRLL will hold the latitude/longitude for
the zipcode specified
IF USED("zrll")
USE IN ZRLL
ENDIF
SELECT * ;
FROM usall ;
WHERE zipcode == tcZipcode ;
INTO CURSOR ZRLL
IF RECCOUNT("zrll")=0
lnResults = -1
ELSE
*' 1 degree of latitude = 60
nautical miles or 1 minute = 1 nautical mile
lnLatRange = tnRadius / ((_NauticalMile
/ _StatuteMile) * _Seconds)
* Longitude
is a bit more complicated;
* 1 degree of longitude at the
equator = 60 nautical miles,
* At the poles 1 degree = 0
nautical miles.
lnLonRange = tnRadius / (((COS(cdbl(ZRLL.latitude
* _PI / 180)) * ;
_NauticalMile) / _StatuteMile) * _Seconds)
lnLowLat = ZRLL.latitude -
lnLatRange
lnHighLat = ZRLL.latitude +
lnLatRange
lnLowLon = ZRLL.longitude -
lnLonRange
lnHighLon = ZRLL.longitude+
lnLonRange
* Then use a SQL
statement to select all locations within the range
IF USED("ZipRadius")
USE IN ZipRadius
ENDIF
SELECT *, ZipDistance(
tcZipcode, zipcode) AS distance ;
FROM usazip ;
HAVING distance <=
tnRadius ;
WHERE zipcode IN
(SELECT zipcode ;
FROM usall;
WHERE latitude <=
lnHighLat;
AND latitude >=
lnLowLat;
AND longitude >=
lnLowLon;
AND longitude <=
lnHighLon) ;
ORDER BY distance ;
INTO CURSOR
ZipRadius
lnResults = RECCOUNT("ZipRadius")
ENDIF
RETURN lnResults
ENDFUNC |
Calculate the
distance between two zipcodes
Please note;
this function is optimized for VFP being called over and over.
If you need a more generic version, please let me know. |
#DEFINE KMRAD 6377
#DEFINE MIRAD 3963
#DEFINE RADIANS DTOR
FUNC ZipDistance
LPARAM tcZip1, tcZip2, tlKM
LOCAL lnDistance, ln1Lat, ln1Lon, ln2Lat, ln2Lon
IF INDEXSEEK( tcZip1, .F., "usall", "Zipcode") AND ;
INDEXSEEK(tcZip2,.F.,"usall","zipcode")
* We're ok -- we can determine distance.
=SEEK( tcZip1, "usall","zipcode")
ln1Lat = usall.latitude
ln1Lon = usall.longitude
=SEEK( tcZip2, "usall","zipcode")
ln2Lat = usall.latitude
ln2Lon = usall.longitude
lnDistance=IIF(tlKM, KMRAD, MIRAD)*;
ACOS(COS(RADIANS(90-ln1Lat))*COS(RADIANS(90-ln2Lat))+;
SIN(RADIANS(90-ln1Lat))*SIN(RADIANS(90-ln2Lat))*;
COS(RADIANS(ln1Lon - ln2Lon)))
ELSE
lnDistance = -1
ENDIF
RETURN lnDistance
ENDFUNC
|
| A version to calculate
distances w/o Indexes |
#DEFINE KMRAD 6377
#DEFINE MIRAD 3963
#DEFINE RADIANS DTORFUNC
ZipDistance
LPARAM tcZip1, tcZip2, tlKM
LOCAL lnDistance, ln1Lat, ln1Lon, ln2Lat,
ln2Lon
IF USED("zipStart")
USE IN zipStart
ENDIF
IF USED("zipEnd")
USE IN zipEnd
ENDIF
SELECT * ;
FROM usall ;
HAVING zipcode == tcZip1 ;
INTO CURSOR zipStart
SELECT * ;
FROM usall ;
HAVING zipcode == tcZip2 ;
INTO CURSOR zipEnd
IF RECCOUNT("zipStart")>0 AND ;
RECCOUNT("ZipEnd") > 0
ln1Lat = zipstart.latitude
ln1Lon = zipstart.longitude
ln2Lat = zipend.latitude
ln2Lon = zipend.longitude
lnDistance=IIF(tlKM, KMRAD,
MIRAD)*;
ACOS(COS(RADIANS(90-ln1Lat))*COS(RADIANS(90-ln2Lat))+;
SIN(RADIANS(90-ln1Lat))*SIN(RADIANS(90-ln2Lat))*;
COS(RADIANS(ln1Lon -
ln2Lon)))
ELSE
lnDistance = -1
ENDIF
RETURN lnDistance
ENDFUNC |
|
