I am trying to find all the records that are with in a given distance of a Lat/Long point. I am successful at doing this but I need the result set to come back faster. Do you have any suggestions on how I could do this different in order to get the data back with in 2 or 3 seconds?
I am running
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
I have the following table:
CREATE TABLE FACILITY_LOCATION
(
FACILITY_LOCATION_ID NUMBER NOT NULL,
FACILITY_ID NUMBER(11) NOT NULL,
LATITUDE NUMBER,
LONGITUDE NUMBER,
ADDRESS1 VARCHAR2(1000 BYTE),
ADDRESS2 VARCHAR2(1000 BYTE),
CITY VARCHAR2(100 BYTE),
STATE VARCHAR2(25 BYTE),
COUNTY VARCHAR2(50 BYTE),
COUNTRY VARCHAR2(50 BYTE),
ZIPCODE VARCHAR2(10 BYTE),
);
Currently there are 112,400 records in this table and growing.
I am executing the following sql statement:
SELECT DISTINCT FACILITY_ID AS facid
FROM facility_location
where round(distance(latitude, longitude, p_lat, p_long), 2) < p_distFromPoint
Which calls the function "distance":
CREATE OR REPLACE FUNCTION UCIIS."DISTANCE" (Lat1 IN NUMBER,
Lon1 IN NUMBER,
Lat2 IN NUMBER,
Lon2 IN NUMBER,
Radius IN NUMBER DEFAULT 3963) RETURN NUMBER IS
-- Convert degrees to radians
DegToRad NUMBER := 57.29577951;
ReturnValue NUMBER;
ACOS_Param NUMBER;
BEGIN
ACOS_Param := (sin(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad)) +
(COS(NVL(Lat1,0) / DegToRad) * COS(NVL(Lat2,0) / DegToRad) *
COS(NVL(Lon2,0) / DegToRad - NVL(Lon1,0)/ DegToRad));
-- Check if greater than 1 due to floating point errors
IF ACOS_Param > 1 THEN
ACOS_Param := 1;
END IF;
-- Check if less than -1 due to floating point errors
IF ACOS_Param < -1 THEN
ACOS_Param := -1;
END IF;
ReturnValue := NVL(Radius,0) * ACOS(ACOS_Param);
RETURN ReturnValue;
END;
Currently it takes 18 seconds for the result set to be returned. Any suggestions what I can do to obtain the result set faster.
Thanks!!