PL/SQL & Yahoo PlaceFinder API - Getting the distance from a location

Here is a quick proof of concept I wrote for our campus to help us augment our need to determine how many students are living within a mile of campus.

You can change what base lat & long are used.

Once you've created this function in the database, it can be called by simply issuing a select with the paramenters:

select miles_from_campus('999 Place St', 'Chico', 'CA') from dual;

Or you can join it to a table with the address info.

More Placefinder API info can be found here


CREATE OR REPLACE FUNCTION MILES_FROM_CAMPUS
(
ADDR VARCHAR2
,CITY VARCHAR2
,STATE VARCHAR2
) RETURN NUMBER AS

BASE_URL VARCHAR2(1000) := 'http://where.yahooapis.com/geocode?';
FULL_REST_REQ VARCHAR2(4000);

REQ UTL_HTTP.REQ;
RESP UTL_HTTP.RESP;

V_RESP_BOD VARCHAR2(32767);

V_RESP_BOD_XML XMLTYPE;

XML_LONG VARCHAR2(100);
XML_LAT VARCHAR2(100);
XML_QUAL NUMBER;

--Radius of Earth
RADIUS NUMBER := 3963;

-- Convert degrees to radians
DEGTORAD NUMBER := 57.29577951;

DIST_FROM_CAMPUS NUMBER := 99999999;

--CSU Chico Lat & Long
LAT1 NUMBER := 39.729708;
LON1 NUMBER := -121.844741;

LAT2 NUMBER;
LON2 NUMBER;

ADDR_FMT VARCHAR2(1000);
CITY_FMT VARCHAR2(100);

BEGIN

-- remove # information
ADDR_FMT := TRIM(REGEXP_REPLACE(ADDR, '(apt((\.)?( )?)+).*|( )?#( )?.*', '', 1, 1, 'i'));
--replace spaces with +
ADDR_FMT := REGEXP_REPLACE(ADDR_FMT, '( )+', '+');

--if city has spaces (e.g. Los Molinos) change to Los+Molinos
CITY_FMT := REGEXP_REPLACE(CITY, '( )+', '+');

-- Prepare REST request
FULL_REST_REQ := BASE_URL || 'line1=' || ADDR_FMT || '&' || 'line2=' || CITY_FMT || '+' || STATE || '&' || 'appid=9999999' || '&' || 'count=1' || '&' ||
'flags=CE' || '&' || 'gflags=Q';

BEGIN
REQ := UTL_HTTP.BEGIN_REQUEST(FULL_REST_REQ, 'GET');

-- request/get response
RESP := UTL_HTTP.GET_RESPONSE(R => REQ);

IF (RESP.STATUS_CODE = 200)
THEN

UTL_HTTP.READ_TEXT(RESP, V_RESP_BOD, 32767);

V_RESP_BOD_XML := XMLTYPE(V_RESP_BOD);
UTL_HTTP.END_RESPONSE(RESP);

SELECT VLAT, VLONG, VQUAL
INTO XML_LAT, XML_LONG, XML_QUAL

FROM XMLTABLE('/ResultSet/Result' PASSING V_RESP_BOD_XML COLUMNS VLAT VARCHAR2(100) PATH '//latitude'
,VLONG VARCHAR2(100) PATH '//longitude'
,VQUAL NUMBER PATH '//quality'

) B;
IF XML_QUAL > 50
THEN
LAT2 := XML_LAT;
LON2 := XML_LONG;

DIST_FROM_CAMPUS := (NVL(RADIUS, 0) *
ACOS((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))));
END IF;

END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(RESP.STATUS_CODE || ' - ' || RESP.REASON_PHRASE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_HTTP.END_RESPONSE(RESP);

END;

RETURN DIST_FROM_CAMPUS;

EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(RESP.STATUS_CODE || ' - ' || RESP.REASON_PHRASE);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
UTL_HTTP.END_RESPONSE(RESP);
RETURN DIST_FROM_CAMPUS;
END;