Google Maps: radius search in Oracle

Written on 21 April 2011, 12:11pm

Tagged with: , ,

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:
ITEM_ID
COORDX --latitude
COORDY --longitude

The latitude and longitude are already calculated, and stored in database as VARCHAR.
The task was to create some sort of function to accept:
-latitude
-longitude
-radius
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.

Leave a response