RADIUS IN MILES FROM ZIP 2

[gentforreal]

My master data contains all zip codes in the US and all longitudes and latitudes assciated with them.

My working database contains the fields zip, longitude and latitude as well as other data.

Has anyone heard of code that will allow me to determine the distance from one zip to another zip. My goal is to pull all zipcodes from, as example, zipcode 12345 that are in a 10 or maybe a 50 mile radius of 12345. Maybe another forum if you have no ideas. Thanks

 

[Aseem1234]

We have done this in one of our applications....this is the code we used...the function at the bottom calculates the distance...

select lat, long from zip where zip_code == lcZip;
into array laBuffer

lnLat = laBuffer[1]
lnLong = laBuffer[2]

lnIncr = (lnDist)/69.13 && 1 degree = 69.13 miles

select this.distOnSphere(lnLat, lnLong, lat, long) as distance, d.ds_agency, d.ds_city, d.ds_state, d.ds_zip;

from drawsite d, zip z;

where d.ds_zip == z.zip_code and z.lat >= (lnLat - lnIncr) and z.lat <= (lnLat + lnIncr) and z.long >= (lnLong - lnIncr) and z.long <= (lnLong + lnIncr);
order by distance into array this.aDrawsite

function distOnSphere(lnLat1, lnLong1, lnLat2, lnLong2)

return 0.62*6378*acos(sin(dtor(lnLat1))*sin(dtor(lnLat2)) + cos(dtor(lnLat1))*cos(dtor(lnLat2))*cos(dtor(lnLong2-lnLong1)))

endfunc

 

[powerprinting]

I've got this one....

But SQL keeps hanging up on the statment 'SQUARE', Not sure if this is a valid function (square) or not. But other than that, it seems simple and easy to implement if I can get past that. Anyone know if Square is valid or not?

******************radius prg***********************

*<cfquery name="get_zip">
SELECT latitude_, longitude_;
FROM automaster WHERE zip = '45403';


radius = 10

miles_per_lat = 69.09
miles_per_lon = Abs(69.09 * Cos(Val(automaster.latitude_) * Pi() / 180))

deg_latitude = radius / miles_per_lat
deg_longitude = radius / miles_per_lon

*<cfquery name="get_businesses">
distance = ROUND(SQRT(SQUARE(miles_per_lat * (val(latitude_) - Val(automaster.latitude_))) + square(miles_per_lon * (val(longitude_) - Val(automaster.longitude_)))),1)
SELECT *;
FROM autobase WHERE val(latitude_) BETWEEN Val(Val(automaster.latitude_) - deg_latitude) AND Val(Val(automaster.latitude_) + deg_latitude)AND val(longitude_) BETWEEN Val(Val(automaster.longitude) - deg_longitude) AND Val(Val(automaster.longitude_) + deg_longitude)

 

[rgbean]

Assuming that SQUARE is meant to provide x[sup]2[/sup], then you could either create a FUNCTION:

FUNCTION SQUARE
LPARAMETER p_nBase
RETURN (p_nBase)^2

or you could just change the statement to:

distance = ROUND(SQRT((miles_per_lat * (val(latitude_) - Val(automaster.latitude_)))^2 ;
                   +(miles_per_lon * (val(longitude_) - Val(automaster.longitude_)))^2),1)

Rick

 

[powerprinting]

Thanks Rick! That might be what I wanted. I changed it a little cause all my lat and long fields are currently numeric.
I trial tested it with terrible results however.

The entire list was selected, not just 10 miles radius, from the final query. Needs work I guess.

*****************************
SELECT latitude_, longitude_;
FROM automaster WHERE zip = '45403'


RADIUS = 10

miles_per_lat = 69.09
miles_per_lon = Abs(69.09 * Cos(automaster.latitude_ * Pi() / 180))

deg_latitude = radius / miles_per_lat
deg_longitude = radius / miles_per_lon

