Re: database function to get distance between 2 latitude/longitude points
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, dd.yakkali@xxxxxxxxx
- Date: Fri, 1 May 2009 17:07:25 -0700 (PDT)
Based on what deen described to me off-line I put this query together given the
schema below.
one table with zip and associated center point of that zip.
one table with zips you are evaluating
one table with the starting zip and the radius that all the other zips should
be tested against:
create table zipcode (zip number, lat number, lon number);
insert into zipcode values (45066, 15,16);
insert into zipcode values (45067, 15,16.1);
insert into zipcode values (45068, 15,16.2);
insert into zipcode values (45069, 15,16.3);
insert into zipcode values (45070, 15,16.4);
insert into zipcode values (45071, 15,16.5);
create table t1 (zip number);
insert into t1 values (45066);
insert into t1 values (45067);
insert into t1 values (45068);
insert into t1 values (45069);
create table t2 (zip number, radius number);
insert into t2 values (45067, 5);
insert into t2 values (45068, 6);
insert into t2 values (45069, 30);
select t1zip,t2zip,radius,dist from (
SELECT t1_alias.zip t1zip,t2_alias.zip t2zip, t1lat, t1lon, t2lat, t2lon,
radius,
SDO_GEOM.SDO_DISTANCE(sdo_geometry(2001, 8307, sdo_point_type(t1lon, t1lat,
NULL),NULL, NULL),sdo_geometry(2001, 8307, sdo_point_type(t2lon, t2lat,
NULL),NULL, NULL),0.05,'unit=KM') dist
FROM
(select t1.zip, lat t1lat, lon t1lon from zipcode,t1 where t1.zip=zipcode.zip)
t1_alias,
(select t2.zip, lat t2lat, lon t2lon,radius from zipcode,t2 where
t2.zip=zipcode.zip) t2_alias
where t1_alias.zip<>t2_alias.zip
)
where radius > dist
----
comments or improvements welcome..
--- On Wed, 4/29/09, dd yakkali <dd.yakkali@xxxxxxxxx> wrote:
> From: dd yakkali <dd.yakkali@xxxxxxxxx>
> Subject: database function to get distance between 2 latitude/longitude points
> To: oracle-l@xxxxxxxxxxxxx
> Date: Wednesday, April 29, 2009, 9:37 AM
> Has anybody written a database funtion to get this?
>
> I got this code from
> http://www.movable-type.co.uk/scripts/latlong.html
>
> R = earth's radius (mean radius = 6,371km)
> Δlat = lat2- lat1
> Δlong = long2- long1
> a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
> c = 2.atan2(√a, √(1-a))
> d = R.c
>
>
> I am not a math guy, i am just wondering has any one
> written any db function
> for this.
>
> thanks
> Deen
--
//www.freelists.org/webpage/oracle-l
Other related posts:
