SQL Server scalar function calculates the distance between two latitude and longitude

 

Scalar functions:

View code

 Create Function DBO. fn_getdistance (@ lngbegin real, @ latbegin real, @ lngend real, @ latend real) returns float as begin declare @ distance real declare @ earth_radius real set @ earth_radius = 6378.137 -- The earth's radius is already divided by 1000, so the following calculated values are kilometers and kilometers declare @ radlatbegin real, @ radlatend real, @ radlatdiff real, @ radlngdiff real set @ radlatbegin. = @ Latbegin * Pi ()/ 180.0  Set @ radlatend = @ Latend * Pi ()/ 180.0 Set @ radlatdiff = @ Radlatbegin- @ Radlatend set @ radlngdiff = @ Lngbegin * Pi ()/ 180.0 -@ Lngend * Pi ()/ 180.0  Set @ distance = 2 * Asin (SQRT (Power (sin (@ radlatdiff/ 2 ), 2 ) + Cos (@ radlatbegin) * Cos (@ radlatend) * power (sin (@ radlngdiff/ 2 ), 2  ) Set @ distance = @ Distance *@ Earth_radius -- Set @ distance = round (@ distance * 10000 )/ 10000  Return @ distance end 

Call method: Before the method name, you must addDBO.

-- Parameter 1: Start longitude parameter 2: Start latitude parameter 3: end point longitude parameter 4: end point latitude

Select DBO. fn_getdistance (115.9856, 36.4507, 115.75, 36.12)
Select DBO. fn_getdistance ('192. 100', '36. 100', '192. 75', '36. 12 ')

The output result is as follows: for example, the Unit is kilometers or kilometers.

 

DBO is the default user of each database and has the owner permission, that is, dbowner.

By using DBO as the owner to define the object, any user in the database can reference it without providing the owner name.
For example, if you log on to user1 and create a table without specifying DBO,
When user2 is logged in and wants to access the table, you must know that this table was created by user1. write it into user1.table. If you do not know it, there will be access problems.
If you direct the owner to DBO when creating a table, you can write DBO. Table when other users come in. You don't have to know user1.
This is not only the case for tables, but also for views and other database objects.

When you create database objects such as tables, stored procedures, and views, the owner of these objects is the user who creates them. It is very troublesome to add a prefix when other login users need to reference these items. And,ProgramTherefore, it is easy to make mistakes. You can check whether the problem exists. This is a waste of time.

The implementation method of C # is the same as the above, which is collected on the network. The two methods work the same way, because the results are the same.

View code

 ///   <Summary>      ///  Calculate the distance between any two points on the earth  ///  </Summary>      ///   <Param name = "long1"> </param>      ///   <Param name = "LAT1"> </param>      ///   <Param name = "long2"> </param>      ///   <Param name = "LAT2"> </param>      ///   <Returns>  The returned length is in meters.  </Returns>      Private  Static   Double Distance ( Double Long1, Double LAT1, Double Long2, Double  LAT2 ){  Double  A, B, R; R = 6378137 ; //  Earth radius LAT1 = LAT1 * Math. PI/ 180.0  ; LAT2 = LAT2 * Math. PI/ 180.0  ; = LAT1- LAT2; B = (Long1-long2) * Math. PI/ 180.0  ;  Double  D;  Double  SA2, sb2; SA2 = Math. Sin (/ 2.0  ); Sb2 = Math. Sin (B/ 2.0  ); D =2 * R * Math. asin (math. SQRT (SA2 * SA2 + math. Cos (LAT1) * Math. Cos (LAT2) * sb2 * Sb2 ));  Return  D ;} 

 

C # method 2

Code in Google Maps scripts

     /*  ** In the Google Maps scriptCode  */      Private   Const   Double Earth_radius = 6378.137 ;  Private   Static   Double Rad ( Double  D ){  Return D * Math. PI/ 180.0  ;}  /*  ** Calculate the distance between two points, in meters, based on the longitude and latitude coordinates (double value) between two points  */      Public   Static   Double Getdistance (Double LAT1, Double Lng1, Double LAT2, Double  Lng2 ){  Double Radlat1 = Rad (LAT1 );  Double Radlat2 = Rad (LAT2 );  Double A = radlat1- Radlat2;  Double B = rad (lng1 )- Rad (lng2 ); Double S = 2 * Math. asin (math. SQRT (math. Pow (math. Sin (/ 2 ), 2 ) + Math. Cos (radlat1) * Math. Cos (radlat2) * Math. Pow (math. Sin (B/ 2 ), 2  ); S = S * Earth_radius; s = Math. Round (S * 10000 )/ 10000  ; Return  S ;} 

Call method:

Double Dis = distance (115.9856, 36.4507, 115.75, 36.11 );
Double dis1 = getdistance (36.4507, 115.9856, 36.11, 115.75 );
Response. Write ("the distance between two points:" + DIS + "---" + dis1 );