Oracle stored procedure-gets the coordinates in the database to the specified longitude and latitude distance

New project requirements:

Combined with Baidu map, specify a location in the map, and get the location and information of all the units stored in the database around this location marked on the map. So I first wrote a stored procedure of oracle, which was written purely by groping.

The functions required by the backend are as follows:

First of all, calculate the arc function

CREATE OR REPLACE FUNCTION RAD (d number) RETURN NUMBERisPI number: = 3.141592625 begin return d * PI/180.0;end

Then, calculate the distance function

CREATE OR REPLACE FUNCTION GetDistance (lat1 number, lng1 number, lat2 number, lng2 number) RETURN NUMBER is earth_padius number: = 6378.137; radLat1 number: = rad (lat1); radLat2 number: = rad (lat2); a number: = radLat1-radLat2 B number: = rad (lng1)-rad (lng2); s number: = 0politics begin s: = 2 * Asin (Sqrt (power (sin (a / 2), 2) + cos (radLat1) * cos (radLat2) * power (sin (b / 2), 2); s: = s * earth_padius; s: = Round (s * 10000) / 10000; return sterend

Next is the stored procedure that I wrote myself:

CREATE OR REPLACE PROCEDURE distance_maintunit (p_cur out sys_refcursor, center_lat in number, center_lng in number) ISv_muids VARCHAR; v_distance NUMERIC (9Power6); BEGIN FOR L_RECORD IN (select * from M_MAINTENACEUNIT) LOOP SELECT GetDistance (Lindsay RECORD.LongitudeLNG) INTO v_distance FROM dual; / * dbms_output.put_line ('distance:' | | v_distance); * / IF (v_distance)

Call in dao:

Public List getMaintUnitList (@ Param ("lng") Double lng, @ Param ("lat") Double lat)

Since the oracle stored procedure is written for the first time, the code only implements the function and needs to be improved.

Reference: http://www.storyday.com/wp-content/uploads/2008/09/latlung_dis.html

Http://blog.csdn.net/iw1210/article/details/9164573

Http://www.cnblogs.com/microsoft-jiang/archive/2008/07/24/1250644.html