首先:认识坐标系 ,只做简单概述 就是这个坐标系统在中国需要变成国内的格式 使用一个存储过程+两个函数来实现
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;