Calculate distances in Oracle

Oracle allows you to calculate a distance between two points. Such calculation is not trivial as one must take into account that distances are calculated over the globe and the points are indicated on a longitude – latitude base. If we calculate the distance between longitude, latitude =(0,0) and (1,1), one has about 156 kilometers. In that case, we are calculating a distance between two points in the Atlantic Ocean, somewhere along the coast of Ghana. However, if we calculate the distance between one degree longitude and and one degree latitude on the North Pole, we only have 111 kilometers.
Oracle allows you to calculate such distances. The code to do so is given below as an example:

SELECT sdo_geom.sdo_distance(sdo_geom.sdo_geometry(2001, 8307, sdo_geom.sdo_point_type(4.7172715,52.030588, NULL), NULL, NULL),
                           sdo_geom.sdo_geometry(2001, 8307, sdo_point_type(-0.1223527,51.49065, NULL), NULL, NULL), 0.0001, 'unit=KM') distance_in_km
                           from DUAL;

The points are given as:
sdo_geom.sdo_geometry([gridtype], [points in latitude, longitude], sdo_geom.sdo_point_type([latitude][longitude], NULL), NULL, NULL).
The function to calculate distance is given as sdo_geom.sdo_distance(point A, point B, 0,0001,’unit=[KM]’).

It is also possible to store these points in a database. Oracle has a sdo_geometry type that allows to store point on a latitude, longitude base. An example:

  plek_id NUMBER,
  description VARCHAR2(100),
  store_geo_location SDO_GEOMETRY);
  (1,'Tom Gouda',
   SDO_GEOMETRY(2001, 8307, 
     SDO_POINT_TYPE (4.7172715,52.030588,NULL),NULL,NULL));
  (2,'Tom Londen', 
   SDO_GEOMETRY(2001, 8307, 
     SDO_POINT_TYPE (-0.1223527,51.49065,NULL),NULL,NULL));

A table store is defined with a field store_geo_location. Its field type is SDO_GEOMETRY. Values must be added as SDO_GEOMETRY(,,SDO_POINT_TYPE(,,),,,).
The distance can then be calculated as:

SELECT a.description,b.description,SDO_GEOM.SDO_DISTANCE(a.store_geo_location, b.store_geo_location, 0.0001, 'unit=KM') as dist_in_KM
   FROM stores a, stores b
   where a.plek_id>b.plek_id;

This query showed a direct fly distance of 339 kilometers between Tom_Gouda and Tom_London.

A final word.
The question is where such latitude, longitude data can be found. As always, google is the master. If a certain point in google maps is found, the latitude, longitude can be found in the URL. One sees several such pairs in the URL, corresponding to different points on the map. However if one zooms in, the pairs do not deviate as the map only covers a small area. The first pair can be read, directly after the @ sign. For example, my home can be found as having this URL:,+2804+PW+Gouda/@52.0306982,4.717726. After the @, the longitude and latitude are displayed.