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) );