distance = ROUND(SQRT((miles_per_lat * (automaster.latitude_ - automaster.latitude_))^2 ;
+(miles_per_lon * (automaster.longitude_ - automaster.longitude_))^2),1)
SELECT *;
FROM automaster WHERE latitude_ BETWEEN automaster.latitude_ - deg_latitude;
AND automaster.latitude_ + deg_latitude;
AND automaster.longitude_ ;
BETWEEN automaster.longitude_ - deg_longitude AND automaster.longitude_ + deg_longitude

 

[gentforreal]

that last looks workable with my limited knowledge, but doesnt there have to be a value for zip= nzip? Something looks wrong. Maybe I will work with it. thanks all fro trying

 

[powerprinting]

Aseem,

I'm trying to adapt your approach to this program as I've been having problems with other methods. My large database is automaster and contains the longitude_ and latitude coordinates. I'm getting hung up on the "select this " portion of the statment below. Any hints?

lczip = '45403'

select latitude_, longitude_ from _zip where zip == lcZip;
into array laBuffer

lnLat = laBuffer[1]
lnLong = laBuffer[2]

lnIncr = (lnDist)/69.13 && 1 degree = 69.13 miles
*********************************************************
select this.distOnSphere(lnLat, lnLong, inlat2, long2) as distance, automaster.name, automaster.city, automaster.state, automaster.zip;

**********************************************************
from drawsite d, zip z;

where automaster.zip == z.zip and z.lat >= (lnLat - lnIncr) and z.lat <= (lnLat + lnIncr) and z.long >= (lnLong - lnIncr) and z.long <= (lnLong + lnIncr);
order by distance into array this.aDrawsite

function distOnSphere(lnLat1, lnLong1, lnLat2, lnLong2)

return 0.62*6378*acos(sin(dtor(lnLat1))*sin(dtor(lnLat2)) + cos(dtor(lnLat1))*cos(dtor(lnLat2))*cos(dtor(lnLong2-lnLong1)))

endfunc

 

[gentforreal]

Jered.. its not pretty, but it seems to work. Try this

set deleted on
SET SAFETY OFF
SELECT automaster
select _zips
locate for zip =ZENTER3.Czip
store str(longitude,8,5) to long
store str(latitude,8,5) to lat
STORE SUBSTR(ZIP,1,5) TO MZIP
SELECT passedzip
REPLACE LATITUDE WITH LAT
REPLACE LONGITUDE WITH LONG
replace zip with MZIP
passedradius = val(DIST.DIST)

set talk off

SELECT zip,city,st,longitude,latitude, (ROUND((ACOS((SIN(val(passedzip.latitude)/57.2958) * SIN(val(passedzip.latitude)/57.2958)) + (COS(val(passedzip.latitude)/57.2958) * COS(val(passedzip.latitude)/57.2958) * COS(val(passedzip.longitude)/57.2958 - val(passedzip.longitude)/57.2958)))) * 3963,3)^2)+00000.00;
FROM automaster;
WHERE (val(latitude) >= val(passedzip.latitude) - (passedradius/111));
And (val(latitude) <= val(passedzip.latitude) + (passedradius/111));
AND (val(longitude) >= val(passedzip.longitude) - (passedradius/111));
AND (val(longitude) <= val(passedzip.longitude) + (passedradius/111));
group by zip;
ORDER BY zip
clear
set talk on
thisform.refresh

 

[adventurous1]

I am trying to do something very similar in Oracle. Does anyone have any thoughts on how this would look in the Oracle flavor of SQL?

 

[gentforreal]

I'll look through my notes.. it seems I saw an examle in oracle. This program has been a pain in the arse to me. LOL

 

[powerprinting]

check this site.

http://download-east.oracle.com/otn_hosted_doc/intermedia/inter.816/intermedia_locator/geo_objr.htm

I think its what you are looking for.

 

[amersanjak]

Try this
r = 3956 && Radios
Distance= = r * acos(sin(DTOR(lat1)) * sin(DTOR(lat2)) + cos(DTOR(lat1)) * cos(DTOR(lat2)) * cos(DTOR(lon2) - DTOR(lon1)))