Find distance between two coordinates using latitude and longitude

I came across a very interesting one recently on how to find distance between two coordinates using latitude and longitude without having to use google map API’s.  The solution I am using is based on Oracle Database using Oracle functions but there are plenty of posts out there explaining it in MySQL too.

This function (I have used it in one of my pieces of development for this) is also useful for working out all points within a specific radius of one point without the need to do it the slow way through a Google API – I will explain that solution too using PHP(WordPress) and Oracle.

Firstly lets define the two points that we want to use, for this example I am going to use Buckingham Palace and the Empire State building.

Buckingham Palace lat/long 51.501364,-0.141890
Empire State lat/long 40.748441,-73.985664

The query for this is below

SELECT sdo_geom.sdo_distance(sdo_geom.sdo_geometry
                      (2001, 8307, sdo_geom.sdo_point_type
                         (-0.141890,51.501364, NULL), NULL, NULL),
       sdo_geom.sdo_geometry(2001, 8307, 
           sdo_point_type(-73.985664,40.748441, NULL), NULL, NULL), 0.0001, 'unit=KM') distance 
from DUAL;

The query gives the below output and the distance in KM

Two main points to note here that

  1. For this Oracle function the latitude and longitude are reversed and
  2. The unit of measurement can be changed at ‘unit=’ to either M(Metres), KM Kilometres or MILES (Self-explanatory)

How can this be used to find a radius for all locations within a specific radius?

Well now we know this function it isn’t that difficult, the only pre-requisite is you have a table with all your locations and columns containing latitude and longitude.

We will work on an example where we have a table called TAB_LOCATIONS with fields in LOC_NAME, LAT and LON, so to return all locations within a 100 km radius of say Buckingham Palace (latitude and longitude are above) the query in Oracle would be as below.

	    FROM tab_locations
      where sdo_geom.sdo_distance(sdo_geom.sdo_geometry
              (2001, 8307, sdo_geom.sdo_point_type(LON, LAT, NULL), NULL, NULL),
            sdo_geom.sdo_geometry(2001, 8307, sdo_point_type
            (-0.141890,51.501364, NULL), NULL, NULL), 0.0001, 'unit=KM') < 100);

Originally I was trying to get distances using Googles distance matrix API but for 2000+ locations this was untenable and taking ages, I ran using this query in seconds.

Hope this helps, please share or comment if you found it useful!







IT consultant with 20+ years experience specialising in Oracle Database, Oracle Business Intelligence, Web/Mobile development, Application Express development, cloud technology and more

Leave a Reply

Your email address will not be published. Required fields are marked *