X

Oracle Spatial and Graph – technical tips, best practices, and news from the product team

Performing spatial analyses on Latitude, Longitude data in Oracle Database

Jayant Sharma, and David Lapp

Many applications use GPS coordinates and other location information that is published as a pair of Longitude and Latitude or X and Y values. A frequent question of late on various forums has been: "We have the locations of our sites in a table with Latitude and Longitude as separate columns. How do we find sites that are say within 10 miles from a given site?".

This post shows how to do that, and other spatial queries, using function-based indexes and spatial operators in an Oracle database. 

First, let's create a table and insert some data into it. 

Note: The following SQL statements were executed using SQLcl 

-- create a table of PRESCHOOLS
Create table preschools (
Id number, 
Name   varchar2(64),
Longitude  number,
Latitude  number) ;
-- insert some sample data  Insert into preschools values(1, 'CS Montessori', 103.871862, 1.35067551);
Insert into preschools values(2, 'Dayspring',  103.898939, 1.41490187);
Insert into preschools values(3, 'Divinity', 103.890186, 1.33249633);
Insert into preschools values(4, 'Dreamkids', 103.903339, 1.30438467);
Insert into preschools values(5, 'Elite Learning', 103.697497, 1.34435137);
Insert into preschools values(6, 'Evelyns', 103.701169, 1.3461552);
Insert into preschools values(7, 'Grace House', 103.816386, 1.29430568);
Insert into preschools values(8, 'Gracefields', 103.824479, 1.30058938);
Insert into preschools values(9, 'Hanis', 103.93705, 1.37406387);
Insert into preschools values(10, 'Iroha', 103.880829, 1.30800945);
Insert into preschools values(11, 'Jenius', 103.883566, 1.37757028);
Insert into preschools values(12, 'ACES', 103.953599, 1.37714073);
Insert into preschools values(13, 'Artskidz', 103.831883, 1.37880334);
Insert into preschools values(14, 'Banyan Tree', 103.87539, 1.37792345);
Insert into preschools values(15, 'Brainy Bunch', 103.891524, 1.32313886);
Insert into preschools values(16, 'Buttercups', 103.788347, 1.30535122);
Insert into preschools values(17, 'Callidus', 103.845963, 1.35760846);
Insert into preschools values(18, 'Bethel', 103.886673, 1.32176645);
Insert into preschools values(19, 'Bibinogs', 103.810676, 1.32313886);
Insert into preschools values(20, 'Barker Road', 103.834489, 1.31918222);
-- commit; commit;

Now we can perform the necessary steps to

  1. Define a function which returns an SDO_GEOMETRY instance constructed using the Longitude and Latitude values for a preschool.
  2. Insert the metadata needed for using spatial operators and indexes.
  3. Create a function-based index.
  4. Execute spatial queries.

Step 1 is to define a function that accepts a preschool name and returns an SDO_GEOMETRY instance which is the location of that school.

-- define the sch_name_to_geom(name) function create or replace function sch_name_to_geom(aName in varchar2)
return sdo_geometry deterministic is
lon number;
lat number;
BEGIN
if aName is not null then
begin
execute immediate
'select longitude, latitude from preschools where name=:1'
into lon, lat
using aName;
RETURN sdo_geometry(2001,
4326,
sdo_point_type(lon, lat, NULL),
NULL,
NULL);
exception when OTHERS then RETURN NULL;
end;
else RETURN null;
end if;
END;
/
 
show errors;

Step 2 is inserting the necessary metadata into the USER_SDO_GEOM_METADATA view. This is needed for creating spatial indexes and using the spatial operators in queries. 

insert into user_sdo_geom_metadata values('PRESCHOOLS', 'STUDIO_HOL.SCH_NAME_TO_GEOM(name)',
sdo_dim_array(sdo_dim_element('Longitude', -180, 180, 0.5),
sdo_dim_element('Latitude', -90, 90, 0.5)), 4326);
Commit;

-- NOTE: This example was done in STUDIO_HOL schema. Replace that with your specific schema/user

-- i.e. '<your_schema>.SCH_NAME_TO_GEOM'

Step 3 is create a function-based spatial index using SCH_NAME_TO_GEOM()

create index sch2geom_sidx on preschools(sch_name_to_geom(name)) indextype is mdsys.spatial_index_v2; -- query the spatial index metadata view to verify it was created 
 
select table_name, index_name from user_sdo_index_info ;                   TABLE_NAME                   INDEX_NAME 
____________________________ ____________________________ 
            PRESCHOOLS                   SCH2GEOM_SIDX                

Now we can perform some spatial queries. 

Find preschools that are within 5 Km of the 'Iroha' school. 

Select name, id from preschools where sdo_within_distance(sch_name_to_geom(name), sch_name_to_geom('Iroha'), 'distance=5 unit=km')='TRUE' and name <> 'Iroha';
NAME                ID 
________________    _____ 
Dreamkids           4  
Brainy Bunch        15 
Divinity            3 
Bethel              18 
CS Montessori       1 

-- Return the distance in KM too

Select name, id, sdo_geom.sdo_distance(sch_name_to_geom(name), sch_name_to_geom('Iroha'), 0.5, 'unit=km') DistInKM from preschools where 
sdo_within_distance(sch_name_to_geom(name), sch_name_to_geom('Iroha'), 'distance=5 unit=km')='TRUE' and name <> 'Iroha';

NAME              ID            DISTINKM 
________________  _____ ___________________ 
Dreamkids          4   2.53701589337829 
Brainy Bunch       15   2.0531435455936 
Divinity           3    2.90097770539312 
Bethel             18   1.65438174282452 
CS Montessori      1    4.82218375759093 
 

Find the 4 nearest schools to the 'Barker Road' school. 

select name from preschools where
sdo_nn(sch_name_to_geom(name), sch_name_to_geom('Barker Road'), 'sdo_num_res=5 sdo_batch_size=10')='TRUE' and name <> 'Barker Road';

NAME 
______________ 
Gracefields    
Bibinogs       
Grace House    
Callidus       
 

Note that 5 preschools are returned by the sdo_nn() operator but we omit the 'Barker Road' itself school so the final result is 4 nearest preschools.

 

Now let's also get the (as the crow flies) distance of these preschools. 

select name, sdo_nn_distance(1) distInKM from preschools where
sdo_nn(sch_name_to_geom(name), sch_name_to_geom('Barker Road'), 'sdo_num_res=5 sdo_batch_size=10 unit=km', 1)='TRUE' and name <> 'Barker Road';

NAME            DISTINKM 
______________  ___________________ 
Gracefields      2.3383253177357 
Bibinogs         2.68602143954703
Grace House      3.4096128337165 
Callidus         4.43670650377725 
 

And there we have it.
 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.