Oracle stored Procedure---Gets the coordinates of the distance from the database to the specified latitude and longitude

New Project Requirements:

Combine Baidu map, specify a location in the map, get the location and information of the units stored in all databases around 1km in this location on the map. So I wrote a piece of Oracle's stored procedure, which was written purely by groping.

The functions required in the background are as follows:

First, calculate the ARC function

CREATE OR REPLACE FUNCTION RAD (d number) RETURN Numberispi number: =3.141592625;beginreturn 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 := 0;begin  s := 2 *        asin (Sqrt (Sin (a / 2),  2)  +                  cos (RADLAT1)  * cos (RADLAT2)  *  Power (sin (b / 2),  2));   s := s * earth_padius;  s  := round (s * 10000)  / 10000;  return s;end;

The

Next is the stored procedure 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 (9,6); begin  for l_record in  (Select * from m_maintenaceunit) LOOP     select getdistance (L_record. Lat,l_record. LONGITUDE,CENTER_LAT,CENTER_LNG)  into v_distance from dual;    /*dbms_ Output.put_line (' Distance: '  | |  v_distance);*/    if  (v_distance <=1)        then        v_muids:= v_muids | |  l_record.muid | |   ', ';     end if; end loop;    v_muids:= v_muids | |   '-1 ';         dbms_output.put_line (v_muids);        open p_cur for  ' Select * from m_maintenaceunit  where muid in  (' | | v_muids| | ') '; exception   when others then       rollback;     dbms_output.put_line (SQLERRM); END;

Because of the use of MyBatis, the service invocation is as follows:

@Overridepublic list<maintunitdto> getmaitunitbyprocedures (double lat, double LNG) {map<string,object> Map=new hashmap<string, object> () map.put ("LNG", LNG), Map.put ("lat", lat); try {Geodao.getmaitunitbyprocedures (map);} catch (Uncategorizedsqlexception e) {//system.out.println (e); E.printstacktrace ();} @SuppressWarnings ("unchecked") list<maintunitdto> sitelist= (list<maintunitdto>) map.get ("Maintunits") ; return sitelist;}

This is related to the article I reproduced earlier about Oracle stored procedures (how to invoke a stored procedure that returns a collection)

Calls in Mapper.xml:

<select id= "Getmaitunitbyprocedures" statementtype= "callable" parametertype= "Java.util.Map" ><! [cdata[{call Distance_maintunit (#{maintunits,jdbctype=cursor,mode=out,resultmap=maintunit_map,javatype= Resultset},#{lat,jdbctype=double,mode=in},#{lng,jdbctype=double,mode=in})}]]> </select><resultMap Type= "Com.cseds.geo.dto.MaintunitDto" id= "Maintunit_map" ></resultMap>

Called in DAO:

Public list<maintunitdto> getmaintunitlist (@Param ("LNG") double LNG, @Param ("lat") double lat);

Because the first time the Oracle stored procedure was written, the code was only implemented with functionality that needed 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

This article is from the "Java Program Ape Blog" blog, be sure to keep this source http://chengxuyuan.blog.51cto.com/5789198/1787169

Oracle stored Procedure---Gets the coordinates of the distance from the database to the specified latitude and longitude