AccuGeoCoder Module: Calculate Distance in a script

Below you'll find a Command that will calculate the distance between a Single Point and All Records in your database that contain an eLatitude and eLongitude coordinate. This is for use with the AccuGeoCoder Module.

You only have to enter the Starting Latitude and Longitude in the command and click Run.

Note: Run AFTER Validating with Lat/Long checked or processing the list using the AccuGeocoder Module.

! Field Names com_DistanceField="distance_" com_LatitudeField="elatitude" com_LongitudeField="elongitude" ! Starting Latitude Value com_Latitude="[color=green]42.048700 [/color]" ! Starting Longitude Value com_Longitude="[color=green]-72.569600[/color]" ! Supress Messages ON ERROR com_ErrorMsg=MESSAGE() ! Add Distance Field ALTER TABLE (ALIAS()) ADD COLUMN &com_DistanceField N(20,15) ! Convert Latitude calc_Lat=str(val(com_Latitude)/57.2958,20,15) calc_Long=str(val(com_Longitude)/57.2958,20,15) ON ERROR com_ReplaceString="REPLACE ALL &com_DistanceField WITH iif(!EMPTY(&com_LatitudeField) AND !EMPTY(&com_LongitudeField),round(3958.75acos(sin("+calc_Lat+")sin(val(&com_LatitudeField)/57.2958)+cos("+calc_Lat+")cos(val(&com_LatitudeField)/57.2958)cos(val(&com_LongitudeField)/57.2958-"+calc_Long+")),15),0)" &com_ReplaceString

*!* Field Names
com_DistanceField="distance_"
com_LatitudeField="elatitude"
com_LongitudeField="elongitude"

*!* Starting Latitude Value
com_Latitude="[color=green]42.048700 [/color]"

*!* Starting Longitude Value
com_Longitude="[color=green]-72.569600[/color]"

*!* Supress Messages
ON ERROR com_ErrorMsg=MESSAGE()

*!* Add Distance Field
ALTER TABLE (ALIAS()) ADD COLUMN &com_DistanceField N(20,15)

*!* Convert Latitude
calc_Lat=str(val(com_Latitude)/57.2958,20,15)
calc_Long=str(val(com_Longitude)/57.2958,20,15)

ON ERROR

com_ReplaceString="REPLACE ALL &com_DistanceField WITH iif(!EMPTY(&com_LatitudeField) AND !EMPTY(&com_LongitudeField),round(3958.75*acos(sin("+calc_Lat+")*sin(val(&com_LatitudeField)/57.2958)+cos("+calc_Lat+")*cos(val(&com_LatitudeField)/57.2958)*cos(val(&com_LongitudeField)/57.2958-"+calc_Long+")),15),0)"

&com_ReplaceString

« Back to Tech Notes

New to the
Mailing
Industry?
More Info

Professional Services
More Info

I have been working in pretty much all areas of senior-level IT and Data Processing for many years and have had countless hours of experience engaging with customer support. I’ve never had better support than what I receive from Accuzip! Seriously, any time I call, email, or go to the online chat, someone responds in less than a minute, is always very patient and pleasant, and resolves my issue quickly. Aside from just fixing the problem, your support team always takes the time to explain what the problem was and how to prevent it. It is very refreshing and encouraging to see that real support still exists. Thanks to the Accuzip Team! I look forward to many more years of engagement with your Team.

M.M., Scottsdale, AZ