Gets the coordinates in the database to the specified latitude and longitude distance, ___ database

Oracle Stored Procedures

New Project Requirements:

Combined with Baidu map, specify a location on the map to obtain the location and information of the units stored in all databases in the 1km area around this location are marked on the map. So the first write a Oracle stored procedures, purely by groping to write.

The functions needed in the background are as follows:

First, the ARC function is computed

CREATE OR REPLACE FUNCTION RAD (d number) return number
is
PI number: =3.141592625;

Begin
return  d* pi/180.0;
End;

Then compute 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 (Power (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

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)
I S
v_muids VARCHAR (MB);
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 the 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 * M_maintenaceunit where Muid in (' | | v_muids| | ') ';

EXCEPTION when
   others THEN
       ROLLBACK;
    Dbms_output.put_line (SQLERRM);

End;

Because of the use of MyBatis, service calls are as follows:

    @Override public
    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 call 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 Oracle stored procedures are written, the code only implements functionality and needs to be improved. calculate the distance between two places based on latitude and longitude in Oracle

Calculate the distance between two places based on latitude and longitude in Oracle

function to get radians:

CREATE OR REPLACE FUNCTION radian (d number) return number
is
PI number: =3.141592625;

Begin
return  d* pi/180.0;
End;

Test this function:

Select Radian (360) from dual;

A function for calculating distances according to latitude and longitude:

CREATE OR REPLACE FUNCTION getdistance (lat1 number,
                                       lng1 number,
                                       lat2 number,
                                       lng2 number) return number is
  earth_padius number: = 6378.137;
  RADLAT1 number      : = Radian (LAT1);
  RADLAT2 number      : = Radian (LAT2);
  A number            : = RADLAT1-RADLAT2;
  b number            : = Radian (lng1)-Radian (lng2);
  S number            : = 0;
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 s;

End

example, calculate the distance according to the city name:

The latitude and longitude of Oriental Pearl Tower are: 121.506656,31.245087
The latitude and longitude of Lujiazui subway station are: 121.508883,31.243481

Sql> Select Getdistance (' 121.506656 ', ' 31.245087 ', ' 121.508883 ', ' 31.243481 ') from dual;

Output is 0.2649 km SQL Server calculates the distance between two longitude points

The algorithm used here and the Earth's radius and other data are from the network, this article is only to organize records.

The value of the Earth's radius is 6378137.0 meters from the equatorial radius, which is the 1980 International standard data.

The following is a custom function written in the database, passing in two longitude and latitude points of data, a total of 4 parameters, return the meter in the distance between two points

    CREATE FUNCTION [f_getdistance]  
    (   
    @GPSLng Decimal (12,6),  
    @GPSLat Decimal (12,6),  
    @Lng  Decimal ( 12,6),  
    @Lat Decimal (12,6)  
    )  
    RETURNS Decimal (12,4)  
    as  
    BEGIN  
       DECLARE @result Decimal (12,4  
       SELECT @result = 6378137.0*acos (SIN (@GPSLat/180*pi ()) *sin (@Lat/180*pi ()) +cos (@GPSLat/180*pi ()) *cos (@Lat/ 180*pi ()) *cos ((@GPSLng-@Lng)/180*pi ()) return  
       @result end  
    Go