GPS坐标系转高德地图坐标系——数据库函数+存储过程实现

首先：认识坐标系  ，只做简单概述   就是这个坐标系统在中国需要变成国内的格式   使用一个存储过程+两个函数来实现 

WGS84坐标系：即地球坐标系，国际上通用的坐标系。设备一般包含GPS芯片或者北斗芯片获取的经纬度为WGS84地理坐标系。

GCJ-02坐标系：它是一种对经纬度数据的加密算法，即加入随机的偏差。国内出版的各种地图系统（包括电子形式），必须至少采用GCJ-02对地理位置进行首次加密。

来吧展示  ：GPS设备获取的经纬度坐标值是（WGS84坐标系） 国内的地图采用GPS坐标+加密算法=国内的坐标系 （GCJ-02坐标系统）高度地图即

参考：原文是C# 版本的 https://blog.csdn.net/m0_38004177/article/details/93974126

 

USE [xxx_Data]
GO
/****** Object:  UserDefinedFunction [dbo].[f_transformLon]    Script Date: 2021/1/26 9:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date, ,>
-- Description:    <经度转换, ,>
-- =============================================
ALTER FUNCTION [dbo].[f_transformLon]
(
    @x FLOAT,
    @y FLOAT
)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ret FLOAT;

    DECLARE @pi FLOAT = 3.14159265358979324;
    DECLARE @a FLOAT = 6378245.0;
    DECLARE @ee FLOAT = 0.00669342162296594323;
	    SET @ret = 300.0 + @x + 2.0 * @y + 0.1 * @x * @x + 0.1 * @x * @y + 0.1 * Sqrt(Abs(@x));
             SET @ret += (20.0 * Sin(6.0 * @x * @pi) + 20.0 * Sin(2.0 * @x * @pi)) * 2.0 / 3.0;
             SET @ret += (20.0 * Sin(@x * @pi) + 40.0 * Sin(@x / 3.0 * @pi)) * 2.0 / 3.0;
             SET @ret += (150.0 * Sin(@x / 12.0 * @pi) + 300.0 * Sin(@x / 30.0 * @pi)) * 2.0 / 3.0;



    RETURN @ret;

END;

USE [xxx_Data]
GO
/****** Object:  UserDefinedFunction [dbo].[f_transformLat]    Script Date: 2021/1/26 9:30:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date, ,>
-- Description:    <纬度转换, ,>
-- =============================================
ALTER FUNCTION [dbo].[f_transformLat]
(
    @x FLOAT,
    @y FLOAT
)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ret FLOAT;

    DECLARE @pi FLOAT = 3.14159265358979324;
    DECLARE @a FLOAT = 6378245.0;
    DECLARE @ee FLOAT = 0.00669342162296594323;

    SET @ret = -100.0 + 2.0 * @x + 3.0 * @y + 0.2 * @y * @y + 0.1 * @x * @y + 0.2 * SQRT(ABS(@x));
    SET @ret += (20.0 * SIN(6.0 * @x * @pi) + 20.0 * SIN(2.0 * @x * @pi)) * 2.0 / 3.0;
    SET @ret += (20.0 * SIN(@y * @pi) + 40.0 * SIN(@y / 3.0 * @pi)) * 2.0 / 3.0;
    SET @ret += (160.0 * SIN(@y / 12.0 * @pi) + 320 * SIN(@y * @pi / 30.0)) * 2.0 / 3.0;

    RETURN @ret;

END;

USE [xxx_Data];
GO
/****** Object:  StoredProcedure [dbo].[GPSTransToAMap]    Script Date: 2021/1/26 9:31:02 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:		<Zbguan>
-- Create date: <2021年1月23日22:31:49 >
-- Description:	<GPS坐标转Amap>
-- =============================================
ALTER PROCEDURE [dbo].[GPSTransToAMap]
    -- Add the parameters for the stored procedure here
    @wgLon FLOAT,
    @wgLat FLOAT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    ---判断是否在中国境内 国外无需转换

    DECLARE @isChina INT;

    IF (@wgLon < 72.004 OR @wgLon > 137.8347)
    BEGIN
        SET @isChina = 1;
    END;
    ELSE
    BEGIN
        SET @isChina = 0;
        SELECT @wgLon,
            @wgLat;
    END;
    IF (@wgLat < 0.8293 OR @wgLat > 55.8271)
    BEGIN
        SET @isChina = 1;
    END;
    ELSE
    BEGIN
        SET @isChina = 0;
        SELECT @wgLon,
            @wgLat;
    END;
    -------------------------------------

    DECLARE @pi FLOAT = 3.14159265358979324;
    DECLARE @a FLOAT = 6378245.0;
    DECLARE @ee FLOAT = 0.00669342162296594323;


    DECLARE @dLat FLOAT = dbo.f_transformLat(@wgLon - 105.0, @wgLat - 35.0);
    DECLARE @dLon FLOAT = dbo.f_transformLon(@wgLon - 105.0, @wgLat - 35.0);
    DECLARE @radLat FLOAT = @wgLat / 180.0 * @pi;
    DECLARE @magic FLOAT = SIN(@radLat);
    SET @magic = 1 - @ee * @magic * @magic;
    DECLARE @sqrtMagic FLOAT = SQRT(@magic);
    SET @dLat = (@dLat * 180.0) / ((@a * (1 - @ee)) / (@magic * @sqrtMagic) * @pi);
    SET @dLon = (@dLon * 180.0) / (@a / @sqrtMagic * COS(@radLat) * @pi);
    SELECT @wgLon + @dLon,
        @wgLat + @dLat;

END;