Google Maps: radius search in Oracle
Written on 21 April 2011, 12:11pm
Tagged with: coldfusion, google maps, oracle
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.
Written by Dorin Moise (Published articles: 259)
- Likes (0)
-
Share
- Comments (0)