In my “Spatially Enabling Your Oracle Business Intelligence Solution” presentation today a question was asked about free geocoding services. The Navteq and TeleAtlas spatial data is expensive and there are some free alternatives out there. One of those options for US data is the website geocoder.us . The data is gathered from the US census data and is definitely not as accurate as the commercial providers, it does an extremely good job at making it easy to geocode your data and is free for non-commercial purposes. Thats right FREE!!! If you do decide to use it for commercial purposes their pricing is more than reasonable in my opinion, as of today they charge $50 US Dollars to geocode 20,000 addresses. There developer documentation is available online and they provide four different web service interfaces to access the data (http://geocoder.us/help/). As promised, here is a simple function that uses their CSV webservice to geocode an address and return it as the Oracle Spatial native datatype sdo_geometry.
CREATE OR REPLACE FUNCTION geocode_address_geocoder_us (
street varchar2 --Includes street number and street name
,city varchar2 --Name of the city for the address
,state varchar2 --US Standard Postal Abbreviation for the state
-- Official List: http://www.usps.com/ncsc/lookups/abbr_state.txt
,zipcode varchar2 --Either the 5 digit or zip+4 notation for the address
)
RETURN sdo_geometry IS
l_geocoder_url varchar2(100) := 'http://rpc.geocoder.us/service/csv?address='; --URL to the csv geocoder.us interface
l_returned_address varchar2(2000); --String of the returned URL from geocoder.us
l_address_not_found varchar2(100) := 'couldn''t find this address! sorry'; --The error string that is returned
-- if an address is not found
l_not_found_position integer; -- Position of not found string
l_latitude varchar2(50);
l_longitude varchar2(50);
l_geo_location sdo_geometry; --Geographical location
l_srid number := '8265'; --The SRID is the Oracle Spatial Projection code for NAD83,
-- the projection that all US census data is in
BEGIN
--Make a call to the csv webservice
l_returned_address := utl_http.request( l_geocoder_url || urlencode( street || ',' || city || ', ' || state || ' ' || zipcode) );
--Check the return string to see if the address was found
l_not_found_position := instr(l_returned_address, l_address_not_found, 1, 1);
--If we find the address not found string we raise a NO_DATA_FOUND exception
if ( l_not_found_position > 0 ) then
RAISE NO_DATA_FOUND;
else
--The data returned is a comma separated list
--The first element returned in the string is the latitude of the address, so we substring out the element
l_latitude := substr(l_returned_address, 0, instr(l_returned_address, ',', 1, 1) - 1);
--The second element returned in the string is the longitude of the address, so we substring out the element
l_longitude := substr(l_returned_address, instr(l_returned_address, ',', 1, 1) + 1, instr(l_returned_address, ',', 1, 1));
--We not create the point location for the address we have geocoded
l_geo_location := sdo_geometry (2001, l_srid, sdo_point_type (l_longitude, l_latitude, null), null, null);
RETURN l_geo_location;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE;
WHEN OTHERS THEN
RAISE;
END geocode_address_geocoder_us;
/
The code uses a function from an askTom article to urlencode the query string sent to the geocoder.us webservice. The function is available here: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10444643777538. This function is included inside of HTMLDB and Oracle Portal, but I can’t assume your using those products so there you have it. The geocoder.us service will attempt to make changes to the address if it doesn’t follow the address listed in the census data. I will warn that this function does not take into account any of those changes to the address that the geocoder returns. I can write a procedure, if need be, that takes that into account if anyone needs one.
Tomorrow night, I’m going to do some analysis to compare how close the geocoder.us data matches the free geocoder data available from Navteq for San Francisco.
