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 longitude and latitude coordinates of a school and returns an SDO_GEOMETRY instance as its location.
-- define the loc_to_geom(longitude, latitude) function
create or replace function loc_to_geom(longitude number, latitude number)
return sdo_geometry deterministic is
BEGIN
if longitude is not null and latitude is not null then
begin
RETURN sdo_geometry(2001,
4326,
sdo_point_type(longitude, latitude, 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.LOC_TO_GEOM(LONGITUDE,LATITUDE)',
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>.LOC_TO_GEOM’
Step 3 is create a function-based spatial index using LOC_TO_GEOM()
create index sch2geom_sidx on preschools(loc_to_geom(longitude, latitude)) 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. We need a join here because we want to get the longitude and latitude for the Iroha school to pass into the second “loc_to_geom” function.
Select s.name, s.id from preschools s, preschools i where
i.name = 'Iroha' and
sdo_within_distance(loc_to_geom(s.longitude, s.latitude),
loc_to_geom(i.longitude, i.latitude), 'distance=5 unit=km')='TRUE' and s.name <> 'Iroha';
NAME ID
________________ _____
Dreamkids 4
Brainy Bunch 15
Divinity 3
Bethel 18
CS Montessori 1
-- Return the distance in KM too
Select s.name, s.id,
sdo_geom.sdo_distance(loc_to_geom(s.longitude, s.latitude),
loc_to_geom(i.longitude, i.latitude), 0.5, 'unit=km') DistInKM from preschools s, preschools i
where
i.name='Iroha' and
sdo_within_distance(loc_to_geom(s.longitude, s.latitude),
loc_to_geom(i.longitude, i.latitude), 'distance=5 unit=km')='TRUE' and s.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 s.name from preschools s, preschools i
where
i.name = 'Barker Road' and
sdo_nn(loc_to_geom(s.longitude, s.latitude),
loc_to_geom(i.longitude, i.latitude), 'sdo_num_res=5 sdo_batch_size=10')='TRUE' and s.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 s.name, sdo_nn_distance(1) distInKM from preschools s, preschools i where
i.name = 'Barker Road' and
sdo_nn(loc_to_geom(s.longitude, s.latitude),
loc_to_geom(i.longitude, i.latitude), 'sdo_num_res=5 sdo_batch_size=10 unit=km', 1)='TRUE'
and s.name <> 'Barker Road';
NAME DISTINKM
______________ ___________________
Gracefields 2.3383253177357
Bibinogs 2.68602143954703
Grace House 3.4096128337165
Callidus 4.43670650377725
And there we have it.