Oracle Spatial and Graph – technical tips, best practices, and news from the product team
-
Spatial Features
April 8, 2020
Performing spatial analyses on Latitude, Longitude data in Oracle Database
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
- Define a function which returns an SDO_GEOMETRY instance constructed using the Longitude and Latitude values for a preschool.
- Insert the metadata needed for using spatial operators and indexes.
- Create a function-based index.
- 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.