A tutorial on practical scripting methods for databases
This article introduces the relevant knowledge of "Database Practical Script Method Tutorial". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!
1. SQL Server Script
- -Calculates the distance between two coordinate points (longitude, latitude) on Earth sql function CREATE FUNCTION [dbo]. [fnGetDistance](@LatBegin REAL, @LngBegin REAL, @LatEnd REAL, @LngEnd REAL) RETURNS FLOAT AS BEGIN--DISTANCE (KM) DECLARE @Distance REAL DECLARE @EARTH_RADIUS REAL SET @EARTH_RADIUS = 6378.137 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 RETURN @Distance END --Use as follows: SELECT dbo.fnGetDistance(25,30,12.56,15.5) ;
2. MySQl script
- -Calculates the distance between two coordinate points (longitude, latitude) on Earth sql function CREATE FUNCTION [dbo]. [fnGetDistance](@LatBegin REAL, @LngBegin REAL, @LatEnd REAL, @LngEnd REAL) RETURNS FLOAT AS BEGIN--DISTANCE (KM) DECLARE @Distance REAL DECLARE @EARTH_RADIUS REAL SET @EARTH_RADIUS = 6378.137 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 RETURN @Distance END --Use as follows: SELECT dbo.fnGetDistance(25,30,12.56,15.5) ;
3. Orcale script
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; --use the method select GetDistance(25,30,12.56,15.5) from dual"Database Practical Script Method Tutorial" content introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!
