Source: Internet
Author: User

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

Related Article