---
# System prepended metadata

title: SQL-GIS JsonGPS資料轉成線
tags: [SQL]

---

適用於WGS84經緯度 EPSG:4326版本
假設Json格式為C#端拋出之GPS軌跡
透過以下SQL可直接輸出為geography類型

後續地理應用參考前輩：https://ithelp.ithome.com.tw/articles/10244022

```
-- Declare the return variable here
	DECLARE @Result geography,@json nvarchar(max)
	set @json = N'
[{
    "Seq": 1,
    "WGS_LON": 121.820068,
    "WGS_LAT": 24.625728,
    "datetime": "2022/04/19 00:32:16",
    "Heading": 343,
    "Speed": 0,
    "Sat": 8,
    "IO1": "0"
}, {
    "Seq": 2,
    "WGS_LON": 121.820068,
    "WGS_LAT": 24.625728,
    "datetime": "2022/04/19 00:32:19",
    "Heading": 343,
    "Speed": 0,
    "Sat": 0,
    "IO1": "0"
}, {
    "Seq": 3,
    "WGS_LON": 121.820166,
    "WGS_LAT": 24.625882,
    "datetime": "2022/04/19 00:32:49",
    "Heading": 343,
    "Speed": 0,
    "Sat": 7,
    "IO1": "0"
}, {
    "Seq": 4,
    "WGS_LON": 121.820231,
    "WGS_LAT": 24.625833,
    "datetime": "2022/04/19 00:33:19",
    "Heading": 343,
    "Speed": 0,
    "Sat": 7,
    "IO1": "0"
}, {
    "Seq": 5,
    "WGS_LON": 121.820247,
    "WGS_LAT": 24.625780,
    "datetime": "2022/04/19 00:33:49",
    "Heading": 343,
    "Speed": 0,
    "Sat": 7,
    "IO1": "0"
}, {
    "Seq": 6,
    "WGS_LON": 121.820215,
    "WGS_LAT": 24.625813,
    "datetime": "2022/04/19 00:34:19",
    "Heading": 343,
    "Speed": 0,
    "Sat": 7,
    "IO1": "0"
}, {
    "Seq": 7,
    "WGS_LON": 121.820231,
    "WGS_LAT": 24.625825,
    "datetime": "2022/04/19 00:34:49",
    "Heading": 343,
    "Speed": 0,
    "Sat": 7,
    "IO1": "0"
}, {
    "Seq": 8,
    "WGS_LON": 121.820247,
    "WGS_LAT": 24.625793,
    "datetime": "2022/04/19 00:35:19",
    "Heading": 343,
    "Speed": 0,
    "Sat": 6,
    "IO1": "0"
}, {
    "Seq": 9,
    "WGS_LON": 121.820280,
    "WGS_LAT": 24.625805,
    "datetime": "2022/04/19 00:35:49",
    "Heading": 343,
    "Speed": 0,
    "Sat": 8,
    "IO1": "0"
}]
'
	-- Add the T-SQL statements to compute the return value here
declare @gps table(
	Sno int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    WGS_LON float,
    WGS_LAT float,
    Heading INT,
	Speed decimal,
    Sat int,
	IO1 int,
	[datetime] datetime
	--,plate_no varchar(255)
	,TM97X float
	,TM97Y float
)

insert into @gps
SELECT WGS_LON,WGS_LAT,Heading,Speed,Sat,IO1,datetime,TM97X,TM97Y
FROM OPENJSON(@json)
  WITH (
    WGS_LON NVARCHAR(50),
    WGS_LAT NVARCHAR(50) ,
    Heading INT,
	Speed decimal,
    Sat int,
	IO1 int,
	[datetime] datetime
		,TM97X float
	,TM97Y float
  )order by DateTime

  --起訖時間
declare @firstTime datetime,@lastTime datetime;
 select @firstTime = Min(datetime)
 ,@lastTime = Max(datetime) from @gps

 --轉成LineString
 declare @string varchar(max)
 Select @string = isnull(@string + ',', '') + cast(CONVERT(DECIMAL(30, 15), WGS_LON) as varchar(200)) + ' ' + cast(CONVERT(DECIMAL(30, 15), WGS_LAT) as varchar(200))
 from @gps
 Set @string = 'LINESTRING(' + @string + ')';   
 declare @LineGeo geography = geography::STLineFromText(@string, 4326).MakeValid()


SELECT @LineGeo
```