SQL & PL/SQL

Where is your code.. Can you please post it here.?

Cheers,

Manik.

I assume you mean you have two points in lat/long, and want to know how far it is between them.

Most editions of Oracle come with no-cost spatial utilities known as Oracle Locator. Consequently, you can probably just do something like this:

select

        sdo_geom.sdo_distance

                (

                sdo_geometry

                        (

                        -- this identifies the object as a two-dimensional point.

                        2001,

                        -- this identifies the object as using the GCS_WGS_1984 geographic coordinate system.

                        4326,

                        null,

                        sdo_elem_info_array(1, 1, 1),

                        -- this is the longitude and latitude of point 1.

                        sdo_ordinate_array(151.20208, -33.883741)

                        ),

                sdo_geometry

                        (

                        -- this identifies the object as a two-dimensional point.

                        2001,

                        -- this identifies the object as using the GCS_WGS_1984 geographic coordinate system.

                        4326,

                        null,

                        sdo_elem_info_array(1, 1, 1),

                        -- this is the longitude and latitude of point 2.

                        sdo_ordinate_array(151.195986, -33.87266)

                        ),

                1,

                'unit=KM'

                ) distance_km

from

        dual

Check this link...

http://www.mrexcel.com/forum/excel-questions/202255-calculating-distance-between-two-latitude-longitude-points.html

I never did this before, but gave it a try using the formula mentioned in that link..

Data:

---------------------------------------------------------------------------

                   Lat                      Long                               R =  6,371 (Radius of earth (approximated)) 

Origin:        44.844263(B2)      -92.914803(C2) 
Destination: 44.822075(B3)     -92.912498(C3)

-----------------------------------------------------------------------------------------
Formula used:

A: =SIN(ABS(B3-B2)*PI()/180/2)^2+COS(B2*PI()/180)*COS(B3*PI()/180)*SIN(ABS(C3-C2)*PI()/180/2)^2

B: =2*ATAN2(SQRT(1-A),SQRT(A))

C: =R*B                  --->  DISTANCE!!!!

----------------------------------------------------------------------------------
WITH t AS
        (SELECT POWER (
                   SIN (ABS (44.822075 - 44.844263) * ( (22 / 7) / 180 / 2)),
                   2)
                + COS (44.844263 * ( (22 / 7) / 180))
                  * COS (44.822075 * ( (22 / 7) / 180))
                  * POWER (
                       SIN (
                          ABS (-92.912498 - (-92.914803))
                          * ( (22 / 7) / 180 / 2)),
                       2)
                   E2
           FROM DUAL)
SELECT (2 * ATAN2 (SQRT ( (1 - E2)), SQRT (E2))) * 6371
  FROM t;

---------------------------------------------------------------------------------------------------------------------

Check if this gives correct values... (I did not verify this properly.. ) And this is faster in my opinion..

Please post your code for better suggestions from volunteers...

Cheers,

Manik.

{code}

        SELECT

                EVENT_ID,

                DRIVE_ID,

                LOCATION_ID,

                B.LAT AS LAT1,

                B.LONG AS LON1,

                YCOORD AS LAT2,

                XCOORD AS LON2,

                (0.0174532925) AS RADIAN,

                (6371) AS RADIUS,

                (1.60934) AS MILES,

                CAST(((LAT2-LAT1)*(RADIAN)) AS NUMERIC(18,6)) AS DLAT,

                CAST(((LON2-LON1)*(RADIAN)) AS NUMERIC(18,6)) AS DLON,

                CAST(((LAT1)*(RADIAN)) AS NUMERIC(18,6)) AS RLAT1,

                CAST(((LAT2)*(RADIAN)) AS NUMERIC(18,6)) AS RLAT2,

                (SIN(DLAT/2) * SIN(DLAT/2) + SIN(DLON/2) * SIN(DLON/2) * COS(RLAT1) * COS(RLAT2)) AS EQA,

                CAST(((2 * ATAN2(SQRT(EQA),SQRT(1-EQA)))*RADIUS*MILES) AS NUMERIC(18,6)) AS DISTANCE_MILES

        FROM TMP_DRIVES_LOCATION A

        INNER JOIN

        TMP_CIQ_DATA_SAMPLE B

        ON A.YCOORD BETWEEN B.MIN_LAT AND B.MAX_LAT

        AND A.XCOORD BETWEEN B.MIN_LONG AND B.MAX_LONG{CODE}

When doing this calculation, it's worth bearing in mind that the Earth is not a sphere, so calculations based on simple trigonometry will be, to some extent, inaccurate. This might or might not matter, depending on what you're trying to achieve.

The Locator functions use coordinate systems that model the Earth as a spheroid, which reduce this inaccuracy to essentially zero.

it looks like your lat and long are numbers.  you'll need to convert them into sdo_geometry objects like 983181

mentions and then do a distance calculation on it properly

I have written stuff for aviation and gaming, and have used the formulas at Aviation Formulary V1.46 a fair bit. If you want an accurate distance calculation you need to take the curvature of the earth into consideration. Have a look at the great circle calculation for distance between two points.