Google Maps: radius search in Oracle
Written on 21 April 2011, 12:11pm
Recently I had to implement a ColdFusion + Oracle application using Google Maps. One of the requirements was search by distance (or radius search) for some items, already geocoded.
The items are stored in
T_ITEM, with the columns:
The latitude and longitude are already calculated, and stored in database as VARCHAR.
The task was to create some sort of function to accept:
and to return the items from
T_ITEM located within
radius kilometers from the given coordinates.
In implementing this task, I started with the excellent Google tutorial about Creating a Store Locator. The only difference was – I was using Oracle instead of MySQL.
This imposed two problems:
1. ORACLE does not have the RADIANS function
So I had to use a user defined one:
CREATE OR REPLACE FUNCTION degree2radian(pin_Degree IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN pin_Degree / 57.2957795; --1R = 180C END degree2radian; /
2. Using an alias in a where clause
If in MySQL, a query like:
SELECT id, name as alias FROM t_foo HAVING alias = 'bar'
is perfectly valid, in Oracle, the same query returns the error: ORA-00904 Invalid Identifier.
After doing some research, the solution was to use nested SELECT statements. So I ended up with this query:
select item_id, coordx, coordy, distance from ( SELECT item_id, coordx, coordy, ( 6371 * acos( cos( degree2radian(47) ) * cos( degree2radian( coordx ) ) * cos( degree2radian( coordy ) - degree2radian(9) ) + sin( degree2radian(47) ) * sin( degree2radian( coordx ) ) ) ) as distance FROM t_item ) where distance < 100
(where 47,9 is a coordinate in Center-Europe, 6371km is the average Earth radius and 100km is the radius search).
Where to go from here
Maybe draw a circle on your Google Maps? For Google Maps v2 API – you can use this solution.
Written by Dorin Moise (Published articles: 233)