OracleDV : Calculating distance using latitude/Longitude

In this blog we will talk about how to compute distance between two points using latitude and longitude using inbuilt functions in Oracle DV. In Geospatial Analysis, requirement to compute distance between two points using latitude and longitude is quite prevalent. Haversine formula is frequently used to calculate distance between two points on earth using latitudes and longitudes. Haversine formula computes great circle distance(distance as measured along the surface of earth/sphere rather than the distance through the sphere/earth). This formula is based on a generic formula in Spherical trignometry, called law of haversines. Following is the formula:

* snapshot taken from Movable Type Script site

Following is the calculation in OracleDV to compute the distance between two lat longs using Haversine formula:

CASE 
WHEN Source_Lat=Dest_Lat AND Source_Long=Dest_Long 
THEN 0 
ELSE 
ACOS(
  COS(RADIANS(90-Source_Lat)) 
* COS(RADIANS(90-Dest_Lat)) 
+ SIN(RADIANS(90-Source_Lat))
* SIN(RADIANS(90-Dest_Lat))
* COS(RADIANS(Source_Long-Dest_Long))  )
* 6371 
END

In this formula:
Source_lat refers to Source Latitude
Source_Long refers to Source Longitude
Dest_Lat refers to Destination Latitude
Dest_Long refers to Destination Longitude

Please note that Source and destination are used only for naming convenience, they can actually be used interchangeably. Distance computed using lat long may differ from the actual driving distance between two points depending on various factors such as road connectivity and presence of other geographic bodies. Here is a snapshot of the project on Oracle DV Desktop.

More information on Haversine formula can be found here.

Applications: Ability of OracleDV to handle such distance calculated formulae demonstrates the capability of DV to perform spatial analytic operations which involve calculating the number of stores/customers within a radius of certain driving distance etc. To demonstrate this capability better we have implemented a sample project using this formula to find out what are the establishments within 2 mile radius of WESTERN STATE BANK. Here is a snapshot:



We have attached the .dva project as well. You can download it from here and play with it.

Are you an Oracle Analytics customer or user?

We want to hear your story!

Please voice your experience and provide feedback with a quick product review for Oracle Analytics Cloud!