SQL Server Geography型


副題

– 時速250Kmを超える台風! –

SQL Server にはGeometry型のほかにGeography型が存在する。
地理座標系、緯度経度、地心からの角度で扱われる座標値を扱うことができる。

GPS情報格納なんかには便利か?
距離も楕円体考慮の値をメートル単位で出してくれる。

STDistance
http://technet.microsoft.com/ja-jp/library/bb933808.aspx

IBtrACS
http://www.ncdc.noaa.gov/oa/ibtracs/index.php?name=ibtracs-data-access

NOAAの台風データで試してみよう。

データを格納して、[Serial_Num] = 台風の一意IDでグループ化して、時間順に並べ差分をとったりすると
経過時間と、前の点からの距離が取れたりする。
例えば以下のようなクエリ

SELECT
 PRE_OBJECTID,
 OBJECTID,
 Serial_Num,
 ISO_Time,
 DATEDIFF(hour, PRE_ISO_TIME, ISO_Time) TSPAN,
 CONVERT(int, Shape.STDistance(PRE_SHAPE) / 1000) DISSTANCE , -- m c18658? Km 小数点省略
 CONVERT(int, Shape.STDistance(PRE_SHAPE) / 1000) / DATEDIFF(hour, PRE_ISO_TIME, ISO_Time) kmph -- Km / hour
FROM (
SELECT
 LAG(OBJECTID, 1) OVER (PARTITION BY Serial_Num ORDER BY ISO_time) PRE_OBJECTID,
 OBJECTID,
 Serial_Num,
 CONVERT(datetime2, ISO_time ) AS ISO_TIME,
 LAG( CONVERT(datetime2, ISO_time ), 1) OVER (PARTITION BY Serial_Num ORDER BY ISO_time) PRE_ISO_TIME,
 Shape,
 LAG(Shape, 1) OVER (PARTITION BY Serial_Num ORDER BY ISO_time) PRE_SHAPE
FROM
 NOAAIBTRACSV03R04
) ORG_TAB
WHERE
 NOT ORG_TAB.PRE_ISO_TIME IS NULL AND
 NOT ORG_TAB.PRE_SHAPE IS NULL
ORDER BY
 OBJECTID --Serial_Num,ISO_Time

ここで順列を時速にする…

ORDER BY
 kmph DESC


!?

台風はえーーー

実際のレコードは下記

OBJECTID * Serial_Num Season Name ISO_time year month day hour min LAT LON
109057 1952303N09149 1952 21W:AGNES:AGNESS 1952-11-07 12:00:00 1952 11 7 12 0 37.82 169.399994
109058 1952303N09149 1952 21W:AGNES:AGNESS 1952-11-07 18:00:00 1952 11 7 18 0 41 -173

DECLARE @P1 geography;
DECLARE @P2 geography;

SET @P1 = geography::STGeomFromText('POINT(169.39 37.82)', 4326);
SET @P2 = geography::STGeomFromText('POINT(-173 41)', 4326);

SELECT
 CONVERT(int, @P1.STDistance(@P2) / 1000 ) km,
 CONVERT(int, @P1.STDistance(@P2) / 1000 )/ 6 kmph -- データが6時1-27245?だったので
;

DECLARE @P3 geography;
DECLARE @P4 geography;

SET @P3 = geography::STGeomFromText('POINT(169.39 37.82)', 4326);
SET @P4 = geography::STGeomFromText('POINT(187 41)', 4326); --187度はないが

SELECT
 CONVERT(int, @P3.STDistance(@P4) / 1000 ) km,
 CONVERT(int, @P3.STDistance(@P4) / 1000 )/ 6 kmph
;

km kmph
1554 259
1554 259

kmph

それは良いとして技術的なお話をしておくと、SQL Server 2012からLAG関数が使え前のレコードと比較が容易になっている。
http://technet.microsoft.com/ja-jp/library/hh231256.aspx

移動距離とか測れるので喫茶店にいた営業をGPS情報とともに洗いだせるかも。

カテゴリー: 開発, 設計 タグ: パーマリンク