Oracle Database standard Geo Location Support using Locator (included in every edition!)

Many databases have native support for locations en geodata – and determining distance and closest locations (within a certain perimeter). Oracle Database has the [Graph and] Spatial Option – that supports even the most advanced and exotic forms of location related data querying (including multidimensional shapes and probably relativistic effects); this option comes on top of Enterprise Edition and carries additional costs. What may be not as well know is the Locator functionality that is part of every edition of the Oracle Database – including XE and SE, (without any additional costs) – with geo support as found in most databases. In this article I will give a very brief introduction of what this Locator feature can be used for.

See for extensive documentation on Locator:  Oracle Database 12c Documenation – Locator (and http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_locator.htm#SPATL340 for Oracle Database 11g).

I will assume the legacy data model of DEPT and EMP (download DDL script for creating SCOTT’s database schema objects: scott_build.sql).

1. Prepare a table for Geo Spatial Data

— add geospatial data for departments (longitude, lattitude)

alter table dept
add (geo_location SDO_GEOMETRY)

SDO_GEOMETRY is an object type that describes and supports any type of geometry. Examples of SDO_GTYPE values include 2001 for a two-dimensional point. The SRID value 8307 is associated with the widely used WGS84 longitude/latitude coordinate system.

2. Add geo information to records in table

Now that a column has been added to hold the SDO_GEOMETRY object, we can start loading location data into the table.

update dept
set    geo_location = SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE (-96.8005, 32.7801,NULL), NULL, NULL)
where  loc = 'DALLAS'

update dept
set    geo_location = SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE (-73.935242, 40.730610,NULL), NULL, NULL)
where  loc = 'NEW YORK'

update dept
set    geo_location = SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE ( -71.0598, 42.3584,NULL), NULL, NULL)
where  loc = 'BOSTON'

update dept
set    geo_location = SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE (-87.6298, 41.8781,NULL), NULL, NULL)
where  loc = 'CHICAGO'

3. Prepare meta data in USER_SDO_GEOM_METADATA

For each spatial column (type SDO_GEOMETRY), you must insert an appropriate row into the USER_SDO_GEOM_METADATA view to reflect the dimensional information for the area in which the data is located. You must do this before creating spatial indexes

-- The USER_SDO_GEOM_METADATA view has the following definition:
-- (   TABLE_NAME   VARCHAR2(32),
--  COLUMN_NAME  VARCHAR2(32),
--  DIMINFO      SDO_DIM_ARRAY,
--  SRID         NUMBER
--);

-- insert dimensional information for the  spatial column
-- the dimensional range is the entire Earth, and the coordinate system is the widely used WGS84 (longitude/latitude) system (spatial reference ID = 8307

INSERT INTO USER_SDO_GEOM_METADATA 
(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
VALUES ('DEPT', 'GEO_LOCATION', 
   SDO_DIM_ARRAY 
     (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5), 
     SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)), 
   8307);

4. Create the Geo Spatial Index

Create index on the column geo_location that holds the SO_GEOMETRY object:

CREATE INDEX dept_spatial_idx 
ON dept(geo_location)
INDEXTYPE IS mdsys.spatial_index;

5. Start querying with Location based conditions

List all departments, ordered by their distance from Washington DC

SELECT d.loc
,      SDO_GEOM.SDO_DISTANCE
       ( SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE ( -77.0364, 38.8951,NULL), NULL, NULL) /* Washington DC */
       , d.geo_location
       , 0.005
       , 'unit=KM'
       ) "distance from Washington"
from   dept d
order 
by     2

We find all departments  within 500 km from Washington DC and get the distance for each department in the property distance in km :

with d as
( SELECT d.loc
  ,      SDO_GEOM.SDO_DISTANCE
         ( SDO_GEOMETRY(2001, 8307,SDO_POINT_TYPE ( -77.0364, 38.8951,NULL), NULL, NULL)
         , d.geo_location
         , 0.005
         , 'unit=KM'
         ) distance
  from   dept d
  order 
  by     2
)
select d.*
from   d
where  d.distance < 500

Find two closest neighbouring departments for NEW YORK:

SELECT /*+ LEADING(d) INDEX(dn dept_spatial_idx)  */ 
       d.deptno,
       d.loc,
       dn.deptno neighbour_deptno,
       dn.loc neighbour_loc,
       sdo_nn_distance (1) distance
FROM   dept d
       cross join
       dept dn
WHERE  d.deptno = 10 /* NEW YORK */
and    dn.deptno !=  10
AND    sdo_nn /* is dn in set of 3 closest neighbours to d */
       (dn.geo_location, d.geo_location, 'sdo_num_res=3', 1) = 'TRUE'
ORDER 
BY    distance;

(note: the hint in the first line is not required on Oracle Database 12c, but it is on 11g – see forum thread) Here are examples for the use of the SDO_NN operator.

Distance matrix, using pivot:

with distances as
(SELECT /*+ LEADING(d) INDEX(dn dept_spatial_idx)  */ 
       d.deptno,
       d.loc,
       dn.deptno neighbour_deptno,
       dn.loc neighbour_loc,
       trunc(sdo_nn_distance (1)) distance
FROM   dept d
       cross join
       dept dn
WHERE  sdo_nn /* is dn in set of 3 closest neighbours to d */
       (dn.geo_location, d.geo_location, 'sdo_num_res=3 unit=km', 1) = 'TRUE'
)
SELECT *
FROM   (SELECT loc, neighbour_loc, distance distance
        FROM   distances)
PIVOT  ( max(distance) AS distance 
         FOR (neighbour_loc) 
         IN ('NEW YORK' AS NEWYORK, 'BOSTON' AS BOSTON, 'CHICAGO' AS CHICAGO, 'DALLAS' as DALLAS)
        );